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

Revision 17737, 38.4 KB checked in by grum, 7 years ago (diff)

First commit for EStat files

  • Property svn:executable set to *
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;
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      if($fmPeriod->pack())
243        $fmPeriod->delete(ASDF_DELETE_UNPACKED); //packed file was unpacked, keep only packed file
244    }
245    return($returned);
246  }
247
248
249
250
251
252  /**
253   * returns stats from useragent strings (os, browser, engine, type)
254   *
255   * $page: the page number asked; first page is 1=>lower value means to get all values
256   * $nbItemsPage: the number of items returned per; 0 means no limit
257   *
258   *
259   * in ASDF_GET_ROWS mode, each returned logs is an array of asked fields
260   * at least, the following fields are always returned:
261   *  'nbVisits' => number of visits
262   *  'uaValue'  => value (meaning of this information can change)
263   *
264   *
265   * only asked fields are filterable, except 'nbVisits' that can't be filtered
266   *
267   * available fields values are:
268   *   'uaData'   => return the type of data; can get one of the following values
269   *                          UA_DATA_BROWSER: stat about browser
270   *                          UA_DATA_ENGINE: stat about engine
271   *                          UA_DATA_OS: stat about OS
272   *                          UA_DATA_BROWSER_TYPE: stat about browser type
273   *   'uaValue'   => return detailled stat per type of data
274   *   'year'      => return detailled stat per year
275   *   'month'     => return detailled stat per month
276   *   'uaVersion' => return detailled stat per version (browser version, os version, ...)
277   *
278   * @param Integer $mode: type of result needed
279   *                          . ASDF_GET_ROWS return an array of logs
280   *                          . ASDF_GET_COUNT return the number of logs
281   * @param Integer $page: page number
282   * @param Integer $nbItemsPage: number of items per page
283   * @param Array $fields : additional fields to be returned
284   * @param Array $filter : filters to apply => see getLogs function for usage
285   * @param Array $order: sort to apply      => see getLogs function for usage; all fields can be sorted
286   * @return
287   */
288  public function getStatUserAgent($mode=ASDF_GET_ROWS, $page=0, $nbItemsPage=0, $fields=array(), $filter=array(), $order=array())
289  {
290    switch($mode)
291    {
292      case ASDF_GET_COUNT:
293        $returned=0;
294        break;
295      case ASDF_GET_ROWS:
296        $returned=array();
297        break;
298    }
299    if($this->dbHandle==null) return($returned);
300
301    $select=array('uaData', 'SUM(visits) AS nbVisits');
302    $groupBy=array('uaData');
303    // initialize fields list
304    foreach($fields as $field)
305    {
306      if($field=='uaValue' or
307         $field=='year' or
308         $field=='month' or
309         $field=='uaVersion')
310      {
311        $select[]=$field;
312        $groupBy[]=$field;
313      }
314    }
315
316    // initialize filter
317    if(!is_array($filter)) $filter=array();
318
319    if(!isset($filter['uaData'])) $filter['uaData']=null;
320    if(!isset($filter['uaValue'])) $filter['uaValue']=null;
321    if(!isset($filter['year'])) $filter['year']=null;
322    if(!isset($filter['month'])) $filter['month']=null;
323    if(!isset($filter['uaVersion'])) $filter['uaVersion']=null;
324
325    // check filter values - getOperator check and 'clean' the filter
326    $filter['uaData']=$this->getOperator($filter['uaData'], 'integer');
327    $filter['uaValue']=$this->getOperator($filter['uaValue'], 'integer');
328    $filter['year']=$this->getOperator($filter['year'], 'integer');
329    $filter['month']=$this->getOperator($filter['month'], 'integer');
330    $filter['uaVersion']=$this->getOperator($filter['uaVersion'], 'string');
331
332
333    // initialize order
334    $orderBy=array();
335    if(!is_array($order)) $order=array();
336    foreach($order as $sort)
337    {
338      if(isset($sort['id']) and isset($sort['direction']))
339      {
340        if((($sort['id']=='year' or
341             $sort['id']=='month' or
342             $sort['id']=='uaData' or
343             $sort['id']=='uaVersion' or
344             $sort['id']=='uaValue' ) and
345            in_array($sort['id'], $select) or
346             $sort['id']=='nbVisits') and
347           ($sort['direction']=='A' or
348            $sort['direction']=='D')
349          ) $orderBy[]=$sort;
350      }
351    }
352    // set default order if nothing available is provided
353    if(count($orderBy)==0)
354    {
355      if(in_array('year', $select))
356        $orderBy[]=array('id'=>'year', 'direction'=>'D');
357      if(in_array('month', $select))
358        $orderBy[]=array('id'=>'month', 'direction'=>'A');
359      $orderBy[]=array('id'=>'nbVisits', 'direction'=>'D');
360    }
361
362
363    // build SELECT & GROUP By clauses
364    $select="SELECT ".implode(',', $select);
365    $groupBy=(count($groupBy)>0)?" GROUP BY ".implode(',', $groupBy):'';
366
367    //build ORDER BY clause
368    $orderBy=$this->buildOrderByClause($orderBy);
369
370    //build WHERE clause
371    $IPList=array();
372    $where=$this->buildWhereClause($filter, $IPList);
373
374    // build LIMIT clause
375    $limit=$this->buildLimitClause($page, $nbItemsPage);
376
377    // execute request
378    switch($mode)
379    {
380      case ASDF_GET_ROWS:
381          $sql=$select." FROM statua ".$where.$groupBy.$orderBy.$limit;
382
383          $sqlStm=$this->dbHandle->prepare($sql);
384
385          $result=$sqlStm->execute();
386          if($result)
387          {
388            while($row=$result->fetchArray(SQLITE3_ASSOC))
389            {
390              $returned[]=$row;
391            }
392          }
393        break;
394      case ASDF_GET_COUNT:
395          $sql="SELECT COUNT(*) AS nb
396                FROM statua $where;";
397
398          $sqlStm=$this->dbHandle->prepare($sql);
399
400          $result=$sqlStm->execute();
401          if($result)
402          {
403            while($row=$result->fetchArray(SQLITE3_ASSOC))
404            {
405              $returned=$row['nb'];
406            }
407          }
408        break;
409    }
410    return($returned);
411  }
412
413
414  /**
415   * returns stats from IP
416   *
417   * $page: the page number asked; first page is 1=>lower value means to get all values
418   * $nbItemsPage: the number of items returned per; 0 means no limit
419   *
420   *
421   * in ASDF_GET_ROWS mode, each returned logs is an array of asked fields
422   * at least, the following fields are always returned:
423   *  'nbVisits' => number of visits
424   *
425   * all fields are filterable, except 'nbVisits' that can't be filtered
426   *
427   * available fields values are:
428   *   'NumIPadress' => return the number of distinct IP address
429   *   'IPadress'    => return the IP address
430   *   'year'        => return detailled stat per year
431   *   'month'       => return detailled stat per month
432   *   'uaType'      => return detailled stat per browser type
433   *   'country'     => return detailled stat per country
434   *   'catId'       => return detailled stat per category
435   *
436   * @param Integer $mode: type of result needed
437   *                          . ASDF_GET_ROWS return an array of logs
438   *                          . ASDF_GET_COUNT return the number of logs
439   * @param Integer $page: page number
440   * @param Integer $nbItemsPage: number of items per page
441   * @param Array $fields : additional fields to be returned
442   * @param Array $filter : filters to apply => see getLogs function for usage
443   * @param Array $order: sort to apply      => see getLogs function for usage; all fields can be sorted
444   * @return
445   */
446  public function getStatIP($mode=ASDF_GET_ROWS, $page=0, $nbItemsPage=0, $fields=array(), $filter=array(), $order=array())
447  {
448    switch($mode)
449    {
450      case ASDF_GET_COUNT:
451        $returned=0;
452        break;
453      case ASDF_GET_ROWS:
454        $returned=array();
455        break;
456    }
457    if($this->dbHandle==null) return($returned);
458
459    $select=array('SUM(visits) AS nbVisits');
460    $groupBy=array();
461    // initialize fields list
462    foreach($fields as $field)
463    {
464      if($field=='IPadress' or
465         $field=='year' or
466         $field=='month' or
467         $field=='catId' or
468         $field=='country' or
469         $field=='uaType')
470      {
471        $select[]=$field;
472        $groupBy[]=$field;
473      }
474      if($field=='NumIPadress')
475          $select[]='COUNT(DISTINCT IPadress) AS NumIPadress';
476    }
477
478    // initialize filter
479    if(!is_array($filter)) $filter=array();
480
481    if(!isset($filter['IPadress'])) $filter['IPadress']=null;
482    if(!isset($filter['year'])) $filter['year']=null;
483    if(!isset($filter['month'])) $filter['month']=null;
484    if(!isset($filter['uaType'])) $filter['uaType']=null;
485    if(!isset($filter['catId'])) $filter['catId']=null;
486    if(!isset($filter['country'])) $filter['country']=null;
487
488    // check filter values - getOperator check and 'clean' the filter
489    $filter['IPadress']=$this->getOperator($filter['IPadress'], 'IP');
490    $filter['catId']=$this->getOperator($filter['catId'], 'integer');
491    $filter['country']=$this->getOperator($filter['country'], 'string');
492    $filter['year']=$this->getOperator($filter['year'], 'integer');
493    $filter['month']=$this->getOperator($filter['month'], 'integer');
494    $filter['uaType']=$this->getOperator($filter['uaType'], 'integer');
495
496    // initialize order
497    $orderBy=array();
498    if(!is_array($order)) $order=array();
499    foreach($order as $sort)
500    {
501      if(isset($sort['id']) and isset($sort['direction']))
502      {
503        if((($sort['id']=='IPadress' or
504             $sort['id']=='country' or
505             $sort['id']=='catId' or
506             $sort['id']=='year' or
507             $sort['id']=='month' or
508             $sort['id']=='uaType' ) and
509            in_array($sort['id'], $select) or
510             $sort['id']=='nbVisits') and
511           ($sort['direction']=='A' or
512            $sort['direction']=='D')
513          ) $orderBy[]=$sort;
514      }
515    }
516    // set default order if nothing available is provided
517    if(count($orderBy)==0)
518    {
519      if(in_array('year', $select))
520        $orderBy[]=array('id'=>'year', 'direction'=>'D');
521      if(in_array('month', $select))
522        $orderBy[]=array('id'=>'month', 'direction'=>'A');
523      $orderBy[]=array('id'=>'nbVisits', 'direction'=>'D');
524    }
525
526    // build SELECT & GROUP By clauses
527    $select="SELECT ".implode(',', $select);
528    $groupBy=(count($groupBy)>0)?" GROUP BY ".implode(',', $groupBy):'';
529
530    //build ORDER BY clause
531    $orderBy=$this->buildOrderByClause($orderBy);
532
533    //build WHERE clause
534    $IPList=array();
535    $where=$this->buildWhereClause($filter, $IPList);
536
537    // build LIMIT clause
538    $limit=$this->buildLimitClause($page, $nbItemsPage);
539
540    // execute request
541    switch($mode)
542    {
543      case ASDF_GET_ROWS:
544          $sql=$select." FROM statip ".$where.$groupBy.$orderBy.$limit;
545
546          $sqlStm=$this->dbHandle->prepare($sql);
547          foreach($IPList as $num=>$IP)
548          {
549            $sqlStm->bindValue(':IP'.$num, $IP, SQLITE3_BLOB);
550          }
551
552          $result=$sqlStm->execute();
553          if($result)
554          {
555            while($row=$result->fetchArray(SQLITE3_ASSOC))
556            {
557              if(isset($row['IPadress']))
558                $row['IPadress']=self::IPBinaryDecode($row['IPadress']);
559              $returned[]=$row;
560            }
561          }
562        break;
563      case ASDF_GET_COUNT:
564          $sql="SELECT COUNT(*) AS nb
565                FROM (SELECT * FROM statip $where $groupBy);";
566
567          $sqlStm=$this->dbHandle->prepare($sql);
568          foreach($IPList as $num=>$IP)
569          {
570            $sqlStm->bindValue(':IP'.$num, $IP, SQLITE3_BLOB);
571          }
572
573          $result=$sqlStm->execute();
574          if($result)
575          {
576            while($row=$result->fetchArray(SQLITE3_ASSOC))
577            {
578              $returned=$row['nb'];
579            }
580          }
581        break;
582    }
583    return($returned);
584  }
585
586
587
588  /**
589   * returns stats from categories
590   *
591   * $page: the page number asked; first page is 1=>lower value means to get all values
592   * $nbItemsPage: the number of items returned per; 0 means no limit
593   *
594   *
595   * in ASDF_GET_ROWS mode, each returned logs is an array of asked fields
596   * at least, the following fields are always returned:
597   *  'nbVisits' => number of visits
598   *
599   * all fields are filterable, except 'nbVisits' that can't be filtered
600   *
601   * available fields values are:
602   *   'year'      => return detailled stat per year
603   *   'month'     => return detailled stat per month
604   *   'uaType'   => return detailled stat per browser type
605   *
606   * @param Integer $mode: type of result needed
607   *                          . ASDF_GET_ROWS return an array of logs
608   *                          . ASDF_GET_COUNT return the number of logs
609   * @param Integer $page: page number
610   * @param Integer $nbItemsPage: number of items per page
611   * @param Array $fields : additional fields to be returned
612   * @param Array $filter : filters to apply => see getLogs function for usage
613   * @param Array $order: sort to apply      => see getLogs function for usage; all fields can be sorted
614   * @return
615   */
616  public function getStatCat($mode=ASDF_GET_ROWS, $page=0, $nbItemsPage=0, $fields=array(), $filter=array(), $order=array())
617  {
618    switch($mode)
619    {
620      case ASDF_GET_COUNT:
621        $returned=0;
622        break;
623      case ASDF_GET_ROWS:
624        $returned=array();
625        break;
626    }
627    if($this->dbHandle==null) return($returned);
628
629    $select=array('SUM(visits) AS nbVisits');
630    $groupBy=array();
631    // initialize fields list
632    foreach($fields as $field)
633    {
634      if($field=='year' or
635         $field=='month' or
636         $field=='catId' or
637         $field=='uaType')
638      {
639        $select[]=$field;
640        $groupBy[]=$field;
641      }
642    }
643
644    // initialize filter
645    if(!is_array($filter)) $filter=array();
646
647    if(!isset($filter['catId'])) $filter['catId']=null;
648    if(!isset($filter['year'])) $filter['year']=null;
649    if(!isset($filter['month'])) $filter['month']=null;
650    if(!isset($filter['uaType'])) $filter['uaType']=null;
651
652    // check filter values - getOperator check and 'clean' the filter
653    $filter['catId']=$this->getOperator($filter['catId'], 'integer');
654    $filter['year']=$this->getOperator($filter['year'], 'integer');
655    $filter['month']=$this->getOperator($filter['month'], 'integer');
656    $filter['uaType']=$this->getOperator($filter['uaType'], 'integer');
657
658
659    // initialize order
660    $orderBy=array();
661    if(!is_array($order)) $order=array();
662    foreach($order as $sort)
663    {
664      if(isset($sort['id']) and isset($sort['direction']))
665      {
666        if((($sort['id']=='catId' or
667             $sort['id']=='year' or
668             $sort['id']=='month' or
669             $sort['id']=='uaType' ) and
670            in_array($sort['id'], $select) or
671             $sort['id']=='nbVisits') and
672           ($sort['direction']=='A' or
673            $sort['direction']=='D')
674          ) $orderBy[]=$sort;
675      }
676    }
677    // set default order if nothing available is provided
678    if(count($orderBy)==0)
679    {
680      if(in_array('year', $select))
681        $orderBy[]=array('id'=>'year', 'direction'=>'D');
682      if(in_array('month', $select))
683        $orderBy[]=array('id'=>'month', 'direction'=>'A');
684      $orderBy[]=array('id'=>'nbVisits', 'direction'=>'D');
685    }
686
687    // build SELECT & GROUP By clauses
688    $select="SELECT ".implode(',', $select);
689    $groupBy=(count($groupBy)>0)?" GROUP BY ".implode(',', $groupBy):'';
690
691    //build ORDER BY clause
692    $orderBy=$this->buildOrderByClause($orderBy);
693
694    //build WHERE clause
695    $IPList=array();
696    $where=$this->buildWhereClause($filter, $IPList);
697
698    // build LIMIT clause
699    $limit=$this->buildLimitClause($page, $nbItemsPage);
700
701    // execute request
702    switch($mode)
703    {
704      case ASDF_GET_ROWS:
705          $sql=$select." FROM statcategories ".$where.$groupBy.$orderBy.$limit;
706
707          $sqlStm=$this->dbHandle->prepare($sql);
708
709          $result=$sqlStm->execute();
710          if($result)
711          {
712            while($row=$result->fetchArray(SQLITE3_ASSOC))
713            {
714              $returned[]=$row;
715            }
716          }
717        break;
718      case ASDF_GET_COUNT:
719          $sql="SELECT COUNT(*) AS nb
720                FROM (SELECT * FROM statcategories $where $groupBy);";
721
722          $sqlStm=$this->dbHandle->prepare($sql);
723
724          $result=$sqlStm->execute();
725          if($result)
726          {
727            while($row=$result->fetchArray(SQLITE3_ASSOC))
728            {
729              $returned=$row['nb'];
730            }
731          }
732        break;
733    }
734    return($returned);
735  }
736
737
738
739
740  /**
741   * returns stats from images
742   *
743   *
744   * $page: the page number asked; first page is 1=>lower value means to get all values
745   * $nbItemsPage: the number of items returned per; 0 means no limit
746   *
747   *
748   * in ASDF_GET_ROWS mode, each returned logs is an array of asked fields
749   * at least, the following fields are always returned:
750   *  'nbVisits' => number of visits
751   *
752   * all fields are filterable, except 'nbVisits' that can't be filtered
753   *
754   * available fields values are:
755   *   'year'      => return detailled stat per year
756   *   'month'     => return detailled stat per month
757   *   'uaType'    => return detailled stat per browser type
758   *   'catId'     => return detailled stat per categories (an image could be linked with more than one category)
759   *
760   * @param Integer $mode: type of result needed
761   *                          . ASDF_GET_ROWS return an array of logs
762   *                          . ASDF_GET_COUNT return the number of logs
763   * @param Integer $page: page number
764   * @param Integer $nbItemsPage: number of items per page
765   * @param Array $fields : additional fields to be returned
766   * @param Array $filter : filters to apply => see getLogs function for usage
767   * @param Array $order: sort to apply      => see getLogs function for usage; all fields can be sorted
768   * @return
769   */
770  public function getStatImages($mode=ASDF_GET_ROWS, $page=0, $nbItemsPage=0, $fields=array(), $filter=array(), $order=array())
771  {
772    switch($mode)
773    {
774      case ASDF_GET_COUNT:
775        $returned=0;
776        break;
777      case ASDF_GET_ROWS:
778        $returned=array();
779        break;
780    }
781    if($this->dbHandle==null) return($returned);
782
783    $select=array('SUM(visits) AS nbVisits');
784    $groupBy=array();
785    // initialize fields list
786    foreach($fields as $field)
787    {
788      if($field=='year' or
789         $field=='month' or
790         $field=='catId' or
791         $field=='imageId' or
792         $field=='uaType')
793      {
794        $select[]=$field;
795        $groupBy[]=$field;
796      }
797    }
798
799    // initialize filter
800    if(!is_array($filter)) $filter=array();
801
802    if(!isset($filter['imageId'])) $filter['imageId']=null;
803    if(!isset($filter['year'])) $filter['year']=null;
804    if(!isset($filter['month'])) $filter['month']=null;
805    if(!isset($filter['catId'])) $filter['catId']=null;
806    if(!isset($filter['uaType'])) $filter['uaType']=null;
807
808    // check filter values - getOperator check and 'clean' the filter
809    $filter['year']=$this->getOperator($filter['year'], 'integer');
810    $filter['month']=$this->getOperator($filter['month'], 'integer');
811    $filter['imageId']=$this->getOperator($filter['imageId'], 'integer');
812    $filter['catId']=$this->getOperator($filter['catId'], 'integer');
813    $filter['uaType']=$this->getOperator($filter['uaType'], 'integer');
814
815
816    // initialize order
817    $orderBy=array();
818    if(!is_array($order)) $order=array();
819    foreach($order as $sort)
820    {
821      if(isset($sort['id']) and isset($sort['direction']))
822      {
823        if((($sort['id']=='year' or
824             $sort['id']=='month' or
825             $sort['id']=='imageId' or
826             $sort['id']=='catId' or
827             $sort['id']=='uaType' ) and
828            in_array($sort['id'], $select) or
829             $sort['id']=='nbVisits') and
830           ($sort['direction']=='A' or
831            $sort['direction']=='D')
832          ) $orderBy[]=$sort;
833      }
834    }
835    // set default order if nothing available is provided
836    if(count($orderBy)==0)
837    {
838      if(in_array('year', $select))
839        $orderBy[]=array('id'=>'year', 'direction'=>'D');
840      if(in_array('month', $select))
841        $orderBy[]=array('id'=>'month', 'direction'=>'A');
842      $orderBy[]=array('id'=>'nbVisits', 'direction'=>'D');
843    }
844
845    // build SELECT & GROUP By clauses
846    $select="SELECT ".implode(',', $select);
847    $groupBy=(count($groupBy)>0)?" GROUP BY ".implode(',', $groupBy):'';
848
849    //build ORDER BY clause
850    $orderBy=$this->buildOrderByClause($orderBy);
851
852    //build WHERE clause
853    $IPList=array();
854    $where=$this->buildWhereClause($filter, $IPList);
855
856    // build LIMIT clause
857    $limit=$this->buildLimitClause($page, $nbItemsPage);
858
859    // execute request
860    switch($mode)
861    {
862      case ASDF_GET_ROWS:
863          $sql=$select." FROM statimages ".$where.$groupBy.$orderBy.$limit;
864          $sqlStm=$this->dbHandle->prepare($sql);
865
866          $result=$sqlStm->execute();
867          if($result)
868          {
869            while($row=$result->fetchArray(SQLITE3_ASSOC))
870            {
871              $returned[]=$row;
872            }
873          }
874        break;
875      case ASDF_GET_COUNT:
876          $sql="SELECT COUNT(*) AS nb
877                FROM (SELECT * FROM statimages $where $groupBy);";
878
879          $sqlStm=$this->dbHandle->prepare($sql);
880
881          $result=$sqlStm->execute();
882          if($result)
883          {
884            while($row=$result->fetchArray(SQLITE3_ASSOC))
885            {
886              $returned=$row['nb'];
887            }
888          }
889        break;
890    }
891
892    return($returned);
893  }
894
895
896
897
898
899
900  /**
901   * returns stats for a period
902   *
903   * $page: the page number asked; first page is 1=>lower value means to get all values
904   * $nbItemsPage: the number of items returned per; 0 means no limit
905   *
906   *
907   * each returned logs is an array of asked fields
908   * at least, the following fields are always returned:
909   *  'nbVisits' => number of visits
910   *
911   * all fields are filterable, except 'nbVisits' that can't be filtered
912   *
913   * available fields values are:
914   *   'year'      => return detailled stat per year
915   *   'month'     => return detailled stat per month
916   *   'catId'     => return detailled stat per category
917   *
918   * @param Integer $page: page number
919   * @param Integer $nbItemsPage: number of items per page
920   * @param Array $fields : additional fields to be returned
921   * @param Array $filter : filters to apply => see getLogs function for usage
922   * @param Array $order: sort to apply      => see getLogs function for usage; all fields can be sorted
923   * @return
924   */
925  public function getStatPeriod($page=0, $nbItemsPage=0, $fields=array(), $filter=array(), $order=array())
926  {
927    $returned=array();
928    if($this->dbHandle==null) return($returned);
929
930    /*
931     * total: 'T' variables
932     * categories: 'C' variables
933     * images 'I' variables
934     * ip adresses: 'A' variables
935     */
936    $select=array(
937      'T' => array('"T" AS nfoType', 'SUM(visits) AS nbVisits'),
938      'C' => array('"C" AS nfoType', 'SUM(visits) AS nbVisits'),
939      'I' => array('"I" AS nfoType', 'SUM(visits) AS nbVisits'),
940      'A' => array('"A" AS nfoType', 'COUNT(DISTINCT IPadress) AS nbVisits')
941    );
942
943    $groupBy=array();
944    // initialize fields list
945    foreach($fields as $field)
946    {
947      if($field=='year' or
948         $field=='month')
949      {
950        $select['T'][]=$field;
951        $select['C'][]=$field;
952        $select['I'][]=$field;
953        $select['A'][]=$field;
954        $groupBy[]=$field;
955      }
956    }
957
958    // initialize filter
959    if(!is_array($filter)) $filter=array();
960    $filter['T']=array();
961    $filter['C']=array();
962    $filter['I']=array();
963    $filter['A']=array();
964
965    if(!isset($filter['year'])) $filter['year']=null;
966    if(!isset($filter['month'])) $filter['month']=null;
967    if(!isset($filter['catId'])) $filter['catId']=null;
968
969    foreach(array('T', 'C', 'I', 'A') as $key)
970    {
971      $filter[$key]['year']=$this->getOperator($filter['year'], 'integer');
972      $filter[$key]['month']=$this->getOperator($filter['month'], 'integer');
973      if($key=='C' and $filter['catId']==null)
974      {
975        $filter[$key]['catId']=$this->getOperator(array('operator' => '>', 'value' => 0), 'integer');
976      }
977      else
978      {
979        $filter[$key]['catId']=$this->getOperator($filter['catId'], 'integer');
980      }
981    }
982
983    // initialize order
984    $orderBy=array();
985    if(!is_array($order)) $order=array();
986    foreach($order as $sort)
987    {
988      if(isset($sort['id']) and isset($sort['direction']))
989      {
990        if((($sort['id']=='year' or
991             $sort['id']=='month') and
992            in_array($sort['id'], $select['T']) or
993             $sort['id']=='nbVisits') and
994           ($sort['direction']=='A' or
995            $sort['direction']=='D')
996          ) $orderBy[]=$sort;
997      }
998    }
999    // set default order if nothing available is provided
1000    if(count($orderBy)==0)
1001    {
1002      if(in_array('year', $select['T']))
1003        $orderBy[]=array('id'=>'year', 'direction'=>'D');
1004      if(in_array('month', $select['T']))
1005        $orderBy[]=array('id'=>'month', 'direction'=>'A');
1006      $orderBy[]=array('id'=>'nbVisits', 'direction'=>'D');
1007    }
1008
1009    // build SELECT & GROUP By clauses
1010    foreach($select as $key => $val)
1011    {
1012      $select[$key]="SELECT ".implode(',', $val);
1013    }
1014    $groupBy=(count($groupBy)>0)?" GROUP BY ".implode(',', $groupBy):'';
1015
1016    //build ORDER BY clause
1017    //$orderBy=$this->buildOrderByClause($orderBy);
1018
1019    //build WHERE clause
1020    $IPList=array();
1021    $where=array(
1022      'T' => $this->buildWhereClause($filter['T'], $IPList),
1023      'C' => $this->buildWhereClause($filter['C'], $IPList),
1024      'I' => $this->buildWhereClause($filter['I'], $IPList),
1025      'A' => $this->buildWhereClause($filter['A'], $IPList)
1026    );
1027
1028    // build LIMIT clause
1029    //$limit=$this->buildLimitClause($page, $nbItemsPage);
1030
1031
1032
1033    $sql=$select['T']." FROM statcategories ".$where['T'].$groupBy;
1034    $sql.=' UNION '.$select['C']." FROM statcategories ".$where['C'].$groupBy;
1035    $sql.=' UNION '.$select['I']." FROM statimages ".$where['I'].$groupBy;
1036    $sql.=' UNION '.$select['A']." FROM statip ".$where['A'].$groupBy;
1037    //$sql.=$orderBy; //.$limit;
1038
1039    $sqlStm=$this->dbHandle->prepare($sql);
1040    $result=$sqlStm->execute();
1041
1042    if($result)
1043    {
1044      $year=array(
1045        'min' => 9999,
1046        'max' => 0
1047      );
1048      $sqlResult=array();
1049      while($row=$result->fetchArray(SQLITE3_ASSOC))
1050      {
1051        if(isset($row['year']))
1052        {
1053          if($row['year']>$year['max']) $year['max']=$row['year'];
1054          if($row['year']<$year['min']) $year['min']=$row['year'];
1055        }
1056        $sqlResult[]=$row;
1057      }
1058
1059      if($year['min']==9999 and $year['max']==0) $year['min']=0;
1060
1061      /*
1062       * $returned have a row per period & info type T,C,I,A
1063       *
1064       * example:
1065       *   2010 09 T 25
1066       *   2010 09 C 10
1067       *   2010 09 I 45
1068       *   2010 09 A 4
1069       *   2010 11 T 8
1070       *   2010 11 C 6
1071       *   2010 11 A 1
1072       *   2010 12 T 1
1073       *   2010 12 A 1
1074       *
1075       * the result is built as a table with:
1076       *  - info type in column
1077       *  - missing periods
1078       *
1079       * example:
1080       *   period   T   C   I   A
1081       *   2010 09  25  10  45  4
1082       *   2010 10  0   0   0   0
1083       *   2010 11  8   6   0   1
1084       *   2010 12  1   0   0   1
1085       */
1086
1087      // first step, build a period list
1088      if(in_array('year', $fields) or in_array('month', $fields))
1089      {
1090        for($y=$year['min'];$y<=$year['max'];$y++)
1091        {
1092          if(in_array('month', $fields))
1093          {
1094            $returned[$y]=array();
1095            for($m=1;$m<=12;$m++)
1096            {
1097              $returned[$y][$m]=array(
1098                'T' => 0,
1099                'C' => 0,
1100                'I' => 0,
1101                'A' => 0
1102              );
1103            }
1104          }
1105          else
1106          {
1107            $returned[$y]=array(
1108              0 => array( // no month => 0
1109                    'T' => 0,
1110                    'C' => 0,
1111                    'I' => 0,
1112                    'A' => 0
1113              )
1114            );
1115          }
1116        }
1117      }
1118      else
1119      {
1120        $returned=array(
1121          0 => array( // no year => 0
1122              0 => array( // no month => 0
1123                    'T' => 0,
1124                    'C' => 0,
1125                    'I' => 0,
1126                    'A' => 0
1127                )
1128            )
1129        );
1130      }
1131
1132      // next step, fill the table
1133      foreach($sqlResult as $row)
1134      {
1135        if(isset($row['year']))
1136        {
1137          $y=$row['year'];
1138        }
1139        else
1140        {
1141          $y=0;
1142        }
1143
1144        if(isset($row['month']))
1145        {
1146          $m=$row['month'];
1147        }
1148        else
1149        {
1150          $m=0;
1151        }
1152
1153        $returned[$y][$m][$row['nfoType']]=$row['nbVisits'];
1154      }
1155
1156    }
1157    return($returned);
1158  }
1159
1160
1161
1162
1163
1164
1165
1166  /**
1167   * check the database schema; update it if needed
1168   *
1169   * @return Boolean: true if OK, otherwise false
1170   */
1171  protected function checkSchema()
1172  {
1173    if(!parent::checkSchema()) return(false);
1174
1175    $version=$this->getInfoProperty('nfo', 'version');
1176
1177    switch($version)
1178    {
1179      case '00.00.00':
1180        // version 00.00.00
1181        //  file is just created...
1182        $result=$this->getDBInfo(ASDF_DB_TYPE_TABLE, 'files', ASDF_DB_INFO);
1183        if(count($result)==0)
1184        {
1185          $this->dbHandle->exec(
1186              "CREATE TABLE 'files' (
1187                  'year' INTEGER,
1188                  'month' INTEGER,
1189                  'logs' INTEGER,
1190                  'packed' INTEGER,
1191                  'unpacked' INTEGER,
1192                  'lastBuilt' TEXT,
1193                  'needBuild' TEXT
1194              );"
1195            );
1196          $this->dbHandle->exec("CREATE UNIQUE INDEX iPeriod ON files ('year', 'month');");
1197        }
1198
1199        $result=$this->getDBInfo(ASDF_DB_TYPE_TABLE, 'statCategories', ASDF_DB_INFO);
1200        if(count($result)==0)
1201        {
1202          $this->dbHandle->exec(
1203              "CREATE TABLE 'statCategories' (
1204                  'catId' INTEGER,
1205                  'uaType' INTEGER,
1206                  'year' INTEGER,
1207                  'month' INTEGER,
1208                  'visits' INTEGER
1209              );"
1210            );
1211          $this->dbHandle->exec("CREATE UNIQUE INDEX iCat ON statCategories ('catId', 'uaType', 'year', 'month');");
1212          $this->dbHandle->exec("CREATE UNIQUE INDEX iCatPeriod ON statCategories ('year', 'month', 'uaType', 'catId');");
1213        }
1214
1215        $result=$this->getDBInfo(ASDF_DB_TYPE_TABLE, 'statImages', ASDF_DB_INFO);
1216        if(count($result)==0)
1217        {
1218          $this->dbHandle->exec(
1219              "CREATE TABLE 'statImages' (
1220                  'imageId' INTEGER,
1221                  'catId' INTEGER,
1222                  'uaType' INTEGER,
1223                  'year' INTEGER,
1224                  'month' INTEGER,
1225                  'visits' INTEGER
1226              );"
1227            );
1228          $this->dbHandle->exec("CREATE UNIQUE INDEX iImg ON statImages ('imageId', 'catId', 'uaType', 'year', 'month');");
1229          $this->dbHandle->exec("CREATE UNIQUE INDEX iImgPeriod ON statImages ('year', 'month', 'uaType', 'imageId');");
1230        }
1231
1232        $result=$this->getDBInfo(ASDF_DB_TYPE_TABLE, 'statIP', ASDF_DB_INFO);
1233        if(count($result)==0)
1234        {
1235          $this->dbHandle->exec(
1236              "CREATE TABLE 'statIP' (
1237                  'uaType' INTEGER,
1238                  'IPadress' BLOB,
1239                  'catId' INTEGER,
1240                  'year' INTEGER,
1241                  'month' INTEGER,
1242                  'visits' INTEGER,
1243                  'country' TEXT
1244              );"
1245            );
1246          $this->dbHandle->exec("CREATE UNIQUE INDEX iIP ON statIP ('uaType', 'IPadress', 'catId', 'year', 'month');");
1247          $this->dbHandle->exec("CREATE UNIQUE INDEX iIPPeriod ON statIP ('year', 'month', 'IPadress', 'uaType');");
1248        }
1249
1250        $result=$this->getDBInfo(ASDF_DB_TYPE_TABLE, 'statUA', ASDF_DB_INFO);
1251        if(count($result)==0)
1252        {
1253          $this->dbHandle->exec(
1254              "CREATE TABLE 'statUA' (
1255                  'uaData' INTEGER,
1256                  'uaValue' INTEGER,
1257                  'uaVersion' TEXT,
1258                  'year' INTEGER,
1259                  'month' INTEGER,
1260                  'visits' INTEGER
1261              );"
1262            );
1263          $this->dbHandle->exec("CREATE UNIQUE INDEX iUA ON statUA ('uaData', 'uaValue', 'uaVersion', 'year', 'month');");
1264          $this->dbHandle->exec("CREATE INDEX iUAPeriod ON statUA ('year', 'month', 'uaData', 'uaValue');");
1265        }
1266
1267        $this->setInfoProperty('nfo', 'version', '01.00.00');
1268        return(true);
1269        break;
1270      default:
1271        break;
1272    }
1273    return(false);
1274  }
1275
1276} // class
1277
1278
1279?>
Note: See TracBrowser for help on using the repository browser.