<?php
  require_once('config.php');
  require_once('excel.php');
 
class Camdpams 
{ 
	private $db_conn;
	function __construct() 
	{
		$dsn  = 'Driver='    . DB_DRIVER;
		$dsn .= ';Server='   . DB_HOST;
		$dsn .= ';Port='     . DB_PORT;
		$dsn .= ';Database=' . DB_DATABASE;
		$this->db_conn = odbc_connect("$dsn",$_SERVER['PHP_AUTH_USER'],$_SERVER['PHP_AUTH_PW'],SQL_CUR_USE_ODBC);
	}
	
	
	/*
    Added by QLJ 19-Jan-2010
  */

  public function getReportFamilyStats($aFamilyName){
    $query = "SELECT *"
           .      ", \"Comment\"::varchar as \"UserComment\" "
           .  "from mis.view_analyst_cs_families "
           . "where \"CsFamilyName\" = '$aFamilyName' "
           . "order by \"AnalystCsFamilyId\" desc";
    $res = odbc_exec($this->db_conn, $query);
    $report = '';
    $error = '';
    if ($res){
  		while( odbc_fetch_row($res) )
  		{
        $report.= odbc_result($res, "AnalystCsFamilyId");
        $report .= "|";
        $name = "";
        $firstname = odbc_result($res,"AnalystFirstName");
        $surname = odbc_result($res,"AnalystLastName");
        if ($firstname){
          $name = $firstname;
        }
        if ($surname){
          if ($name){
            $name .= " $surname";
          }else{
            $name = $surname;
          }
        }
  			$report .= $name;
  			$report .= "|";
  			$timestamp = odbc_result($res,"AssignedTimestamp");
  			if ($timestamp){
          $timestamp = date( 'd/m/Y H:i:s', strtotime($timestamp) );
        }else{
          $timestamp = "";
        }
  			$report .= $timestamp;
  			$report .= "|";
  			$timestamp = odbc_result($res,"UnAssignedTimestamp");
  			if ($timestamp){
          $timestamp = date( 'd/m/Y H:i:s', strtotime($timestamp) );
        }else{
          $timestamp = "";
        }
  			$report .= $timestamp;
  			$report .= "|";
  			$isActive = odbc_result($res,"IsActive");
  			if ($isActive){
          $report .= "Yes";
        }else{
          $report .= "No";
  			}
  			$report .= "|";
        $comment = odbc_result($res,"UserComment");
        if (!$comment){
          $comment = "";
        }
  			$report .= $comment;
  			$report .= "\n";
  		}
    }else{
      $error = odbc_errormsg($this->db_conn);
      $error = str_replace('Unknown error;\n', '', $error);
    }
		return array( array('tblReportFamilyStatsAnalysts', $report, $error)
                , array()
                );
  }
	
	
	/*
    Added by QLJ 19-Jan-2010
	*/

  function creatingSearchPattern($aPattern){
    if ( stripos($aPattern, '*') == false ){
      $arrPattern = array( $aPattern . "%" );
    }else{
      $arrPattern = explode('*', $aPattern);
      if ( $arrPattern[ count($arrPattern) - 1 ] == '' ){
        array_pop($arrPattern);
      }
      for ($i = 0; $i < count($arrPattern); $i++){
        if ($i == 0){
          $arrPattern[$i] .= "%";
        }else{
          $arrPattern[$i] = "%" . $arrPattern[$i] . "%";
        }
      }
    }
    return $arrPattern;
	}
	
	/*
    Added by QLJ 18-Jan-2010
	*/
	
	function getReportFamilySearch($aPattern){
    // Deconstructing pattern
    $arrPattern = $this->creatingSearchPattern($aPattern);
    // Building each shard into a where-clause
    $where = "";
    for ($i = 0; $i < count($arrPattern); $i++){
      if ($where){ $where .= " and "; }
      $search = $arrPattern[$i];
      $where .= "\"CsFamily\" ilike '$search'";
    }
    if ($where){ $where = " where $where"; }
    $query = "select DISTINCT(\"CsFamily\") from cs_malware_names $where order by 1 limit 200";
    $res = odbc_exec($this->db_conn, $query);
    $error = '';
    $report = '';
    if ($res){
      while ( odbc_fetch_row($res) ){
        $report.= odbc_result($res, "CsFamily");
        $report.= "\n";
      }
    }else{
      $error = odbc_errormsg($this->db_conn);
      $error = str_replace('Unknown error;\n', '', $error);
    }
    return array('tblReportFamilySearch', $report, $error);
	}
	

	/*
    Added by QLJ 13-Jan-2010
  */
  
  function setTrueOrFalse($aValue){
    $retVal = 'false';
    if ($aValue === '1' || $aValue === 1 || $aValue === 'true' || $aValue === true){
      $retVal = 'true';
    }
    return $retVal;
  }

	function editAnalystPerFamilyData($aID, $aType, $aValue){
    // Get existing isActive
    $query = "select *, \"Comment\"::varchar as \"AnalystComment\" from analyst_cs_families where \"AnalystCsFamilyId\" = $aID";
    $res = odbc_exec($this->db_conn, $query);
    $error = '';
    if ($res){
      $success = false;
      if ( odbc_fetch_row($res) ){
        $isActive = $this->setTrueOrFalse( odbc_result($res, "IsActive") );
        $comment = odbc_result($res, "AnalystComment");
        $latestDueDate = odbc_result($res, "LatestDueDate");
        $isPoly = $this->setTrueOrFalse( odbc_result($res, "IsPoly") );
        $needsEmulator = $this->setTrueOrFalse( odbc_result($res, "NeedsEmulator") );
        $needsDisassembler = $this->setTrueOrFalse( odbc_result($res, "NeedsDisassembler") );
        $needsPackerSupport = $this->setTrueOrFalse( odbc_result($res, "PackerSupportNeeded") );
        switch ($aType){
          case 'comment':
            $comment = $aValue;
            break;
          case 'latestdue':
            $latestDueDate = $aValue;
            break;
          case 'isactive':
            $isActive = $this->setTrueOrFalse( $aValue );
            break;
          case 'ispoly':
            $isPoly = $this->setTrueOrFalse( $aValue );
            break;
          case 'needsemulator':
            $needsEmulator = $this->setTrueOrFalse( $aValue );
            break;
          case 'needsdisassembler':
            $needsDisassembler = $this->setTrueOrFalse( $aValue );
            break;
          case 'packersupport':
            $needsPackerSupport = $this->setTrueOrFalse( $aValue );
            break;
        }
        if ($latestDueDate){
          $latestDueDate = "'$latestDueDate'";
        }else{
          $latestDueDate = 'null';
        }
        odbc_free_result($res);
        $query = "select \"mis\".\"analyst_cs_families_upsert\"( $aID::integer"
               .                                      ", null"
               .                                      ", null"
               .                                      ", $isActive::boolean"
               .                                      ", '$comment'::text"
               .                                      ", $latestDueDate::date"
               .                                      ", $isPoly::boolean"
               .                                      ", $needsEmulator::boolean"
               .                                      ", $needsDisassembler::boolean"
               .                                      ", $needsPackerSupport::boolean"
               .                                      ")";
        $res = odbc_exec($this->db_conn, $query);
        if ($res){
          if ( odbc_fetch_row($res) ){
            $success = odbc_result($res,"mis"."analyst_cs_families_upsert");
          }
        }
      }
    }
    if (!$success){
      $error = odbc_errormsg($this->db_conn);
      $error = str_replace('Unknown error;\n', '', $error);
    }
    return array($success, $error);
  }

  /*
    Added by QLJ 11-Jan-2010
  */

	public function familyIntellisense($aStub){
    $query = "select distinct(\"CsFamily\") as familyname "
           .   "from cs_malware_names "
           .  "where \"CsFamily\" ilike '$aStub%' "
           .  "order by 1 asc "
           .  "limit 50";
    $query = "select family_name as familyname from \"lookups\".\"families\" where \"permitted\" and family_name ilike '$aStub%' order by 1 asc limit 100";
    $res = odbc_exec($this->db_conn, $query);
    $arrData = array();
    while( odbc_fetch_row($res) ){
      array_push( $arrData, odbc_result($res,"familyname") );
    }
    return $arrData;
	}
	
  /*
    Added by QLJ 11-Jan-2010
  */

  public function getReportAnalystsPerFamily($aFamilyName){
    $query = "SELECT \"AnalystCsFamilyId\""
           .      ", \"CsVendorName\""
           .      ", \"AnalystFirstName\""
           .      ", \"AnalystLastName\""
           .      ", \"AssignedTimestamp\""
           .      ", \"UnAssignedTimestamp\""
           .      ", \"IsActive\""
           .      ", \"Comment\"::varchar "
           .  "from mis.view_analyst_cs_families "
           . "where \"CsFamilyName\" = '$aFamilyName'";
    $res = odbc_exec($this->db_conn, $query);
    $report = '';
		while( odbc_fetch_row($res) )
		{
      $report .= odbc_result($res,"AnalystCsFamilyId");
			$report .= "|";
      $name = "";
      $firstname = odbc_result($res,"AnalystFirstName");
      $surname = odbc_result($res,"AnalystLastName");
      if ($firstname){
        $name = $firstname;
      }
      if ($surname){
        if ($name){
          $name .= " $surname";
        }else{
          $name = $surname;
        }
      }
			$report .= $name;
			$report .= "|";
			$timestamp = odbc_result($res,"AssignedTimestamp");
			if ($timestamp){
        $timestamp = date( 'd/m/Y H:i:s', strtotime($timestamp) );
      }else{
        $timestamp = "";
      }
			$report .= $timestamp;
			$report .= "|";
			$timestamp = odbc_result($res,"UnAssignedTimestamp");
			if ($timestamp){
        $timestamp = date( 'd/m/Y H:i:s', strtotime($timestamp) );
      }else{
        $timestamp = "";
      }
			$report .= $timestamp;
			$report .= "|";
			$isActive = odbc_result($res,"IsActive");
			if ($isActive){
        $report .= "Yes";
      }else{
        $report .= "No";
			}
			$report .= "|";
      $comment = odbc_result($res,"Comment");
      if (!$comment){
        $comment = "";
      }
			$report .= $comment;
			$report .= "\n";
		}
		return $report;
  }


  /*
    Added by QLJ 11-Jan-2009
  */

  public function isFamilyAssignedToSomeone($aFamilyName){
    $query = "select \"AnalystUserName\""
           .      ", \"AnalystFirstName\""
           .      ", \"AnalystLastName\" "
           .   "FROM \"mis.view_analyst_cs_families\" "
           .  "WHERE \"CsFamilyName\" = '$aFamilyName' "
           .    "AND \"UnAssignedTimestamp\" IS NULL";
    $res = odbc_exec($this->db_conn, $query);
    $userName = "";
    $firstName = "";
    $lastName = "";
    $success = false;
    if( odbc_fetch_row($res) ){
      $success = true;
      $userName = odbc_result($res, "AnalystUserName");
      $firstName = odbc_result($res, "AnalystFirstName");
      $lastName = odbc_result($res, "AnalystLastName");
    }
    return array($success, $userName, $firstName, $lastName);
  }

  /*
    Added by QLJ 08-Jan-2009
  */
  
  public function addAssignFamilyToAnalyst($aFamilyName, $aAnalystName){
    $query = "select \"mis\".\"analyst_cs_families_upsert\"( null::integer"
           .                                      ", '$aFamilyName'::varchar"
           .                                      ", '$aAnalystName'::varchar"
           .                                      ", true::boolean"
           .                                      ", null::text"
           .                                      ", null::date"
           .                                      ", false::boolean"
           .                                      ", false::boolean"
           .                                      ", false::boolean"
           .                                      ", false::boolean"
           .                                      ")";
    $res = odbc_exec($this->db_conn, $query);
    $error = "";
    $success = false;
    if ($res){
      if ( odbc_fetch_row($res) ){
        $success = odbc_result($res,"mis"."analyst_cs_families_upsert");
      }
    }else{
      $error = odbc_errormsg($this->db_conn);
      $error = str_replace('Unknown error;\n', '', $error);
    }
    return array($success, $error);
  }
  
  /*
    Added by QLJ 08-Jan-2009
  */
  public function editAssignFamilyToAnalyst($aFamilyName, $aAnalystName){}

  

  /*
    Added by QLJ 10-Dec-2009
  */
  public function getReport0($aFrom, $aTo, $aPeriod)
  {
    $fromTime = strtotime($aFrom);
    $fromTime = date("Y-m-d H:i:s", $fromTime);
    $toTime = strtotime($aTo);
    $toTime = date("Y-m-d H:i:s", $toTime);
    $query = "select mal.\"CsFamily\" as \"Family\""
           .      ", count( DISTINCT(sam.\"SampleId\") ) as \"TotalSamples\""
           .      ", count(case when sig.\"SigFormatId\" = 11 then true else null end) AS \"DetectedByFirstSigs\""
           .      ", count(case when sig.\"SigFormatId\" = 2 OR sig.\"SigFormatId\" = 7 then sig.\"SigId\" else null end) AS \"DetectedByPeSigs\""
           .      ", count(distinct res.\"SampleId\")-count(case when sig.\"SigFormatId\" = 2 OR sig.\"SigFormatId\" = 7 then sig.\"SigId\" else null end) AS \"NotDetectedByPeSigs\""
           .      ", count(distinct(case when sig.\"SigFormatId\" = 2 OR sig.\"SigFormatId\"  = 7 then sig.\"SigId\" else null end)) AS \"PeSigs\" "
           .   "from cs_vendors ven "
           .  "inner join cs_results res on res.\"CsVendorId\" = ven.\"CsVendorId\" "
           .  "inner join \"samples\" sam on sam.\"SampleId\" = res.\"SampleId\" "
           .  "inner join \"samples_sigs\" samsig on samsig.\"SampleId\" = res.\"SampleId\" "
           .  "inner join \"sigs\" sig on sig.\"SigId\" = samsig.\"SigId\" "
           .  "inner join cs_malware_names mal on mal.\"CsMalwareNameId\" = res.\"CsMalwareNameId\" "
           .  "where res.\"DateTimeScanned\" between '$fromTime'::timestamp and '$toTime'::timestamp "
           .    "and ven.\"CsVendorId\" = 8 "
           .  "group by mal.\"CsFamily\"";
    $fileName = "Report0_Results_$aPeriod". "_" . date( 'Y_m_d_H_i_s', strtotime('now') );
    $res = odbc_exec($this->db_conn, $query);
    // Creating array to hold queried data
    $arrData = array();
    $numRows = 0;
    while( odbc_fetch_row($res) )
		{
      $familyName = odbc_result($res,"Family");
      $totalSamples = odbc_result($res,"TotalSamples");
      $detectedByFirstSigs = odbc_result($res,"DetectedByFirstSigs");
      $detectedByPeSigs = odbc_result($res,"DetectedByPeSigs");
      $notDetectedByPeSigs = odbc_result($res,"NotDetectedByPeSigs");
      $numPeSigs = odbc_result($res,"PeSigs");
      array_push( $arrData, array( "Family"=>$familyName
                                 , "Total Samples"=>$totalSamples
                                 , "Detected By First Sigs"=>$detectedByFirstSigs
                                 , "Detected By PE Sigs"=>$detectedByPeSigs
                                 , "Not Detected By PE Sigs"=>$notDetectedByPeSigs
                                 , "PE Sigs"=>$numPeSigs )
                );
      $numRows++;
    }
    if ($numRows > 0){
      // Exporting data to xl spreadsheet
      /*
        This uses the class made available in excel.php
        Read documentation for it in sub-folder ./xls
      */
      $export_file = "xlsfile://home/camdpams_www/reports/$fileName.xls";
      $fp = fopen($export_file, "wb");
      if (!is_resource($fp))
      {
          die("Cannot open $export_file");
      }
      fwrite( $fp, serialize($arrData) );
      fclose($fp);
      return "fileName = $fileName";
    }else{
      return "fileName = empty";
    }
  }

  /*
    Added by QLJ 21-Dec-2009
  */
  public function getReportSamplesVsSigs2($aFrom, $aTo, $aPeriod)
	{
    $fromTime = strtotime($aFrom);
    $fromTime = date("Y-m-d H:i:s", $fromTime);
    $toTime = strtotime($aTo);
    $toTime = date("Y-m-d H:i:s", $toTime);
    $query = "SELECT * FROM \"mis\".\"get_report_samples_cs_vs_sigs2\"('$fromTime'::timestamp, '$toTime'::timestamp, 8) "
           .     "AS ( \"CsVendorName\" varchar"
           .        ", \"MalwareFamily\" varchar"
           .        ", \"SamplesCnt\" bigint"
           .        ", \"DetectedByPeSigs\" bigint"
           .        ", \"DetectedByGenericSigs\" bigint"
           .        ", \"DetectedByFirstSigs\" bigint"
           .        ", \"NotDetectedByPeAndGenericSigs\" bigint"
           .        ", \"PercentageDetection\" double precision"
           .        ", \"TotalPeSigs\" bigint"
           .        ", \"TotalGenericSigs\" bigint"
           .        ")";
    $period = str_replace( " ", "_", $aPeriod );
    $fileName = "AllFamiliesReport_" . $period . "_" . str_replace( " ", "_", $_SERVER['PHP_AUTH_USER'] ) . "_" . date( 'Y_m_d_H_i_s', strtotime('now') );
    $res = odbc_exec($this->db_conn, $query);
    // Creating array to hold queried data
    $arrData = array();
    $numRows = 0;
    while( odbc_fetch_row($res) )
		{
      $vendorName = odbc_result($res, "CsVendorName");
      $familyName = odbc_result($res,"MalwareFamily");
      $totalSamples = odbc_result($res,"SamplesCnt");
      $detectedByFirstSigs = odbc_result($res,"DetectedByFirstSigs");
      $detectedByPeSigs = odbc_result($res,"DetectedByPeSigs");
      $detectedByGenericSigs = odbc_result($res,"DetectedByGenericSigs");
      $notDetectedByPeGenericSigs = odbc_result($res,"NotDetectedByPeAndGenericSigs");
      $percentDetected = odbc_result($res,"PercentageDetection");
      $totalPeSigs = odbc_result($res,"TotalPeSigs");
      $totalGenericSigs = odbc_result($res,"TotalGenericSigs");
      array_push( $arrData, array( "VendorName"=>$vendorName
                                 , "MalwareFamily"=>$familyName
                                 , "Total Samples"=>$totalSamples
                                 , "Detected By First Sigs"=>$detectedByFirstSigs
                                 , "Detected By PE Sigs"=>$detectedByPeSigs
                                 , "Detected By Generic Sigs"=>$detectedByGenericSigs
                                 , "Not Detected By PE or Generic Sigs"=>$notDetectedByPeGenericSigs
                                 , "Percentage Detection"=>$percentDetected
                                 , "Total PE Sigs"=>$totalPeSigs
                                 , "Total Generic Sigs"=>$totalGenericSigs )
                );
      $numRows++;
    }
    $retVal1 = "fileName = empty";
    $folder = "reports_ondemand/AllFamilies";
    $retVal2 = $folder;
    if ($numRows > 0){
      $result = $this->writeToXL("$folder/$fileName.xls", $arrData);
      if ($result){
        $retVal1 = "fileName = $fileName";
      }
    }
    return array($retVal1, $retVal2);
  }
  
  
  /*
    Added by QLJ 23-Dec-2009
  */
  public function getReportSha1PerFamily($aFrom, $aTo, $aFamily, $aDetectedBy, $aPeriod, $aIncludeVariant)
	{
    $fromTime = strtotime($aFrom);
    $fromTime = date("Y-m-d H:i:s", $fromTime);
    $toTime = strtotime($aTo);
    $toTime = date("Y-m-d H:i:s", $toTime);
    $query = "SELECT * FROM \"mis\".\"get_report_samples_list_sha1_in_family\"('$fromTime'::timestamp"
           .                                                                          ", '$toTime'::timestamp"
           .                                                                          ", '$aFamily'::varchar"
           .                                                                          ", '$aDetectedBy'::varchar ) "
           .     "AS ( \"sha1\" varchar"
           .        ", \"variant\" varchar"
           .        ")";
    $familyName = str_replace( " ", "_", $aFamily );
    $familyName = str_replace( ".", "_", $familyName );
    $period = str_replace( " ", "_", $aPeriod );
    $fileName = $familyName . "_Sha1PerFamilyReport_" . $aDetectedBy . "_" . $period. "_" .  str_replace( " ", "_", $_SERVER['PHP_AUTH_USER'] )  . "_" . date( 'Y_m_d_H_i_s', strtotime('now') );
    $res = odbc_exec($this->db_conn, $query);
    // Creating array to hold queried data
    $arrData = array();
    $numRows = 0;
    while( odbc_fetch_row($res) )
		{
      $sha1 = odbc_result($res, "sha1");
      $variant = odbc_result($res, "variant");
      if ($aIncludeVariant){
        $arrResult = array( "Sha1"=>$sha1, "Variant"=>$variant );
      }else{
        $arrResult = array( "Sha1"=>$sha1 );
      }
      array_push( $arrData, $arrResult );
      $numRows++;
    }
    $retVal1 = "fileName = empty";
    $folder = "reports_ondemand/FamilySpecific";
    $retVal2 = $folder;
    if ($numRows > 0){
      $result = $this->writeToXL("$folder/$fileName.xls", $arrData);
      if ($result){
        $retVal1 = "fileName = $fileName";
      }
    }
    return array($retVal1, $retVal2);
  }
  
  /*
    Added by QLJ 14-Jan-2010
  */
  
  function writeToXL($aPath, $aArrData){
    $export_file = "xlsfile://home/camdpams_www/$aPath";
    $fp = fopen($export_file, "wb");
    $retVal = false;
    if ( !is_resource($fp) ){
        die("Cannot open $export_file");
    }
    fwrite( $fp, serialize($aArrData) );
    fclose($fp);
    if ( file_exists("//home/camdpams_www/$aPath") ){
      $retVal = true;
    }
    return $retVal;
  }
  
  /*
    Added by QLJ 05-Jan-2010
  */
  public function getReportSha1PerVariant($aFrom, $aTo, $aVariant, $aDetectedBy, $aPeriod)
	{
    $fromTime = strtotime($aFrom);
    $fromTime = date("Y-m-d H:i:s", $fromTime);
    $toTime = strtotime($aTo);
    $toTime = date("Y-m-d H:i:s", $toTime);
    $query = "SELECT * FROM \"mis\".\"get_report_samples_list_sha1_in_variant\"('$fromTime'::timestamp"
           .                                                                          ", '$toTime'::timestamp"
           .                                                                          ", '$aVariant'::varchar"
           .                                                                          ", '$aDetectedBy'::varchar) "
           .     "AS ( \"sha1\" varchar"
           .        ")";
    $lastPeriod = strripos( $aVariant, "." );
    $variantName = str_replace( " ", "_", $aVariant );
    $variantName = str_replace( ".", "_", $variantName );
    $familyName = substr($variantName, 0, $lastPeriod + 1);
    $period = str_replace( " ", "_", $aPeriod );
    $fileName = $variantName . "_Sha1PerVariantReport_" . $aDetectedBy . "_" . $period . "_" .  str_replace( " ", "_", $_SERVER['PHP_AUTH_USER'] ) . "_" . date( 'Y_m_d_H_i_s', strtotime('now') );
    $res = odbc_exec($this->db_conn, $query);
    // Creating array to hold queried data
    $arrData = array();
    $numRows = 0;
    while( odbc_fetch_row($res) )
		{
      $sha1 = odbc_result($res, "sha1");
      $arrResult = array( "Sha1"=>$sha1 );
      array_push( $arrData, $arrResult );
      $numRows++;
    }
    $retVal1 = "fileName = empty";
    $folder = "reports_ondemand/VariantSpecific";
    $retVal2 = $folder;
    if ($numRows > 0){
      $result = $this->writeToXL("$folder/$fileName.xls", $arrData);
      if ($result){
        $retVal1 = "fileName = $fileName";
      }
    }
    return array($retVal1, $retVal2);
  }

  /*
    Added by QLJ 06-Jan-2010
  */
  public function getAnalystNames(){
    $query = "select * from \"mis\".\"view_analysts\" order by \"AnalystFirstName\", \"AnalystLastName\"";
    $res = odbc_exec($this->db_conn, $query);
    $stream = "[";
    $bof = true;
    while( odbc_fetch_row($res) )
		{
      if ($bof){
        $bof = false;
      }else{
        $stream .= ",";
      }
      $id = odbc_result($res, "UserId");
      $firstname = odbc_result($res,"AnalystFirstName");
      $surname = odbc_result($res,"AnalystLastName");
			$user = odbc_result($res,"UserName");
      if ( $firstname ){
        $name = $firstname;
        if ( $surname ){
          $name .= " " . $surname;
        }
      }
      $stream .= "['$user', '$name']";
		}
		$stream .= "]";
		return $stream;
  }
  
  /*
    Added by QLJ 07-Jan-2010
  */
  
  public function getReportFamiliesPerAnalyst($aName){
trigger_error("getReportFamiliesPerAnalyst() START", E_USER_WARNING);
    $query = "SELECT *"
           .       ", \"Comment\"::varchar as \"AnalystComment\" "
           .  "from mis.view_analyst_cs_families "
           . "where \"AnalystUserName\" = '$aName'";
trigger_error("getReportFamiliesPerAnalyst() 1: query = $query", E_USER_WARNING);
    $res = odbc_exec($this->db_conn, $query);
    $report = '';
		while( odbc_fetch_row($res) )
		{
			$report .= odbc_result($res,"AnalystCsFamilyId");
			$report .= "|";
			$report .= odbc_result($res,"CsFamilyName");
			$report .= "|";
			$report .= odbc_result($res,"CsVendorName");
			$report .= "|";
			$timestamp = odbc_result($res,"AssignedTimestamp");
			if ($timestamp){
        $timestamp = date( 'd/m/Y H:i:s', strtotime($timestamp) );
      }else{
        $timestamp = "";
      }
			$report .= $timestamp;
			$report .= "|";
			$timestamp = odbc_result($res,"UnAssignedTimestamp");
			if ($timestamp){
        $timestamp = date( 'd/m/Y H:i:s', strtotime($timestamp) );
      }else{
        $timestamp = "";
      }
			$report .= $timestamp;
			$report .= "|";
			$isActive = odbc_result($res,"IsActive");
			if ($isActive){
        $report .= "Yes";
      }else{
        $report .= "No";
			}
			$report .= "|";
      $comment = odbc_result($res,"AnalystComment");
      if (!$comment){
        $comment = "";
      }
			$report .= $comment;
      $report .= "|";
			$date = odbc_result($res,"LatestDueDate");
			if ( empty($date) || $date === NULL ){
        $date = "None";
      }else{
        $date = date( 'd/m/Y', strtotime($date) );
      }
			$report .= $date;
      $report .= "|";
      $value = odbc_result($res,"IsPoly");
			if ($value){
        $report .= "Yes";
      }else{
        $report .= "No";
			}
      $report .= "|";
      $value = odbc_result($res,"NeedsEmulator");
			if ($value){
        $report .= "Yes";
      }else{
        $report .= "No";
			}
      $report .= "|";
      $value = odbc_result($res,"NeedsDisassembler");
			if ($value){
        $report .= "Yes";
      }else{
        $report .= "No";
			}
      $report .= "|";
      $value = odbc_result($res,"PackerSupportNeeded");
			if ($value){
        $report .= "Yes";
      }else{
        $report .= "No";
			}
      $report .= "\n";
		}
trigger_error("getReportFamiliesPerAnalyst() STOP", E_USER_WARNING);
		return $report;
  }

	public function getPeriodActive_user($from, $to){
	$report = '';
	$d1= date("z",strtotime($from));
	if(date("Y",strtotime($to))==date("Y",strtotime($from))){$d2=date("z",strtotime($to));}
	else{$d2 = (date("Y",strtotime($to))-date("Y",strtotime($from)))*365+date("z",strtotime($to));}
	$k=$d2-$d1;
	$query="select sum(case when \"CisVer\"='updates40' then \"ActiveUsersCnt\" else null end)::integer/$k as \"u40\",
		sum(case when \"CisVer\"='updates313' then \"ActiveUsersCnt\" else null end)::integer/$k as \"u313\",
		sum(case when \"CisVer\"='updates314' then \"ActiveUsersCnt\" else null end)::integer/$k as \"u314\",
 		sum(case when \"CisVer\"='updates50' then \"ActiveUsersCnt\" else null end)::integer/$k as \"u50\",
 		sum(case when \"CisVer\"='updates51' then \"ActiveUsersCnt\" else null end)::integer/$k as \"u51\", 
 		sum(case when \"CisVer\"='updates53' then \"ActiveUsersCnt\" else null end)::integer/$k as \"u53\",  
 		sum(case when \"CisVer\"='updates54' then \"ActiveUsersCnt\" else null end)::integer/$k as \"u54\", 
 		sum(case when \"CisVer\"='updatesgb' then \"ActiveUsersCnt\" else null end)::integer/$k as \"GeekBuddy\",
 		sum(case when \"CisVer\"='updatesmac' then \"ActiveUsersCnt\" else null end)::integer/$k as \"CAV-MAC\" 
		  from cis_users_stats where \"Ts\" between '$from' and '$to'";
	$res = odbc_exec($this->db_conn, $query);
	while(odbc_fetch_row($res)){
	$x= odbc_result($res,"u50") + odbc_result($res,"u40") + odbc_result($res,"u314") + odbc_result($res,"u313");
	$x += odbc_result($res,"u51") + odbc_result($res,"u53") + odbc_result($res,"u54") + odbc_result($res,"GeekBuddy");
	$x += (odbc_result($res,"CAV-MAC")==NULL)?0:odbc_result($res,"CAV-MAC");
	$report .= (strtotime($to)*1000);
	$report .= "|";
	$report .= $x;
	$report .= "|";
	(odbc_result($res,"u54") == NUll)?$report .=0:$report .=odbc_result($res,"u54");
	$report .= "|";
	(odbc_result($res,"u53") == NUll)?$report .=0:$report .=odbc_result($res,"u53");
	$report .= "|";
	(odbc_result($res,"u51") == NUll)?$report .=0:$report .=odbc_result($res,"u51");
	$report .= "|";
	(odbc_result($res,"u50") == NUll)?$report .=0:$report .=odbc_result($res,"u50");
	$report .= "|";
	(odbc_result($res,"u40") == NULL)?$report .=0:$report .= odbc_result($res,"u40");
	$report .= "|";
	(odbc_result($res,"u314")== NULL)?$report .= 0:$report .= odbc_result($res,"u314");
	$report .= "|";
	(odbc_result($res,"u313")== NULL)?$report .= 0:$report .= odbc_result($res,"u313");
	$report .= "|";
	(odbc_result($res,"GeekBuddy") == NUll)?$report .=0:$report .=odbc_result($res,"GeekBuddy");
	$report .= "|";
	(odbc_result($res,"CAV-MAC") == NUll)?$report .=0:$report .=odbc_result($res,"CAV-MAC");
	$report .= "\n";
	
	}
	return $report;

	}



	public function getWeekActive_user($from, $to){
	$report = '';
	$query="select sum(case when \"CisVer\"='updates40' then \"ActiveUsersCnt\" else null end)::integer/7 as \"u40\",
		sum(case when \"CisVer\"='updates313' then \"ActiveUsersCnt\" else null end)::integer/7 as \"u313\",
		sum(case when \"CisVer\"='updates314' then \"ActiveUsersCnt\" else null end)::integer/7 as \"u314\",
		sum(case when \"CisVer\"='updates50' then \"ActiveUsersCnt\" else null end)::integer/7 as \"u50\",
 		sum(case when \"CisVer\"='updates51' then \"ActiveUsersCnt\" else null end)::integer/7 as \"u51\", 
 		sum(case when \"CisVer\"='updates53' then \"ActiveUsersCnt\" else null end)::integer/7 as \"u53\",  
 		sum(case when \"CisVer\"='updates54' then \"ActiveUsersCnt\" else null end)::integer/7 as \"u54\", 
 		sum(case when \"CisVer\"='updatesgb' then \"ActiveUsersCnt\" else null end)::integer/7 as \"GeekBuddy\",
 		sum(case when \"CisVer\"='updatesmac' then \"ActiveUsersCnt\" else null end)::integer/7 as \"CAV-MAC\" 
		  from cis_users_stats where \"Ts\" between '$from' and '$to'";
	$res = odbc_exec($this->db_conn, $query);
	while(odbc_fetch_row($res)){
	$x= odbc_result($res,"u50") + odbc_result($res,"u40") + odbc_result($res,"u314") + odbc_result($res,"u313");
	$report .= (strtotime($to)*1000);
	$report .= "|";
	$report .= $x;
	$report .= "|";
	(odbc_result($res,"u54") == NUll)?$report .=0:$report .=odbc_result($res,"u54");
	$report .= "|";
	(odbc_result($res,"u53") == NUll)?$report .=0:$report .=odbc_result($res,"u53");
	$report .= "|";
	(odbc_result($res,"u51") == NUll)?$report .=0:$report .=odbc_result($res,"u51");
	$report .= "|";
	(odbc_result($res,"u50") == NUll)?$report .=0:$report .=odbc_result($res,"u50");
	$report .= "|";
	(odbc_result($res,"u40") == NULL)?$report .=0:$report .= odbc_result($res,"u40");
	$report .= "|";
	(odbc_result($res,"u314")== NULL)?$report .= 0:$report .= odbc_result($res,"u314");
	$report .= "|";
	(odbc_result($res,"u313")== NULL)?$report .= 0:$report .= odbc_result($res,"u313");
	$report .= "|";
	(odbc_result($res,"GeekBuddy") == NUll)?$report .=0:$report .=odbc_result($res,"GeekBuddy");
	$report .= "|";
	(odbc_result($res,"CAV-MAC") == NUll)?$report .=0:$report .=odbc_result($res,"CAV-MAC");
	$report .= "\n";
	
	}
	return $report;

	}

	public function getWeekGraph_for_av_active_user($from, $to){
	$report = '';
	if(date("m",strtotime($from))==1){$w_start=1;}
	else{$w_start=date("W",strtotime($from));}
	$w_end=date("W",strtotime($to));
	$k = $w_end -$w_start; 
	$dm =1-date("w",strtotime($from));
	for($j=0;$j<=$k;$j++){
	    $w1 = date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($from))  , date("d",strtotime($from))+$dm+$j, date("Y",strtotime($from))));
	    $w2 = date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($w1))  , date("d",strtotime($w1))+6, date("Y",strtotime($w1))));
	    $dm=$dm+6;
	    $report .= $this->getWeekActive_user($w1,$w2);
	}
	$report .= ((strtotime($w2))*1000) + 21600000;
	$report .= "\n";

	return $report;
    }
	public function get_sig_gen_analist_data($from,$to){
		$report = '';
		$query = 'SELECT "SigsSafeInserted" FROM "mis"."get_report_users_activity"(\'' . $from . '\',\'' . $to . '\') AS
                         ("UserName" varchar, "LabName" varchar, "SamplesSubmitted" integer, "SamplesLocked" integer,
                          "SigsInsertedTotal" integer, "SigsUpdatedTotal" integer, "SigsDeletedTotal" integer, "SigsRevertedTotal" integer,
                          "SigsFirstNewInserted" integer, "SigsPeInserted" integer, "SigsSafeInserted" integer, "SigsCommonInserted" integer,
                          "SigsMemoryInserted" integer, "SigsGenericInserted" integer, "DetectRoutinesInserted" integer,"TotalSamplesDetectedByPESigns" integer)
			  where "UserName" = \'dirty_sig_gen\'';
		$res = odbc_exec($this->db_conn, $query);  
		while(odbc_fetch_row($res))
		{
			$report .= (strtotime($from)*1000);
			$report .= "|";
			$report .= odbc_result($res,"SigsSafeInserted");
			$report .= "\n";
		}
		return $report;



	}

	public function getGraph_for_sig_genan_analyst($from, $to, $period){
	$report = '';
	$i =0;
	$d1=0;
	$d2=0;
	$j=0;
	switch($period){
	  case 'daily':
		$d1=date("z",strtotime($from));
		if(date("Y",strtotime($to))==date("Y",strtotime($from))){$d2=date("z",strtotime($to));}
		else{$d2 = 365+date("z",strtotime($to));}
		for($j=$d1;$j<=$d2;$j++){
		    $w1 = date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($from))  , date("d",strtotime($from))+$i, date("Y",strtotime($from))));
		    $i++;
		    $w2 = date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($from))  , date("d",strtotime($from))+$i, date("Y",strtotime($from))));
		    $report .= $this->get_sig_gen_analist_data($w1,$w2);
		}
	  break;
	  case 'weekly':
	      if(date("m",strtotime($from))==1){$d1=1;}
	      else{$d1=date("W",strtotime($from));}
		$n= date("w",strtotime($from));
		if(date("Y",strtotime($to))>date("Y",strtotime($from))){$d2=42+date("W",strtotime($to));}
		else{$d2=date("W",strtotime($to));}
		for($j=$d1;$j<=$d2;$j++){
		    $w1 = date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($from))  , date("d",strtotime($from))+$i, date("Y",strtotime($from))));
		    $i+=6;
		    if($j==$d1){$w2 = date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($from))  , date("d",strtotime($from))+6-$n, date("Y",strtotime($from))));}
		    else{$w2 = date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($from))  , date("d",strtotime($from))+$i, date("Y",strtotime($from))));}
		    $report .= $this->get_sig_gen_analist_data($w1,$w2);
		}

	  break;
	  case 'monthly':
		$m1=date("m",strtotime($from));
		$m2=date("m",strtotime($to));
	    for($i=$m1;$i<$m2;$i++){
	      if($i == $m1){$w1= date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($from))+$j  , date("d",strtotime($from)), date("Y",strtotime($from))));}
	      else{$w1= date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($from))+$j  , 01, date("Y",strtotime($from))));}
		$j++;
		$w2= date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($from))+$j  , 01, date("Y",strtotime($from))));
		$report .= $this->get_sig_gen_analist_data($w1,$w2);
	    }
	    if(date("d",strtotime($to))>1){
		$w1 = date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($to))  , 01, date("Y",strtotime($to))));
		$w2= date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($to))  , date("d",strtotime($to)), date("Y",strtotime($to))));
		$report .= $this->get_sig_gen_analist_data($w1,$w2);
	      }
	  break;
	  case 'quarter':
	      $m1=date("n",strtotime($from));
	      $m2=date("n",strtotime($to));
	      if($m2>$m1){
		  for($i=0;$i<ceil(($m2-$m1)/3);$i++){
		      if($i==0){
			$w1= date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($from))+$j  , date("d",strtotime($from)), date("Y",strtotime($from))));
			      if ($m1==1){$j+=2;}
			      elseif ($m1>1 && $m1<3){$j=3-$m1;}
			      elseif ($m1==3||$m1==6||$m1==9){$j=3;}
			      elseif ($m1>3 && $m1<6){ $j=6-$m1;}
			      elseif ($m1>6 && $m1<9){$j=9-$m1;}
			      elseif ($m1>9 && $m1<12){$j=12-$m1;}
		      }
		      else{$w1= date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($from))+$j  , 01, date("Y",strtotime($from))));
			  $j+=3;}
		      if($i==ceil(($m2-$m1)/3)-1){$w2= date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($from))+$j, date("d",strtotime($to)), date("Y",strtotime($from))));}
		      else{$w2= date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($from))+$j , 01, date("Y",strtotime($from))));}
			  $report .= $this->get_sig_gen_analist_data($w1,$w2);
		  }
		  
		  if(date("d",strtotime($to))>1 || date("m",strtotime($to))<12) 
		  {
		      if(date("m",strtotime($to))!=6 && date("m",strtotime($to))!=3 && date("m",strtotime($to))!=9 && date("m",strtotime($to))!=12){
			    $w1 = date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($to1))  , 01, date("Y",strtotime($to1))));
			    $w2= date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($to))  , date("d",strtotime($to)), date("Y",strtotime($to))));
			    $report .= $this->get_sig_gen_analist_data($w1,$w2);
		      }
		  }
	      }
	      else{
		  $w1 = date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($from))  , date("d",strtotime($from)), date("Y",strtotime($from))));
		  $w2= date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($to))  , date("d",strtotime($to)), date("Y",strtotime($to))));
		  $report .= $this->get_sig_gen_analist_data($w1,$w2);
	      }
	  break;
	 case 'year':
	      $m1=date("Y",strtotime($from));
	      $m2=date("Y",strtotime($to));
	      for($i=$m1;$i<$m2;$i++){
		  if($i == $m1){$w1= date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($from))  , date("d",strtotime($from)), date("Y",strtotime($from))+$j));}
		  else{$w1= date("Y-m-d",mktime(0, 0, 0, 01 , 01, date("Y",strtotime($from))+$j));}
		  $j++;
		  $w2= date("Y-m-d",mktime(0, 0, 0, 01  , 01, date("Y",strtotime($from))+$j));
		  $report .= $this->get_sig_gen_analist_data($w1,$w2);
	      }
	      if(date("d",strtotime($to))>1|| date("m",strtotime($to))>1){
		  $w1 = date("Y-m-d",mktime(0, 0, 0, 01  , 01, date("Y",strtotime($to))));
		  $w2= date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($to))  , date("d",strtotime($to)), date("Y",strtotime($to))));
		  $report .= $this->get_sig_gen_analist_data($w1,$w2);
	}
	  
	 break;
	 case 'd_of_week':
	      $m1=date("w",strtotime($from));
	      $m2=date("w",strtotime($to));
	      for($i=$m1;$i<$m2;$i++){
		$w1 = date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($from))  , date("d",strtotime($from)), date("Y",strtotime($from))));
		$j++;
		$w2= date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($from))  , date("d",strtotime($from))+$j, date("Y",strtotime($from))));
		$report .= $this->get_sig_gen_analist_data($w1,$w2);
	      }
	 break;
	 case 'h_of_day':
	      $m1=date("H",strtotime($from));
	      $m2=date("H",strtotime($to));
	      for($i=$m1;$i<=$m2;$i++){
		  if($i==$m1){
		      $w1 = date("Y-m-d H:i",mktime(date("H",strtotime($from))+$j, date("i",strtotime($from)), 0, date("m",strtotime($from))  , date("d",strtotime($from)), date("Y",strtotime($from))));}
		  else{
		      $w1 = date("Y-m-d H:i",mktime(date("H",strtotime($from))+$j, 0, 0, date("m",strtotime($from))  , date("d",strtotime($from)), date("Y",strtotime($from))));
		  }
		  $j++;
		  if($i==$m2-1){
		  $w2= date("Y-m-d H:i",mktime(date("H",strtotime($from))+$j, date("i",strtotime($to)), 0, date("m",strtotime($from))  , date("d",strtotime($from)), date("Y",strtotime($from))));
		  }
		  else{
		  $w2= date("Y-m-d H:i",mktime(date("H",strtotime($from))+$j, 0, 0, date("m",strtotime($from))  , date("d",strtotime($from)), date("Y",strtotime($from))));
		  }
		  $report .= $this->get_sig_gen_analist_data($w1,$w2);
	      }
	 break;
	}

	$report .= ((strtotime($w2))*1000) + 21600000;
	$report .= "\n";

	return $report;
    }


	public function getMonthGraph_for_av_active_user($from, $to){
	$report = '';
	$j = 0;
	$m1=date("m",strtotime($from));
	$m2=date("m",strtotime($to));
	for($i=$m1;$i<$m2;$i++){
	if($i == $m1){$f1= date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($from))+$j  , date("d",strtotime($from)), date("Y",strtotime($from))));}
	else{$f1= date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($from))+$j  , 01, date("Y",strtotime($from))));}
	$j++;
	$to1= date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($from))+$j  , 01, date("Y",strtotime($from))));
	$report .= $this->getPeriodActive_user($f1,$to1);
	}
	if(date("d",strtotime($to))>1){
	$f1 = date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($to))  , 01, date("Y",strtotime($to))));
	$to1= date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($to))  , date("d",strtotime($to)), date("Y",strtotime($to))));
	$report .= $this->getPeriodActive_user($f1,$to1);
	}
	$report .= ((strtotime($to1))*1000) + 21600000;
	$report .= "\n";

	return $report;
    }

	public function getQuarter_for_av_active_user($from, $to){
	$report = '';
	$j = 0;
	$m1=date("n",strtotime($from));
	$m2=date("n",strtotime($to));
	if($m2>$m1){
	for($i=0;$i<ceil(($m2-$m1)/3);$i++){
	if($i==0){
	  $f1= date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($from))+$j  , date("d",strtotime($from)), date("Y",strtotime($from))));
	    	if ($m1==1){$j+=2;}
		elseif ($m1>1 && $m1<3){$j=3-$m1;}
		elseif ($m1==3||$m1==6||$m1==9){$j=3;}
		elseif ($m1>3 && $m1<6){ $j=6-$m1;}
		elseif ($m1>6 && $m1<9){$j=9-$m1;}
		elseif ($m1>9 && $m1<12){$j=12-$m1;}
	}
	else{$f1= date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($from))+$j  , 01, date("Y",strtotime($from))));
	    $j+=3;
	  }
	if($i==ceil(($m2-$m1)/3)-1){$to1= date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($from))+$j  , date("d",strtotime($to)), date("Y",strtotime($from))));}
	else{$to1= date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($from))+$j  , 01, date("Y",strtotime($from))));}
	    $report .= $this->getPeriodActive_user($f1,$to1);
	}
	
	if(date("d",strtotime($to))>1 || date("m",strtotime($to))<12) 
	{
	if(date("m",strtotime($to))!=6 && date("m",strtotime($to))!=3 && date("m",strtotime($to))!=9 && date("m",strtotime($to))!=12){
	    $f1 = date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($to1))  , 01, date("Y",strtotime($to1))));
	    $to1= date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($to))  , date("d",strtotime($to)), date("Y",strtotime($to))));
	    $report .= $this->getPeriodActive_user($f1,$to1);
	}
	}
	}
	else{
	    $f1 = date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($from))  , date("d",strtotime($from)), date("Y",strtotime($from))));
	    $to1= date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($to))  , date("d",strtotime($to)), date("Y",strtotime($to))));
	    $report .= $this->getPeriodActive_user($f1,$to1);
	}
	$report .= ((strtotime($to1))*1000) + 21600000;
	$report .= "\n";

	return $report;
    }

	public function getPerHourOfDay_for_av_active_user($from, $to){
	$report = '';
	$j = 0;
	$m1=date("H",strtotime($from));
	$m2=date("H",strtotime($to));
	for($i=$m1;$i<=$m2;$i++){
	if($i==$m1){
	$f1 = date("Y-m-d H:i",mktime(date("H",strtotime($from))+$j, date("i",strtotime($from)), 0, date("m",strtotime($from))  , date("d",strtotime($from)), date("Y",strtotime($from))));}
	else{
	$f1 = date("Y-m-d H:i",mktime(date("H",strtotime($from))+$j, 0, 0, date("m",strtotime($from))  , date("d",strtotime($from)), date("Y",strtotime($from))));
	}
	$j++;
	if($i==$m2-1){
	$to1= date("Y-m-d H:i",mktime(date("H",strtotime($from))+$j, date("i",strtotime($to)), 0, date("m",strtotime($from))  , date("d",strtotime($from)), date("Y",strtotime($from))));
	}
	else{
	$to1= date("Y-m-d H:i",mktime(date("H",strtotime($from))+$j, 0, 0, date("m",strtotime($from))  , date("d",strtotime($from)), date("Y",strtotime($from))));
	}
	$report .= $this->getPeriodActive_user($f1,$to1);
	}
	$report .= ((strtotime($to1))*1000) + 21600000;
	$report .= "\n";

	return $report;
    }




	public function getPerDayOfWeek_for_av_active_user($from, $to){
	$report = '';
	$j = 0;
	$m1=date("w",strtotime($from));
	$m2=date("w",strtotime($to));
	for($i=$m1;$i<$m2;$i++){
	$f1 = date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($from))  , date("d",strtotime($from)), date("Y",strtotime($from))));
	$j++;
	$to1= date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($from))  , date("d",strtotime($from))+$j, date("Y",strtotime($from))));
	$report .= $this->getPeriodActive_user($f1,$to1);
	}
	$report .= ((strtotime($to1))*1000) + 21600000;
	$report .= "\n";

	return $report;
    }



	public function getYear_for_av_active_user($from, $to){
	$report = '';
	$j = 0;
	$m1=date("Y",strtotime($from));
	$m2=date("Y",strtotime($to));
	for($i=$m1;$i<$m2;$i++){
	if($i == $m1){$f1= date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($from))  , date("d",strtotime($from)), date("Y",strtotime($from))+$j));}
	else{$f1= date("Y-m-d",mktime(0, 0, 0, 01 , 01, date("Y",strtotime($from))+$j));}
	$j++;
	$to1= date("Y-m-d",mktime(0, 0, 0, 01  , 01, date("Y",strtotime($from))+$j));
	$report .= $this->getPeriodActive_user($f1,$to1);
	}
	if(date("d",strtotime($to))>1|| date("m",strtotime($to))>1){
	$f1 = date("Y-m-d",mktime(0, 0, 0, 01  , 01, date("Y",strtotime($to))));
	$to1= date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($to))  , date("d",strtotime($to)), date("Y",strtotime($to))));
	$report .= $this->getPeriodActive_user($f1,$to1);
	}
	$report .= ((strtotime($to1))*1000) + 21600000;
	$report .= "\n";

	return $report;
    }



	public function getDayGraph_for_av_active_user($from, $to)
	{
		$report = '';
		$j = 0;$x=$x1=$x2=$x3=$x4=$x5=$x6=$x7=$dst=$tmp='';

		$query = "select \"Ts\",array_accum((\"CisVer\"||':'||\"ActiveUsersCnt\")) as \"UserCnt\" from cis_users_stats 
				where \"Ts\" between '$from' and '$to' group by \"Ts\" order by \"Ts\"";
		$res = odbc_exec($this->db_conn, $query);
		while(odbc_fetch_row($res))
		{
		$tmp = str_replace("{","",odbc_result($res,"UserCnt"));
		$tmp = str_replace("}","",$tmp);
		$arr = split(",",$tmp);
		//$arr = split(",",odbc_result($res,"UserCnt"));
		$report .= (strtotime(odbc_result($res,"Ts"))*1000);
		for($j = 0;$j<count($arr);$j++){	
		      $dst = split(':',$arr[$j]);
		      switch($dst[0]){
			  case 'updates54':
			  $x1 = $dst[1];
			  break;
			  case 'updates53':
			  $x2 = $dst[1];
			  break;
			  case 'updates51':
			  $x3 = $dst[1];
			  break;
			  case 'updates50':
			  $x4 = $dst[1];
			  break;
			  case 'updates40':
			  $x5 = $dst[1];
			  break;
			  case 'updates314':
			  $x6 = $dst[1];
			  break;
			  case 'updates313':
			  $x7 = $dst[1];
			  break;
		      }
		 }
		$x = $x1 + $x2 + $x3 + $x4 + $x5 + $x6 + $x7;
		$report .= "|";
		$report .= $x;
		$report .= "|";
		$report .= $x1;
		$report .= "|";
		$report .= $x2;
		$report .= "|";
		$report .= $x3;
		$report .= "|";
		$report .= $x4;
		$report .= "|";
		$report .= $x5;
		$report .= "|";
		$report .= $x6;
		$report .= "|";
		$report .= $x7;
		$report .= "\n";
		}
		$report .= (strtotime(odbc_result($res,"Ts"))*1000) + 21600000;
		$report .= "\n";

		return $report;
	
	}

 /*
    Added by Oleg Krotenko 03-Feb-2010
  */

	public function getDayGraph_for_CAV($from, $to)
	{
		$report = '';
		$i = 0;
		$query = 'SELECT * from mis.report_samples_summary where "DayDate" >= \''
			. date('d-F-Y', strtotime($from))  . '\' and "DayDate" < \''
			. date('d-F-Y', strtotime($to)) .'\'' ;
		$res = odbc_exec($this->db_conn, $query);
		while(odbc_fetch_row($res))
		{
//			$report .= (strtotime($from) * 1000 + $i*86400000);//86400000 = 24*60*60*1000
			$report .= (strtotime(odbc_result($res,"DayDate"))*1000);
			$report .= "|";
			$report .= odbc_result($res,"DetectedByCavOnly");
			$report .= "|";
			$report .= odbc_result($res,"DetectedByCsOnly");
			$report .= "\n";
			$i++;
		}
		return $report;
	
	}



  /*
    Added by Oleg Krotenko 03-Feb-2010
  */

	public function getMonthGraph_for_CAV($from,$to)
	{

		$report = '';
		$i = 0;
//		$query = 'select * from housekeeping.get_report_year_cavvscs('. date('d-F-Y', strtotime($from)) .') as (MN date,CAV integer, CS integer);' ;
		$query = 'select * from mis.get_report_year_cavvscs( \''
			. date('d-F-Y', strtotime($from))  . '\') as (MN date,CAV integer, CS integer);';
		$res = odbc_exec($this->db_conn, $query);
		while(odbc_fetch_row($res))
		{
			$report .= (strtotime(odbc_result($res,"MN"))*1000);
			$report .= "|";
			$report .= odbc_result($res,"CAV");
			$report .= "|";
			$report .= odbc_result($res,"CS");
			$report .= "\n";

		}
		return $report;

	}
  



  
  
  
	public function getReportSigsSummary($from, $to) 
	{
		$report = '';
		$query = 'SELECT * FROM "mis"."get_report_sigs_summary"(\'' . $from . '\',\'' . $to . '\') as ("ParamName" varchar, "Value" varchar)';
		$res = odbc_exec($this->db_conn, $query);
		$success = false;
		$error  = '';
		if ($res){
      $success = true;
  		while(odbc_fetch_row($res))
  		{
  			$report .= odbc_result($res,"ParamName");
  			$report .= "|";
  			$report .= odbc_result($res,"Value");
  			$report .= "\n";
  		}
		}else{
      $error = odbc_errormsg($this->db_conn);
      $error = str_replace('Unknown error;\n', '', $error);
    }
		return array($success, $report, $error);
	}
	
	public function getReportSigsFormats() 
	{
		$report = '';
		$query = 'SELECT * FROM "mis"."get_report_sigs_formats"() as ("SigFormatName" varchar, "TotalCnt" integer, "ExistingCnt" integer, "DeletedCnt" integer)';
		$res = odbc_exec($this->db_conn, $query);
		while(odbc_fetch_row($res))
		{
			$report .= odbc_result($res,"SigFormatName");
			$report .= "|";
			$report .= odbc_result($res,"TotalCnt");
			$report .= "|";
			$report .= odbc_result($res,"ExistingCnt");
			$report .= "|";
			$report .= odbc_result($res,"DeletedCnt");			
			$report .= "\n";
		}
		return $report;
	}

	public function getReportSamplesSummary($from, $to) 
	{
		$report = '';
		$query = 'SELECT * FROM "mis"."get_report_samples_summary"(\'' . $from . '\',\'' . $to . '\') as ("ParamName" varchar, "Value" varchar)';
		$res = odbc_exec($this->db_conn, $query);  
		while(odbc_fetch_row($res))
		{
			$report .= odbc_result($res,"ParamName");
			$report .= "|";
			$report .= odbc_result($res,"Value");
			$report .= "\n";
		}
		return $report;
	}
	public function getReportSamplesFormats($from, $to) 
	{
		$report = '';
		$query = 'SELECT * FROM "mis"."get_report_samples_formats"(\'' . $from . '\',\'' . $to . '\') as ("SampleFormatName" varchar, "Count" integer)';
		$res = odbc_exec($this->db_conn, $query);  
		while(odbc_fetch_row($res))
		{
			$report .= odbc_result($res,"SampleFormatName");
			$report .= "|";
			$report .= odbc_result($res,"Count");
			$report .= "\n";
		}
		return $report;
	}

	  public function cnt_SpellPower_arr($a,$b){
		$c = array();
		foreach($a as $val){
		    $c[]=$b[$val];
		}
		$c_u=array_unique($c);
		return count($c_u);
	}

	public	function sum_SpellPower_arr($a,$b){
		$c = array();
		foreach($a as $val){
		    $c[]=$b[$val];
		}
		return array_sum($c);
		}

	public function getSpellPower($from, $to) 
	{

	    $fromdate = strtotime($from);
	    $fromdate = date("Y-m-d", $fromdate);
	    $todate = strtotime($to);
	    $todate = date("Y-m-d", $todate);

		$report = '';
		$query = "select * from mis.get_report_spell_power_direct('$fromdate','$todate') 
			      as (\"AnalystName\" character varying,\"LabName\" character varying,\"SigId\" integer,\"MalwareName\" character varying,\"Total\" integer)";
		$res = odbc_exec($this->db_conn, $query);  
		$lab=array();
		$analist=array();
		$sigs = array();
		$totals = array();
		while(odbc_fetch_row($res))
		{
			$report .= odbc_result($res,"AnalystName");
			$report .= "|";
/*			switch(odbc_result($res,"AnalystName")){
			    case 'signexpl':
			      $report .= "SignExplorer";
			    break;
			    case 'daisy_ai':
			      $report .= "DAISY";
			    break;
			    default:
*/
		      $report .= odbc_result($res,"LabName");
//			    break;
//			}
			$report .= "|";
			$report .= odbc_result($res,"SigId");
			$report .= "|";
			$report .= odbc_result($res,"MalwareName");
			$report .= "|";
 			$report .= odbc_result($res,"Total");
			$report .= "\n";
			$lab[]=odbc_result($res,"LabName");
			$analist[]=odbc_result($res,"AnalystName");
			$sigs[]=odbc_result($res,"SigId");
			$totals[]=odbc_result($res,"Total");
		}
		$summary='';
		$lab_u=array_unique($lab);
		  foreach ($lab_u as $value) {
		  $summary .=$value;
		  $summary .= "|";
		  $summary.=$this->cnt_SpellPower_arr(array_keys($lab,$value),$analist);
		  $summary .= "|";
		  $summary.=$this->cnt_SpellPower_arr(array_keys($lab,$value),$sigs);
		  $summary .= "|";
		  $summary.=$this->sum_SpellPower_arr(array_keys($lab,$value),$totals);
		  $summary .= "\n";

		  }
		return array($report,$summary);
	}

	public function getSummary($from, $to) 
	{
		$report = '';
		$query = "select * from mis.get_report_lab_summary_direct('$from','$to') as (\"Team\" character varying,
				\"Analyst Count\" bigint,\"Total Signs\" bigint,\"Total Detection\" bigint)";
		$res = odbc_exec($this->db_conn, $query);  
		while(odbc_fetch_row($res))
		{
			$report .= odbc_result($res,"Team");
			$report .= "|";
			$report .= odbc_result($res,"Analyst Count");
			$report .= "|";
			$report .= odbc_result($res,"Total Signs");
			$report .= "|";
 			$report .= odbc_result($res,"Total Detection");
			$report .= "\n";
		}
		return $report;
	}







	public function getReportUsersActivity($from, $to) 
	{
		$report = '';
		$query = 'SELECT * FROM "mis"."get_report_users_activity"(\'' . $from . '\',\'' . $to . '\') AS
                         ("UserName" varchar, "LabName" varchar, "SamplesSubmitted" integer, "SamplesLocked" integer,
                          "SigsInsertedTotal" integer, "SigsUpdatedTotal" integer, "SigsDeletedTotal" integer, "SigsRevertedTotal" integer,
                          "SigsFirstNewInserted" integer, "SigsPeInserted" integer, "SigsSafeInserted" integer, "SigsCommonInserted" integer,
                          "SigsMemoryInserted" integer, "SigsGenericInserted" integer, "DetectRoutinesInserted" integer,"TotalSamplesDetectedByPESigns" integer)';
		$res = odbc_exec($this->db_conn, $query);  
		while(odbc_fetch_row($res))
		{
			$report .= odbc_result($res,"UserName");
			$report .= "|";
			$report .= odbc_result($res,"LabName");
			$report .= "|";
			$report .= odbc_result($res,"SamplesSubmitted");
			$report .= "|";
			$report .= odbc_result($res,"SamplesLocked");
			$report .= "|";
			$report .= odbc_result($res,"SigsInsertedTotal");
			$report .= "|";
			$report .= odbc_result($res,"SigsUpdatedTotal");
			$report .= "|";
			$report .= odbc_result($res,"SigsDeletedTotal");
			$report .= "|";
			$report .= odbc_result($res,"SigsRevertedTotal");
			$report .= "|";
			$report .= odbc_result($res,"SigsFirstNewInserted");
			$report .= "|";
			$report .= odbc_result($res,"SigsPeInserted");
			$report .= "|";
			$report .= odbc_result($res,"SigsSafeInserted");
			$report .= "|";
			$report .= odbc_result($res,"SigsCommonInserted");
			$report .= "|";
			$report .= odbc_result($res,"SigsMemoryInserted");
			$report .= "|";
			$report .= odbc_result($res,"SigsGenericInserted");
			$report .= "|";
			$report .= odbc_result($res,"DetectRoutinesInserted");
			$report .= "|";
			$report .= odbc_result($res,"TotalSamplesDetectedByPESigns");
			$report .= "\n";
		}
		return $report;
	}
	public function getReportComponentsSummary() 
	{
		$report = '';
		$query = 'SELECT * FROM "mis"."view_components" WHERE "IsActive" IS TRUE';
		$res = odbc_exec($this->db_conn, $query);
		while(odbc_fetch_row($res))
		{
			$report .= odbc_result($res,"ComponentName");
			$report .= "|";
			$report .= odbc_result($res,"ComponentVersion");
			$report .= "|";
			$report .= date("Y-m-d", strtotime(odbc_result($res,"ComponentVersionDateTime")));
			$report .= "|";
			$report .= odbc_result($res,"ScanEngineVersion");
			$report .= "|";
			$report .= odbc_result($res,"SigsBaseVersion");
			$report .= "|";
			$report .= odbc_result($res,"ComponentTypeShort");
			$report .= "|";
			$report .= odbc_result($res,"ServerName");
			$report .= "|";
			$report .= odbc_result($res,"LastAliveAgo");
			$report .= "|";
			$report .= odbc_result($res,"MaxResponseSeconds");		
			$report .= "|";
			$report .= odbc_result($res,"LastAliveSecondsAgo");
			$report .= "\n";
		}
		return $report;
	}
	public function getReportCs($from, $to) 
	{
		$report = '';
		$query = 'SELECT * FROM "mis"."get_report_components_cs"(\'' . $from . '\',\'' . $to . '\') AS 
                          ("CompanyName" varchar, "ProductName" varchar, "EngineVersion" varchar, "SigsBaseUpdate" timestamp,
                           "SpellPower" integer, "SamplesScanned" integer, "MalwaresFound" integer) ORDER BY "MalwaresFound" desc';
		$res = odbc_exec($this->db_conn, $query);
		while(odbc_fetch_row($res))
		{
			$report .= odbc_result($res,"CompanyName");
			$report .= "|";
			$report .= odbc_result($res,"ProductName");
			$report .= "|";
			$report .= odbc_result($res,"EngineVersion");
			$report .= "|";
			$report .= date("Y-m-d");//odbc_result($res,"SigsBaseUpdate");
			$report .= "|";			
			$report .= odbc_result($res,"SpellPower");
			$report .= "|";
			$report .= odbc_result($res,"SamplesScanned");
			$report .= "|";
			$report .= odbc_result($res,"MalwaresFound");
			$report .= "\n";
		}
		return $report;
	}
	public function getReportFpcs($from, $to) 
	{
		$report = '';
		$query = 'SELECT * FROM "mis"."get_report_components_fpcs"(\'' . $from . '\',\'' . $to . '\') AS ("FpcsName" varchar, "FpSigsDetected" integer)';
		$res = odbc_exec($this->db_conn, $query);
		while(odbc_fetch_row($res))
		{
			$report .= odbc_result($res,"FpcsName");
			$report .= "|";
			$report .= odbc_result($res,"FpSigsDetected");
			$report .= "\n";
		}
		return $report;
	}
	public function getReportStlt($from, $to) 
	{
		$report = '';
		$query = 'SELECT * FROM "mis"."get_report_components_stlt"(\'' . $from . '\',\'' . $to . '\') AS ("StltName" varchar, "ProcessedTotalCnt" integer,
                	"ProcessedUniqueCnt" integer, "Untrusted" integer, "TrustedSafe" integer, "TrustedSafeFpc" integer,
			"TrustedMalware" integer, "UntrustedOld" integer, "UntrustedAnalyst" integer, "UntrustedCisSuspic" integer,
			"UntrustedCisSafeFpc" integer, "TrustedSafeFpcAnal" integer, "UntrustedCisUnknown" integer,
			"UntrustedFpc" integer, "TrustedSafeFpcCrawler" integer, "UntrustedCrawler" integer, 
			"UntrustedCrawlerSuspicious" integer, "UntrustedCcsSuspicious" integer, "UntrustedFpcCcs" integer,
			"IntCisSuspiciousUntrusted" integer,"IntCisAutoUnknownUntrusted" integer,"IntCisUserUnknownUntrusted" integer)';

		$res = odbc_exec($this->db_conn, $query);
		while(odbc_fetch_row($res))
		{
			$report .= odbc_result($res,"StltName");
			$report .= "|";
			$report .= odbc_result($res,"ProcessedTotalCnt");
			$report .= "|";
			$report .= odbc_result($res,"ProcessedUniqueCnt");
			$report .= "|";
			$report .= odbc_result($res,"Untrusted");
			$report .= "|";
			$report .= odbc_result($res,"TrustedSafe");
			$report .= "|";
			$report .= odbc_result($res,"TrustedSafeFpc");
			$report .= "|";
			$report .= odbc_result($res,"TrustedMalware");
			$report .= "|";
			$report .= odbc_result($res,"UntrustedOld");
			$report .= "|";
			$report .= odbc_result($res,"UntrustedAnalyst");
			$report .= "|";
			$report .= odbc_result($res,"UntrustedCisSuspic");
			$report .= "|";
			$report .= odbc_result($res,"UntrustedCisSafeFpc");
			$report .= "|";
			$report .= odbc_result($res,"TrustedSafeFpcAnal");
			$report .= "|";
			$report .= odbc_result($res,"UntrustedCisUnknown");
			$report .= "|";
			$report .= odbc_result($res,"UntrustedFpc");
			$report .= "|";
			$report .= odbc_result($res,"TrustedSafeFpcCrawler");
			$report .= "|";
			$report .= odbc_result($res,"UntrustedCrawler");
			$report .= "|";
			$report .= odbc_result($res,"UntrustedCrawlerSuspicious");
			$report .= "|";
			$report .= odbc_result($res,"UntrustedCcsSuspicious");
			$report .= "|";
			$report .= odbc_result($res,"UntrustedFpcCcs");
			$report .= "|";
			$report .= odbc_result($res,"IntCisSuspiciousUntrusted");
			$report .= "|";
			$report .= odbc_result($res,"IntCisAutoUnknownUntrusted");
			$report .= "|";
			$report .= odbc_result($res,"IntCisUserUnknownUntrusted");
			$report .= "\n";
		}
		return $report;
	}
	public function getReportSpp($from, $to) 
	{
		$report = '';
		$query = 'SELECT * FROM "mis"."get_report_components_spp"(\'' . $from . '\',\'' . $to . '\') AS ("SppName" varchar, "TotalCnt" integer,
                	"UniqueCnt" integer, "Untrusted" integer, "TrustedSafe" integer, "TrustedSafeFpc" integer,
			"TrustedMalware" integer, "UntrustedOld" integer, "UntrustedAnalyst" integer, "UntrustedCisSuspicious" integer,
			"UntrustedCisSafeFpc" integer, "TrustedSafeFpcAnalyst" integer, "UntrustedCisUnknown" integer,
			"UntrustedFpc" integer, "TrustedSafeFpcCrawler" integer, "UntrustedCrawler" integer, "UntrustedCrawlerSuspicious" integer, "UntrustedCcsSuspicious" integer, "UntrustedFpcCcs" integer,
			"IntCisSuspiciousUntrusted" integer,"IntCisAutoUnknownUntrusted" integer,
			"IntCisUserUnknownUntrusted" integer)';
		$res = odbc_exec($this->db_conn, $query);
		while(odbc_fetch_row($res))
		{
			$report .= odbc_result($res,"SppName");
			$report .= "|";
			$report .= odbc_result($res,"TotalCnt");
			$report .= "|";
			$report .= odbc_result($res,"UniqueCnt");
			$report .= "|";
			$report .= odbc_result($res,"Untrusted");
			$report .= "|";
			$report .= odbc_result($res,"TrustedSafe");
			$report .= "|";
			$report .= odbc_result($res,"TrustedSafeFpc");
			$report .= "|";
			$report .= odbc_result($res,"TrustedMalware");
			$report .= "|";
			$report .= odbc_result($res,"UntrustedOld");
			$report .= "|";
			$report .= odbc_result($res,"UntrustedAnalyst");
			$report .= "|";
			$report .= odbc_result($res,"UntrustedCisSuspicious");
			$report .= "|";
			$report .= odbc_result($res,"UntrustedCisSafeFpc");
			$report .= "|";
			$report .= odbc_result($res,"TrustedSafeFpcAnalyst");
			$report .= "|";
			$report .= odbc_result($res,"UntrustedCisUnknown");
			$report .= "|";
			$report .= odbc_result($res,"UntrustedFpc");
			$report .= "|";
			$report .= odbc_result($res,"TrustedSafeFpcCrawler");
			$report .= "|";
			$report .= odbc_result($res,"UntrustedCrawler");
			$report .= "|";
			$report .= odbc_result($res,"UntrustedCrawlerSuspicious");
			$report .= "|";
			$report .= odbc_result($res,"UntrustedCcsSuspicious");
			$report .= "|";
			$report .= odbc_result($res,"UntrustedFpcCcs");
			$report .= "|";
			$report .= odbc_result($res,"IntCisSuspiciousUntrusted");
			$report .= "|";
			$report .= odbc_result($res,"IntCisAutoUnknownUntrusted");
			$report .= "|";
			$report .= odbc_result($res,"IntCisUserUnknownUntrusted");
			$report .= "\n";
		}
		return $report;
	}
	public function getReportCamas($from, $to) 
	{
		$report = '';
		$query = 'SELECT * FROM "mis"."get_report_components_camas"(\'' . $from . '\',\'' . $to . '\') AS (
    			"CamasName" varchar, "SamplesProcessedCnt" integer, "VerdictUndefined" integer, "VerdictAnalysing" integer,
    			"VerdictUndetected" integer, "VerdictSuspicious" integer, "VerdictSuspicious+" integer,
    			"VerdictSuspicious++" integer, "VerdictSuspiciousFamily" integer, "VerdictSuspiciousClass" integer,
    			"VerdictUnexecutable" integer, "VerdictSkippedSafe" integer, "VerdictSkippedMalware" integer,
    			"VerdictSkippedDetected" integer)';
		$res = odbc_exec($this->db_conn, $query);
		while(odbc_fetch_row($res))
		{
			$report .= odbc_result($res,"CamasName");
			$report .= "|";
			$report .= odbc_result($res,"SamplesProcessedCnt");
			$report .= "|";
			$report .= odbc_result($res,"VerdictUndefined");
			$report .= "|";
			$report .= odbc_result($res,"VerdictAnalysing");
			$report .= "|";
			$report .= odbc_result($res,"VerdictUndetected");
			$report .= "|";
			$report .= odbc_result($res,"VerdictSuspicious");
			$report .= "|";
			$report .= odbc_result($res,"VerdictSuspicious+");
			$report .= "|";
			$report .= odbc_result($res,"VerdictSuspicious++");
			$report .= "|";
			$report .= odbc_result($res,"VerdictSuspiciousFamily");
			$report .= "|";
			$report .= odbc_result($res,"VerdictSuspiciousClass");
			$report .= "|";
			$report .= odbc_result($res,"VerdictUnexecutable");
			$report .= "|";
			$report .= odbc_result($res,"VerdictSkippedSafe");
			$report .= "|";
			$report .= odbc_result($res,"VerdictSkippedMalware");
			$report .= "|";
			$report .= odbc_result($res,"VerdictSkippedDetected");
			$report .= "\n";
		}
		return $report;
	}	
	public function getReportSamplesSources($from, $to) 
	{	
		$SubmittedTotal =0;
		$SubmittedUniqueNew =0;
		$DetectedByCav =0;
		$DetectedByCs =0;
		$VerdictCamasSuspicious =0;
		$VerdictCamasSuspicious_p =0;
		$a_b =0;
		$b_c =0;
		$b_d =0;
		$report = '';
		$query = 'SELECT * FROM "mis"."get_report_samples_sources"(\'' . $from . '\',\'' . $to . '\') AS
        		("SourceName" varchar, "SubmittedTotal" integer, "SubmittedUniqueNew" integer,
        		"DetectedByCav" integer, "DetectedByCs" integer,
        		"VerdictCamasSuspicious" integer, "VerdictCamasSuspicious+" integer,"A_B" integer,"B_C" integer,"B_D" integer)';
		$res = odbc_exec($this->db_conn, $query);  
		while(odbc_fetch_row($res))
		{
			$report .= odbc_result($res,"SourceName");
			$report .= "|";
			$report .= odbc_result($res,"SubmittedTotal");
			$SubmittedTotal += odbc_result($res,"SubmittedTotal");
			$report .= "|";
			$report .= odbc_result($res,"SubmittedUniqueNew");
			$SubmittedUniqueNew += odbc_result($res,"SubmittedUniqueNew");
			$report .= "|";
			$report .= odbc_result($res,"DetectedByCav");
			$DetectedByCav += odbc_result($res,"DetectedByCav");
			$report .= "|";
			$report .= odbc_result($res,"DetectedByCs");
			$DetectedByCs += odbc_result($res,"DetectedByCs");
			$report .= "|";
			$report .= odbc_result($res,"VerdictCamasSuspicious");
			$VerdictCamasSuspicious += odbc_result($res,"VerdictCamasSuspicious");
			$report .= "|";
			$report .= odbc_result($res,"VerdictCamasSuspicious+");
			$VerdictCamasSuspicious_p += odbc_result($res,"VerdictCamasSuspicious+");
			$report .= "|";
			$report .= odbc_result($res,"A_B");
			$a_b += odbc_result($res,"A_B");
			$report .= "|";
			$report .= odbc_result($res,"B_C");
			$b_c += odbc_result($res,"B_C");
			$report .= "|";
			$report .= odbc_result($res,"B_D");
			$b_d += odbc_result($res,"B_D");
			$report .= "\n";
		}
			$report .= "Total";
			$report .= "|";
			$report .= $SubmittedTotal;
			$report .= "|";
			$report .= $SubmittedUniqueNew;
			$report .= "|";
			$report .= $DetectedByCav;
			$report .= "|";
			$report .= $DetectedByCs;
			$report .= "|";
			$report .= $VerdictCamasSuspicious;
			$report .= "|";
			$report .= $VerdictCamasSuspicious_p;
			$report .= "|";
			$report .= $a_b;
			$report .= "|";
			$report .= $b_c;
			$report .= "|";
			$report .= $b_d;
			$report .= "\n";

		return $report;
	}

	public function getReportInterfaceSources($from, $to) 
	{
		$report = '';
		$query = 'SELECT * FROM "mis"."get_report_interface_sources"(\'' . $from . '\',\'' . $to . '\') AS
        		("InterfaceName" varchar, "SubmittedTotal" integer, "SubmittedUniqueNew" integer,
        		"DetectedByCav" integer, "DetectedByCs" integer,
        		"VerdictCamasSuspicious" integer, "VerdictCamasSuspicious+" integer,"A_B" integer,"B_C" integer,"B_D" integer)';
		$res = odbc_exec($this->db_conn, $query);  
		while(odbc_fetch_row($res))
		{
			$report .= odbc_result($res,"InterfaceName");
			$report .= "|";
			$report .= odbc_result($res,"SubmittedTotal");
			$report .= "|";
			$report .= odbc_result($res,"SubmittedUniqueNew");
			$report .= "|";
			$report .= odbc_result($res,"DetectedByCav");
			$report .= "|";
			$report .= odbc_result($res,"DetectedByCs");
			$report .= "|";
			$report .= odbc_result($res,"VerdictCamasSuspicious");
			$report .= "|";
			$report .= odbc_result($res,"VerdictCamasSuspicious+");
			$report .= "\n";
		}
		return $report;
	}



  /*
    Added by Oleg Krotenko 29-Jan-2010
  */

	function conformityVendorEmailID_old ($emailid) {
	$mail = array("anthony.aykut@frame4.com","songbing@antiy.com","arthur@arcabit.com","kkoniec@arcabit.com",
			"pavel.krcma@avg.com","philipp.wolf@avira.com","andreir@checkpoint.com","mwatchinski@sourcefire.com","weipengfei@ztgame.com",
			"songliujian@viruschina.com","BONO.M@ikarus.at","chumun@incalab.com","newvirus@incalab.co.kr","gabby@iris.co.il",
			"yanshaowen@jiangmin.com","submissions@jotti.org","sheebagrace@k7computing.com","mashevsky3@kaspersky.com",
			"Yury.Mashevsky@kaspersky.com","liangjiajun@kingsoft.com","lijunfeng@kingsoft.com","tf@tomfi.net","Igor_Muttik@avertlabs.com",
			"zhouzhongyi@micropoint.cn","tony.lee@microsoft.com","gunnar.johansen@Norman.com","aku@novirusthanks.org","newvirus@incalab.co.kr",
			"Xabier.francisco@pandasecurity.com","Jerome.Segura@paretologic.com","jacques@prevx.com","ranjeet@quickheal.com","shixinfeng@snda.com",
			"nick@avresearch.net","stuart.taylor@sophos.com","dodig@sunbelt-software.com","A.Sachedina@symantec.com","lion_gu@trendmicro.com.cn",
			"yangzuohui@unnoo.com","samples@virscan.org","john.hawes@virusbtn.com","mort666@virus.org","gszappanos@virusbuster.hu",
			"andy@karimov.ru","tesket1@gmail.com","mikula@eset.sk","umesh@avlab.comodo.com","fp_cis@comodo.com","qa_cis@comodo.com","sb_cis@comodo.com",
			"ur_cis@comodo.com","crawler-dnsites@avlab.comodo.com","zhang95@ahnlab.com","bono.m@ikarus.at","spam@avlab.comodo.com",
			"mallinks@avlab.comodo.com","lcheck@avira.com","kubecj@asw.cz","camas_cloud@comodo.com","0xf30fc7_8k@gmail.com","udi@binalyze.com",
			"hivesoft@hotmail.com","milosz.czernikowski@gmail.com");
	$vendor = array("Anthony","Antiy_AVL","Arcabit","Arcabit","AVG Anti-Virus","Avira","Checkpoint","ClamAV","Gaint",
			"Greatsoft","Ikarus","Incalab", "nProtect","IRIS","JiangMin Antivirus Software","Jotti.Org","K7 Computing","Karspersky",
			"Kaspersky","Kingsoft","Kingsoft","Malcode BFK","Mcafee","Micropoint","Microsoft","Norman","NoVirusThanks.Org","Nprotect",
			"Panda","Paretologic","Prevx","QuickHeal","Shengda","SonicWALL","Sophos","Sunbelt","Symantec","Trendmicro","UNNOO","Virscan.Org",
			"Virus Bulletin","Virus.Org","Virusbuster","ProAntivirus","Thomas Sloth","ESet-NOD32","npogroups.org","CIS False-Positive",
			"CIS Quarantine","CIS SandBox","CIS User Submitted","Download-Sites","AhnLab","Ikarus","Spam Mails","Malware Links Mails",
			"Malware Links(Avira)","Avast","CAMAS-Cloud","Andreas","binalyze","hivesoft","Morphiusz");
	$vendorEmailId= array_combine($mail,$vendor);
	if (array_key_exists($emailid, $vendorEmailId)){
		return $vendorEmailId[$emailid];
		}
	else{$text1 = explode("@",$emailid);
			$text2 = explode(".",$text1[1]);
			return $text2[0];}
	}


/*Canged 2011-03-25 Oleg Krotenko*/
	function conformityVendorEmailID($emailid) {
	    $src = "email_exception.txt";
	    $vendorEmailId= array();
	    $mail = array();
	    $vendor =array();
	if(file_exists($src)){
	    $handle = fopen($src, "r");
	while (!feof($handle)) {
		$buffer = explode(";",trim(fgets($handle, 4096)));
	      array_push($mail,$buffer[0]);
	      array_push($vendor,$buffer[1]);
	    }
	    fclose($handle);
	    $vendorEmailId= array_combine($mail,$vendor);

	if (array_key_exists($emailid, $vendorEmailId)){
		return $vendorEmailId[$emailid];
		}
	else{$text1 = explode("@",$emailid);
			$text2 = explode(".",$text1[1]);
			return $text2[0];}
	}
	}


	public function getReportSamplesSubmitterEmails($from, $to) 
	{
		$report = '';
		$query = 'SELECT * FROM "mis"."get_report_samples_submitter_emails"(\'' . $from . '\',\'' . $to . '\') AS
        		("SubmitterEmail" varchar, "SubmittedTotal" integer, "SubmittedUniqueNew" integer,
        		"DetectedByCav" integer, "DetectedByCs" integer,
        		"VerdictCamasSuspicious" integer, "VerdictCamasSuspicious+" integer)';
		$res = odbc_exec($this->db_conn, $query);  
		while(odbc_fetch_row($res))
		{	
			$report .= $this->conformityVendorEmailID(odbc_result($res,"SubmitterEmail"));
			$report .= "|";
			$report .= odbc_result($res,"SubmitterEmail");
			$report .= "|";
			$report .= odbc_result($res,"SubmittedTotal");
			$report .= "|";
			$report .= odbc_result($res,"SubmittedUniqueNew");
			$report .= "|";
			$report .= odbc_result($res,"DetectedByCav");
			$report .= "|";
			$report .= odbc_result($res,"DetectedByCs");
			$report .= "|";
			$report .= odbc_result($res,"VerdictCamasSuspicious");
			$report .= "|";
			$report .= odbc_result($res,"VerdictCamasSuspicious+");
			$report .= "\n";
		}
		return $report;
	}

	
	public function getViewSamplesComodoSigned($from, $to) 
	{
		$report = '';
		$query = 'SELECT * FROM "mis"."view_samples_signed" 
			WHERE "FirstCatchedDateTime" BETWEEN \'' . $from . '\' AND \'' . $to . '\' AND
                        ("IssuerName" IN (\'UTN-USERFirst-Object\', \'AddTrust Qualified CA Root\') OR
                         "IssuersIssuerName" IN (\'UTN-USERFirst-Object\', \'AddTrust Qualified CA Root\'))';		
		$res = odbc_exec($this->db_conn, $query);
    		odbc_binmode($res, ODBC_BINMODE_RETURN); 
		while(odbc_fetch_row($res))
		{		
			$report .= odbc_result($res,"SampleId");
			$report .= "|";			
			$report .= odbc_result($res,"SampleSha1");
			$report .= "|";
			$report .= odbc_result($res,"VerdictName");
			$report .= "|";
			$report .= odbc_result($res,"SerialNumber");
			$report .= "|";
			$report .= odbc_result($res,"RevokeVotesCnt");
			$report .= "|";
			$report .= odbc_result($res,"IsValidOverall");
			$report .= "|";
			$report .= odbc_result($res,"ReasonOfInvalidity");
			$report .= "\n";
		}
		return $report;
	}
	public function getViewSamplesSigned($from, $to) 
	{
		$report = '';
		$query = 'SELECT * FROM "mis"."view_samples_signed" 
			WHERE "FirstCatchedDateTime" BETWEEN \'' . $from . '\' AND \'' . $to . '\' AND
                        ("IssuerName" NOT IN (\'UTN-USERFirst-Object\', \'AddTrust Qualified CA Root\') AND
                         "IssuersIssuerName" NOT IN (\'UTN-USERFirst-Object\', \'AddTrust Qualified CA Root\'))';		
		$res = odbc_exec($this->db_conn, $query);
    		odbc_binmode($res, ODBC_BINMODE_RETURN); 
		while(odbc_fetch_row($res))
		{		
			$report .= odbc_result($res,"SampleId");
			$report .= "|";			
			$report .= odbc_result($res,"SampleSha1");
			$report .= "|";
			$report .= odbc_result($res,"VerdictName");
			$report .= "|";
			$report .= odbc_result($res,"IssuerName");
			$report .= "|";
			$report .= odbc_result($res,"IssuersIssuerName");
			$report .= "|";
			$report .= odbc_result($res,"SerialNumber");
			$report .= "|";
			$report .= odbc_result($res,"RevokeVotesCnt");
			$report .= "|";
			$report .= odbc_result($res,"IsValidOverall");
			$report .= "|";
			$report .= odbc_result($res,"ReasonOfInvalidity");
			$report .= "\n";
		}
		return $report;
	}




	public function getViewSamplesSignedSummary($from, $to) 
	{
		$report = '';
		$query = 'SELECT "IssuerName", count("SampleId") as "SamplesSignedTotalCnt", 
			COUNT(CASE WHEN "VerdictName" = \'Malware\' THEN TRUE ELSE NULL END)::integer AS "SamplesSignedMalwareCnt",
			COUNT(distinct("SerialNumber")) AS "UniqueSerialsCnt",
			a."UniqueSerialsNotRevokedCnt"
			FROM "mis"."view_samples_signed"
			LEFT JOIN
			(SELECT "IssuerName", COUNT(DISTINCT "SerialNumber") as "UniqueSerialsNotRevokedCnt" FROM mis.view_samples_signed
			WHERE "IsRevoked" IS FALSE AND "FirstCatchedDateTime" BETWEEN \'' . $from . '\' AND \'' . $to . '\'
			GROUP BY "IssuerName") a USING("IssuerName")
			WHERE "FirstCatchedDateTime" BETWEEN \'' . $from . '\' AND \'' . $to . '\'
			GROUP BY "IssuerName", a."UniqueSerialsNotRevokedCnt"';
		$res = odbc_exec($this->db_conn, $query);
    		odbc_binmode($res, ODBC_BINMODE_RETURN); 
		while(odbc_fetch_row($res))
		{		
			$report .= odbc_result($res,"IssuerName");
			$report .= "|";			
			$report .= odbc_result($res,"SamplesSignedTotalCnt");
			$report .= "|";
			$report .= odbc_result($res,"SamplesSignedMalwareCnt");
			$report .= "|";
			$report .= odbc_result($res,"UniqueSerialsCnt");
			$report .= "|";
			$report .= odbc_result($res,"UniqueSerialsNotRevokedCnt");
			$report .= "\n";
		}
		return $report;
	}

	public function getReportSamplesCsVsSigs($from, $to) 
	{
		$report = '';
		$query = 'SELECT * FROM "mis"."get_report_samples_cs_vs_sigs"(\'' . $from . '\',\'' . $to . '\') as ("CsVendorName" varchar, "MalwareName" varchar, "SamplesCnt" integer, "DetectedByPeSigs" integer, "NotDetectedByPeSigs" integer, "DetectedByFirstSigs" integer, "PeSigs" integer)';
		$res = odbc_exec($this->db_conn, $query);  
		while(odbc_fetch_row($res))
		{
			$report .= odbc_result($res,"CsVendorName");
			$report .= "|";
			$report .= odbc_result($res,"MalwareName");
			$report .= "|";
			$report .= odbc_result($res,"SamplesCnt");
			$report .= "|";
			$report .= odbc_result($res,"DetectedByFirstSigs");
			$report .= "|";
			$report .= odbc_result($res,"DetectedByPeSigs");
			$report .= "|";
			$report .= odbc_result($res,"NotDetectedByPeSigs");
			$report .= "|";
			$report .= odbc_result($res,"PeSigs");
			$report .= "\n";
		}
		return $report;
	}

	public function getReportSamplesCsVsSigsvt($from, $to) 
	{
		$report = '';
		$query = 'SELECT * FROM mis.get_report_samples_cs_vs_sigsvt(\'' . $from . '\',\'' . $to . '\') AS("CsVendorName" varchar, "MalwareName" varchar, "SamplesCnt" integer, 
			    "DetectedByPeSigs" integer, "NotDetectedByPeSigs" integer, "DetectedByFirstSigs" integer, "PeSigs" integer)';
		$res = odbc_exec($this->db_conn, $query);  
		while(odbc_fetch_row($res))
		{
			$report .= odbc_result($res,"CsVendorName");
			$report .= "|";
			$report .= odbc_result($res,"MalwareName");
			$report .= "|";
			$report .= odbc_result($res,"SamplesCnt");
			$report .= "|";
			$report .= odbc_result($res,"DetectedByFirstSigs");
			$report .= "|";
			$report .= odbc_result($res,"DetectedByPeSigs");
			$report .= "|";
			$report .= odbc_result($res,"NotDetectedByPeSigs");
			$report .= "|";
			$report .= odbc_result($res,"PeSigs");
			$report .= "\n";
		}
		return $report;
	}

	public function getreportDSSDetectingConflict($from, $to) 
	{
		$report = '';
		$query = 'SELECT * FROM "mis"."get_report_dss_detecting_conflict"(\'' . $from . '\',\'' . $to . '\')  as (
				"AnalistCreatId" character varying(50),
				"AnalistSubmId" character varying(50),
				"TimeSigCreat" timestamp without time zone,
				"SafeSigsId" integer,
				"AnalistDelId" character varying(50),
				"TimeSigDel" timestamp without time zone)';
		$res = odbc_exec($this->db_conn, $query);  
		while(odbc_fetch_row($res))
		{
			$report .= odbc_result($res,"AnalistCreatId");
			$report .= "|";
			$report .= odbc_result($res,"AnalistSubmId");
			$report .= "|";
			$report .= odbc_result($res,"TimeSigCreat");
			$report .= "|";
			$report .= odbc_result($res,"SafeSigsId");
			$report .= "|";
			$report .= odbc_result($res,"AnalistDelId");
			$report .= "|";
			$report .= odbc_result($res,"TimeSigDel");
			$report .= "\n";
		}
		return $report;
	}
	public function getreportDSSNOTDetectingConflict($from, $to) 
	{
		$report = '';
		$query = 'SELECT * FROM "mis"."get_report_dss_not_detecting_conflict"(\'' . $from . '\',\'' . $to . '\') as (
			    "AnalistCreatId" character varying(50),
			    "AnalistSubmId" character varying(50),
			    "TimeSigCreat" timestamp without time zone,
			    "SafeSigsId" integer,
			    "AnalistDelId" character varying(50),
			    "TimeSigDel" timestamp without time zone)';
		$res = odbc_exec($this->db_conn, $query);  
		while(odbc_fetch_row($res))
		{
			$report .= odbc_result($res,"AnalistCreatId");
			$report .= "|";
			$report .= odbc_result($res,"AnalistSubmId");
			$report .= "|";
			$report .= odbc_result($res,"TimeSigCreat");
			$report .= "|";
			$report .= odbc_result($res,"SafeSigsId");
			$report .= "|";
			$report .= odbc_result($res,"AnalistDelId");
			$report .= "|";
			$report .= odbc_result($res,"TimeSigDel");
			$report .= "\n";
		}
		return $report;
	}
	public function getReportDMSConfirmedFP($from, $to) 
	{
		$report = '';
		$query = 'SELECT * FROM "mis"."get_report_dms_confirmed_fp"(\'' . $from . '\',\'' . $to . '\') as 
			  ("Sha1" varchar,
			    "AnalistSubm" character varying(50),"TimeSubmitSample" timestamp without time zone,
			    "MalvareSignId" integer,
			    "AnalistCreateMal" character varying(50),"TimeCreateMalId" timestamp without time zone,
			    "AnalistDelMal" character varying(50),"TimeMalwareSigDel" timestamp without time zone,
			    "TypeOfSign" character varying(50),"FPFixType" interval )';
		$res = odbc_exec($this->db_conn, $query);  
		while(odbc_fetch_row($res))
		{

			$report .= odbc_result($res,"Sha1");
			$report .= "|";
			$report .= odbc_result($res,"AnalistSubm");
			$report .= "|";
			$report .= odbc_result($res,"TimeSubmitSample");
			$report .= "|";
			$report .= odbc_result($res,"MalvareSignId");
			$report .= "|";
			$report .= odbc_result($res,"AnalistCreateMal");
			$report .= "|";
			$report .= odbc_result($res,"TimeCreateMalId");
			$report .= "|";
			$report .= odbc_result($res,"AnalistDelMal");
			$report .= "|";
			$report .= odbc_result($res,"TimeMalwareSigDel");
			$report .= "|";
			$report .= odbc_result($res,"TypeOfSign");
			$report .= "|";
			$report .= odbc_result($res,"FPFixType");
			$report .= "\n";
		}
		return $report;
	}
	public function getReportFPFoundConfirMalware($from, $to) 
	{
		$report = '';
		$query = 'SELECT * FROM "mis"."get_report_fp_found_confir_malware"(\'' . $from . '\',\'' . $to . '\') as ("Sha1" varchar,
			"AnalistSubmId" integer,"TimeSubmitSample" timestamp without time zone,
			"AnalistLockMalId" integer,"TimeMalwareSigLock" timestamp without time zone,
			"SignId" integer,"Malwname" character varying(250),"TypeOfSign" character varying(50),
			"TimeCreateSignId" timestamp without time zone, "TimeUpdtSignId" timestamp without time zone,
			"FPFixType" interval)';
		$res = odbc_exec($this->db_conn, $query);  
		while(odbc_fetch_row($res))
		{
			$report .= odbc_result($res,"Sha1");
			$report .= "|";
			$report .= odbc_result($res,"AnalistSubmId");
			$report .= "|";
			$report .= odbc_result($res,"TimeSubmitSample");
			$report .= "|";
			$report .= odbc_result($res,"AnalistLockMalId");
			$report .= "|";
			$report .= odbc_result($res,"TimeMalwareSigLock");
			$report .= "|";
			$report .= odbc_result($res,"SignId");
			$report .= "|";
			$report .= odbc_result($res,"Malwname");
			$report .= "|";
			$report .= odbc_result($res,"TypeOfSign");
			$report .= "|";
			$report .= odbc_result($res,"TimeCreateSignId");
			$report .= "|";
			$report .= odbc_result($res,"TimeUpdtSignId");
			$report .= "|";
			$report .= odbc_result($res,"FPFixType");
			$report .= "\n";
		}
		return $report;
	}

	public function getReportFPFoundConfirSafe($from, $to) 
	{
		$report = '';
		$query = 'SELECT * FROM "mis"."get_report_fp_found_confir_safe"(\'' . $from . '\',\'' . $to . '\') AS(
			  "Sha1" varchar,
			  "AnalistSubmId" character varying(50),
			  "TimeSubmitSample" timestamp without time zone,
			  "AnalistLockId" character varying(50),
			  "TimeMarkSafe" timestamp without time zone,
			  "SignId" integer,
			  "Signame" character varying(250),
			  "TypeOfSign" character varying(50),
			  "TimeUpdtSignId" timestamp without time zone,
			  "FPFixType" interval,
			  "FPType" character varying(150))';
		$res = odbc_exec($this->db_conn, $query);  
		while(odbc_fetch_row($res))
		{
			$report .= odbc_result($res,"Sha1");
			$report .= "|";
			$report .= odbc_result($res,"AnalistSubmId");
			$report .= "|";
			$report .= odbc_result($res,"TimeSubmitSample");
			$report .= "|";
			$report .= odbc_result($res,"AnalistLockId");
			$report .= "|";
			$report .= odbc_result($res,"TimeMarkSafe");
			$report .= "|";
			$report .= odbc_result($res,"SignId");
			$report .= "|";
			$report .= odbc_result($res,"Signame");
			$report .= "|";
			$report .= odbc_result($res,"TypeOfSign");
			$report .= "|";
			//$report .= odbc_result($res,"TimeCreateSignId");
			//$report .= "|";
			$report .= odbc_result($res,"TimeUpdtSignId");
			$report .= "|";
			$report .= odbc_result($res,"FPFixType");
			$report .= "|";
			$report .= odbc_result($res,"FPType");
			$report .= "\n";
		}
		return $report;
	}
	public function getPendingFps($from, $to)
	{
		$report = '';
/*		  $query = 'SELECT * FROM "mis"."view_pending_fps" where "Submission" BETWEEN \''.$from.'\' and \''.$to.'\'
			      group by "FPC Interface","Sample\'s Source Name","Sample\'s SHA1Checksum","File Name",
					"CursePoint","Submission","SubmitterEmail","Detection Name by CAV"';		
*/
		$query = "SELECT * FROM mis.get_report_pending_fps('$from','$to') AS(
			  \"FPC Interface\" character varying(50),\"Sample's Source Name\" character varying(50),
			  \"Sample's SHA1Checksum\" character(40),\"File Name\" character varying(256), 
			  \"CursePoint\" integer,\"Submission\" timestamp without time zone,
			  \"SubmitterEmail\" character varying(256),\"Detection Name by CAV\" character varying)";
		$res = odbc_exec($this->db_conn, $query);
    		odbc_binmode($res, ODBC_BINMODE_RETURN); 
		while(odbc_fetch_row($res))
		{		
			$report .= odbc_result($res,"FPC Interface");
			$report .= "|";			
			$report .= odbc_result($res,"Sample's Source Name");
			$report .= "|";
			$report .= odbc_result($res,"Sample's SHA1Checksum");
			$report .= "|";
			$report .= odbc_result($res,"File Name");
			$report .= "|";
			$report .= odbc_result($res,"CursePoint");
			$report .= "|";
			$report .= odbc_result($res,"Submission");
			$report .= "|";
			$report .= odbc_result($res,"SubmitterEmail");
			$report .= "|";
			$report .= odbc_result($res,"Detection Name by CAV");
			$report .= "\n";
		}
		return $report;
	}


	public function getViewAnalysts() 
	{
		$report = '';
		$query = 'SELECT * FROM "mis"."view_analysts"';		
		$res = odbc_exec($this->db_conn, $query);
    		odbc_binmode($res, ODBC_BINMODE_RETURN); 
		while(odbc_fetch_row($res))
		{		
			$report .= odbc_result($res,"UserId");
			$report .= "|";			
			$report .= odbc_result($res,"UserName");
			$report .= "|";
			$report .= odbc_result($res,"UserPassword");
			$report .= "|";
			$report .= odbc_result($res,"AnalystFirstName");
			$report .= "|";
			$report .= odbc_result($res,"AnalystLastName");
			$report .= "|";
			$report .= odbc_result($res,"AnalystBirthDate");
			$report .= "|";
			$report .= odbc_result($res,"AnalystEmail");
			//$report .= "|";
			//$report .= odbc_result($res,"AnalystPhoto");
			$report .= "|";
			$report .= odbc_result($res,"LabId");
			$report .= "|";
			$report .= odbc_result($res,"IsSuperanalyst");
			$report .= "\n";
		}
		return $report;
	}
	public function analystsUpsert($from) 
	{
		$report = '';
		$query = 'SELECT * FROM "analysts_upsert"(' . $from . ')';
		$res = odbc_exec($this->db_conn, $query);		
		return $report;
	}

	public function geReportSignchecker($from, $to){
	    $fromTime = strtotime($from);
	    $fromTime = date("Y-m-d H:i:s", $fromTime);
	    $toTime = strtotime($to);
	    $toTime = date("Y-m-d H:i:s", $toTime);
	    $report = '';
		$query = "SELECT * FROM \"mis\".\"get_report_sign_checker\"('$fromTime','$toTime') 
			    AS(\"Sha1\" character(40),\"SigId\" integer[])";
		$res = odbc_exec($this->db_conn, $query);  
		while(odbc_fetch_row($res))
		{
			$report .= odbc_result($res,"Sha1");
			$report .= "|";	
			$report .= odbc_result($res,"SigId");
			$report .= "\n";
		}
	
	    return $report;
	}

	public function getReportFlsTest($from,$to,$sigid,$top){
	    $fromTime = strtotime($from);
	    $fromTime = date("Y-m-d", $fromTime);
	    $toTime = strtotime($to);
	    $toTime = date("Y-m-d", $toTime);
  	    $report='';
	    $query ='';
	    if($sigid=='all'){
	    $query = "select * from mis.get_report_zero_false_positive('$fromTime','$toTime',$top) as (\"Guid\" character(32), \"SigId\" integer, \"SampleSha1\" character(40),
		\"FilePath\" character varying(1000), \"IsHidden\" boolean, 
		\"CallerTypeId\" integer, \"IsSigned\" boolean,\"CursePoints\" integer,\"CAV_Heur_Name\" character varying(40),
		\"CAV_Heur_Level\" character varying(40),\"RPDTC_Verdict\" character varying(40),\"LastSectionEntropy\" real, 
		\"LastSectionAttributes\" integer,\"EpSectionEntropy\" real, \"EpSectionAttributes\" integer, 
		\"Peid_Entropy\" real, \"Peid_Packer\" character varying(100), \"AddedTs\" timestamp without time zone)";
	    }
	    else{
	    $query = "select * from mis.get_report_zero_false_positive('$fromTime','$toTime',$top,$sigid) as (\"Guid\" character(32), \"SigId\" integer, \"SampleSha1\" character(40),
		\"FilePath\" character varying(1000), \"IsHidden\" boolean, 
		\"CallerTypeId\" integer, \"IsSigned\" boolean,\"CursePoints\" integer,\"CAV_Heur_Name\" character varying(40),
		\"CAV_Heur_Level\" character varying(40),\"RPDTC_Verdict\" character varying(40),\"LastSectionEntropy\" real, 
		\"LastSectionAttributes\" integer,\"EpSectionEntropy\" real, \"EpSectionAttributes\" integer, 
		\"Peid_Entropy\" real, \"Peid_Packer\" character varying(100), \"AddedTs\" timestamp without time zone)";

	    }
	    $res = odbc_exec($this->db_conn, $query);
		while(odbc_fetch_row($res))
		{
			$report .= odbc_result($res,"Guid");
			$report .= "|";	
			$report .= odbc_result($res,"SigId");
			$report .= "|";	
			$report .= odbc_result($res,"SampleSha1");
			$report .= "|";	
			if(odbc_result($res,"FilePath")){$fname = explode("\\",odbc_result($res,"FilePath"));
			$report .= $fname[count($fname)-1];}
			else {$report .= "";}
			$report .= "|";	
			$report .= odbc_result($res,"FilePath");
			$report .= "|";	
			if(odbc_result($res,"IsHidden")==1){$report .= 'true';}
			else{$report .= 'false';}
			$report .= "|";	
			switch(odbc_result($res,"CallerTypeId")){
			case 1:
			      $report .= "On-Access";
			      break;
			case 2:
			      $report .= "On-Demand";
			      break;
			case 3:
			      $report .= "Sandbox";
			      break;
			default:
			      $report .= odbc_result($res,"CallerTypeId");
			      break;
			}
			$report .= "|";	
			if(odbc_result($res,"IsSigned")==1){$report .= 'true';}
			else{$report .= 'false';}
			$report .= "|";	
			$report .= odbc_result($res,"CursePoints");
			$report .= "|";	
			$report .= odbc_result($res,"CAV_Heur_Name");
			$report .= "|";	
			$report .= odbc_result($res,"CAV_Heur_Level");
			$report .= "|";	
			$report .= odbc_result($res,"RPDTC_Verdict");
			$report .= "|";	
			$report .= odbc_result($res,"LastSectionEntropy");
			$report .= "|";	
			$report .= odbc_result($res,"LastSectionAttributes");
			$report .= "|";	
			$report .= odbc_result($res,"EpSectionEntropy");
			$report .= "|";	
			$report .= odbc_result($res,"EpSectionAttributes");
			$report .= "|";	
			$report .= odbc_result($res,"Peid_Entropy");
			$report .= "|";	
			$report .= odbc_result($res,"Peid_Packer");
			$report .= "|";	
			if(preg_match("/program files/i",odbc_result($res,"FilePath")) ||preg_match("/program files (x86)/i",odbc_result($res,"FilePath")))
			{$prog= 'true';}
			else{$prog = 'false';}
			$report .= $prog;
			$report .= "|";	
			if(preg_match("/windows/i",odbc_result($res,"FilePath"))){$win= 'true';}
			else{$win = 'false';}
			$report .= $win;
			$report .= "|";	
			if ($prog =='false' || $win == 'false'){$nostand = 'true';}
			else{$nostand = 'false';}
			$report .= $nostand;
			$report .= "|";	
			if(odbc_result($res,"Peid_Entropy")>=7){
			$report .= 'true';}
			else{$report .= 'false';}
			$report .= "\n";
		}

	    return $report;


	}


	public function geReportFLS($from, $to,$top){
	    $fromTime = strtotime($from);
	    $fromTime = date("Y-m-d H:i:s", $fromTime);
	    $toTime = strtotime($to);
	    $toTime = date("Y-m-d H:i:s", $toTime);
	    $report = '';
		$query = "SELECT * FROM mis.get_report_fls_sigid_verdict('$fromTime','$toTime') AS(
	\"SigId\" integer,  \"SigFormatName\" character varying(50) , \"Malwarename\" character varying(250) ,\"Sign Creator Analyst Name\" character varying (50),
	\"Verdict_Absent\" bigint , \"Verdict_Absent Uniq\" bigint ,\"Safe\" bigint , \"Uniq Safe\" bigint ,
	\"Malware\" bigint , \"Uniq Malware\" bigint , \"Unknown\" bigint , \"Uniq Unknown\" bigint ,\"Guid Uniq\" bigint ,
	\"Ips Uniq\" bigint ,\"FirstSeen\" timestamp without time zone ,\"LastSeen\" timestamp without time zone ,
	\"Total Caught\" bigint ,\"Total Caught Uniq\" bigint) limit $top";
		$i = 0;
		$res = odbc_exec($this->db_conn, $query);  
		while(odbc_fetch_row($res))
		{
			$i++;
			$report .= "$i";	
			$report .= "|";	
			$report .= odbc_result($res,"SigId");
			$report .= "|";	
			$report .= odbc_result($res,"SigFormatName");	
			$report .= "|";	
			$report .= odbc_result($res,"Verdict_Absent");	
			$report .= "|";	
			$report .= odbc_result($res,"Verdict_Absent Uniq");	
			$report .= "|";	
			$report .= odbc_result($res,"Safe");
			$report .= "|";	
			$report .= odbc_result($res,"Uniq Safe");
			$report .= "|";	
			$report .= odbc_result($res,"Malware");
			$report .= "|";	
			$report .= odbc_result($res,"Uniq Malware");
			$report .= "|";	
			$report .= odbc_result($res,"Unknown");
			$report .= "|";	
			$report .= odbc_result($res,"Uniq Unknown");
			$report .= "|";	
			$report .= odbc_result($res,"Total Caught");
			$report .= "|";	
			$report .= odbc_result($res,"Total Caught Uniq");
			$report .= "|";	
			$report .= odbc_result($res,"Guid Uniq");
			$report .= "|";	
			$report .= odbc_result($res,"Ips Uniq");
			$report .= "|";	
			$report .= odbc_result($res,"FirstSeen");	
			$report .= "|";	
			$report .= odbc_result($res,"LastSeen");	
			$report .= "\n";
		}
	
	    return $report;
	}
	
	public function CSFamilyToAnalysts() 
	{
		$report = '';
		//$query = 'SELECT * FROM "analysts_upsert"(' . $from . ')';
		$query = 'SELECT * FROM mis.view_analyst_cs_families';
		$res = odbc_exec($this->db_conn, $query);
    while(odbc_fetch_row($res))
		{
			$report .= odbc_result($res,"AnalystCsFamilyId");
			$report .= "|";			
			$report .= odbc_result($res,"AnalystName");
			$report .= "|";
			$report .= odbc_result($res,"CsVendorName");
			$report .= "|";
			$report .= 'FAMILY NAME';
			$report .= "|";
			$report .= 'ACTIVE';
			$report .= "|";
			$report .= 'COMMENT';
			$report .= "\n";
		}
		return $report;
	}
//FVS classifire start

	public function get_FVS_Classifier($from,$to,$src){
		$report = '';
		$malic;$safe;$suspic;$unknown;$conflict;$row_total;$camdpams_total;
		$i=0;
		if($src=='All'){
		      $query = "select * from mis.fvs_class('$from','$to') as (\"FVS Malware\" bigint, \"FVS Safe\" bigint ,\"FVS Undefined\" bigint)";
		}
		else{
		      $query = "select * from mis.fvs_class_camas('$from','$to') as (\"FVS Malware\" bigint, \"FVS Safe\" bigint ,\"FVS Undefined\" bigint)";
		}
		$res = odbc_exec($this->db_conn,$query);
		while(odbc_fetch_row($res)){

			$row_total = 0;
			switch($i){
			  case 0:
			   $report .= "CAMDPAMS Safe";
			   $report .= "|";
			  break;
			  case 1:
			   $report .= "CAMDPAMS Malware";
			   $report .= "|";
			  break;
			  case 2:
			   $report .= "CAMDPAMS Conflict";
			   $report .= "|";
			  break;
			  case 3:
			   $report .= "CAMDPAMS Unknown";
			   $report .= "|";
			  break;
			}
			$report .= odbc_result($res,"FVS Malware");
			$report .= "|";			
			$report .= odbc_result($res,"FVS Safe");
			$report .= "|";
			$report .= odbc_result($res,"FVS Undefined");
			$report .= "|";
			$report .= "\n";
			$i++;
		}
		return $report;
	}


	public function get_FVS_Classifier_enhanced($from, $to, $src){
		$report = '';
		$i=0;
		$rest;
		if($src=='All'){
		    $query = "select * from mis.fvs_class_enhanced('$from','$to') as (\"Alone\" bigint,\"Suspicious\" bigint, \"Suspicious+\" bigint,
				      \"Suspicious++\" bigint, \"cnt\" bigint)";
		}
		else{
  		    $query = "select * from mis.fvs_class_camas_enhanced('$from','$to') as (\"Alone\" bigint,\"Suspicious\" bigint, \"Suspicious+\" bigint,
				      \"Suspicious++\" bigint, \"cnt\" bigint)";
		}
		$res = odbc_exec($this->db_conn,$query);
		while(odbc_fetch_row($res)){
			
			$row_total = 0;
			switch($i){
			  case 0:
			   $report .= "FVS Malware";
			   $report .= "|";
			  break;
			  case 1:
			   $report .= "FVS Safe";
			   $report .= "|";
			  break;
			  case 2:
			   $report .= "FVS Undefined";
			   $report .= "|";
			  break;
			}
			$rest=0;
			$cnt = odbc_result($res,"cnt");
			if($cnt > 0 ){}
			
			$report .= odbc_result($res,"Alone");
			$report .= "|";			
			$report .= odbc_result($res,"Suspicious");
			$report .= "|";
			$report .= odbc_result($res,"Suspicious+");
			$report .= "|";
			$report .= odbc_result($res,"Suspicious++");
			$report .= "|";
			$report .= $cnt;
			$report .= "\n";
			
			$i++;
		
		}

		return $report;

	}





//FVS classifre end





	public function get_RPDTC_Classifier($from,$to,$src){
		$report = '';
		$malic;$safe;$suspic;$unknown;$conflict;$row_total;$camdpams_total;
		$i=0;
		if($src=='All'){
		      $query = "select * from mis.rpdtc_class('$from','$to') as (\"RPDTC Malicious\" bigint,\"RPDTC Safe\" bigint,\"RPDTC Suspicious\" bigint,
			    \"RPDTC Unknown\" bigint,\"RPDTC Conflict\" bigint)";
		}
		else{
		      $query = "select * from mis.rpdtc_class_camas('$from','$to') as (\"RPDTC Malicious\" bigint,\"RPDTC Safe\" bigint,\"RPDTC Suspicious\" bigint,
			    \"RPDTC Unknown\" bigint,\"RPDTC Conflict\" bigint)";
		}
		$res = odbc_exec($this->db_conn,$query);
		while(odbc_fetch_row($res)){
			
			$row_total = 0;
			switch($i){
			  case 0:
			   $report .= "Malware Count";
			   $report .= "|";
			  break;
			  case 1:
			   $report .= "Safe Count";
			   $report .= "|";
			  break;
			  case 2:
			   $report .= "Conflict Count";
			   $report .= "|";
			  break;
			  case 3:
			   $report .= "Unknown Count";
			   $report .= "|";
			  break;
			}
			$report .= odbc_result($res,"RPDTC Malicious");
			$malic += odbc_result($res,"RPDTC Malicious");
			$row_total += odbc_result($res,"RPDTC Malicious");
			$report .= "|";			
			$report .= odbc_result($res,"RPDTC Safe");
			$safe += odbc_result($res,"RPDTC Safe");
			$row_total += odbc_result($res,"RPDTC Safe");
			$report .= "|";
			$report .= odbc_result($res,"RPDTC Suspicious");
			$suspic += odbc_result($res,"RPDTC Suspicious");
			$row_total += odbc_result($res,"RPDTC Suspicious");
			$report .= "|";
			$report .= odbc_result($res,"RPDTC Unknown");
			$unknown += odbc_result($res,"RPDTC Unknown");
			$row_total += odbc_result($res,"RPDTC Unknown");
			$report .= "|";
			$report .= odbc_result($res,"RPDTC Conflict");
			$conflict += odbc_result($res,"RPDTC Conflict");
			$row_total += odbc_result($res,"RPDTC Conflict");
			$report .= "|";
			$report .= $row_total;
			$camdpams_total += $row_total;
			$report .= "\n";
			$i++;
		}
			$report .= "Total";
			$report .= "|";
			$report .= $malic;
			$report .= "|";			
			$report .= $safe;
			$report .= "|";
			$report .= $suspic;
			$report .= "|";
			$report .= $unknown;
			$report .= "|";
			$report .= $conflict;
			$report .= "|";
			$report .= $camdpams_total;
			$report .= "\n";
		
		return $report;
	}

	public function get_RPDTC_Classifier_enhanced($from, $to, $src){
		$report = '';
		$i=0;
		$rest;
		if($src=='All'){
		    $query = "select * from mis.rpdtc_class_enhanced_new('$from','$to') as (\"Alone\" bigint,\"Suspicious\" bigint, \"Suspicious+\" bigint,
				      \"Suspicious++\" bigint, \"cnt\" bigint)";
		}
		else{
  		    $query = "select * from mis.rpdtc_class_camas_enhanced_new('$from','$to') as (\"Alone\" bigint,\"Suspicious\" bigint, \"Suspicious+\" bigint,
				      \"Suspicious++\" bigint, \"cnt\" bigint)";
		}
		$res = odbc_exec($this->db_conn,$query);
		while(odbc_fetch_row($res)){
			
			$row_total = 0;
			switch($i){
			  case 0:
			   $report .= "Malware Count";
			   $report .= "|";
			  break;
			  case 1:
			   $report .= "Safe Count";
			   $report .= "|";
			  break;
			  case 2:
			   $report .= "Conflict Count";
			   $report .= "|";
			  break;
			  case 3:
			   $report .= "Unknown Count";
			   $report .= "|";
			  break;
			}
			$rest=0;
			$cnt = odbc_result($res,"cnt");
			if($cnt > 0 ){
			
			$rest = abs(odbc_result($res,"cnt")- odbc_result($res,"Alone")-odbc_result($res,"Suspicious")-odbc_result($res,"Suspicious+")
				-odbc_result($res,"Suspicious++"));}
			$report .= odbc_result($res,"Alone");
			$report .= "|";			
			$report .= odbc_result($res,"Suspicious");
			$report .= "|";
			$report .= odbc_result($res,"Suspicious+");
			$report .= "|";
			$report .= odbc_result($res,"Suspicious++");
			$report .= "|";
		//	$report .= $rest;
			$report .= $cnt;
			$report .= "\n";
			$i++;
		}

		return $report;


	}

	public function geReportCavHeur($from, $to){
	    $report = '';
		$query = "SELECT * FROM mis.get_report_cav_heur('$from','$to') AS(\"SampleSha1\" bpchar, \"Name\" character varying)";
		$res = odbc_exec($this->db_conn, $query);  
		while(odbc_fetch_row($res))
		{
			$report .= odbc_result($res,"SampleSha1");
			$report .= "|";	
			$report .= odbc_result($res,"Name");
			$report .= "\n";
		}
	
	    return $report;
	}

	public function getInfectedWorldView($from,$to,$src){
	$report ="";
	$i=1;
	$query  = "select * from mis.return_fls_infection_view('$from','$to',$src) as (\"MalwareName\" character varying, \"Total\" integer);";
		$res = odbc_exec($this->db_conn, $query);  
		while(odbc_fetch_row($res))
		{
			$report .= $i++;
			$report .= "|";	
			$report .= odbc_result($res,"SigId");
			$report .= "|";	
			$report .= preg_replace("/(\.)$/","",(odbc_result($res,"MalwareName")=='taipan.438.b')?"DetectedViaCloud":odbc_result($res,"MalwareName"));
			$report .= "|";	
			$report .=odbc_result($res,"Total"); 
			$report .= "\n";
		}
	
	    return $report;

	}

	public function getInfectedWorldView_chart($from,$to,$src){
	$val =array();
	$legend= array();
	
//	$query = "select (case when \"Total\" >= 500 then mis.malware_namebysigid(\"SigId\") else 'Other' end) as \"MalwareName\",sum(\"Total\") as total
//		      from mis.report_fls_infection  where (\"Ts\" between '$from' and '$to') and \"DetectSrc\" = $src
//		      group by \"MalwareName\" order by total desc limit $lim";
	$query  = "select * from mis.return_fls_infection_view('$from','$to',$src,50) as (\"MalwareName\" character varying, \"Total\" integer);";
	
	$res = odbc_exec($this->db_conn, $query);  
	while(odbc_fetch_row($res))
		{
			array_push($legend,(odbc_result($res,"MalwareName")=='taipan.438.b')?"DetectedViaCloud":odbc_result($res,"MalwareName"));
			array_push($val,odbc_result($res,"Total"));

		}
	    return array($legend,$val);
	}

	public function getInfectedCountryView_chart($from,$to,$src){
	$val =array();
	$legend= array();
	
	$query  = "select * from mis.return_fls_infection_country('$from','$to',$src,50) as (\"Country\" character varying, \"Total\" integer);";
	
	$res = odbc_exec($this->db_conn, $query);  
	while(odbc_fetch_row($res))
		{
			array_push($legend,odbc_result($res,"Country"));
			array_push($val,odbc_result($res,"Total"));

		}
	    return array($legend,$val);
	}



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