<?php 
$from;
$to;
function cut_site($site){
$patterns[0] = "/http:\/\//";
$patterns[1] = "/https:\/\//";
$patterns[2] = "/www./";
return preg_replace($patterns, "", $site);
}

function set_range($range){
global $from;
global $to;
switch($range){
    case 'Today':
    $from = date("Y-m-d",mktime(0, 0, 0, date("m")  , date("d"), date("Y")));
    $to =date("Y-m-d",mktime(0, 0, 0, date("m")  , date("d")+1, date("Y")));
    break;
    case 'Yesterday':
    $from = date("Y-m-d",mktime(0, 0, 0, date("m") , date("d")-1, date("Y")));
    $to =date("Y-m-d", mktime(0, 0, 0, date("m")  , date("d"), date("Y")));
    break;
    case 'This Week':
    $day_of_week = date("w");
    $end_of_week = 7 - date("w");
    $from = date("Y-m-d",mktime(0, 0, 0, date("m") , date("d")-$day_of_week, date("Y")));
    $to =date("Y-m-d", mktime(0, 0, 0, date("m")  , date("d")+$end_of_week, date("Y")));
    break;
    case 'Last Week':
    $day_of_week = 7+date("w");
    $end_of_week = 7;
    $from = date("Y-m-d",mktime(0, 0, 0, date("m") , date("d")-$day_of_week, date("Y")));
    $to =date("Y-m-d", mktime(0, 0, 0, date("m")  , date("d")-$day_of_week+7, date("Y")));
    break;
    case 'This Month':
    $day_of_m = date("t");
    $from = date("Y-m-d",mktime(0, 0, 0, date("m") ,01 , date("Y")));
    $to =date("Y-m-d", mktime(0, 0, 0, date("m")  , $day_of_m, date("Y")));
    break;
    case 'Last Month':
    $day_of_m = date("t", mktime(0, 0, 0, date("m")-1,date("d"), date("Y")));
    $from = date("Y-m-d",mktime(0, 0, 0, date("m")-1 ,01 , date("Y")));
    $to =date("Y-m-d", mktime(0, 0, 0, date("m")-1 , $day_of_m, date("Y")));
    break;
    case 'This Year':
    $from = date("Y-m-d",mktime(0, 0, 0, 01 ,01 , date("Y")));
    $to =date("Y-m-d", mktime(0, 0, 0, 12 ,31, date("Y")));
    break;
    case 'Last Year':
    $from = date("Y-m-d",mktime(0, 0, 0, 01 ,01 , date("Y")-1));
    $to =date("Y-m-d", mktime(0, 0, 0, 12 , 31, date("Y")-1));
    break;
    }
}

function update_user($login,$u_name,$email,$teamid,$roleid,$descr){
global $db_conn;

$sql = "select \"WhiteListing\".updt_user('$login','$u_name','$email',$roleid,$teamid ,'$descr')";
$usr_updt = odbc_exec($db_conn,$sql);
    if ($usr_updt) return "User info has been changed";
              else  return "Changes not save";
}

function update_team($team,$descr){
global $db_conn;

$sql = "select \"WhiteListing\".updt_team('$team','$descr')";
$usr_updt = odbc_exec($db_conn,$sql);
    if ($usr_updt) return "Team info has been changed";
              else  return "Changes not save";
}

function update_role($role,$descr){
global $db_conn;

$sql = "select \"WhiteListing\".updt_role('$role','$descr')";
$usr_updt = odbc_exec($db_conn,$sql);
    if ($usr_updt) return "Role info has been changed";
              else  return "Changes not save";
}

function del_user($login){
global $db_conn;

$sql = "select \"WhiteListing\".del_user('$login')";
$usr_updt = odbc_exec($db_conn,$sql);
    if ($usr_updt) return "User has been deleted";
              else  return "User has not been deleted";

}

function del_team($team){
global $db_conn;

$sql = "select \"WhiteListing\".del_team('$team')";
$usr_updt = odbc_exec($db_conn,$sql);
    if ($usr_updt) return "Team $team has been deleted";
              else  return "Team $team  has not been deleted";
}

function del_role($role){
global $db_conn;

$sql = "select \"WhiteListing\".del_role('$role')";
$usr_updt = odbc_exec($db_conn,$sql);
    if ($usr_updt) return "Role $role has been deleted";
              else  return "Role $role has not been deleted";
}

function update_user_info_pers($login,$u_name,$email){
global $db_conn;

$sql = "select \"WhiteListing\".update_user_info_pers('$login','$u_name','$email')";
$usr_updt = odbc_exec($db_conn,$sql);
    if ($usr_updt) return "Personal info has been changed";
              else  return "Changes not save";
}

function update_pass($login,$pass){
global $db_conn;

        $md_pass = md5($pass.$login);
        $sql = "select \"WhiteListing\".update_pass('$login', '$md_pass')";
                
        $ins = odbc_exec($db_conn,$sql);
        if ($ins) return "Current password been updating ";
              else  return "Current password not been updating";
        // else  return $sql;
 }


function add_new_user($login,$name,$email,$pass,$team,$role,$descr){
global $db_conn;

$md_pass = md5($pass.$login);
$sql = "select  \"WhiteListing\".add_newuser( '$login','$name', '$md_pass','$email', $role,$team ,'$descr')";
            
$ins = odbc_exec($db_conn,$sql);
  if ($ins) return "User $name - been added ";
// else  return "User $name -not  been added ";
else return $sql;
}


function add_new_team($t_name,$t_descr){
global $db_conn;
$sql = "select \"WhiteListing\".add_team('$t_name', '$t_descr')";
        
$ins = odbc_exec($db_conn,$sql);
  if ($ins) return "New teams ' $t_name ' - been created ";
  else  return "New teams ' $t_name ' - been not created ";
                       //      else  return $sql;
}

function add_new_role($r_name,$r_descr){
global $db_conn;

$sql = "select \"WhiteListing\".add_role('$r_name', '$r_descr')";
$ins = odbc_exec($db_conn,$sql);
     if ($ins) return "New role  '$r_name' - been created ";
     else  return "New role  '$r_name' - been not created  ";
}
                       
function next_from_same_source($site,$fileid){
global $db_conn;

$login = $_SESSION['login'];

$usrquery = odbc_exec($db_conn,"select \"UserId\" from \"WhiteListing\".\"Users\" where \"Login\" ='$login'");
$usrid = odbc_result($usrquery, "UserId");

$sql = "Select \"WhiteListing\".update_filesource($site,$fileid,$usrid)";
$tmp = odbc_exec($db_conn,$sql);
return 1;
}
                                                      
function give_verdict($site,$fileid,$verd,$prior){
global $db_conn;
$login = $_SESSION['login'];

$usrquery = odbc_exec($db_conn,"select \"UserId\" from \"WhiteListing\".\"Users\" where \"Login\" ='$login'");
$usrid = odbc_result($usrquery, "UserId");
$site_rep = cut_site($site);
$sql = "Select \"WhiteListing\".give_verdict($usrid, $fileid, $verd, '$site_rep',$prior)";
$tmp = odbc_exec($db_conn,$sql);
return $sql;
}

function change_verdict($site,$verd){
global $db_conn;
$login = $_SESSION['login'];

$usrquery = odbc_exec($db_conn,"select \"UserId\" from \"WhiteListing\".\"Users\" where \"Login\" ='$login'");
$usrid = odbc_result($usrquery, "UserId");
$site_rep = cut_site($site);
$sql = "Select \"WhiteListing\".CHANGE_verdict($usrid, $verd, '$site_rep')";
$tmp = odbc_exec($db_conn,$sql);
return $sql;
}

function change_labstate($site,$lab_state){
global $db_conn;
$login = $_SESSION['login'];

$usrquery = odbc_exec($db_conn,"select \"UserId\" from \"WhiteListing\".\"Users\" where \"Login\" ='$login'");
$usrid = odbc_result($usrquery, "UserId");
$site_rep = cut_site($site);
$sql = "Select \"WhiteListing\".\"Update_labstatus\"($lab_state, '$site', $usrid)";
$tmp = odbc_exec($db_conn,$sql);
return $sql;
}


function give_verdict_first($site,$fileid,$verd,$prior){
global $db_conn;
$login = $_SESSION['login'];

$usrquery = odbc_exec($db_conn,"select \"UserId\" from \"WhiteListing\".\"Users\" where \"Login\" ='$login'");
$usrid = odbc_result($usrquery, "UserId");

$site_rep = cut_site($site);
$sql = "Select \"WhiteListing\".give_verdict_first($usrid, $fileid, $verd, '$site_rep',$prior)";
$tmp = odbc_exec($db_conn,$sql);
return 1;
}


function add_site($site,$verd,$prior){
global $db_conn;
$login = $_SESSION['login'];

$usrquery = odbc_exec($db_conn,"select \"UserId\" from \"WhiteListing\".\"Users\" where \"Login\" ='$login'");
$usrid = odbc_result($usrquery, "UserId");

$site_rep = cut_site($site);
$sql = "Select \"WhiteListing\".add_sate($usrid, $verd, '$site_rep',$prior)";
$tmp = odbc_exec($db_conn,$sql);
return $sql;
}

function reports(){
global $db_conn;
$sql = 'select * from "WhiteListing".get_rep_per_sites() as ("Report name" character varying(250),"Result" integer) ';
$cnt = odbc_exec($db_conn, $sql);
$text= "
<style type=\"text/css\">
th {padding-top:3px;padding-bottom:3px;
    text-align:center;color:#000;
    background-color:#DCDCDC;font-size:1.1em;}
//td { background-color:#FFDAB9;font-size:1.0em;}
td { background-color:#FFFAF0;font-size:1.0em;}
</style>
 <div id='page_report_per_Site' name='page_reports' style='margin-left:1%;margin-top:5%;margin-right:1%'>
 
         <table cellpadding=4 cellspacing=4 width=95% class='idtbl' border=0>
                 <tr>
                 <th>Report Name</th>
                 <th>Results</th>
                 </tr>";
        while(odbc_fetch_row($cnt)){
        $text .="<tr><td align=left>".odbc_result($cnt,"Report name")."</td><td align=center>".odbc_result($cnt,"Result")."</td></tr>";
        }         
$text .= "</table></div>";
return $text;
}

function rep_priority(){
global $db_conn;
$sql = 'select * from "WhiteListing".get_rep_per_priority() as ("Report name" character varying(250),"Result" integer) ';
$cnt = odbc_exec($db_conn, $sql);
$text= "
<style type=\"text/css\">
th {padding-top:3px;padding-bottom:3px;
    text-align:center;color:#000;
    background-color:#DCDCDC;font-size:1.1em;}
//td { background-color:#FFDAB9;font-size:1.0em;}
td { background-color:#FFFAF0;font-size:1.0em;}
</style>
 <div id='page_report_per_Site' name='page_reports' style='margin-left:1%;margin-top:5%;margin-right:1%'>
 
         <table cellpadding=4 cellspacing=4 width=95% class='idtbl' border=0>
                 <tr>
                 <th>Report Name</th>
                 <th>Results</th>
                 </tr>";
        while(odbc_fetch_row($cnt)){
        $text .="<tr><td align=left>".odbc_result($cnt,"Report name")."</td><td align=center>".odbc_result($cnt,"Result")."</td></tr>";
        }         
$text .= "</table>";
return $text;
}

function rep_personal($range){
global $db_conn;
$login = $_SESSION['login'];
set_range($range);
global $from;
global $to;

$sql = 'select * from "WhiteListing".get_personal_rep(\''.$from.'\',\''.$to.'\') as(
						    "Analist" character varying,
						    "Sha1_SiteNotFound" integer,
						    "Sha1_Undecided" integer,
						    "Sha1_Safe" integer,"Sha1_Reffered" integer,
						    "Sha1_SiteInActive" integer,"AVlab" integer)';
$cnt = odbc_exec($db_conn, $sql);
//$sql1 = "SELECT \"UserId\", \"Login\", \"UserName\", \"Email\", \"RoleId\", \"TeamId\",\"Coments\" FROM \"WhiteListing\".\"Users\" order by \"UserId\"";
//$usr1 = odbc_exec($db_conn,$sql1);

$text= "
<style type=\"text/css\">
th {padding-top:3px;padding-bottom:3px;
    text-align:center;color:#000;
    background-color:#DCDCDC;font-size:1.1em;}
//td { background-color:#FFDAB9;font-size:1.0em;}
td { background-color:#FFFAF0;font-size:1.0em;}
</style>
 <div id='page_report_per_Site' name='page_reports' style='margin-left:1%;margin-top:5%;margin-right:1%'>";

$text .="         <table cellpadding=4 cellspacing=4 width=95% class='idtbl' border=0>
                 <tr>
                 <th>Analist Name</th>
                 <th>Sha1 he marked as SiteNotFound </th>
                 <th>Sha1 he marked as Undecided</th>
                 <th>Sha1 he marked as Safe</th>                 
                 <th>Sha1 he marked as Reffered</th>                                  
                 <th>Sha1 he marked as SiteInActive</th>                                                   
                 <th>AVLab analyst marked from Referred to Lab-Completed-Safe or Lab-Completed-Malware</th>                                                   
                 </tr>";
$snf=0;
$undec=0;
$safe=0;
$reff=0;
$inactive=0;
$avlab=0;
        while(odbc_fetch_row($cnt)){
	$snf +=odbc_result($cnt,"Sha1_SiteNotFound");
	$undec +=odbc_result($cnt,"Sha1_Undecided");
	$safe +=odbc_result($cnt,"Sha1_Safe");
	$reff +=odbc_result($cnt,"Sha1_Reffered");
	$inactive +=odbc_result($cnt,"Sha1_SiteInActive");
	$avlab +=odbc_result($cnt,"AVLab");
        $text .="<tr><td align=left>".odbc_result($cnt,"Analist")."</td>
        <td align=center>".odbc_result($cnt,"Sha1_SiteNotFound")."</td>
        <td align=center>".odbc_result($cnt,"Sha1_Undecided")."</td>        
        <td align=center>".odbc_result($cnt,"Sha1_Safe")."</td>        
        <td align=center>".odbc_result($cnt,"Sha1_Reffered")."</td>        
        <td align=center>".odbc_result($cnt,"Sha1_SiteInActive")."</td>
        <td align=center>".odbc_result($cnt,"AVLab")."</td>
        </tr>";
        }         
$text .= "
	<tr><th>Total</th>
	    <th>$snf</th>
            <th>$undec</th>
            <th>$safe</th>                 
            <th>$reff</th>                                  
            <th>$inactive</th>                                                   
            <th>$avlab</th>                                                   
	</tr>
</table>";
return $text;
//return $sql;
}

function rep_class_site($range){
global $db_conn;
set_range($range);
global $from;
global $to;
$sql = 'select
	    (SELECT count("SiteId") FROM "WhiteListing"."Sites" where "VerdictId" = 3 
		and "DateLastModified" between \''.$from.'\' and \''.$to.'\') as "Safe",
	    (SELECT count("SiteId") FROM "WhiteListing"."Sites" where "VerdictId" = 4
		and "DateLastModified" between \''.$from.'\' and \''.$to.'\') as "Malware",
	    (SELECT count("SiteId") FROM "WhiteListing"."Sites" where "VerdictId" = 1
		and "DateLastModified" between \''.$from.'\' and \''.$to.'\') as "Unknown",
	    count("SiteId") as "Referred" FROM "WhiteListing"."Sites" where "VerdictId" = 4
		and "DateLastModified" between \''.$from.'\' and \''.$to.'\'';
$cl_site = odbc_exec($db_conn,$sql);

$text= "
<style type=\"text/css\">
th {padding-top:3px;padding-bottom:3px;
    text-align:center;color:#000;
    background-color:#DCDCDC;font-size:1.1em;}
//td { background-color:#FFDAB9;font-size:1.0em;}
td { background-color:#FFFAF0;font-size:1.0em;}
</style>
 <table cellpadding=4 cellspacing=4 width=98% class='idtbl' border=0>
                 <tr>
                 <th>Safe</th>
                 <th>Malware</th>
                 <th>Unknown</th>                 
                 <th>Referred</th>
                 </tr>";
        while(odbc_fetch_row($cl_site)){
        $text .="<tr><td align=center>".odbc_result($cl_site,"Safe")."</td>
        <td align=center>".odbc_result($cl_site,"Malware")."</td>
        <td align=center>".odbc_result($cl_site,"Unknown")."</td>        
        <td align=center>".odbc_result($cl_site,"Referred")."</td>        
        </tr>";
        }         
$text .= "</table>";                 
return $text;
}

function set_rep_personal(){
return "
<style type=\"text/css\">
th {padding-top:3px;padding-bottom:3px;
    text-align:center;color:#000;
    background-color:#DCDCDC;font-size:1.1em;}
//td { background-color:#FFDAB9;font-size:1.0em;}
td { background-color:#FFFAF0;font-size:1.0em;}
</style>
 <table cellpadding=4 cellspacing=4 width=98% class='idtbl' border=0>
                 <tr>
                 <th>Analist Name</th>
                 <th>Sha1 he marked as SiteNotFound </th>
                 <th>Sha1 he marked as Undecided</th>
                 <th>Sha1 he marked as Safe</th>                 
                 <th>Sha1 he marked as Reffered</th>                                  
                 <th>Sha1 he marked as SiteInActive</th>                                                   
                 <th>AVLab analyst marked from Referred to Lab-Completed-Safe or Lab-Completed-Malware</th>                                                   
                 </tr></table>";
}

function set_rep_class_site(){
return "
<style type=\"text/css\">
table#tab2 th {padding-top:3px;padding-bottom:3px;
    text-align:center;color:#000;
    background-color:#DCDCDC;font-size:1.1em;}
//td { background-color:#FFDAB9;font-size:1.0em;}
table#tab2 td { background-color:#FFFAF0;font-size:1.0em;}
</style>
 <table id = 'tab2' cellpadding=4 cellspacing=4 width=98% class='idtbl' border=0>
                 <tr>
                 <th>Safe</th>
                 <th>Malware</th>
                 <th>Unknown</th>                                  
                 <th>Referred</th>
                 </tr></table>";
}
function lab_head(){
$text = "
<style type=\"text/css\">
table#tab1 th {padding-top:3px;padding-bottom:3px;
    text-align:center;color:#000;
        background-color:#fff;font-size:1.1em;}
        //td { background-color:#FFDAB9;font-size:1.0em;}
        table#tab1 td { background-color:#fff;font-size:1.0em;}
        </style>
    	    <table id='tab1'><tr>
    	    <td><b>LAB STATUS</b></td>
    	    <td style='width:10px'></td>
    	    <td>
    	    <select id='sel_lab_stat' style='width:250px'>
    	    <option selected value='NULL'>NULL</options>
    	    <option value='1'>Do_Not_Submit_To_Crawler</options>
    	    <option value='2'>Submit_To_Crawler</options>
    	    <option value='3'>Submitted_To_Crawler</options>
    	    <option value='4'>Crawler_Failed</options>
    	    <option value='5'>Referred_Delayed</options>
    	    <option value='6'>Crawler_Completed_Safe</options>
    	    <option value='7'>Crawler_Completed_But_Did_Not_Find_Samples</options>
    	    <option value='8'>Under_Processing_By_Analyst</options>
    	    <option value='9'>Analyst_Completed_Safe</options>
        </select>
        </td>
        <td style='width:10px'></td>
        <td>
        <button class='btn1' id='get_report' value='get_report' onclick='get_labs()' style='height:22px'>Get Report</button>
        </td></tr></table>";                                                                                                                                
return $text;
}

function crwl_head(){
$text = "
<style type=\"text/css\">
table#tab1 th {padding-top:3px;padding-bottom:3px;
    text-align:center;color:#000;
        background-color:#fff;font-size:1.1em;}
        //td { background-color:#FFDAB9;font-size:1.0em;}
        table#tab1 td { background-color:#fff;font-size:1.0em;}
        </style>
    	    <table id='tab1'><tr>
    	    <td><b>CRAWLER STATUS</b></td>
    	    <td style='width:10px'></td>
    	    <td>
    	    <select id='sel_crwl_stat' style='width:150px'>
    	    <option selected value='NULL'>NULL</options>
    	    <option value='1'>Cancel</options>
    	    <option value='2'>Pending</options>
    	    <option value='3'>Crawling</options>
    	    <option value='4'>Done</options>
        </select>
        </td>
        <td style='width:10px'></td>
        <td>
        <button class='btn1' id='get_report' value='get_report' onclick='get_crwlr()' style='height:22px'>Get Report</button>
        </td></tr></table>";                                                                                                                                

return $text;
}

function rep_head($who){
$text =  "
<style type=\"text/css\">
table#tab1 th {padding-top:3px;padding-bottom:3px;
    text-align:center;color:#000;
    background-color:#fff;font-size:1.1em;}
//td { background-color:#FFDAB9;font-size:1.0em;}
table#tab1 td { background-color:#fff;font-size:1.0em;}
</style>

	<table id='tab1'><tr>";
switch ($who){
  case 'user':
//      $text .="get_user_reports()";
      break;
  case 'class_site':
//      $text .="get_site_class()";
      break;
case 'all_site':
//      $text .="get_all_site()";
      break;
  case 'site_stat_chg':
      $text .="<td><b>Lab/Crawler status changes</b>&nbsp;</td>";
      break;
  }
	
$text .="<td>
	<select id='sel_period' style='width:150px'>
	<option selected value='Today'>Today</options>
	<option value='Yesterday'>Yesterday</options>	
	<option value='This Week'>This Week</options>	
	<option value='Last Week'>Last Week</options>	
	<option value='This Month'>This Month</options>		
	<option value='Last Month'>Last Month</options>		
	<option value='This Year'>This Year</options>		
	<option value='Last Year'>Last Year</options>		
	</select>
	</td>
	<td>
	<button class='btn1' id='get_report' value='get_report' onclick='";
switch ($who){
  case 'user':
      $text .="get_user_reports()";
      break;
  case 'class_site':
      $text .="get_site_class()";
      break;
case 'all_site':
      $text .="get_all_site()";
      break;
  case 'site_stat_chg':
      $text .="get_tracs()";
      break;
  }
$text .="' style='height:22px'>Get Report</button>
	</td></tr>";
switch ($who){
  case 'all_site':
      $text .= "<tr><td colspan=2>
	      <select id='sel_opt' style='width:150px'>
	      <option selected value='all'>all option</options>
	      <option value='isoem'>Is OEM Vendor</options>	
	      <option value='ispop'>Is Popular</options>
	      <option value='issig'>Is Code Signer</options>
	      <option value='fp'>FP Site</options>				
	      </select></td></tr>";
  
  break;
}
$text .= "</table>";
return $text;
}

function set_lab_stat(){
return "
<style type=\"text/css\">
th {padding-top:3px;padding-bottom:3px;
    text-align:center;color:#000;
background-color:#DCDCDC;font-size:1.1em;}
td { background-color:#FFFAF0;font-size:1.0em;}
}
</style>
<table cellpadding=4 cellspacing=4 width=98% class='sortable' border=0>
<thead>
  <tr>
     <th>#</th>
     <th>Site Name</th>
   </tr>
</thead>";

}

function lab_status($stat){
global $db_conn;
$text ='';
if($stat !='NULL'){
    $sql = "SELECT \"SiteId\",\"SiteURL\",\"LabVerdictDesc\" from \"WhiteListing\".\"Sites\"
	    left join \"WhiteListing\".\"LabVerdicts\" on(\"LabStatus\" =\"LabVerdictId\")
    	    where \"VerdictId\" =3 and \"LabStatus\" =$stat order by \"DateAdded\"";
}
else{
    $sql = "SELECT \"SiteId\", \"SiteURL\",\"LabVerdictDesc\" from \"WhiteListing\".\"Sites\"
            left join \"WhiteListing\".\"LabVerdicts\" on(\"LabStatus\" =\"LabVerdictId\")
            where \"VerdictId\" =3 and \"LabStatus\" isnull order by \"DateAdded\"";
}
    $lab_res = odbc_exec($db_conn,$sql);
    $i = 1;
$text = "<style type=\"text/css\">
    th {padding-top:3px;padding-bottom:3px;
	text-align:center;color:#000;
    background-color:#DCDCDC;font-size:1.1em;}
    td { background-color:#FFFAF0;font-size:1.0em;}
    }
    </style>
    <table cellpadding=4 cellspacing=4 width=98% class='sortable' border=0>
	<thead>
	  <tr>
    	     <th>#</th>
	     <th>Site Name</th>
	  </tr>
	</thead>";
if(odbc_num_rows($lab_res)>0){
    while(odbc_fetch_row($lab_res)){
         $text .= "<tr><td>$i</td><td>".odbc_result($lab_res,"SiteURL")."</td>";
         $text .= "</tr>";
	 $i++;
    }
    $text .= "</table>";
}
return $text;                                                                                
}


function crwls_status($stat){
global $db_conn;
$text ='';
if($stat !='NULL'){
    $sql = "SELECT \"SiteId\",\"SiteURL\",\"CrawlerVerdictDesc\" from \"WhiteListing\".\"Sites\"
	    left join \"WhiteListing\".\"CrawlerVerdicts\" on(\"CrawlerStatus\" =\"CrawlerVerdictId\")
    	    where \"VerdictId\" =3 and \"CrawlerVerdictId\" =$stat order by \"DateAdded\"";
}
else{
    $sql = "SELECT \"SiteId\",\"SiteURL\",\"CrawlerVerdictDesc\" from \"WhiteListing\".\"Sites\"
	    left join \"WhiteListing\".\"CrawlerVerdicts\" on(\"CrawlerStatus\" =\"CrawlerVerdictId\")
    	    where \"VerdictId\" =3 and \"CrawlerVerdictId\" isnull order by \"DateAdded\"";
}
    $lab_res = odbc_exec($db_conn,$sql);
    $i = 1;
$text = "<style type=\"text/css\">
    th {padding-top:3px;padding-bottom:3px;
	text-align:center;color:#000;
    background-color:#DCDCDC;font-size:1.1em;}
    td { background-color:#FFFAF0;font-size:1.0em;}
    }
    </style>
    <table cellpadding=4 cellspacing=4 width=98% class='sortable' border=0>
	<thead>
	  <tr>
    	     <th>#</th>
	     <th>Site Name</th>
	  </tr>
	</thead>";
if(odbc_num_rows($lab_res)>0){
    while(odbc_fetch_row($lab_res)){
         $text .= "<tr><td>$i</td><td>".odbc_result($lab_res,"SiteURL")."</td>";
         $text .= "</tr>";
	 $i++;
    }
    $text .= "</table>";
}
return $text;                                                                                
}


function set_site_trac(){
return "
<style type=\"text/css\">
th {padding-top:3px;padding-bottom:3px;
    text-align:center;color:#000;
background-color:#DCDCDC;font-size:1.1em;}
td { background-color:#FFFAF0;font-size:1.0em;}
}
</style>
<table cellpadding=4 cellspacing=4 width=98% class='sortable' border=0>
<thead>
  <tr>
     <th>Time Stamp</th>
     <th>Status Changed</th>
     <th>Changed By</th>     
   </tr>
</thead>";

}


function get_site_trac($range){
global $db_conn;
global $from;
global $to;
set_range($range);
$sql = "SELECT \"Ts\", \"Status Changed\", \"Login\", \"ObjectDesc\" 
	  FROM \"WhiteListing\".\"Site_Tracking\"
	    join \"WhiteListing\".\"Users\" on \"UserId\" = userid
	    join \"WhiteListing\".\"Objects\" on \"ObjectId\" = objectid
	     where \"Ts\" between '$from' and '$to'";
$trac = odbc_exec($db_conn,$sql);
$text = '';

$text .= "
<style type=\"text/css\">
th {padding-top:3px;padding-bottom:3px;
    text-align:center;color:#000;
background-color:#DCDCDC;font-size:1.1em;}
td { background-color:#FFFAF0;font-size:1.0em;}
}
</style>
<table cellpadding=4 cellspacing=4 width=98% class='sortable' border=0>
<thead>
  <tr>
     <th>Time Stamp</th>
     <th>Status Changed</th>
     <th>Changed By</th>     
   </tr>
</thead>";
while(odbc_fetch_row($trac)){
    $text .= "<tr><td>".odbc_result($trac,"Ts")."</td><td>".(odbc_result($trac,"ObjectDesc").":".odbc_result($trac,"Status Changed"))."</td>
    <td>".odbc_result($trac,"Login")."</td></tr>";
}
return $text;
}


function set_all_sites(){
return "
<style type=\"text/css\">
th {padding-top:3px;padding-bottom:3px;
    text-align:center;color:#000;
background-color:#DCDCDC;font-size:1.1em;}
td { background-color:#FFFAF0;font-size:1.0em;}
}

/*table.sortable thead {
    background-color:#eee;
    color:#666666;
    font-weight: bold;
    cursor: default;}*/
</style>
<table cellpadding=4 cellspacing=4 width=98% class='sortable' border=0>
<thead>
  <tr>
     <th>#</th>
     <th>Site Name</th>
     <th>Source</th>
     <th>Verdict</th>
     <th>User Added</th>
     <th>Data Added</th>     
     <!--th>Comments</th-->     
      <th>Sha1</th> 
      <th>Count Sha1</th> 
   </tr>
</thead>";
}

function get_trust_vendr_list($vname){
$ret = 'No';
$db_sigdb = pg_connect('host=192.168.9.25 user=flsuser dbname=signerdb'); 
$sql = "SELECT count(*)as c_vendor from data where md5(signer_name)='$vname'";
$result = pg_query($sql);
$cnt_vendor = pg_fetch_assoc($result);
if($cnt_vendor['c_vendor']>0){$ret = "Yes";}
return $ret;
}


function all_sites($range,$opt,$sort){
global $db_conn;
set_range($range);
global $from;
global $to;
switch($sort){
  case 0:
    $sortBy = "DateAdded";
  break;
}
switch($opt){
    case 'all';
	$sql = "
	SELECT  \"SiteURL\", \"PriorityDesc\" as \"Source\", \"VerdictDesc\", \"SiteId\",
	    (select \"VerdictDesc\" from \"WhiteListing\".\"Verdicts\" where \"VerdictId\" = \"PreviousVerdictId\") as \"PreviousVerdict\", 
	    (select \"Login\" from \"WhiteListing\".\"Users\" where \"UserId\"=\"UserAdded\" )as \"User Added\",
	    (select \"Login\" from \"WhiteListing\".\"Users\" where \"UserId\"=\"UserLastModified\" )as \"User Last Modified\",
	    \"DateAdded\", \"DateLastModified\",\"Comments\"
	FROM \"WhiteListing\".\"Sites\"
	join \"WhiteListing\".\"Verdicts\" using (\"VerdictId\")
	join \"WhiteListing\".\"Prioritytes\" using (\"PriorityId\") where \"DateAdded\" between '$from' and '$to'
	order by \"$sortBy\"";
	break;
    case 'isoem';
	$sql = "
	SELECT  \"SiteURL\", \"PriorityDesc\" as \"Source\", \"VerdictDesc\", \"SiteId\",
	    (select \"VerdictDesc\" from \"WhiteListing\".\"Verdicts\" where \"VerdictId\" = \"PreviousVerdictId\") as \"PreviousVerdict\", 
	    (select \"Login\" from \"WhiteListing\".\"Users\" where \"UserId\"=\"UserAdded\" )as \"User Added\",
	    (select \"Login\" from \"WhiteListing\".\"Users\" where \"UserId\"=\"UserLastModified\" )as \"User Last Modified\",
	    \"DateAdded\", \"DateLastModified\",\"Comments\"
	FROM \"WhiteListing\".\"Sites\"
	join \"WhiteListing\".\"Verdicts\" using (\"VerdictId\")
	join \"WhiteListing\".\"Prioritytes\" using (\"PriorityId\") where \"DateAdded\" between '$from' and '$to' and \"IsOEM\" =1
	 order by \"$sortBy\"";
	break;
    case 'ispop';
	$sql = "
	SELECT  \"SiteURL\", \"PriorityDesc\" as \"Source\", \"VerdictDesc\", \"SiteId\",
	    (select \"VerdictDesc\" from \"WhiteListing\".\"Verdicts\" where \"VerdictId\" = \"PreviousVerdictId\") as \"PreviousVerdict\", 
	    (select \"Login\" from \"WhiteListing\".\"Users\" where \"UserId\"=\"UserAdded\" )as \"User Added\",
	    (select \"Login\" from \"WhiteListing\".\"Users\" where \"UserId\"=\"UserLastModified\" )as \"User Last Modified\",
	    \"DateAdded\", \"DateLastModified\",\"Comments\"
	FROM \"WhiteListing\".\"Sites\"
	join \"WhiteListing\".\"Verdicts\" using (\"VerdictId\")
	join \"WhiteListing\".\"Prioritytes\" using (\"PriorityId\") where \"DateAdded\" between '$from' and '$to' and \"Popler\" =1 
	order by \"$sortBy\"";
	break;
    case 'issig';
	$sql = "
	SELECT  \"SiteURL\", \"PriorityDesc\" as \"Source\", \"VerdictDesc\", \"SiteId\",
	    (select \"VerdictDesc\" from \"WhiteListing\".\"Verdicts\" where \"VerdictId\" = \"PreviousVerdictId\") as \"PreviousVerdict\", 
	    (select \"Login\" from \"WhiteListing\".\"Users\" where \"UserId\"=\"UserAdded\" )as \"User Added\",
	    (select \"Login\" from \"WhiteListing\".\"Users\" where \"UserId\"=\"UserLastModified\" )as \"User Last Modified\",
	    \"DateAdded\", \"DateLastModified\",\"Comments\"
	FROM \"WhiteListing\".\"Sites\"
	join \"WhiteListing\".\"Verdicts\" using (\"VerdictId\")
	join \"WhiteListing\".\"Prioritytes\" using (\"PriorityId\") where \"DateAdded\" between '$from' and '$to' and \"IsCodeSigned\" =1 
	order by \"$sortBy\"";
	break;
    case 'fp';
	$sql = "
	SELECT  \"SiteURL\", \"PriorityDesc\" as \"Source\", \"VerdictDesc\", \"SiteId\",
	    (select \"VerdictDesc\" from \"WhiteListing\".\"Verdicts\" where \"VerdictId\" = \"PreviousVerdictId\") as \"PreviousVerdict\", 
	    (select \"Login\" from \"WhiteListing\".\"Users\" where \"UserId\"=\"UserAdded\" )as \"User Added\",
	    (select \"Login\" from \"WhiteListing\".\"Users\" where \"UserId\"=\"UserLastModified\" )as \"User Last Modified\",
	    \"DateAdded\", \"DateLastModified\",\"Comments\"
	FROM \"WhiteListing\".\"Sites\"
	join \"WhiteListing\".\"Verdicts\" using (\"VerdictId\")
	join \"WhiteListing\".\"Prioritytes\" using (\"PriorityId\") where \"DateAdded\" between '$from' and '$to' and \"LastFPDate\" is not null 
	order by \"$sortBy\"";
	break;

}

$all_sites = odbc_exec($db_conn,$sql);

$text= "
<style type=\"text/css\">
th {padding-top:3px;padding-bottom:3px;
    text-align:center;color:#000;
background-color:#DCDCDC;font-size:1.1em;}
td { background-color:#FFFAF0;font-size:1.0em;}
}
a {display: block; padding:0; text-decoration:none;color:#555;font-size:0.8em; }

a:hover{font-style:oblique;color:red}
</style>
<!--table cellpadding=2 cellspacing=2 width=98% class='sort-table' id='table-1' border=0-->
<table cellpadding=2 cellspacing=2 class='sort-table' id='table-1' border=0>
	<col />
	<col />
	<col />
	<col />
	<col />
	<col />
	<col />
	<col />
<thead>
  <tr>
     <th>#</th>
     <th>Site Name</th>
     <th>Source</th>
     <th>Verdict</th>
     <th>User Added</th>
     <th>Data Added</th>     
     <!--th>Comments</th-->     
     <th width='300px'>Sha1</th> 
     <th><div id='sha1_first'  style='display:none'><a class='menu_rep1' href='javascript:void(0)' onclick='tablesort();'>Count Sha1</a></div>
	 <div id='sha1_sec'>Count Sha1	</div>
     </th> ";
     if ($opt=='issig'){
    $text .="<th>Signer Name</th> 
    <th>Is In Trusted Vendor List</th>"; }
   $text.="</tr>
</thead>
  <tbody>";
   $i=1;
   
 while(odbc_fetch_row($all_sites)){
      $text .="<tr><td align=center>".$i."</td>
    		<td align=center>".odbc_result($all_sites,"SiteURL")."</td>
               <td align=center>".odbc_result($all_sites,"Source")."</td>
	       <td align=center>".odbc_result($all_sites,"VerdictDesc")."</td>
               <td align=center>".odbc_result($all_sites,"User Added")."</td>
               <td align=center>".odbc_result($all_sites,"DateAdded")."</td>               
               <!--td align=center>".odbc_result($all_sites,"Comments")."</td-->
	       <td align=center><a href='javascript:void(0);' class='menu_rep'
			    onclick=\"show_hide_sha1('sha1_".odbc_result($all_sites,"SiteId")."');\">show/hide sha1</a></br>
		<div id='sha1_".odbc_result($all_sites,"SiteId")."' style='display:none;width:300px'>";
	$sql_sha1="select \"SHA1\",cert_subjects.\"Name\"::character varying(250),md5(cert_subjects.\"Name\")::character varying as v_name from \"WhiteListing\".\"FileSource\" 
		      join samples on (lower(\"SampleSha1\")=lower(\"SHA1\")) 
		      join samples_authcodes using (\"SampleId\")
		      join certs using(\"CertId\")
		      join cert_subjects using(\"SubjectId\")
		  where \"ChildAuthcodeId\" isnull and \"SiteId\"=".odbc_result($all_sites,"SiteId")."";               

//	$sql_sha1="select \"SHA1\",'test' as \"Name\" from \"WhiteListing\".\"FileSource\" where \"SiteId\"=".odbc_result($all_sites,"SiteId")."";               
	$sha1_sites = odbc_exec($db_conn,$sql_sha1);
	$j=0;
		 while(odbc_fetch_row($sha1_sites)){
		  $text .=odbc_result($sha1_sites,"SHA1")."</br>";
		  $j++;
  		    }
	$text.="</div></td> 
	      <td align=center>$j</td>";
	if ($opt=='issig'){
	 $text .= "<td>".odbc_result($sha1_sites,"Name")."</td>
		<td>".get_trust_vendr_list(odbc_result($sha1_sites,"v_name"))."</td>";
	}
         $text .= "</tr>";
               $i++;
  }
//get_trust_vendr_list(odbc_result($sha1_sites,"Name"))
   $text .= "</tbody></table>";
   return $text;
//return $sql_sha1;

}

function prnUnlock_sha1(){
global $db_conn;
$sql = "SELECT count(\"SHA1\") as \"FLS_Sha1\",
(SELECT count(\"SHA1\") FROM \"WhiteListing\".\"FileSource\"  where \"isLock\" isnull and \"SourceId\" = 2) as \"Camdpams_Sha1\"
  FROM \"WhiteListing\".\"FileSource\"
  where \"isLock\" isnull and \"SourceId\" = 1;";

$all_sites = odbc_exec($db_conn,$sql);

$text= "
<style type=\"text/css\">
th {padding-top:3px;padding-bottom:3px;
    text-align:center;color:#000;
background-color:#DCDCDC;font-size:1.1em;}
//td { background-color:#FFDAB9;font-size:1.0em;}
td { background-color:#FFFAF0;font-size:1.0em;}
</style>
<table cellpadding=4 cellspacing=4 width=98% class='idtbl' border=0>
  <tr>
     <th>Priotiy 1 Unlocked SHA1 Count</th>
     <th>Priotiy 2 Unlocked SHA1 Count</th>
     <th>Total</th>
   </tr>";
   $all=0;
 while(odbc_fetch_row($all_sites)){
      $all = odbc_result($all_sites,"FLS_SHA1") + odbc_result($all_sites,"Camdpams_Sha1");
      $text .="<tr><td align=center>".odbc_result($all_sites,"FLS_SHA1")."</td>
               <td align=center>".odbc_result($all_sites,"Camdpams_Sha1")."</td>
	       <td align=center>$all</td>
               </tr>";
  }
   $text .= "</table>";
   return $text;
}


function prnlock_sha1(){
global $db_conn;
$sql = "
SELECT \"SHA1\", fs.\"DateAdded\",\"SourceName\",\"Login\", \"DateTimeLock\"
  FROM \"WhiteListing\".\"FileSource\" as fs
  join \"WhiteListing\".\"Sources\" using (\"SourceId\")
  join \"WhiteListing\".\"Users\" on (\"isLock\" = \"UserId\")
  where \"isLock\" is not null;";

$all_sites = odbc_exec($db_conn,$sql);

$text= "
<style type=\"text/css\">
th {padding-top:3px;padding-bottom:3px;
    text-align:center;color:#000;
background-color:#DCDCDC;font-size:1.1em;}
//td { background-color:#FFDAB9;font-size:1.0em;}
td { background-color:#FFFAF0;font-size:1.0em;}
</style>
<table cellpadding=4 cellspacing=4 width=98% class='idtbl' border=0>
  <tr>
     <th>#</th>
     <th>Sha1</th>
     <th>DateAdded</th>
     <th>SourceName</th>
     <th>Login</th>
     <th>DateTimeLock</th>     
   </tr>";
   $i=1;
 while(odbc_fetch_row($all_sites)){
      $text .="<tr><td align=center>".$i."</td>
    		<td align=center>".odbc_result($all_sites,"SHA1")."</td>
               <td align=center>".odbc_result($all_sites,"DateAdded")."</td>
	       <td align=center>".odbc_result($all_sites,"SourceName")."</td>
               <td align=center>".odbc_result($all_sites,"Login")."</td>
               <td align=center>".odbc_result($all_sites,"DateTimeLock")."</td>               
               </tr>";
               $i++;
  }
   $text .= "</table>";
   return $text;
}


function whois(){
$login = $_SESSION['login'];
return $login;
}

function whoisId(){
global $db_conn;
$login = $_SESSION['login'];

$usrquery = odbc_exec($db_conn,"select \"UserId\" from \"WhiteListing\".\"Users\" where \"Login\" ='$login'");
$usrid = odbc_result($usrquery, "UserId");
return $usrid;
}


function addemail($email,$site){
global $db_conn;
    $site_rep = cut_site($site);
    $sql = "select \"WhiteListing\".add_email('$email','$site_rep')";
    $eml_add = odbc_exec($db_conn,$sql);
    if ($eml_add) return "email added";
    else  return "Changes not save";
                  
}

function add_email_s($email,$siteid){
global $db_conn;
    $mail_sql = "select \"WhiteListing\".add_email_s('$email',$siteid )";
    $add_mail = odbc_exec($db_conn,$mail_sql);
    if ($add_mail) return odbc_result($add_mail,1);
    else  return "-1";
                  
}

function del_email_s($emailid){
global $db_conn;
    $mail_sql = "select \"WhiteListing\".del_email_s($emailid )";
    $add_mail = odbc_exec($db_conn,$mail_sql);
    if ($add_mail) return "del email";
    else  return "-1";
                  
}

function change_URL($curr_url,$new_url){
global $db_conn;
$login = $_SESSION['login'];

$usrquery = odbc_exec($db_conn,"select \"UserId\" from \"WhiteListing\".\"Users\" where \"Login\" ='$login'");
$usrid = odbc_result($usrquery, "UserId");

    $url = "select \"WhiteListing\".change_url('$curr_url','$new_url',$usrid)";
    $ch_url= odbc_exec($db_conn,$url);
    if ($ch_url) return "1";//"select \"WhiteListing\".change_url('$curr_url','$new_url',$usrid)";
    else  return "-1";
                  
}

function del_site_url($siteid){
global $db_conn;
    $sql = "select \"WhiteListing\".del_site($siteid)";
    $del_site = odbc_exec($db_conn,$sql);
    if ($del_site) return "del $site";
    else  return $sql;
                  
}

function empty_sha1($fileid){
global $db_conn;
    $sql = "select \"WhiteListing\".empty_sha1($fileid )";
    $empty_sha1 = odbc_exec($db_conn,$sql);
    if ($empty_sha1) return "$fileid - not associated with site";
    else  return $sql;
                  
}

function add_site_atrib($isoem,$iscodesigner,$popler,$lastfp,$site,$comm){
global $db_conn;
$site_rep = cut_site($site);
    if($lastfp=='' ){
	$sql = "select \"WhiteListing\".add_site_atrib($isoem,$iscodesigner,$popler,'$site_rep','$comm')";
    }
    else{
	$sql = "select \"WhiteListing\".add_site_atrib($isoem,$iscodesigner,$popler,'$lastfp','$site_rep','$comm')";
    }
    $atr_add = odbc_exec($db_conn,$sql);
    if ($atr_add) return "atributes added";
//    else  return "Changes not save";
    else return $sql;  
}

function lock_sha1($source){
global $db_conn;
$usrid=whoisId();
$sql = "select \"WhiteListing\".lock_sha1($usrid,$source)";
$locks=odbc_exec($db_conn,$sql);
if($locks) return "sha1 locks";
else return $sql;
}
function unlock_sha1($fileid){
global $db_conn;
$sql = "select \"WhiteListing\".unlock_sha1($fileid)";
$unlocks=odbc_exec($db_conn,$sql);
if($unlocks) return "sha1 unlocks";
else return $sql;
}
function unlock_all_sha1(){
global $db_conn;
$usrid=whoisId();
$sql = "select \"WhiteListing\".unlock_all_sha1($usrid)";
$unlocks=odbc_exec($db_conn,$sql);
if($unlocks) return "sha1 unlocks";
else return $sql;
}

function search_site(){
return "
            <div style='float:left;width:39%'>
            <table cellspacing=3 cellpading=4>
        	<tr>
        	    <td width=180px><label for='site_name_search' style='font-size:14px;color:maroon'>Enter Site</label></td>
        	    <td><input type='text' id='site_name_search' style='width:250px' name='site_name_search'></td>
        	</tr>
    	    </table>
	    </div>
            <div style='float:left;width:60%'>
                <button class='btn' style='width:90px' onclick=\"search_site(document.getElementById('site_name_search').value)\">Find</button>
            </div>";
}

function search_sha1_head(){
return "
            <div style='float:left;width:49%'>
            <table cellspacing=3 cellpading=4>
        	<tr>
        	    <td><label for='sha1_search' style='font-size:14px;color:maroon'>Search Site Related to SHA1:</label></td>
        	    <td><input type='text' id='sha1_search' style='width:350px' name='sha1_search'></td>
        	</tr>
    	    </table>
	    </div>
            <div style='float:left;width:50%'>
                <button class='btn' style='width:90px' onclick=\"search_sha1_site(document.getElementById('sha1_search').value)\">Find</button>
            </div>";
}

function set_search_sha1(){
return "
<style type=\"text/css\">
th {padding-top:3px;padding-bottom:3px;
    text-align:center;color:#000;
background-color:#DCDCDC;font-size:1.1em;}
//td { background-color:#FFDAB9;font-size:1.0em;}
td { background-color:#FFFAF0;font-size:1.0em;}
</style>
<table cellpadding=4 cellspacing=4 width=100% class='idtbl' border=0>
  <tr>
     <th>Site Name</th>
     <th>Source</th>
     <th>Verdict</th>
     <th>User Added</th>
     <th>Data Added</th>     
     <th>Comments</th>     
  </tr>";
}

function search_sha1($sha1){
global $db_conn;
$sql = "SELECT  \"SiteURL\", \"PriorityDesc\" as \"Source\", \"VerdictDesc\", \"SiteId\",
        (select \"Login\" from \"WhiteListing\".\"Users\" where \"UserId\"=\"UserAdded\" )as \"User Added\",
	\"Sites\".\"DateAdded\", \"Sites\".\"Comments\"
FROM \"WhiteListing\".\"FileSource\" 
join \"WhiteListing\".\"Sites\" as \"Sites\" using(\"SiteId\")
join \"WhiteListing\".\"Verdicts\" using (\"VerdictId\")
join \"WhiteListing\".\"Prioritytes\" using (\"PriorityId\") 
where \"SHA1\" ='$sha1'";

$site = odbc_exec($db_conn,$sql);

$text =  "
<style type=\"text/css\">
th {padding-top:3px;padding-bottom:3px;
    text-align:center;color:#000;
background-color:#DCDCDC;font-size:1.1em;}
//td { background-color:#FFDAB9;font-size:1.0em;}
td { background-color:#FFFAF0;font-size:1.0em;}
</style>
<table cellpadding=4 cellspacing=4 width=100% class='idtbl' border=0>
  <tr>
     <th>Site Name</th>
     <th>Source</th>
     <th>Verdict</th>
     <th>User Added</th>
     <th>Data Added</th>     
     <th>Comments</th>     
  </tr>";
while(odbc_fetch_row($site)){
      $text .="<tr><td align=center>".odbc_result($site,"SiteURL")."</td>";
      $text .="<td align=center>".odbc_result($site,"Source")."</td>";
      $text .="<td align=center>".odbc_result($site,"VerdictDesc")."</td>";
      $text .="<td align=center>".odbc_result($site,"User Added")."</td>";
      $text .="<td align=center>".odbc_result($site,"DateAdded")."</td>";
      $text .="<td align=center>".odbc_result($site,"Comments")."</td></tr>";
}
$text .="</table>";
return $text;
  
}

?>