[Sommaire] [Imprimer]

   1  <?php
   2  /* 
   3   * phpMyVisites : website statistics and audience measurements
   4   * Copyright (C) 2002 - 2006
   5   * http://www.phpmyvisites.net/ 
   6   * phpMyVisites is free software (license GNU/GPL)
   7   * Authors : phpMyVisites team
   8  */
   9  
  10  // $Id: ArchivePeriod.class.php 102 2006-10-01 16:28:30Z matthieu_ $
  11  
  12  
  13  /**
  14   * Used for misc periods, weeks, months, and years
  15   * Can archive a period using the DAYS of the period, or the MONTHS,
  16   * depending on the var periodType value
  17   */
  18  class ArchivePeriod extends Archive
  19  {
  20      
  21      var $subPeriodValues = array();
  22      var $archiveDay;
  23      
  24  	function ArchivePeriod($site, $s_date, $s_date2, $typePeriod)
  25      {
  26          parent::Archive($site);
  27          $this->setPeriodType($typePeriod);
  28          $this->date = $this->offsetDate(new Date($s_date));
  29          $this->date2 = $this->offsetDate(new Date($s_date2));
  30      }
  31      
  32  	function setDate($s_date)
  33      {
  34          $this->reset();
  35          $this->getPeriodDatesLimit($s_date);
  36          
  37          if($this->date->isPeriodFinished($this->periodType))
  38          {
  39              $this->state = DB_ARCHIVES_DONE;
  40          }
  41          else
  42          {
  43              $this->state = DB_ARCHIVES_TEMP;
  44          }
  45          
  46          
  47          $this->setLiteralDate();
  48      }
  49      
  50  	function reset()
  51      {
  52          
  53          $this->date = null;
  54          $this->toRecord = null;
  55          $this->idArchives = null; 
  56          $this->state = null;
  57          
  58          $this->date2 = null;
  59          $this->subPeriodValues = null;
  60      }
  61      
  62      /**
  63       * If period isn't archived, then it assign var state, var archiveSubPeriod,
  64       * and each subperiod values in subPeriodValues array 
  65       * If period is already archived, true
  66       * 
  67       * @return bool 
  68       */
  69  	function isArchived()
  70      {        
  71          $r = query("SELECT idarchives 
  72                      FROM ".T_ARCHIVES."
  73                       WHERE idsite = ".$this->site->getId()."
  74                      AND date1 = '".$this->date->get()."'
  75                      AND date2 = '".$this->date2->get()."'
  76                      AND period = ".$this->periodType."
  77                      AND done <> ".DB_ARCHIVES_FAIL
  78              );
  79  
  80          // not archived
  81          if(mysql_num_rows($r) == 0)
  82          {
  83              // if YEAR, we use Months for processing
  84              if( $this->periodType ===  DB_ARCHIVES_PERIOD_YEAR)
  85              {
  86                  $this->archiveSubPeriod = new ArchiveMonth($this->site);
  87                  $a_subPeriod = getDayOfMonthBetween($this->date->get(), $this->date2->get());
  88              }
  89              // in the other cases, we use Days for processing
  90              else
  91              {
  92                  $this->archiveSubPeriod = new ArchiveDay($this->site);
  93                  $a_subPeriod = getDaysBetween($this->date->get(), $this->date2->get());
  94                  printDebug("Not archive... launch computation for ");
  95                  printDebug($a_subPeriod);
  96              }
  97              
  98              
  99              foreach($a_subPeriod as $subPeriod)
 100              {
 101                  // printDebug("<br>avant set date : ".$this->archiveSubPeriod->date->get());
 102                  $this->archiveSubPeriod->setDate($subPeriod);
 103                  //printDebug("<br>apres set date : devrait etre $subPeriod et est ".$this->archiveSubPeriod->date->get());
 104                                  
 105                  // if any of the days in period is temp, then the whole period is set to temp
 106                  if($this->archiveSubPeriod->state === DB_ARCHIVES_TEMP)
 107                  {
 108                      $this->state = DB_ARCHIVES_TEMP;
 109                  }
 110                  
 111                  // store in ->subPeriodValues[] days to compute and select * for each day
 112                  $l = $this->archiveSubPeriod->getArchived();
 113                  
 114                  // if subperiod is the day, stocke KEY = DATE
 115                  // else if period, stock KEY = IDARCHIVES
 116                  // needed for computing nb_uniq_vis
 117                  if($this->periodType ===  DB_ARCHIVES_PERIOD_YEAR)
 118                  {
 119                      $key = $l['idarchives'];
 120                  }
 121                  else
 122                  {
 123                      $key = $subPeriod;
 124                  }
 125                  
 126                  $this->subPeriodValues[$key] = $l; 
 127              }
 128              //printDebug("OK we have all datas<br>");
 129              //printDebug($this->subPeriodValues);
 130              
 131              return false;
 132          }
 133          // if the period date, date2 is known, means ever archived, it's ok
 134          // because the period is only computed ONCE for each couple (date, date2)
 135          else
 136          {
 137              printDebug("<u>Period known ! Don't archive</u><br>");
 138              $l = mysql_fetch_assoc($r);
 139              
 140              $this->idArchives = $l['idarchives'];
 141              return true;
 142          }
 143      }
 144      
 145      /**
 146       * returns 'select *' for current period
 147       * 
 148       * @return array SELECT * FROM archives [...]
 149       */
 150  	function getArchived()
 151      {
 152          if(!$this->isArchived())
 153          {
 154              printDebug("Compute new period...");
 155              $this->compute();
 156          }
 157          else
 158          { 
 159              printDebug("period already computed...");
 160          }
 161          $r = query("SELECT * 
 162                      FROM ".T_ARCHIVES."
 163                      WHERE idarchives = ".$this->idArchives ."
 164                      ORDER BY idarchives DESC
 165                      LIMIT 1"
 166          );
 167          
 168          return mysql_fetch_assoc($r);
 169      }
 170  
 171          
 172      /**
 173       * Computes datas for all subPeriodValues values 
 174       * and saves it in a new row in the database table archives
 175       * 
 176       * @return void
 177       */
 178  	function compute()
 179      {            
 180          if(CURRENT_PERIOD_SIMPLE_ARCHIVE && $this->state === DB_ARCHIVES_TEMP)
 181          {
 182              $this->toRecord['simple'] = 1;
 183          }
 184          else
 185          {
 186              $this->toRecord['simple'] = 0;
 187          }
 188          // date in the IN(...) SQL field for unique visitors
 189          $inIdArchives = '';
 190          $this->toRecord['vis_pag_grp'] = array();
 191          
 192          // init 
 193          $toInitInt = $this->intValuesToSum;
 194          $toInitArray = array_merge($this->arrayOneDimToSum, 
 195                                      $this->arrayPmvSumToSum, 
 196                                      $this->arrayIntToSum);
 197          foreach($toInitInt as $value)
 198          {
 199              $this->toRecord[$value] = 0;
 200          }
 201          foreach($toInitArray as $value)
 202          {
 203              $this->toRecord[$value] = array();
 204          }
 205          
 206          
 207          // for each subPeriod, sum all values
 208          foreach($this->subPeriodValues as $idArchives => $subPeriodValues)
 209          {            
 210              
 211              foreach($this->intValuesToSum as $name)
 212              {
 213                  $this->toRecord[$name] += $subPeriodValues[$name];
 214              }
 215              
 216              // case we don't want a SIMPLE archiving 
 217              if($this->toRecord['simple'] == 0)
 218              {
 219                  //printDebug("Sum2 : <br>");
 220                  foreach ($this->arrayOneDimToSum as $name) 
 221                  {
 222                      if(!isset($subPeriodValues[$name]))
 223                      {
 224                          $subPeriodValues[$name] = compress(serialize(array()), $subPeriodValues['compressed']);
 225                      }
 226                      $this->toRecord[$name] = 
 227                              getArrayOneDimVeryVeryVerySpecialSum($this->toRecord[$name], 
 228                                  unserialize(uncompress($subPeriodValues[$name], $subPeriodValues['compressed'])), "sum");
 229      
 230                  }
 231                  
 232                  //printDebug("Sum3 : <br>");
 233                  foreach($this->arrayPmvSumToSum as $name)
 234                  {
 235                      if(!isset($subPeriodValues[$name]))
 236                      {
 237                          $subPeriodValues[$name] = compress(serialize(array()), $subPeriodValues['compressed']);
 238                      }
 239      
 240                          $this->toRecord[$name] = 
 241                              getArrayOneDimVeryVeryVerySpecialSum($this->toRecord[$name], 
 242                                      unserialize(uncompress($subPeriodValues[$name], $subPeriodValues['compressed'])), "sumArray");
 243                  }
 244                  
 245                  //printDebug("Sum4 : <br>");
 246                  foreach($this->arrayIntToSum as $name)
 247                  {
 248                      if(!isset($subPeriodValues[$name]))
 249                      {
 250                          $subPeriodValues[$name] = compress(serialize(array()), $subPeriodValues['compressed']);
 251                      }
 252      
 253                      $this->toRecord[$name] = 
 254                          getArrayOneDimVeryVeryVerySpecialSum($this->toRecord[$name], 
 255                                  unserialize(uncompress($subPeriodValues[$name], $subPeriodValues['compressed'])), "sumArray");
 256                  }
 257                  
 258                  //printDebug("<h1>Sum5  $idArchives: </h1><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br>");
 259                  //printDebug("table to add");
 260                  //printDebug(unserialize($subPeriodValues['vis_pag_grp']));
 261                  if(!is_string($subPeriodValues['vis_pag_grp']))
 262                      $subPeriodValues['vis_pag_grp'] = compress(serialize(array()), $subPeriodValues['compressed']);
 263                      
 264                  $this->toRecord['vis_pag_grp'] = getPagGrpArrayMultiDimSum($this->toRecord['vis_pag_grp'],
 265                                                      unserialize(uncompress($subPeriodValues['vis_pag_grp'], $subPeriodValues['compressed'])));
 266              }
 267  
 268              $inIdArchives .= "'".$idArchives."', ";
 269          }
 270          
 271          //printDebug($this->toRecord['vis_pag_grp']); 
 272          //exit;
 273          /*
 274           * Sorting arrays
 275           */
 276          
 277          $this->sortAndLimitToRecord();
 278          
 279          $inIdArchives = substr($inIdArchives, 0, strlen($inIdArchives) - 2);
 280                  
 281          // if year, look for info averaging month archives
 282          if($this->periodType === DB_ARCHIVES_PERIOD_YEAR)
 283          {
 284              $r = query("SELECT sum(nb_uniq_vis) as nb_uniq_vis, max(nb_max_pag) as nb_max_pag
 285                      FROM ".T_ARCHIVES."
 286                      WHERE idarchives IN (".$inIdArchives.")"
 287                      );
 288                      
 289              // unique visitors returning sum on the period
 290              $nb_uniq_vis_returning = query("SELECT sum(nb_uniq_vis_returning) as nb_uniq_vis_returning
 291                                              FROM ".T_ARCHIVES."
 292                                              WHERE idarchives IN (".$inIdArchives.")"
 293                                              );
 294              // returning visitors
 295              $returning = query("SELECT nb_vis_returning as s, idarchives as pseudodate, 
 296                                      nb_pag_returning as sp, date1 as pseudodate2
 297                          FROM ".T_ARCHIVES."
 298                          WHERE idsite = ".$this->site->getId()."
 299                          AND idarchives IN (".$inIdArchives.")
 300                          GROUP BY pseudodate
 301                          ");
 302              
 303          }
 304          // but for other periods, look directly into logs tables
 305          // CAREFUL : WONT WORK FOR PERIOD OLDER THAN 1 MONTH !!
 306          else
 307          {
 308              $r = query("SELECT count(distinct idcookie) as nb_uniq_vis, max(total_pages) as nb_max_pag
 309                  FROM ".T_VISIT."
 310                  WHERE idsite = ".$this->site->getId()."
 311                  AND server_date IN (".$inIdArchives.")"
 312                  );
 313                  
 314              
 315              // unique visitors returning sum on the period
 316              $nb_uniq_vis_returning = query("SELECT count(distinct idcookie) as nb_uniq_vis_returning    
 317                                              FROM ".T_VISIT."
 318                                              WHERE idsite = ".$this->site->getId()."
 319                                              AND server_date IN (".$inIdArchives.")
 320                                              AND returning = 1
 321                                               GROUP BY returning"
 322                                              );
 323  
 324              // returning visitors
 325              $returning = query("SELECT count(*) as s, server_date as pseudodate, 
 326                                          sum(total_pages) as sp
 327                          FROM ".T_VISIT."
 328                          WHERE idsite = ".$this->site->getId()."
 329                          AND server_date IN (".$inIdArchives.")
 330                          AND returning = 1
 331                          GROUP BY pseudodate"); 
 332              
 333              // for each returning visit, how many time did they return?            
 334              $f1 = query("SELECT count(*) as s
 335                          FROM ".T_VISIT."
 336                          WHERE idsite = ".$this->site->getId()."
 337                          AND server_date IN (".$inIdArchives.")
 338                          GROUP BY idcookie");    
 339              $res = array();                    
 340              while($fx = mysql_fetch_assoc($f1))
 341              {
 342                  @$res[(int)$fx['s']]++;
 343              }
 344              ksort($res);        
 345              $this->toRecord['vis_nb_vis'] = $res;
 346              
 347          }    
 348          $nb_uniq_vis_returning = mysql_fetch_assoc($nb_uniq_vis_returning);
 349          $this->toRecord['nb_uniq_vis_returning'] = $nb_uniq_vis_returning['nb_uniq_vis_returning'];    
 350              
 351          $l = mysql_fetch_assoc($r);
 352          $this->toRecord['nb_uniq_vis'] = $l['nb_uniq_vis'];
 353          $this->toRecord['nb_max_pag']  = $l['nb_max_pag'];
 354          
 355          // visits per period, new vs returning, and pages hits
 356          $returningTotal = 0;
 357          $returningPag = 0;
 358          $this->toRecord['vis_period'] = array();
 359          while($l2 = mysql_fetch_assoc($returning))
 360          {
 361              $visitsTotal = $this->subPeriodValues[$l2['pseudodate']]['nb_vis'];
 362              $pagesTotal =  $this->subPeriodValues[$l2['pseudodate']]['nb_pag'];
 363              $returningVisits = $l2['s'];
 364              
 365              $returningPag += $l2['sp'];
 366              
 367              $returningTotal += $returningVisits;
 368              
 369              $this->toRecord['vis_period'][isset($l2['pseudodate2'])?$l2['pseudodate2']:$l2['pseudodate']] = array(
 370                          ARRAY_INDEX_RETURNING_COUNT => (int)$returningVisits,
 371                          ARRAY_INDEX_NEW_COUNT => $visitsTotal - $returningVisits,
 372                          ARRAY_INDEX_PAGES_COUNT => (int)$pagesTotal
 373                          );
 374          }
 375          ksort($this->toRecord['vis_period']);
 376          // number of returning visits
 377          $this->toRecord['nb_vis_returning'] = $returningTotal;
 378          $this->toRecord['nb_pag_returning'] = $returningPag;
 379              
 380          //printDebug("TO RECORD<br>");
 381          //printDebug($this->toRecord);
 382                  
 383          /*
 384           * init
 385           */
 386           
 387          $this->initDb();
 388          
 389          /*
 390           * final save, close your eyes and prey 
 391           */
 392           $this->saveDb();
 393           
 394           /**
 395            * delete all visit/month records
 396            */
 397            
 398          $this->deleteOldRecords();
 399      }
 400      
 401      // TODO protéger le champ pmv_sum des mots clés    
 402  	function deleteOldRecords()
 403      {
 404          parent::deleteOldRecords();
 405          
 406          // delete records in VISIT that are useless 
 407          // (means when week AND month using these records are archived)
 408          if(
 409              ($this->periodType === DB_ARCHIVES_PERIOD_MONTH
 410                  || $this->periodType === DB_ARCHIVES_PERIOD_WEEK)
 411              && $this->state !== DB_ARCHIVES_TEMP)
 412          {
 413              // select all weeks and months begin and end
 414              $r = query("SELECT date1, date2, idsite, period, idarchives
 415                          FROM ".T_ARCHIVES."
 416                          WHERE done = ".DB_ARCHIVES_DONE."
 417                              AND (period = ".DB_ARCHIVES_PERIOD_WEEK."
 418                                  OR period = ".DB_ARCHIVES_PERIOD_MONTH."
 419                                  )
 420                          "); 
 421              while($l = mysql_fetch_assoc($r))
 422              {
 423                  // stock all dates between these two in an array
 424                  $dateBetween = getDaysBetween( $l['date1'], $l['date2']);
 425                  
 426                  //print($l['idarchives'] ."<br>");
 427                  //print( $l['date1'].",". $l['date2']." id=:". $l['idsite']." period=:". $l['period']." "    );
 428                  //print_r($dateBetween); print("<br> which is ".sizeof($dateBetween)." elements <br><br>");
 429                  
 430                  if(isset($flag[$l['idsite']][$l['period']][$l['date1']]))
 431                  {
 432                      $c = $flag[$l['idsite']][$l['period']][$l['date1']];
 433                      if(sizeof($c) < sizeof($dateBetween)
 434                          && sizeof($dateBetween) >= 7)
 435                      {
 436                          $flag[$l['idsite']][$l['period']][$l['date1']] = $dateBetween;
 437                      }
 438                  }
 439                  else
 440                  {
 441                      $flag[$l['idsite']][$l['period']][$l['date1']] = $dateBetween;
 442                  }
 443              }
 444              //exit;
 445              
 446              foreach($flag as $idsite => $a_idsite)
 447              {
 448                  foreach($a_idsite as $period => $a_period)
 449                  {
 450                      foreach($a_period as $a_date)
 451                      {
 452                          foreach($a_date as $d)
 453                          {
 454                              if(!isset($toCheck[$idsite][$d]))
 455                              {
 456                                  $toCheck[$idsite][$d] = 1;
 457                              }
 458                              else
 459                              {
 460                                  $toCheck[$idsite][$d]++;
 461                              }
 462                          }
 463                      }
 464                  }
 465              }
 466              //print("<pre>Apres sommes des dates <br>");
 467              //print_r($toCheck);
 468              
 469              $deleted = 0;
 470              foreach($toCheck as $idsite => $a_date)
 471              {
 472                  foreach($a_date as $date => $hits)
 473                  {
 474                      // for the days who have 2 hits (mean that weeks and month that contain 
 475                      // this day are archived)
 476                      // delete these day records!
 477                      if($hits > 1)
 478                      {
 479                          $r = query("DELETE
 480                                      FROM ".T_VISIT."
 481                                      WHERE server_date = '".$date."'
 482                                          AND idsite = $idsite
 483                                          ");
 484                          $deleted+=mysql_affected_rows();
 485                      }
 486                  }
 487              }
 488              //print("rows deleted = $deleted");
 489              if(time()%3==0)
 490              {
 491                  $r = query("OPTIMIZE TABLE ".T_VISIT);
 492              }
 493          }
 494      }    
 495  }
 496  ?>