<?php
echo "d&#233;but";
$table="aol_access_log";
$conn;
$connected=false;
$lastlogdate=0;

function getTime($date){
preg_match (
  '/^(\S+)\/(\S+)\/(\S+):(\S+):(\S+):(\S+)/'
, $date
, $match
);
$res=mktime($match[4],$match[5],$match[6],$match[2],$match[1],$match[3]);
if ($res<0) $res=0;
return $res;
}


function dropTable(){
  global $connected,$table,$lastlogdate;
  if (!$connected)
     connect();
  $result = pg_query( "Drop table $table");
  if (!$result) 
    echo "Impossible de supprimer la table.<br>\n";
}
function getLastLogDate(){
global $connected,$table,$lastlogdate;
echo "check logdate<br>";
  if (!$connected)
     connect();
$result = pg_query("select max(date) from $table" );
    if (!$result) {
      echo "first time.\n";
      $lastlogdate=0;return;
    } 
  while ($row = pg_fetch_row($result)) {
  print_r($row);
  echo "Val: $row[0]";
  echo "<br />\n";
  if (isset($row[0]) && ($row[0]!="") && ($row[0]!=0))
  $lastlogdate=$row[0];
  else
  $lastlogdate="1/1/1970";
  
  return $lastlogdate;
  }
  return 0;
}
  

function connect(){
  global $connected,$table,$lastlogdate;
  if ($connected)
     return true;
  $conn = pg_pconnect("dbname=rezo4 user=nsadmin");
  if (!$conn) {
    echo "Une erreur s'est produite.\n";
    return false;
  }
  $connected=true;
  return true;

}
function createTable (){
  global $connected,$table,$lastlogdate;
  if (!$connected)
     connect();
  $firsttime=true;
  $result = pg_query( "SELECT date FROM $table");
  if (!$result) {
    echo "Essaye de cr&#233;er la table.\n";
    $query="    CREATE TABLE  $table (
                              host varchar(40),
                              misc1 varchar(40),
                              misc2 varchar(40),
                              date timestamp,
                              url varchar(1400),
                              code1 integer,
                              code2 integer,
                              refer varchar(1400),
                              csoft varchar(1400),
                              extra varchar(4000),
                              userid varchar(1400),
                              community varchar(1400));";
    
echo "$query";
    $result = pg_query( $query);
    if (!$result) {
      echo "Impossible de cr&#233;er la table<br>.\n";
      return false;
    }
    
  }
     echo "Table cr&#233;e<br>.\n";
  return true;
}
function _clean(&$value){
//echo "value=$value ->";
 $value=preg_replace("/'/","",$value);
 $value=substr($value,0,1400);
 //$value=preg_replace("/\/d.c\//","dec",$value);
 //echo "value=$value<br>";
 return $value;
}
function pg_store($array){
  global $table,$lastlogdate;
  
  //echo strtotime($lastlogdate)." > ".getTime($array['date'])."<br>";
  if (strtotime($lastlogdate)>getTime($array['date']))
  {
    echo "Skip entry<br>";
    return;
  }
  $firsttime=true;
  $query="INSERT INTO $table (";
  foreach ( $array as $key => $value ){
        if (!$firsttime)
        $query.=","; else $firsttime=false;
          $query.=" $key";
      }
      $query.=" ) VALUES ( ";
       $firsttime=true;
  foreach ( $array as $key => $value ){
  $value=_clean($value);
        if (!$firsttime)
        $query.=","; else $firsttime=false;
          $query.="'$value'";
      }
      $query.=" ) ; ";
      //echo "$query.\n";
    $result = pg_query($query);
    if (!$result) {
      echo "eh merde $query.\n";
      exit;
    }  
      
      
/*while ($row = pg_fetch_row($result)) {
  echo "Auteur: $row[0]  E-mail: $row[1]";
  echo "<br />\n";
}é
 */
}

function storeResults($result){
    if (isset($result) && (count($result)))
    {
         /* foreach ($result as $key => $value)
          {
          
          $value=preg_replace("/'/","",$value);
          $value=preg_replace("/déc/","dec",$value);
          
          //echo "$key => $value<br>";
          }*/
          
          //echo "----------------------------------------------------------------------------------------------<br>";
          
          pg_store($result);
    }
}

function parseLine($line){
//10.0.0.122 - - [05/mar/2006:11:52:48 +0000] "GET /images/ic_entr_off.gif HTTP/1.1" 200 67 "http://10.0.0.122/default.css" "Mozilla/5.0 (X11; U; Linux i686; fr; rv:1.8.0.1) Gecko/20060224 Ubuntu/dapper Firefox/1.5.0.1" "859925" "community_id=0" 

  $nbres=preg_match (
  //  1      2      3    4         5      6      7   8       9           10      11
  // host    -     -    Date      URL    code1 code2 refer  csoft     userid  community
  '/^(\S+) (\S+) (\S+) \[(.+)\] \"(.+)\" (\S+) (\S+) (\S+) \"(.*)\" \"(.*)\" \"(.*)\"/'
, $line
, $match
);
if ($nbres){
$result=array();
    $result['host']     = $match[1];
    $result['misc1']     = $match[2];
    $result['misc2']     = $match[3];
    $result['date']     = $match[4];
    $result['url']     = $match[5];
    $result['code1']     = $match[6];
    $result['code2']     = $match[7];
    $result['refer']     = $match[8];
    $result['csoft']     = $match[9];
    $result['userid']     = $match[10];
    $result['community']     = $match[11];
return $result;
}
else{
//62.108.229.106 - - [20/Dec/2005:02:16:14 +0000] "GET //cgi-bin/awstats/awstats.pl HTTP/1.1" 200 26 "" "Mozilla/4.0 (compatible; MSIE 6.0; Windows 98)" - 

  $nbres=preg_match (
  //  1      2      3    4         5      6      7   8       9     
  // host    -     -    Date      URL    code1 code2 refer  csoft     
  '/^(\S+) (\S+) (\S+) \[(.+)\] \"(.+)\" (\S+) (\S+) (\S+) \"([^\"]*)\" (.*)/'
, $line
, $match
);
if ($nbres){
$result=array();
    $result['host']     = $match[1];
    $result['misc1']     = $match[2];
    $result['misc2']     = $match[3];
    $result['date']     = $match[4];
    $result['url']     = $match[5];
    $result['code1']     = $match[6];
    $result['code2']     = $match[7];
    $result['refer']     = $match[8];
    $result['csoft']     = $match[9];
    $result['extra']     = $match[10];
    
    /*pour le 10 "ad_session_id=12706407%2c859925+%7b69+1135084478+F413E1B3DE84DEE4BEF7E577E1BC746EDAABCBC8%7d; PPA_ID=de3c7d1bbb570d6cb0dfab3f14dba415; user_session_id=585; TreeMenuBranchStatus=objTreeMenu_1_node_1_2_3"*/
return $result;
}
}
}
function parseLog($filename){
//$filename=preg_replace("/:/","\:",$filename);
echo "Parsing:$filename<br>";

if (preg_match('/gz$/',$filename))
 $handle = gzopen($filename, "r");
else
 $handle = fopen("$filename", "r");
if ($handle) {
   while (!feof($handle)) {
     $buffer = fgets($handle);
     //echo "Analyse $buffer<br>\n";
     storeResults(parseLine ($buffer));
   }
   //echo "-----------1) Failed to parse $filename finished<br>";
   
   fclose($handle);
}else echo "-----------2) Failed to parse $filename<br>";
}

function parseAll(){
dropTable();
createTable();
      getLastLogDate();
      echo "---->$lastlogdate<br>";
      //parseLog("/var/log/aolserver4/rezo-access.log.2005-12-24-00_00");exit;
      $res=array();
      exec("/var/log/aolserver4/cleanlog",$res);
      exec("ls -lha /var/log/aolserver4| grep rezo-acc | sort -k 6 | sed -re 's/.* (.*)$/\\1/'",$res);
      
      //print_r($res);exit;
      foreach($res as $key => $value){
      //$value=preg_replace("/:/","\:",$value);
      echo "<br><br><br><br>\n";
      parseLog("/var/log/aolserver4/".$value);
}
}
function getUsername($uid){
if ((!$uid)||($uid=='')||($uid==0))
  return;
global $connected,$table,$lastlogdate;
  if (!$connected)
     connect();
$result = pg_query("
select first_names,last_name ,title from persons where person_id=$uid" );
  while ($row = pg_fetch_row($result)) {
  //print_r($row);
  echo "$row[0],$row[1],$row[2]";
  
  }


}
function getVisitedPage($uid){
if ((!$uid)||($uid=='')||($uid==0))
  return;
global $connected,$table,$lastlogdate;
  if (!$connected)
     connect();
$result = pg_query("select distinct url from $table where userid=$uid" );
//$result = pg_query("select distinct url from aol_access_log where userid=859925;");
   echo "ap";
  while ($row = pg_fetch_row($result)) {
  if ($row[0]!=''){
  echo "URL: $row[0]";
  //getUsername($row[0]);
  getenteprise($row[0]);
  echo "<br />\n";
  }
  }
  return 0;
}

function getenteprise($url,$id=-1){
  if ($id==-1)
  {
  $nbres=preg_match (
  //rezo/entreprise/entreprise-view?group_id=754452
  '/entreprise.*group_id=([0-9]*).*$/'
    , $url
    , $match
   );
  if (!$nbres) return;
   $id     = $match[1];
  }
   
  //echo "<br>Entid=$id<br>";
  if ((!$id)||($id=='')||($id==0))
  return;
  global $connected,$table,$lastlogdate;
    if (!$connected)
      connect();
  $result = pg_query("
  select ent_name,ent_country_iso2 ,ent_adress1,ent_city,ent_city from entreprise_ext where group_id=$id" );
    while ($row = pg_fetch_row($result)) {
    //print_r($row);
    echo "$row[0],$row[1],$row[2],$row[3],$row[4]";
    }
    return $id;
/*group_id ent_name ent_country_iso2 ent_usp ent_adress1 ent_adress2 ent_cp ent_city ent_canton_id ent_url ent_phone_number nt_fax_number ent_mobile_number ent_email ent_capital ent_creation_date ent_activity_range ent_size_id ent_legal_id ent_domain_id ent_corporate_summary_mt ent_position ent_products_services ent_target ent_skills ent_reference ent_story ent_awards ent_press ent_have_logo ent_deleted ent_picture_title_1 ent_picture_title_2 ent_picture_title_3 ent_picture_comment_1 ent_picture_comment_2 ent_picture_comment_3 ent_picture_order_1 ent_picture_order_2 ent_picture_order_3 ent_page_creation_date ent_page_edition_date ent_page_visit_count */
}

function getAllenterprisestat(){
  
global $connected,$table,$lastlogdate;
$enterpriseList=array();
  if (!$connected)
     connect();
$result = pg_query("select url,userid from $table where url LIKE '%entreprise%'" );

  while ($row = pg_fetch_row($result)) {
  if ($row[0]!=''){
  echo "URL: $row[0]";
  //getUsername($row[0]);
  $id=getenteprise($row[0]);
  if ($id && $id<>0)
  $enterpriseList[$id][]=$row[1];
  echo "<br />\n";
  }
  }
  
  foreach ($enterpriseList as $key => $value)
  {
    echo "<br><br>   For enterprise :";
    getenteprise("",$key);
    echo "<br>";
    echo "----------------------------------------------------------------------------<br />\n";
    $user=array();
    $anonymous=0;
        foreach ($value as $key1 => $value1)
          {
            if (!$value1 || $value1==0)
              $anonymous++;
              else
              $user[$value1]++;
            
          }
            if ($anonymous>0)
            echo "        anonymous:($anonymous visits)<br>";
          foreach ( $user as $key1 => $value1)
          {
            echo "        ".getUsername($key1)." ($value1 visits)<br>" ;
            
          }
          
  }
  return 0;
}


function getLastConnections(){
global $connected,$table,$lastlogdate;

  if (!$connected)
     connect();
$result = pg_query("select distinct userid from $table " );
   
  while ($row = pg_fetch_row($result)) {
  if (($row[0]!='') &&($row[0]!='0')){
  echo "Val: $row[0]";
  getUsername($row[0]);
  getVisitedPage($row[0]);
 
  echo "<br />\n";
  }
  }
  return 0;

}
//parseAll();
getAllenterprisestat();
//getLastConnections();


?>