source: extensions/EStat/lib/statDBGlobal.class.inc.php @ 22483

Last change on this file since 22483 was 17862, checked in by grum, 12 years ago

version 0.1.0b
. fix bug on thumb view
. fix SQLite compression managment & 'compress.method' config
. manage number of items per page by config
  • Property svn:executable set to *
File size: 38.5 KB
Line 
1<?php
2
3require_once('statDB.class.inc.php');
4require_once('statDBMonth.class.inc.php');
5
6/**
7 * specific class for SQLite global stat managment
8 *
9 * statDBMonth is needed to connect monthly stat DB and make consolidation
10 */
11class StatDBGlobal extends StatDB
12{
13  /**
14   * constructor
15   *
16   * @param String $directory: directory where the sqlite file is saved
17   * @param String $fileName: file name (without extension)
18   */
19  public function __construct($directory='', $fileName='')
20  {
21    parent::__construct($directory, $fileName);
22    $this->fileRootName=ASDF_FILE_ROOT_GLOBAL;
23  }
24
25  /**
26   * check if a year/month exists in the file list
27   * if not, create it
28   *
29   * return true if the an update was done, otherwise false
30   *
31   * @param String $year:
32   * @param String $month:
33   * @return Boolean
34   */
35  public function checkFilePeriod($year, $month)
36  {
37    if($this->dbHandle==null) return(false);
38
39    $sql="SELECT COUNT(*)
40          FROM files
41          WHERE `year` = $year
42            AND `month` = $month;";
43    $returned=$this->dbHandle->querySingle($sql);
44
45    if($returned==0)
46    {
47      $sql="REPLACE INTO files (year, month, logs, packed, unpacked, lastBuilt, needBuild)
48          VALUES ($year, $month, 0, 0, 0, '', 'Y');";
49      return($this->dbHandle->exec($sql));
50    }
51    return(false);
52  }
53
54  /**
55   * return the list of monthly files
56   * can be filtered to return packed or unpacked only
57   *
58   * for each file, return the following informations:
59   *  - year
60   *  - month
61   *  - logs (number of logs)
62   *  - packed (packed size)
63   *  - unpacked (unpacked size)
64   *  - lastBuilt (last date the consolidation was done)
65   *  - needBuild (need to be built or not?)
66   *
67   * @param Integer $packed: combination of ASDF_EXIST_PACKED and ASDF_EXIST_UNPACKED
68   * @param Integer $build: combination of ASDF_BUILD_DONE and ASDF_BUILD_MISSING
69   * @return Array
70   */
71  public function getFilesList($packed=ASDF_EXIST_ALL, $build=ASDF_BUILD_ALL)
72  {
73    $returned=array();
74    if($this->dbHandle)
75    {
76      $where=array();
77
78      $sql="SELECT `year`, `month`, `logs`, `packed`, `unpacked`, `lastBuilt`, `needBuild`
79            FROM files";
80
81      if($packed!=(ASDF_EXIST_PACKED|ASDF_EXIST_UNPACKED))
82        switch($packed)
83        {
84          case ASDF_EXIST_PACKED:
85            $where[]=" packed=0 ";
86            break;
87          case ASDF_EXIST_UNPACKED:
88            $where[]=" packed>0 ";
89            break;
90        }
91
92      if($build!=(ASDF_BUILD_DONE|ASDF_BUILD_MISSING ))
93        switch($build)
94        {
95          case ASDF_BUILD_DONE:
96            $where[]=" needBuild='N' ";
97            break;
98          case ASDF_BUILD_MISSING:
99            $where[]=" needBuild='Y' ";
100            break;
101        }
102
103      if(count($where)>0)
104        $sql.=" WHERE ".implode(' AND ', $where);
105
106      $sql.=" ORDER BY `year`, `month`";
107
108      $result=$this->dbHandle->query($sql);
109      if($result)
110      {
111        while($row=$result->fetchArray(SQLITE3_ASSOC))
112        {
113          $returned[]=$row;
114        }
115      }
116    }
117    return($returned);
118  }
119
120  /**
121   * set packed size for a file year, month and packed file size have to be
122   * provided
123   *
124   * @param Integer $year
125   * @param Integer $month
126   * @param Integer $fileSize
127   * @return Boolean
128   */
129  public function updatePackedSize($year, $month, $fileSize)
130  {
131    if($this->dbHandle)
132    {
133      $sql="UPDATE files
134            SET packed=$fileSize
135            WHERE `year`=$year
136              AND `month`=$month;";
137      return($this->dbHandle->exec($sql));
138    }
139    return(false);
140  }
141
142
143
144  /**
145   * build stats for a year/month
146   *
147   * @param String $fileDir: directory where file is saved
148   * @param String $fileName: root name of the file
149   * @param Integer $year
150   * @param Integer $month
151   * @return Boolean
152   */
153  public function buildStatPeriod($fileDir, $fileName, $year, $month)
154  {
155    if($this->dbHandle==null) return(false);
156    $packed='';
157    $returned=true;
158
159    // test if a file exist for the period
160    $fmPeriod=new StatDBMonth($fileDir, $fileName, $year, $month);
161
162    $unpacked=$fmPeriod->getFileName(ASDF_EXIST_UNPACKED, true);
163    if($unpacked=='')
164    {
165      $packed=$fmPeriod->getFileName(ASDF_EXIST_PACKED, true);
166      if($packed=='') return(false);
167      $fmPeriod->unpack();
168    }
169
170    $unpacked=$fmPeriod->getFileName(ASDF_EXIST_UNPACKED, true);
171    if($unpacked=='') return(false); //not unpacked?
172
173    $fmPeriod->setIpCountryFile($this->ipCountryFile);
174    $fmPeriod->open(ASDF_OPEN_WRITE);
175    $fmPeriod->buildIPCountry();
176    $fmInfos=$fmPeriod->getInfo();
177    $fmPeriod->close();
178
179    $result=$this->dbHandle->exec("ATTACH '$unpacked' AS period");
180    if($result)
181    {
182      $needBuild=(date('Ym')==$year.$month)?'Y':'N';
183
184      $sql="
185        -- start a transaction, if something is wrong database won't be poluted
186        BEGIN TRANSACTION;
187
188        REPLACE INTO main.info (domain, key, value)
189          VALUES('log',
190                 'buildStatPeriod($year, $month)',
191                 '".date('Y-m-d H:i:s')."');
192
193        REPLACE INTO period.info (domain, key, value)
194          VALUES('log',
195                 'buildStatPeriod($year, $month)',
196                 '".date('Y-m-d H:i:s')."');
197
198        UPDATE main.info
199        SET value=value+1
200        WHERE domain = 'log'
201          AND key = 'buildStatPeriod-count';
202
203        UPDATE period.info
204        SET value=value+1
205        WHERE domain = 'log'
206          AND key = 'buildStatPeriod-count';
207
208
209        REPLACE INTO main.files (year, month, logs, packed, unpacked, lastBuilt, needBuild)
210          VALUES ($year, $month, ".$fmInfos['nbRecords']['logs'].", ".$fmInfos['packedFile']['fileSize'].", ".$fmInfos['unpackedFile']['fileSize'].", '".date('Y-m-d H:i:s')."', '".$needBuild."');
211
212        REPLACE INTO main.statCategories (catId, uaType, year, month, visits)
213            SELECT catId, uaType, $year, $month, SUM(visits)
214            FROM period.statCategories
215            GROUP BY catId, uaType;
216
217        REPLACE INTO main.statImages (imageId, catId, uaType, year, month, visits)
218            SELECT imageId, catId, uaType, $year, $month, SUM(visits)
219            FROM period.statImages
220            GROUP BY imageId, catId, uaType;
221
222        REPLACE INTO main.statIP (uaType, IPadress, catId, year, month, visits, country)
223            SELECT uaType, IPadress, catId, $year, $month, SUM(visits), country
224            FROM period.statIP
225            GROUP BY uaType, IPadress, catId, country;
226
227        REPLACE INTO main.statUA (uaData, uaValue, uaVersion, year, month, visits)
228            SELECT uaData, uaValue, uaVersion, $year, $month, SUM(visits)
229            FROM period.statUA
230            GROUP BY uaData, uaValue, uaVersion;
231
232        -- commit changes
233        COMMIT TRANSACTION;
234      ";
235      $returned=$this->dbHandle->exec($sql);
236
237      if(!$this->dbHandle->exec("DETACH period")) $returned=false;
238    }
239
240    if($packed!='')
241    {
242      // packed file was unpacked, pack file only if file was already packed
243      if($fmPeriod->pack())
244        $fmPeriod->delete(ASDF_DELETE_UNPACKED);
245    }
246    return($returned);
247  }
248
249
250
251
252
253  /**
254   * returns stats from useragent strings (os, browser, engine, type)
255   *
256   * $page: the page number asked; first page is 1=>lower value means to get all values
257   * $nbItemsPage: the number of items returned per; 0 means no limit
258   *
259   *
260   * in ASDF_GET_ROWS mode, each returned logs is an array of asked fields
261   * at least, the following fields are always returned:
262   *  'nbVisits' => number of visits
263   *  'uaValue'  => value (meaning of this information can change)
264   *
265   *
266   * only asked fields are filterable, except 'nbVisits' that can't be filtered
267   *
268   * available fields values are:
269   *   'uaData'   => return the type of data; can get one of the following values
270   *                          UA_DATA_BROWSER: stat about browser
271   *                          UA_DATA_ENGINE: stat about engine
272   *                          UA_DATA_OS: stat about OS
273   *                          UA_DATA_BROWSER_TYPE: stat about browser type
274   *   'uaValue'   => return detailled stat per type of data
275   *   'year'      => return detailled stat per year
276   *   'month'     => return detailled stat per month
277   *   'uaVersion' => return detailled stat per version (browser version, os version, ...)
278   *
279   * @param Integer $mode: type of result needed
280   *                          . ASDF_GET_ROWS return an array of logs
281   *                          . ASDF_GET_COUNT return the number of logs
282   * @param Integer $page: page number
283   * @param Integer $nbItemsPage: number of items per page
284   * @param Array $fields : additional fields to be returned
285   * @param Array $filter : filters to apply => see getLogs function for usage
286   * @param Array $order: sort to apply      => see getLogs function for usage; all fields can be sorted
287   * @return
288   */
289  public function getStatUserAgent($mode=ASDF_GET_ROWS, $page=0, $nbItemsPage=0, $fields=array(), $filter=array(), $order=array())
290  {
291    switch($mode)
292    {
293      case ASDF_GET_COUNT:
294        $returned=0;
295        break;
296      case ASDF_GET_ROWS:
297        $returned=array();
298        break;
299    }
300    if($this->dbHandle==null) return($returned);
301
302    $select=array('uaData', 'SUM(visits) AS nbVisits');
303    $groupBy=array('uaData');
304    // initialize fields list
305    foreach($fields as $field)
306    {
307      if($field=='uaValue' or
308         $field=='year' or
309         $field=='month' or
310         $field=='uaVersion')
311      {
312        $select[]=$field;
313        $groupBy[]=$field;
314      }
315    }
316
317    // initialize filter
318    if(!is_array($filter)) $filter=array();
319
320    if(!isset($filter['uaData'])) $filter['uaData']=null;
321    if(!isset($filter['uaValue'])) $filter['uaValue']=null;
322    if(!isset($filter['year'])) $filter['year']=null;
323    if(!isset($filter['month'])) $filter['month']=null;
324    if(!isset($filter['uaVersion'])) $filter['uaVersion']=null;
325
326    // check filter values - getOperator check and 'clean' the filter
327    $filter['uaData']=$this->getOperator($filter['uaData'], 'integer');
328    $filter['uaValue']=$this->getOperator($filter['uaValue'], 'integer');
329    $filter['year']=$this->getOperator($filter['year'], 'integer');
330    $filter['month']=$this->getOperator($filter['month'], 'integer');
331    $filter['uaVersion']=$this->getOperator($filter['uaVersion'], 'string');
332
333
334    // initialize order
335    $orderBy=array();
336    if(!is_array($order)) $order=array();
337    foreach($order as $sort)
338    {
339      if(isset($sort['id']) and isset($sort['direction']))
340      {
341        if((($sort['id']=='year' or
342             $sort['id']=='month' or
343             $sort['id']=='uaData' or
344             $sort['id']=='uaVersion' or
345             $sort['id']=='uaValue' ) and
346            in_array($sort['id'], $select) or
347             $sort['id']=='nbVisits') and
348           ($sort['direction']=='A' or
349            $sort['direction']=='D')
350          ) $orderBy[]=$sort;
351      }
352    }
353    // set default order if nothing available is provided
354    if(count($orderBy)==0)
355    {
356      if(in_array('year', $select))
357        $orderBy[]=array('id'=>'year', 'direction'=>'D');
358      if(in_array('month', $select))
359        $orderBy[]=array('id'=>'month', 'direction'=>'A');
360      $orderBy[]=array('id'=>'nbVisits', 'direction'=>'D');
361    }
362
363
364    // build SELECT & GROUP By clauses
365    $select="SELECT ".implode(',', $select);
366    $groupBy=(count($groupBy)>0)?" GROUP BY ".implode(',', $groupBy):'';
367
368    //build ORDER BY clause
369    $orderBy=$this->buildOrderByClause($orderBy);
370
371    //build WHERE clause
372    $IPList=array();
373    $where=$this->buildWhereClause($filter, $IPList);
374
375    // build LIMIT clause
376    $limit=$this->buildLimitClause($page, $nbItemsPage);
377
378    // execute request
379    switch($mode)
380    {
381      case ASDF_GET_ROWS:
382          $sql=$select." FROM statua ".$where.$groupBy.$orderBy.$limit;
383
384          $sqlStm=$this->dbHandle->prepare($sql);
385
386          $result=$sqlStm->execute();
387          if($result)
388          {
389            while($row=$result->fetchArray(SQLITE3_ASSOC))
390            {
391              $returned[]=$row;
392            }
393          }
394        break;
395      case ASDF_GET_COUNT:
396          $sql="SELECT COUNT(*) AS nb
397                FROM statua $where;";
398
399          $sqlStm=$this->dbHandle->prepare($sql);
400
401          $result=$sqlStm->execute();
402          if($result)
403          {
404            while($row=$result->fetchArray(SQLITE3_ASSOC))
405            {
406              $returned=$row['nb'];
407            }
408          }
409        break;
410    }
411    return($returned);
412  }
413
414
415  /**
416   * returns stats from IP
417   *
418   * $page: the page number asked; first page is 1=>lower value means to get all values
419   * $nbItemsPage: the number of items returned per; 0 means no limit
420   *
421   *
422   * in ASDF_GET_ROWS mode, each returned logs is an array of asked fields
423   * at least, the following fields are always returned:
424   *  'nbVisits' => number of visits
425   *
426   * all fields are filterable, except 'nbVisits' that can't be filtered
427   *
428   * available fields values are:
429   *   'NumIPadress' => return the number of distinct IP address
430   *   'IPadress'    => return the IP address
431   *   'year'        => return detailled stat per year
432   *   'month'       => return detailled stat per month
433   *   'uaType'      => return detailled stat per browser type
434   *   'country'     => return detailled stat per country
435   *   'catId'       => return detailled stat per category
436   *
437   * @param Integer $mode: type of result needed
438   *                          . ASDF_GET_ROWS return an array of logs
439   *                          . ASDF_GET_COUNT return the number of logs
440   * @param Integer $page: page number
441   * @param Integer $nbItemsPage: number of items per page
442   * @param Array $fields : additional fields to be returned
443   * @param Array $filter : filters to apply => see getLogs function for usage
444   * @param Array $order: sort to apply      => see getLogs function for usage; all fields can be sorted
445   * @return
446   */
447  public function getStatIP($mode=ASDF_GET_ROWS, $page=0, $nbItemsPage=0, $fields=array(), $filter=array(), $order=array())
448  {
449    switch($mode)
450    {
451      case ASDF_GET_COUNT:
452        $returned=0;
453        break;
454      case ASDF_GET_ROWS:
455        $returned=array();
456        break;
457    }
458    if($this->dbHandle==null) return($returned);
459
460    $select=array('SUM(visits) AS nbVisits');
461    $groupBy=array();
462    // initialize fields list
463    foreach($fields as $field)
464    {
465      if($field=='IPadress' or
466         $field=='year' or
467         $field=='month' or
468         $field=='catId' or
469         $field=='country' or
470         $field=='uaType')
471      {
472        $select[]=$field;
473        $groupBy[]=$field;
474      }
475      if($field=='NumIPadress')
476          $select[]='COUNT(DISTINCT IPadress) AS NumIPadress';
477    }
478
479    // initialize filter
480    if(!is_array($filter)) $filter=array();
481
482    if(!isset($filter['IPadress'])) $filter['IPadress']=null;
483    if(!isset($filter['year'])) $filter['year']=null;
484    if(!isset($filter['month'])) $filter['month']=null;
485    if(!isset($filter['uaType'])) $filter['uaType']=null;
486    if(!isset($filter['catId'])) $filter['catId']=null;
487    if(!isset($filter['country'])) $filter['country']=null;
488
489    $filter['IPadress']=$this->checkIPFilter($filter['IPadress']);
490    // check filter values - getOperator check and 'clean' the filter
491    $filter['IPadress']=$this->getOperator($filter['IPadress'], 'IP');
492    $filter['catId']=$this->getOperator($filter['catId'], 'integer');
493    $filter['country']=$this->getOperator($filter['country'], 'string');
494    $filter['year']=$this->getOperator($filter['year'], 'integer');
495    $filter['month']=$this->getOperator($filter['month'], 'integer');
496    $filter['uaType']=$this->getOperator($filter['uaType'], 'integer');
497
498    // initialize order
499    $orderBy=array();
500    if(!is_array($order)) $order=array();
501    foreach($order as $sort)
502    {
503      if(isset($sort['id']) and isset($sort['direction']))
504      {
505        if((($sort['id']=='IPadress' or
506             $sort['id']=='country' or
507             $sort['id']=='catId' or
508             $sort['id']=='year' or
509             $sort['id']=='month' or
510             $sort['id']=='uaType' ) and
511            in_array($sort['id'], $select) or
512             $sort['id']=='nbVisits') and
513           ($sort['direction']=='A' or
514            $sort['direction']=='D')
515          ) $orderBy[]=$sort;
516      }
517    }
518    // set default order if nothing available is provided
519    if(count($orderBy)==0)
520    {
521      if(in_array('year', $select))
522        $orderBy[]=array('id'=>'year', 'direction'=>'D');
523      if(in_array('month', $select))
524        $orderBy[]=array('id'=>'month', 'direction'=>'A');
525      $orderBy[]=array('id'=>'nbVisits', 'direction'=>'D');
526    }
527
528    // build SELECT & GROUP By clauses
529    $select="SELECT ".implode(',', $select);
530    $groupBy=(count($groupBy)>0)?" GROUP BY ".implode(',', $groupBy):'';
531
532    //build ORDER BY clause
533    $orderBy=$this->buildOrderByClause($orderBy);
534
535    //build WHERE clause
536    $IPList=array();
537    $where=$this->buildWhereClause($filter, $IPList);
538
539    // build LIMIT clause
540    $limit=$this->buildLimitClause($page, $nbItemsPage);
541
542    // execute request
543    switch($mode)
544    {
545      case ASDF_GET_ROWS:
546          $sql=$select." FROM statip ".$where.$groupBy.$orderBy.$limit;
547          $sqlStm=$this->dbHandle->prepare($sql);
548          foreach($IPList as $num=>$IP)
549          {
550            $sqlStm->bindValue(':IP'.$num, $IP, SQLITE3_BLOB);
551          }
552
553          $result=$sqlStm->execute();
554          if($result)
555          {
556            while($row=$result->fetchArray(SQLITE3_ASSOC))
557            {
558              if(isset($row['IPadress']))
559                $row['IPadress']=self::IPBinaryDecode($row['IPadress']);
560              $returned[]=$row;
561            }
562          }
563        break;
564      case ASDF_GET_COUNT:
565          $sql="SELECT COUNT(*) AS nb
566                FROM (SELECT * FROM statip $where $groupBy);";
567
568          $sqlStm=$this->dbHandle->prepare($sql);
569          foreach($IPList as $num=>$IP)
570          {
571            $sqlStm->bindValue(':IP'.$num, $IP, SQLITE3_BLOB);
572          }
573
574          $result=$sqlStm->execute();
575          if($result)
576          {
577            while($row=$result->fetchArray(SQLITE3_ASSOC))
578            {
579              $returned=$row['nb'];
580            }
581          }
582        break;
583    }
584    return($returned);
585  }
586
587
588
589  /**
590   * returns stats from categories
591   *
592   * $page: the page number asked; first page is 1=>lower value means to get all values
593   * $nbItemsPage: the number of items returned per; 0 means no limit
594   *
595   *
596   * in ASDF_GET_ROWS mode, each returned logs is an array of asked fields
597   * at least, the following fields are always returned:
598   *  'nbVisits' => number of visits
599   *
600   * all fields are filterable, except 'nbVisits' that can't be filtered
601   *
602   * available fields values are:
603   *   'year'      => return detailled stat per year
604   *   'month'     => return detailled stat per month
605   *   'uaType'   => return detailled stat per browser type
606   *
607   * @param Integer $mode: type of result needed
608   *                          . ASDF_GET_ROWS return an array of logs
609   *                          . ASDF_GET_COUNT return the number of logs
610   * @param Integer $page: page number
611   * @param Integer $nbItemsPage: number of items per page
612   * @param Array $fields : additional fields to be returned
613   * @param Array $filter : filters to apply => see getLogs function for usage
614   * @param Array $order: sort to apply      => see getLogs function for usage; all fields can be sorted
615   * @return
616   */
617  public function getStatCat($mode=ASDF_GET_ROWS, $page=0, $nbItemsPage=0, $fields=array(), $filter=array(), $order=array())
618  {
619    switch($mode)
620    {
621      case ASDF_GET_COUNT:
622        $returned=0;
623        break;
624      case ASDF_GET_ROWS:
625        $returned=array();
626        break;
627    }
628    if($this->dbHandle==null) return($returned);
629
630    $select=array('SUM(visits) AS nbVisits');
631    $groupBy=array();
632    // initialize fields list
633    foreach($fields as $field)
634    {
635      if($field=='year' or
636         $field=='month' or
637         $field=='catId' or
638         $field=='uaType')
639      {
640        $select[]=$field;
641        $groupBy[]=$field;
642      }
643    }
644
645    // initialize filter
646    if(!is_array($filter)) $filter=array();
647
648    if(!isset($filter['catId'])) $filter['catId']=null;
649    if(!isset($filter['year'])) $filter['year']=null;
650    if(!isset($filter['month'])) $filter['month']=null;
651    if(!isset($filter['uaType'])) $filter['uaType']=null;
652
653    // check filter values - getOperator check and 'clean' the filter
654    $filter['catId']=$this->getOperator($filter['catId'], 'integer');
655    $filter['year']=$this->getOperator($filter['year'], 'integer');
656    $filter['month']=$this->getOperator($filter['month'], 'integer');
657    $filter['uaType']=$this->getOperator($filter['uaType'], 'integer');
658
659
660    // initialize order
661    $orderBy=array();
662    if(!is_array($order)) $order=array();
663    foreach($order as $sort)
664    {
665      if(isset($sort['id']) and isset($sort['direction']))
666      {
667        if((($sort['id']=='catId' or
668             $sort['id']=='year' or
669             $sort['id']=='month' or
670             $sort['id']=='uaType' ) and
671            in_array($sort['id'], $select) or
672             $sort['id']=='nbVisits') and
673           ($sort['direction']=='A' or
674            $sort['direction']=='D')
675          ) $orderBy[]=$sort;
676      }
677    }
678    // set default order if nothing available is provided
679    if(count($orderBy)==0)
680    {
681      if(in_array('year', $select))
682        $orderBy[]=array('id'=>'year', 'direction'=>'D');
683      if(in_array('month', $select))
684        $orderBy[]=array('id'=>'month', 'direction'=>'A');
685      $orderBy[]=array('id'=>'nbVisits', 'direction'=>'D');
686    }
687
688    // build SELECT & GROUP By clauses
689    $select="SELECT ".implode(',', $select);
690    $groupBy=(count($groupBy)>0)?" GROUP BY ".implode(',', $groupBy):'';
691
692    //build ORDER BY clause
693    $orderBy=$this->buildOrderByClause($orderBy);
694
695    //build WHERE clause
696    $IPList=array();
697    $where=$this->buildWhereClause($filter, $IPList);
698
699    // build LIMIT clause
700    $limit=$this->buildLimitClause($page, $nbItemsPage);
701
702    // execute request
703    switch($mode)
704    {
705      case ASDF_GET_ROWS:
706          $sql=$select." FROM statcategories ".$where.$groupBy.$orderBy.$limit;
707
708          $sqlStm=$this->dbHandle->prepare($sql);
709
710          $result=$sqlStm->execute();
711          if($result)
712          {
713            while($row=$result->fetchArray(SQLITE3_ASSOC))
714            {
715              $returned[]=$row;
716            }
717          }
718        break;
719      case ASDF_GET_COUNT:
720          $sql="SELECT COUNT(*) AS nb
721                FROM (SELECT * FROM statcategories $where $groupBy);";
722
723          $sqlStm=$this->dbHandle->prepare($sql);
724
725          $result=$sqlStm->execute();
726          if($result)
727          {
728            while($row=$result->fetchArray(SQLITE3_ASSOC))
729            {
730              $returned=$row['nb'];
731            }
732          }
733        break;
734    }
735    return($returned);
736  }
737
738
739
740
741  /**
742   * returns stats from images
743   *
744   *
745   * $page: the page number asked; first page is 1=>lower value means to get all values
746   * $nbItemsPage: the number of items returned per; 0 means no limit
747   *
748   *
749   * in ASDF_GET_ROWS mode, each returned logs is an array of asked fields
750   * at least, the following fields are always returned:
751   *  'nbVisits' => number of visits
752   *
753   * all fields are filterable, except 'nbVisits' that can't be filtered
754   *
755   * available fields values are:
756   *   'year'      => return detailled stat per year
757   *   'month'     => return detailled stat per month
758   *   'uaType'    => return detailled stat per browser type
759   *   'catId'     => return detailled stat per categories (an image could be linked with more than one category)
760   *
761   * @param Integer $mode: type of result needed
762   *                          . ASDF_GET_ROWS return an array of logs
763   *                          . ASDF_GET_COUNT return the number of logs
764   * @param Integer $page: page number
765   * @param Integer $nbItemsPage: number of items per page
766   * @param Array $fields : additional fields to be returned
767   * @param Array $filter : filters to apply => see getLogs function for usage
768   * @param Array $order: sort to apply      => see getLogs function for usage; all fields can be sorted
769   * @return
770   */
771  public function getStatImages($mode=ASDF_GET_ROWS, $page=0, $nbItemsPage=0, $fields=array(), $filter=array(), $order=array())
772  {
773    switch($mode)
774    {
775      case ASDF_GET_COUNT:
776        $returned=0;
777        break;
778      case ASDF_GET_ROWS:
779        $returned=array();
780        break;
781    }
782    if($this->dbHandle==null) return($returned);
783
784    $select=array('SUM(visits) AS nbVisits');
785    $groupBy=array();
786    // initialize fields list
787    foreach($fields as $field)
788    {
789      if($field=='year' or
790         $field=='month' or
791         $field=='catId' or
792         $field=='imageId' or
793         $field=='uaType')
794      {
795        $select[]=$field;
796        $groupBy[]=$field;
797      }
798    }
799
800    // initialize filter
801    if(!is_array($filter)) $filter=array();
802
803    if(!isset($filter['imageId'])) $filter['imageId']=null;
804    if(!isset($filter['year'])) $filter['year']=null;
805    if(!isset($filter['month'])) $filter['month']=null;
806    if(!isset($filter['catId'])) $filter['catId']=null;
807    if(!isset($filter['uaType'])) $filter['uaType']=null;
808
809    // check filter values - getOperator check and 'clean' the filter
810    $filter['year']=$this->getOperator($filter['year'], 'integer');
811    $filter['month']=$this->getOperator($filter['month'], 'integer');
812    $filter['imageId']=$this->getOperator($filter['imageId'], 'integer');
813    $filter['catId']=$this->getOperator($filter['catId'], 'integer');
814    $filter['uaType']=$this->getOperator($filter['uaType'], 'integer');
815
816
817    // initialize order
818    $orderBy=array();
819    if(!is_array($order)) $order=array();
820    foreach($order as $sort)
821    {
822      if(isset($sort['id']) and isset($sort['direction']))
823      {
824        if((($sort['id']=='year' or
825             $sort['id']=='month' or
826             $sort['id']=='imageId' or
827             $sort['id']=='catId' or
828             $sort['id']=='uaType' ) and
829            in_array($sort['id'], $select) or
830             $sort['id']=='nbVisits') and
831           ($sort['direction']=='A' or
832            $sort['direction']=='D')
833          ) $orderBy[]=$sort;
834      }
835    }
836    // set default order if nothing available is provided
837    if(count($orderBy)==0)
838    {
839      if(in_array('year', $select))
840        $orderBy[]=array('id'=>'year', 'direction'=>'D');
841      if(in_array('month', $select))
842        $orderBy[]=array('id'=>'month', 'direction'=>'A');
843      $orderBy[]=array('id'=>'nbVisits', 'direction'=>'D');
844    }
845
846    // build SELECT & GROUP By clauses
847    $select="SELECT ".implode(',', $select);
848    $groupBy=(count($groupBy)>0)?" GROUP BY ".implode(',', $groupBy):'';
849
850    //build ORDER BY clause
851    $orderBy=$this->buildOrderByClause($orderBy);
852
853    //build WHERE clause
854    $IPList=array();
855    $where=$this->buildWhereClause($filter, $IPList);
856
857    // build LIMIT clause
858    $limit=$this->buildLimitClause($page, $nbItemsPage);
859
860    // execute request
861    switch($mode)
862    {
863      case ASDF_GET_ROWS:
864          $sql=$select." FROM statimages ".$where.$groupBy.$orderBy.$limit;
865          $sqlStm=$this->dbHandle->prepare($sql);
866
867          $result=$sqlStm->execute();
868          if($result)
869          {
870            while($row=$result->fetchArray(SQLITE3_ASSOC))
871            {
872              $returned[]=$row;
873            }
874          }
875        break;
876      case ASDF_GET_COUNT:
877          $sql="SELECT COUNT(*) AS nb
878                FROM (SELECT * FROM statimages $where $groupBy);";
879
880          $sqlStm=$this->dbHandle->prepare($sql);
881
882          $result=$sqlStm->execute();
883          if($result)
884          {
885            while($row=$result->fetchArray(SQLITE3_ASSOC))
886            {
887              $returned=$row['nb'];
888            }
889          }
890        break;
891    }
892
893    return($returned);
894  }
895
896
897
898
899
900
901  /**
902   * returns stats for a period
903   *
904   * $page: the page number asked; first page is 1=>lower value means to get all values
905   * $nbItemsPage: the number of items returned per; 0 means no limit
906   *
907   *
908   * each returned logs is an array of asked fields
909   * at least, the following fields are always returned:
910   *  'nbVisits' => number of visits
911   *
912   * all fields are filterable, except 'nbVisits' that can't be filtered
913   *
914   * available fields values are:
915   *   'year'      => return detailled stat per year
916   *   'month'     => return detailled stat per month
917   *   'catId'     => return detailled stat per category
918   *
919   * @param Integer $page: page number
920   * @param Integer $nbItemsPage: number of items per page
921   * @param Array $fields : additional fields to be returned
922   * @param Array $filter : filters to apply => see getLogs function for usage
923   * @param Array $order: sort to apply      => see getLogs function for usage; all fields can be sorted
924   * @return
925   */
926  public function getStatPeriod($page=0, $nbItemsPage=0, $fields=array(), $filter=array(), $order=array())
927  {
928    $returned=array();
929    if($this->dbHandle==null) return($returned);
930
931    /*
932     * total: 'T' variables
933     * categories: 'C' variables
934     * images 'I' variables
935     * ip adresses: 'A' variables
936     */
937    $select=array(
938      'T' => array('"T" AS nfoType', 'SUM(visits) AS nbVisits'),
939      'C' => array('"C" AS nfoType', 'SUM(visits) AS nbVisits'),
940      'I' => array('"I" AS nfoType', 'SUM(visits) AS nbVisits'),
941      'A' => array('"A" AS nfoType', 'COUNT(DISTINCT IPadress) AS nbVisits')
942    );
943
944    $groupBy=array();
945    // initialize fields list
946    foreach($fields as $field)
947    {
948      if($field=='year' or
949         $field=='month')
950      {
951        $select['T'][]=$field;
952        $select['C'][]=$field;
953        $select['I'][]=$field;
954        $select['A'][]=$field;
955        $groupBy[]=$field;
956      }
957    }
958
959    // initialize filter
960    if(!is_array($filter)) $filter=array();
961    $filter['T']=array();
962    $filter['C']=array();
963    $filter['I']=array();
964    $filter['A']=array();
965
966    if(!isset($filter['year'])) $filter['year']=null;
967    if(!isset($filter['month'])) $filter['month']=null;
968    if(!isset($filter['catId'])) $filter['catId']=null;
969
970    foreach(array('T', 'C', 'I', 'A') as $key)
971    {
972      $filter[$key]['year']=$this->getOperator($filter['year'], 'integer');
973      $filter[$key]['month']=$this->getOperator($filter['month'], 'integer');
974      if($key=='C' and $filter['catId']==null)
975      {
976        $filter[$key]['catId']=$this->getOperator(array('operator' => '>', 'value' => 0), 'integer');
977      }
978      else
979      {
980        $filter[$key]['catId']=$this->getOperator($filter['catId'], 'integer');
981      }
982    }
983
984    // initialize order
985    $orderBy=array();
986    if(!is_array($order)) $order=array();
987    foreach($order as $sort)
988    {
989      if(isset($sort['id']) and isset($sort['direction']))
990      {
991        if((($sort['id']=='year' or
992             $sort['id']=='month') and
993            in_array($sort['id'], $select['T']) or
994             $sort['id']=='nbVisits') and
995           ($sort['direction']=='A' or
996            $sort['direction']=='D')
997          ) $orderBy[]=$sort;
998      }
999    }
1000    // set default order if nothing available is provided
1001    if(count($orderBy)==0)
1002    {
1003      if(in_array('year', $select['T']))
1004        $orderBy[]=array('id'=>'year', 'direction'=>'D');
1005      if(in_array('month', $select['T']))
1006        $orderBy[]=array('id'=>'month', 'direction'=>'A');
1007      $orderBy[]=array('id'=>'nbVisits', 'direction'=>'D');
1008    }
1009
1010    // build SELECT & GROUP By clauses
1011    foreach($select as $key => $val)
1012    {
1013      $select[$key]="SELECT ".implode(',', $val);
1014    }
1015    $groupBy=(count($groupBy)>0)?" GROUP BY ".implode(',', $groupBy):'';
1016
1017    //build ORDER BY clause
1018    //$orderBy=$this->buildOrderByClause($orderBy);
1019
1020    //build WHERE clause
1021    $IPList=array();
1022    $where=array(
1023      'T' => $this->buildWhereClause($filter['T'], $IPList),
1024      'C' => $this->buildWhereClause($filter['C'], $IPList),
1025      'I' => $this->buildWhereClause($filter['I'], $IPList),
1026      'A' => $this->buildWhereClause($filter['A'], $IPList)
1027    );
1028
1029    // build LIMIT clause
1030    //$limit=$this->buildLimitClause($page, $nbItemsPage);
1031
1032
1033
1034    $sql=$select['T']." FROM statcategories ".$where['T'].$groupBy;
1035    $sql.=' UNION '.$select['C']." FROM statcategories ".$where['C'].$groupBy;
1036    $sql.=' UNION '.$select['I']." FROM statimages ".$where['I'].$groupBy;
1037    $sql.=' UNION '.$select['A']." FROM statip ".$where['A'].$groupBy;
1038    //$sql.=$orderBy; //.$limit;
1039
1040    $sqlStm=$this->dbHandle->prepare($sql);
1041    $result=$sqlStm->execute();
1042
1043    if($result)
1044    {
1045      $year=array(
1046        'min' => 9999,
1047        'max' => 0
1048      );
1049      $sqlResult=array();
1050      while($row=$result->fetchArray(SQLITE3_ASSOC))
1051      {
1052        if(isset($row['year']))
1053        {
1054          if($row['year']>$year['max']) $year['max']=$row['year'];
1055          if($row['year']<$year['min']) $year['min']=$row['year'];
1056        }
1057        $sqlResult[]=$row;
1058      }
1059
1060      if($year['min']==9999 and $year['max']==0) $year['min']=0;
1061
1062      /*
1063       * $returned have a row per period & info type T,C,I,A
1064       *
1065       * example:
1066       *   2010 09 T 25
1067       *   2010 09 C 10
1068       *   2010 09 I 45
1069       *   2010 09 A 4
1070       *   2010 11 T 8
1071       *   2010 11 C 6
1072       *   2010 11 A 1
1073       *   2010 12 T 1
1074       *   2010 12 A 1
1075       *
1076       * the result is built as a table with:
1077       *  - info type in column
1078       *  - missing periods
1079       *
1080       * example:
1081       *   period   T   C   I   A
1082       *   2010 09  25  10  45  4
1083       *   2010 10  0   0   0   0
1084       *   2010 11  8   6   0   1
1085       *   2010 12  1   0   0   1
1086       */
1087
1088      // first step, build a period list
1089      if(in_array('year', $fields) or in_array('month', $fields))
1090      {
1091        for($y=$year['min'];$y<=$year['max'];$y++)
1092        {
1093          if(in_array('month', $fields))
1094          {
1095            $returned[$y]=array();
1096            for($m=1;$m<=12;$m++)
1097            {
1098              $returned[$y][$m]=array(
1099                'T' => 0,
1100                'C' => 0,
1101                'I' => 0,
1102                'A' => 0
1103              );
1104            }
1105          }
1106          else
1107          {
1108            $returned[$y]=array(
1109              0 => array( // no month => 0
1110                    'T' => 0,
1111                    'C' => 0,
1112                    'I' => 0,
1113                    'A' => 0
1114              )
1115            );
1116          }
1117        }
1118      }
1119      else
1120      {
1121        $returned=array(
1122          0 => array( // no year => 0
1123              0 => array( // no month => 0
1124                    'T' => 0,
1125                    'C' => 0,
1126                    'I' => 0,
1127                    'A' => 0
1128                )
1129            )
1130        );
1131      }
1132
1133      // next step, fill the table
1134      foreach($sqlResult as $row)
1135      {
1136        if(isset($row['year']))
1137        {
1138          $y=$row['year'];
1139        }
1140        else
1141        {
1142          $y=0;
1143        }
1144
1145        if(isset($row['month']))
1146        {
1147          $m=$row['month'];
1148        }
1149        else
1150        {
1151          $m=0;
1152        }
1153
1154        $returned[$y][$m][$row['nfoType']]=$row['nbVisits'];
1155      }
1156
1157    }
1158    return($returned);
1159  }
1160
1161
1162
1163
1164
1165
1166
1167  /**
1168   * check the database schema; update it if needed
1169   *
1170   * @return Boolean: true if OK, otherwise false
1171   */
1172  protected function checkSchema()
1173  {
1174    if(!parent::checkSchema()) return(false);
1175
1176    $version=$this->getInfoProperty('nfo', 'version');
1177
1178    switch($version)
1179    {
1180      case '00.00.00':
1181        // version 00.00.00
1182        //  file is just created...
1183        $result=$this->getDBInfo(ASDF_DB_TYPE_TABLE, 'files', ASDF_DB_INFO);
1184        if(count($result)==0)
1185        {
1186          $this->dbHandle->exec(
1187              "CREATE TABLE 'files' (
1188                  'year' INTEGER,
1189                  'month' INTEGER,
1190                  'logs' INTEGER,
1191                  'packed' INTEGER,
1192                  'unpacked' INTEGER,
1193                  'lastBuilt' TEXT,
1194                  'needBuild' TEXT
1195              );"
1196            );
1197          $this->dbHandle->exec("CREATE UNIQUE INDEX iPeriod ON files ('year', 'month');");
1198        }
1199
1200        $result=$this->getDBInfo(ASDF_DB_TYPE_TABLE, 'statCategories', ASDF_DB_INFO);
1201        if(count($result)==0)
1202        {
1203          $this->dbHandle->exec(
1204              "CREATE TABLE 'statCategories' (
1205                  'catId' INTEGER,
1206                  'uaType' INTEGER,
1207                  'year' INTEGER,
1208                  'month' INTEGER,
1209                  'visits' INTEGER
1210              );"
1211            );
1212          $this->dbHandle->exec("CREATE UNIQUE INDEX iCat ON statCategories ('catId', 'uaType', 'year', 'month');");
1213          $this->dbHandle->exec("CREATE UNIQUE INDEX iCatPeriod ON statCategories ('year', 'month', 'uaType', 'catId');");
1214        }
1215
1216        $result=$this->getDBInfo(ASDF_DB_TYPE_TABLE, 'statImages', ASDF_DB_INFO);
1217        if(count($result)==0)
1218        {
1219          $this->dbHandle->exec(
1220              "CREATE TABLE 'statImages' (
1221                  'imageId' INTEGER,
1222                  'catId' INTEGER,
1223                  'uaType' INTEGER,
1224                  'year' INTEGER,
1225                  'month' INTEGER,
1226                  'visits' INTEGER
1227              );"
1228            );
1229          $this->dbHandle->exec("CREATE UNIQUE INDEX iImg ON statImages ('imageId', 'catId', 'uaType', 'year', 'month');");
1230          $this->dbHandle->exec("CREATE UNIQUE INDEX iImgPeriod ON statImages ('year', 'month', 'uaType', 'imageId');");
1231        }
1232
1233        $result=$this->getDBInfo(ASDF_DB_TYPE_TABLE, 'statIP', ASDF_DB_INFO);
1234        if(count($result)==0)
1235        {
1236          $this->dbHandle->exec(
1237              "CREATE TABLE 'statIP' (
1238                  'uaType' INTEGER,
1239                  'IPadress' BLOB,
1240                  'catId' INTEGER,
1241                  'year' INTEGER,
1242                  'month' INTEGER,
1243                  'visits' INTEGER,
1244                  'country' TEXT
1245              );"
1246            );
1247          $this->dbHandle->exec("CREATE UNIQUE INDEX iIP ON statIP ('uaType', 'IPadress', 'catId', 'year', 'month');");
1248          $this->dbHandle->exec("CREATE INDEX iIPPeriod ON statIP ('year', 'month', 'IPadress', 'uaType');");
1249        }
1250
1251        $result=$this->getDBInfo(ASDF_DB_TYPE_TABLE, 'statUA', ASDF_DB_INFO);
1252        if(count($result)==0)
1253        {
1254          $this->dbHandle->exec(
1255              "CREATE TABLE 'statUA' (
1256                  'uaData' INTEGER,
1257                  'uaValue' INTEGER,
1258                  'uaVersion' TEXT,
1259                  'year' INTEGER,
1260                  'month' INTEGER,
1261                  'visits' INTEGER
1262              );"
1263            );
1264          $this->dbHandle->exec("CREATE UNIQUE INDEX iUA ON statUA ('uaData', 'uaValue', 'uaVersion', 'year', 'month');");
1265          $this->dbHandle->exec("CREATE INDEX iUAPeriod ON statUA ('year', 'month', 'uaData', 'uaValue');");
1266        }
1267
1268        $this->setInfoProperty('nfo', 'version', '01.00.00');
1269        return(true);
1270        break;
1271      default:
1272        break;
1273    }
1274    return(false);
1275  }
1276
1277} // class
1278
1279
1280?>
Note: See TracBrowser for help on using the repository browser.