<?php

require_once('statDB.class.inc.php');

/**
 * specific class for SQLite monthly stat managment
 */
class StatDBMonth extends StatDB
{
  protected $year='';
  protected $month='';
  protected $nbDaysMonth=0;

  /**
   * constructor
   *
   * @param String $directory: directory where the sqlite file is saved
   * @param String $fileName: file name (without extension)
   */
  public function __construct($directory='', $fileName='', $year='', $month='')
  {
    parent::__construct($directory, $fileName);
    $this->setPeriod($year, $month);
  }


  /**
   * define the covered period (year/month)
   * @param String $year;
   * @param String $month;
   * @return String
   */
  public function setPeriod($year, $month)
  {
    $this->month=$month;
    $this->year=$year;
    $this->fileRootName=ASDF_FILE_ROOT_MONTH.$year.$month;

    $this->nbDaysMonth=date('d', strtotime('-1 day', strtotime('+1 month', strtotime("$year-$month-01"))))*1;

    return($this->fileRootName);
  }


  /**
   * return the number of days for the month
   *
   * @return Integer
   */
  public function getNbDays()
  {
    return($this->nbDaysMonth);
  }

  /**
   * add a log record
   *
   * Data have to be provided as an array with keys:
   *      array(
   *         'date'             => integer
   *         'IPadress'         => string
   *         'userId'           => integer
   *         'catId'            => integer
   *         'imageId'          => integer
   *         'tagsId'           => string
   *         'section'          => string
   *         'userAgent'        => string
   *         'uaBrowser'        => integer
   *         'uaBrowserVersion' => integer
   *         'uaEngine'         => integer
   *         'uaEngineVersion'  => integer
   *         'uaOS'             => integer
   *         'uaOSVersion'      => integer
   *         'uaType'           => integer
   *         'screenSizeW'      => integer
   *         'screenSizeH'      => integer
   *      )
   *
   * missing values are set to default value
   * country is defined automatically after buildIPCountry call; set to '' while
   * call is not made
   *
   * @param Array $log : a record
   * @return String : true if log was added, otherwise false
   */
  public function addLog($value)
  {
    if(is_array($value) and $this->dbHandle!=null)
    {
      // set default values if needed
      if(!isset($value['date'])) $value['date']=time();
      if(!isset($value['IPadress'])) $value['IPadress']='0.0.0.0';
      if(!isset($value['userId'])) $value['userId']=0;
      if(!isset($value['catId'])) $value['catId']=0;
      if(!isset($value['imageId'])) $value['imageId']=0;
      if(!isset($value['section'])) $value['section']='';
      if(!isset($value['tagsId'])) $value['tagsId']='';
      if(!isset($value['userAgent'])) $value['userAgent']='';
      if(!isset($value['uaBrowser'])) $value['uaBrowser']=0;
      if(!isset($value['uaBrowserVersion'])) $value['uaBrowserVersion']='';
      if(!isset($value['uaEngine'])) $value['uaEngine']=0;
      if(!isset($value['uaEngineVersion'])) $value['uaEngineVersion']='';
      if(!isset($value['uaOS'])) $value['uaOS']=0;
      if(!isset($value['uaOSVersion'])) $value['uaOSVersion']='';
      if(!isset($value['uaType'])) $value['uaType']=0;
      if(!isset($value['screenSizeW'])) $value['screenSizeW']=0;
      if(!isset($value['screenSizeH'])) $value['screenSizeH']=0;

      $value['IPadress']=self::IPBinaryEncode($value['IPadress']);

      // insert log
      $sql="INSERT INTO logs
            (date, IPadress, country, userId, catId, imageId, tagsId, section,
             userAgent, uaBrowser, uaBrowserVersion, uaEngine, uaEngineVersion,
             uaOS, uaOSVersion, uaType, screenSizeW, screenSizeH)
            VALUES (".$value['date'].", ".
                      //"'".$this->dbHandle->escapeString($value['IPadress'])."', ".
                      ":IP, ".
                      "'', ".
                      $value['userId'].", ".
                      $value['catId'].", ".
                      $value['imageId'].", ".
                      "'".$value['tagsId']."', ".
                      "'".$value['section']."', ".
                      "'".$this->dbHandle->escapeString($value['userAgent'])."', ".
                      $value['uaBrowser'].", ".
                      "'".$value['uaBrowserVersion']."', ".
                      $value['uaEngine'].", ".
                      "'".$value['uaEngineVersion']."', ".
                      $value['uaOS'].", ".
                      "'".$value['uaOSVersion']."', ".
                      $value['uaType'].", ".
                      $value['screenSizeW'].", ".
                      $value['screenSizeH']." );";
//echo "$sql\n";
      $sqlStm=$this->dbHandle->prepare($sql);
      $sqlStm->bindValue(':IP', $value['IPadress'], SQLITE3_BLOB);
      return($sqlStm->execute());
    }
    return(false);
  }

  /**
   * return detailled logs
   *
   * $page: the page number asked; first page is 1=>lower value means to get all values
   * $nbItemsPage: the number of items returned per; 0 means no limit
   *
   *
   * in ASDF_GET_ROWS mode, each returned logs is an array:
   *      array(
   *         'date'             => integer
   *         'IPadress'         => string
   *         'userId'           => integer
   *         'catId'            => integer
   *         'imageId'          => integer
   *         'tagsId'           => string
   *         'section'          => string
   *         'userAgent'        => string
   *         'uaBrowser'        => integer
   *         'uaBrowserVersion' => integer
   *         'uaEngine'         => integer
   *         'uaEngineVersion'  => integer
   *         'uaOS'             => integer
   *         'uaOSVersion'      => integer
   *         'uaType'           => integer
   *         'screenSizeW'      => integer
   *         'screenSizeH'      => integer
   *      )
   *
   * for each filtered item, are given an array:
   *  array(
   *   'operator' => '',
   *   'value' => '',
   *   'minValue' => '',
   *   'maxValue' =>
   *  )
   *
   * operator can take the following values:
   *    '=', '>', '<', '>=', '<=', '!='
   *        in this case, the index 'value' have to be set
   *
   *    'in'
   *        in this case, the index 'value' have to be an array of needed value
   *
   *    'between'
   *        in this case, the indexes 'minValue' and 'maxValue' have to be set
   *
   *
   * filterable items are
   *  'date'       (string) 'YYYY-MM-DD HH:II:SS' : '=', '>', '<', '>=', '<=', '!=', 'between'
   *  'IPadress'   (string) 'xxx.xxx.xxx.xxx'     : '=', '!=', 'in', 'between'
   *  'section'    (string)                       : '=', '!=', 'in'
   *  'tagsId'     (integer)                      : '=', '!=', 'in'
   *  'userId'     (integer)                      : '=', '!=', 'in'
   *  'catId'      (integer)                      : '=', '!=', 'in'
   *  'imageId'    (integer)                      : '=', '!=', 'in'
   *  'uaBrowser'  (integer)                      : '=', '!=', 'in'
   *  'uaEngine'   (integer)                      : '=', '!=', 'in'
   *  'uaOS'       (integer)                      : '=', '!=', 'in'
   *  'uaType'     (integer)                      : '=', '!=', 'in'
   *  => version are not filterable, it's normal, it's not implemented :)
   *     May be one day, if it's really interesting do to it...
   *
   * $order is an array allowing to set how data are sorted; column priority is set by
   * the order of items in the array
   * Each item of the array is an array with the following properties:
   * Array(
   *  'id'        => string (columnId)
   *  'direction' => string ('A' for Asc, 'D' for Desc)
   * )
   *
   * In the following example:
   *  $order=Array(
   *     Array('id' => 'uaBrowser', 'direction' => 'D'),
   *     Array('id' => 'uaOS', 'direction' => 'A')
   *  )
   * the 'order by' clause will be: ORDER BY uaBrowser ASC, uaOS DESC
   * => as for filter, version are not sortable
   *
   *
   * @param Integer $mode: type of result needed
   *                          . ASDF_GET_ROWS return an array of logs
   *                          . ASDF_GET_COUNT return the number of logs
   * @param Integer $page: page number
   * @param Integer $nbItemsPage: number of items per page
   * @param Array $filter : filters to apply
   * @param Array $order: sort to apply
   * @return
   */
  public function getLogs($mode=ASDF_GET_ROWS, $page=0, $nbItemsPage=0, $filter=array(), $order=array())
  {
    switch($mode)
    {
      case ASDF_GET_COUNT:
        $returned=0;
        break;
      case ASDF_GET_ROWS:
        $returned=array();
        break;
    }
    if($this->dbHandle==null) return($returned);

    // initialize filter
    if(!is_array($filter)) $filter=array();

    if(!isset($filter['date'])) $filter['date']=null;
    if(!isset($filter['IPadress'])) $filter['IPadress']=null;
    if(!isset($filter['section'])) $filter['section']=null;
    if(!isset($filter['tagsId'])) $filter['tagsId']=null;
    if(!isset($filter['userId'])) $filter['userId']=null;
    if(!isset($filter['catId'])) $filter['catId']=null;
    if(!isset($filter['imageId'])) $filter['imageId']=null;
    if(!isset($filter['uaBrowser'])) $filter['uaBrowser']=null;
    if(!isset($filter['uaEngine'])) $filter['uaEngine']=null;
    if(!isset($filter['uaOS'])) $filter['uaOS']=null;
    if(!isset($filter['uaType'])) $filter['uaType']=null;

    $filter['IPadress']=$this->checkIPFilter($filter['IPadress']);
    // check filter values - getOperator check and 'clean' the filter
    $filter['date']=$this->getOperator($filter['date'], 'date');
    $filter['IPadress']=$this->getOperator($filter['IPadress'], 'IP');
    $filter['section']=$this->getOperator($filter['section'], 'string');
    $filter['tagsId']=$this->getOperator($filter['tagsId'], 'integer');
    $filter['userId']=$this->getOperator($filter['userId'], 'integer');
    $filter['catId']=$this->getOperator($filter['catId'], 'integer');
    $filter['imageId']=$this->getOperator($filter['imageId'], 'integer');
    $filter['uaBrowser']=$this->getOperator($filter['uaBrowser'], 'integer');
    $filter['uaEngine']=$this->getOperator($filter['uaEngine'], 'integer');
    $filter['uaOS']=$this->getOperator($filter['uaOS'], 'integer');
    $filter['uaType']=$this->getOperator($filter['uaType'], 'integer');

    // initialize order
    $orderBy=array();
    if(!is_array($order)) $order=array();
    foreach($order as $sort)
    {
      if(isset($sort['id']) and isset($sort['direction']))
      {
        if(($sort['id']=='date' or
            $sort['id']=='IPadress' or
            $sort['id']=='section' or
            $sort['id']=='tagsId' or
            $sort['id']=='userId' or
            $sort['id']=='catId' or
            $sort['id']=='imageId' or
            $sort['id']=='uaBrowser' or
            $sort['id']=='uaEngine' or
            $sort['id']=='uaOS' or
            $sort['id']=='uaType') and
           ($sort['direction']=='A' or
            $sort['direction']=='D')
          ) $orderBy[]=$sort;
      }
    }
    // set default order if nothing available is provided
    if(count($orderBy)==0)
      $orderBy=array(
        array('id'=>'date', 'direction'=>'D'),
        array('id'=>'id', 'direction'=>'A')
      );
    // to optimize query, if date is the first field selected for order by,
    // all other fields are removed; exception: if IPadress is the second one this
    // field is kept
    $tmp=$orderBy;
    if($tmp[0]['id']=='date')
    {
      $orderBy=array($tmp[0]);
      if($tmp[1]['id']=='IPadress')
      {
        $orderBy[]=$tmp[1];
      }
    }

    //build ORDER BY clause
    $orderBy=$this->buildOrderByClause($orderBy);

    //build WHERE clause
    $IPList=array();
    $where=$this->buildWhereClause($filter,$IPList);

    // build LIMIT clause
    $limit=$this->buildLimitClause($page, $nbItemsPage);

    // execute request
    switch($mode)
    {
      case ASDF_GET_ROWS:
          $sql="SELECT id, date, IPadress, country, userId, catId, imageId, tagsId, section,
                       userAgent, uaBrowser, uaBrowserVersion, uaEngine, uaEngineVersion,
                       uaOS, uaOSVersion, uaType, screenSizeW, screenSizeH
                FROM logs $where $orderBy $limit;";

          $sqlStm=$this->dbHandle->prepare($sql);
          foreach($IPList as $num=>$IP)
          {
            $sqlStm->bindValue(':IP'.$num, $IP, SQLITE3_BLOB);
          }

          $result=$sqlStm->execute();
          if($result)
          {
            while($row=$result->fetchArray(SQLITE3_ASSOC))
            {
              $row['IPadress']=self::IPBinaryDecode($row['IPadress']);
              $returned[]=$row;
            }
          }
        break;
      case ASDF_GET_COUNT:
          $sql="SELECT COUNT(id) AS nb
                FROM logs $where;";

          $sqlStm=$this->dbHandle->prepare($sql);
          foreach($IPList as $num=>$IP)
          {
            $sqlStm->bindValue(':IP'.$num, $IP, SQLITE3_BLOB);
          }

          $result=$sqlStm->execute();
          if($result)
          {
            while($row=$result->fetchArray(SQLITE3_ASSOC))
            {
              $returned=$row['nb'];
            }
          }
        break;
    }
    return($returned);
  }


  /**
   * returns stats from useragent strings (os, browser, engine, type)
   *
   * $page: the page number asked; first page is 1=>lower value means to get all values
   * $nbItemsPage: the number of items returned per; 0 means no limit
   *
   *
   * in ASDF_GET_ROWS mode, each returned logs is an array of asked fields
   * at least, the following fields are always returned:
   *  'nbVisits' => number of visits
   *  'uaValue'  => value (meaning of this information can change)
   *
   *
   * all fields are filterable, except 'nbVisits' that can't be filtered
   *
   * available fields values are:
   *   'uaData'    => return the type of data; can get one of the following values
   *                          UA_DATA_BROWSER: stat about browser
   *                          UA_DATA_ENGINE: stat about engine
   *                          UA_DATA_OS: stat about OS
   *                          UA_DATA_BROWSER_TYPE: stat about browser type
   *   'day'       => return detailled stat per day
   *   'uaVersion' => return detailled stat per version (browser version, os version, ...)
   *
   * @param Integer $mode: type of result needed
   *                          . ASDF_GET_ROWS return an array of logs
   *                          . ASDF_GET_COUNT return the number of logs
   * @param Integer $page: page number
   * @param Integer $nbItemsPage: number of items per page
   * @param Array $fields : additional fields to be returned
   * @param Array $filter : filters to apply => see getLogs function for usage
   * @param Array $order: sort to apply      => see getLogs function for usage; all fields can be sorted
   * @return
   */
  public function getStatUserAgent($mode=ASDF_GET_ROWS, $page=0, $nbItemsPage=0, $fields=array(), $filter=array(), $order=array())
  {
    switch($mode)
    {
      case ASDF_GET_COUNT:
        $returned=0;
        break;
      case ASDF_GET_ROWS:
        $returned=array();
        break;
    }
    if($this->dbHandle==null) return($returned);

    $select=array('uaData', 'SUM(visits) AS nbVisits');
    $groupBy=array('uaData');
    // initialize fields list
    foreach($fields as $field)
    {
      if($field=='uaValue' or
         $field=='day' or
         $field=='uaVersion')
      {
        $select[]=$field;
        $groupBy[]=$field;
      }
    }

    // initialize filter
    if(!is_array($filter)) $filter=array();

    if(!isset($filter['uaData'])) $filter['uaData']=null;
    if(!isset($filter['uaValue'])) $filter['uaValue']=null;
    if(!isset($filter['day'])) $filter['day']=null;
    if(!isset($filter['uaVersion'])) $filter['uaVersion']=null;

    // check filter values - getOperator check and 'clean' the filter
    $filter['uaData']=$this->getOperator($filter['uaData'], 'integer');
    $filter['uaValue']=$this->getOperator($filter['uaValue'], 'integer');
    $filter['day']=$this->getOperator($filter['day'], 'integer');
    $filter['uaVersion']=$this->getOperator($filter['uaVersion'], 'string');


    // initialize order
    $orderBy=array();
    if(!is_array($order)) $order=array();
    foreach($order as $sort)
    {
      if(isset($sort['id']) and isset($sort['direction']))
      {
        if((($sort['id']=='uaData' or
             $sort['id']=='day' or
             $sort['id']=='uaVersion' or
             $sort['id']=='uaValue' ) and
            in_array($sort['id'], $select) or
             $sort['id']=='nbVisits') and
           ($sort['direction']=='A' or
            $sort['direction']=='D')
          ) $orderBy[]=$sort;
      }
    }
    // set default order if nothing available is provided
    if(count($orderBy)==0)
    {
      if(in_array('day', $select))
        $orderBy[]=array('id'=>'day', 'direction'=>'A');
      $orderBy[]=array('id'=>'nbVisits', 'direction'=>'D');
    }

    // build SELECT & GROUP By clauses
    $select="SELECT ".implode(',', $select);
    $groupBy=(count($groupBy)>0)?" GROUP BY ".implode(',', $groupBy):'';

    //build ORDER BY clause
    $orderBy=$this->buildOrderByClause($orderBy);

    //build WHERE clause
    $IPList=array();
    $where=$this->buildWhereClause($filter, $IPList);

    // build LIMIT clause
    $limit=$this->buildLimitClause($page, $nbItemsPage);

    // execute request
    switch($mode)
    {
      case ASDF_GET_ROWS:
          $sql=$select." FROM statua ".$where.$groupBy.$orderBy.$limit;

          $sqlStm=$this->dbHandle->prepare($sql);

          $result=$sqlStm->execute();
          if($result)
          {
            while($row=$result->fetchArray(SQLITE3_ASSOC))
            {
              $returned[]=$row;
            }
          }
        break;
      case ASDF_GET_COUNT:
          $sql="SELECT COUNT(*) AS nb
                FROM statua $where;";

          $sqlStm=$this->dbHandle->prepare($sql);

          $result=$sqlStm->execute();
          if($result)
          {
            while($row=$result->fetchArray(SQLITE3_ASSOC))
            {
              $returned=$row['nb'];
            }
          }
        break;
    }
    return($returned);
  }


  /**
   * returns stats from IP
   *
   * $page: the page number asked; first page is 1=>lower value means to get all values
   * $nbItemsPage: the number of items returned per; 0 means no limit
   *
   *
   * in ASDF_GET_ROWS mode, each returned logs is an array of asked fields
   * at least, the following fields are always returned:
   *  'nbVisits' => number of visits
   *
   * all fields are filterable, except 'nbVisits' that can't be filtered
   *
   * available fields values are:
   *   'IPadress' => return the IP address
   *   'day'      => return detailled stat per day
   *   'uaType'   => return detailled stat per browser type
   *   'country'  => return detailled stat per country
   *   'catId'    => return detailled stat per category
   *
   * @param Integer $mode: type of result needed
   *                          . ASDF_GET_ROWS return an array of logs
   *                          . ASDF_GET_COUNT return the number of logs
   * @param Integer $page: page number
   * @param Integer $nbItemsPage: number of items per page
   * @param Array $fields : additional fields to be returned
   * @param Array $filter : filters to apply => see getLogs function for usage
   * @param Array $order: sort to apply      => see getLogs function for usage; all fields can be sorted
   * @return
   */
  public function getStatIP($mode=ASDF_GET_ROWS, $page=0, $nbItemsPage=0, $fields=array(), $filter=array(), $order=array())
  {
    switch($mode)
    {
      case ASDF_GET_COUNT:
        $returned=0;
        break;
      case ASDF_GET_ROWS:
        $returned=array();
        break;
    }
    if($this->dbHandle==null) return($returned);

    $select=array('SUM(visits) AS nbVisits');
    $groupBy=array();
    // initialize fields list
    foreach($fields as $field)
    {
      if($field=='IPadress' or
         $field=='day' or
         $field=='catId' or
         $field=='country' or
         $field=='uaType')
      {
        $select[]=$field;
        $groupBy[]=$field;
      }
    }

    // initialize filter
    if(!is_array($filter)) $filter=array();

    if(!isset($filter['IPadress'])) $filter['IPadress']=null;
    if(!isset($filter['day'])) $filter['day']=null;
    if(!isset($filter['uaType'])) $filter['uaType']=null;
    if(!isset($filter['catId'])) $filter['catId']=null;
    if(!isset($filter['country'])) $filter['country']=null;

    $filter['IPadress']=$this->checkIPFilter($filter['IPadress']);
    // check filter values - getOperator check and 'clean' the filter
    $filter['IPadress']=$this->getOperator($filter['IPadress'], 'IP');
    $filter['country']=$this->getOperator($filter['country'], 'string');
    $filter['day']=$this->getOperator($filter['day'], 'integer');
    $filter['uaType']=$this->getOperator($filter['uaType'], 'integer');
    $filter['catId']=$this->getOperator($filter['catId'], 'integer');

    // initialize order
    $orderBy=array();
    if(!is_array($order)) $order=array();
    foreach($order as $sort)
    {
      if(isset($sort['id']) and isset($sort['direction']))
      {
        if((($sort['id']=='IPadress' or
             $sort['id']=='catId' or
             $sort['id']=='country' or
             $sort['id']=='day' or
             $sort['id']=='uaType' ) and
            in_array($sort['id'], $select) or
             $sort['id']=='nbVisits') and
           ($sort['direction']=='A' or
            $sort['direction']=='D')
          ) $orderBy[]=$sort;
      }
    }
    // set default order if nothing available is provided
    if(count($orderBy)==0)
    {
      if(in_array('day', $select))
        $orderBy[]=array('id'=>'day', 'direction'=>'A');
      $orderBy[]=array('id'=>'nbVisits', 'direction'=>'D');
    }

    // build SELECT & GROUP By clauses
    $select="SELECT ".implode(',', $select);
    $groupBy=(count($groupBy)>0)?" GROUP BY ".implode(',', $groupBy):'';

    //build ORDER BY clause
    $orderBy=$this->buildOrderByClause($orderBy);

    //build WHERE clause
    $IPList=array();
    $where=$this->buildWhereClause($filter, $IPList);

    // build LIMIT clause
    $limit=$this->buildLimitClause($page, $nbItemsPage);

    // execute request
    switch($mode)
    {
      case ASDF_GET_ROWS:
          $sql=$select." FROM statip ".$where.$groupBy.$orderBy.$limit;

          $sqlStm=$this->dbHandle->prepare($sql);
          foreach($IPList as $num=>$IP)
          {
            $sqlStm->bindValue(':IP'.$num, $IP, SQLITE3_BLOB);
          }

          $result=$sqlStm->execute();
          if($result)
          {
            while($row=$result->fetchArray(SQLITE3_ASSOC))
            {
              if(isset($row['IPadress']))
                $row['IPadress']=self::IPBinaryDecode($row['IPadress']);
              $returned[]=$row;
            }
          }
        break;
      case ASDF_GET_COUNT:
          $sql="SELECT COUNT(*) AS nb
                FROM statip $where;";

          $sqlStm=$this->dbHandle->prepare($sql);
          foreach($IPList as $num=>$IP)
          {
            $sqlStm->bindValue(':IP'.$num, $IP, SQLITE3_BLOB);
          }

          $result=$sqlStm->execute();
          if($result)
          {
            while($row=$result->fetchArray(SQLITE3_ASSOC))
            {
              $returned=$row['nb'];
            }
          }
        break;
    }
    return($returned);
  }


  /**
   * returns stats from categories
   *
   * $page: the page number asked; first page is 1=>lower value means to get all values
   * $nbItemsPage: the number of items returned per; 0 means no limit
   *
   *
   * in ASDF_GET_ROWS mode, each returned logs is an array of asked fields
   * at least, the following fields are always returned:
   *  'catId'    => category Id
   *  'nbVisits' => number of visits
   *
   * all fields are filterable, except 'nbVisits' that can't be filtered
   *
   * available fields values are:
   *   'day'      => return detailled stat per day
   *   'uaType'   => return detailled stat per browser type
   *
   * @param Integer $mode: type of result needed
   *                          . ASDF_GET_ROWS return an array of logs
   *                          . ASDF_GET_COUNT return the number of logs
   * @param Integer $page: page number
   * @param Integer $nbItemsPage: number of items per page
   * @param Array $fields : additional fields to be returned
   * @param Array $filter : filters to apply => see getLogs function for usage
   * @param Array $order: sort to apply      => see getLogs function for usage; all fields can be sorted
   * @return
   */
  public function getStatCat($mode=ASDF_GET_ROWS, $page=0, $nbItemsPage=0, $fields=array(), $filter=array(), $order=array())
  {
    switch($mode)
    {
      case ASDF_GET_COUNT:
        $returned=0;
        break;
      case ASDF_GET_ROWS:
        $returned=array();
        break;
    }
    if($this->dbHandle==null) return($returned);

    $select=array('catId', 'SUM(visits) AS nbVisits');
    $groupBy=array('catId');
    // initialize fields list
    foreach($fields as $field)
    {
      if($field=='day' or
         $field=='uaType')
      {
        $select[]=$field;
        $groupBy[]=$field;
      }
    }

    // initialize filter
    if(!is_array($filter)) $filter=array();

    if(!isset($filter['day'])) $filter['day']=null;
    if(!isset($filter['uaType'])) $filter['uaType']=null;

    // check filter values - getOperator check and 'clean' the filter
    $filter['day']=$this->getOperator($filter['day'], 'integer');
    $filter['uaType']=$this->getOperator($filter['uaType'], 'integer');


    // initialize order
    $orderBy=array();
    if(!is_array($order)) $order=array();
    foreach($order as $sort)
    {
      if(isset($sort['id']) and isset($sort['direction']))
      {
        if((($sort['id']=='day' or
             $sort['id']=='uaType' ) and
            in_array($sort['id'], $select) or
             $sort['id']=='nbVisits') and
           ($sort['direction']=='A' or
            $sort['direction']=='D')
          ) $orderBy[]=$sort;
      }
    }
    // set default order if nothing available is provided
    if(count($orderBy)==0)
    {
      if(in_array('day', $select))
        $orderBy[]=array('id'=>'day', 'direction'=>'A');
      $orderBy[]=array('id'=>'nbVisits', 'direction'=>'D');
    }

    // build SELECT & GROUP By clauses
    $select="SELECT ".implode(',', $select);
    $groupBy=(count($groupBy)>0)?" GROUP BY ".implode(',', $groupBy):'';

    //build ORDER BY clause
    $orderBy=$this->buildOrderByClause($orderBy);

    //build WHERE clause
    $IPList=array();
    $where=$this->buildWhereClause($filter, $IPList);

    // build LIMIT clause
    $limit=$this->buildLimitClause($page, $nbItemsPage);

    // execute request
    switch($mode)
    {
      case ASDF_GET_ROWS:
          $sql=$select." FROM statcategories ".$where.$groupBy.$orderBy.$limit;

          $sqlStm=$this->dbHandle->prepare($sql);

          $result=$sqlStm->execute();
          if($result)
          {
            while($row=$result->fetchArray(SQLITE3_ASSOC))
            {
              $returned[]=$row;
            }
          }
        break;
      case ASDF_GET_COUNT:
          $sql="SELECT COUNT(*) AS nb
                FROM statcategories $where;";

          $sqlStm=$this->dbHandle->prepare($sql);

          $result=$sqlStm->execute();
          if($result)
          {
            while($row=$result->fetchArray(SQLITE3_ASSOC))
            {
              $returned=$row['nb'];
            }
          }
        break;
    }
    return($returned);
  }


  /**
   * returns stats from images
   *
   *
   * $page: the page number asked; first page is 1=>lower value means to get all values
   * $nbItemsPage: the number of items returned per; 0 means no limit
   *
   *
   * in ASDF_GET_ROWS mode, each returned logs is an array of asked fields
   * at least, the following fields are always returned:
   *  'imageId'  => image Id
   *  'nbVisits' => number of visits
   *
   * all fields are filterable, except 'nbVisits' that can't be filtered
   *
   * available fields values are:
   *   'day'      => return detailled stat per day
   *   'uaType'   => return detailled stat per browser type
   *   'catId'    => return detailled stat per categories (an image could be linked with more than one category)
   *
   * @param Integer $mode: type of result needed
   *                          . ASDF_GET_ROWS return an array of logs
   *                          . ASDF_GET_COUNT return the number of logs
   * @param Integer $page: page number
   * @param Integer $nbItemsPage: number of items per page
   * @param Array $fields : additional fields to be returned
   * @param Array $filter : filters to apply => see getLogs function for usage
   * @param Array $order: sort to apply      => see getLogs function for usage; all fields can be sorted
   * @return
   */
  public function getStatImages($mode=ASDF_GET_ROWS, $page=0, $nbItemsPage=0, $fields=array(), $filter=array(), $order=array())
  {
    switch($mode)
    {
      case ASDF_GET_COUNT:
        $returned=0;
        break;
      case ASDF_GET_ROWS:
        $returned=array();
        break;
    }
    if($this->dbHandle==null) return($returned);

    $select=array('imageId', 'SUM(visits) AS nbVisits');
    $groupBy=array('imageId');
    // initialize fields list
    foreach($fields as $field)
    {
      if($field=='day' or
         $field=='catId' or
         $field=='uaType')
      {
        $select[]=$field;
        $groupBy[]=$field;
      }
    }

    // initialize filter
    if(!is_array($filter)) $filter=array();

    if(!isset($filter['day'])) $filter['day']=null;
    if(!isset($filter['catId'])) $filter['catId']=null;
    if(!isset($filter['uaType'])) $filter['uaType']=null;

    // check filter values - getOperator check and 'clean' the filter
    $filter['day']=$this->getOperator($filter['day'], 'integer');
    $filter['catId']=$this->getOperator($filter['catId'], 'integer');
    $filter['uaType']=$this->getOperator($filter['uaType'], 'integer');


    // initialize order
    $orderBy=array();
    if(!is_array($order)) $order=array();
    foreach($order as $sort)
    {
      if(isset($sort['id']) and isset($sort['direction']))
      {
        if((($sort['id']=='day' or
             $sort['id']=='catId' or
             $sort['id']=='uaType' ) and
            in_array($sort['id'], $select) or
             $sort['id']=='nbVisits') and
           ($sort['direction']=='A' or
            $sort['direction']=='D')
          ) $orderBy[]=$sort;
      }
    }
    // set default order if nothing available is provided
    if(count($orderBy)==0)
    {
      if(in_array('day', $select))
        $orderBy[]=array('id'=>'day', 'direction'=>'A');
      $orderBy[]=array('id'=>'nbVisits', 'direction'=>'D');
    }

    // build SELECT & GROUP By clauses
    $select="SELECT ".implode(',', $select);
    $groupBy=(count($groupBy)>0)?" GROUP BY ".implode(',', $groupBy):'';

    //build ORDER BY clause
    $orderBy=$this->buildOrderByClause($orderBy);

    //build WHERE clause
    $IPList=array();
    $where=$this->buildWhereClause($filter, $IPList);

    // build LIMIT clause
    $limit=$this->buildLimitClause($page, $nbItemsPage);

    // execute request
    switch($mode)
    {
      case ASDF_GET_ROWS:
          $sql=$select." FROM statimages ".$where.$groupBy.$orderBy.$limit;

          $sqlStm=$this->dbHandle->prepare($sql);

          $result=$sqlStm->execute();
          if($result)
          {
            while($row=$result->fetchArray(SQLITE3_ASSOC))
            {
              $returned[]=$row;
            }
          }
        break;
      case ASDF_GET_COUNT:
          $sql="SELECT COUNT(*) AS nb
                FROM statimages $where;";

          $sqlStm=$this->dbHandle->prepare($sql);

          $result=$sqlStm->execute();
          if($result)
          {
            while($row=$result->fetchArray(SQLITE3_ASSOC))
            {
              $returned=$row['nb'];
            }
          }
        break;
    }
    return($returned);
  }


  /**
   * returns stats for a period
   *
   * $page: the page number asked; first page is 1=>lower value means to get all values
   * $nbItemsPage: the number of items returned per; 0 means no limit
   *
   *
   * each returned logs is an array of asked fields
   * at least, the following fields are always returned:
   *  'nbVisits' => number of visits
   *
   * all fields are filterable, except 'nbVisits' that can't be filtered
   *
   * available fields values are:
   *   'day'       => return detailled stat per day
   *   'catId'     => return detailled stat per category
   *
   * @param Integer $page: page number
   * @param Integer $nbItemsPage: number of items per page
   * @param Array $fields : additional fields to be returned
   * @param Array $filter : filters to apply => see getLogs function for usage
   * @param Array $order: sort to apply      => see getLogs function for usage; all fields can be sorted
   * @return
   */
  public function getStatPeriod($page=0, $nbItemsPage=0, $fields=array(), $filter=array(), $order=array())
  {
    $returned=array();
    if($this->dbHandle==null) return($returned);

    /*
     * total: 'T' variables
     * categories: 'C' variables
     * images 'I' variables
     * ip adresses: 'A' variables
     */
    $select=array(
      'T' => array('"T" AS nfoType', 'SUM(visits) AS nbVisits'),
      'C' => array('"C" AS nfoType', 'SUM(visits) AS nbVisits'),
      'I' => array('"I" AS nfoType', 'SUM(visits) AS nbVisits'),
      'A' => array('"A" AS nfoType', 'COUNT(DISTINCT IPadress) AS nbVisits')
    );

    $groupBy=array();
    // initialize fields list
    foreach($fields as $field)
    {
      if($field=='day')
      {
        $select['T'][]=$field;
        $select['C'][]=$field;
        $select['I'][]=$field;
        $select['A'][]=$field;
        $groupBy[]=$field;
      }
    }

    // initialize filter
    if(!is_array($filter)) $filter=array();
    $filter['T']=array();
    $filter['C']=array();
    $filter['I']=array();
    $filter['A']=array();

    if(!isset($filter['day'])) $filter['day']=null;
    if(!isset($filter['catId'])) $filter['catId']=null;

    foreach(array('T', 'C', 'I', 'A') as $key)
    {
      $filter[$key]['day']=$this->getOperator($filter['day'], 'integer');
      if($key=='C' and $filter['catId']==null)
      {
        $filter[$key]['catId']=$this->getOperator(array('operator' => '>', 'value' => 0), 'integer');
      }
      else
      {
        $filter[$key]['catId']=$this->getOperator($filter['catId'], 'integer');
      }
    }

    // initialize order
    $orderBy=array();
    if(!is_array($order)) $order=array();
    foreach($order as $sort)
    {
      if(isset($sort['id']) and isset($sort['direction']))
      {
        if(($sort['id']=='day' and
            in_array($sort['id'], $select['T']) or
             $sort['id']=='nbVisits') and
           ($sort['direction']=='A' or
            $sort['direction']=='D')
          ) $orderBy[]=$sort;
      }
    }
    // set default order if nothing available is provided
    if(count($orderBy)==0)
    {
      if(in_array('day', $select['T']))
        $orderBy[]=array('id'=>'day', 'direction'=>'A');
      $orderBy[]=array('id'=>'nbVisits', 'direction'=>'D');
    }

    // build SELECT & GROUP By clauses
    foreach($select as $key => $val)
    {
      $select[$key]="SELECT ".implode(',', $val);
    }
    $groupBy=(count($groupBy)>0)?" GROUP BY ".implode(',', $groupBy):'';

    //build ORDER BY clause
    $orderBy=$this->buildOrderByClause($orderBy);

    //build WHERE clause
    $IPList=array();
    $where=array(
      'T' => $this->buildWhereClause($filter['T'], $IPList),
      'C' => $this->buildWhereClause($filter['C'], $IPList),
      'I' => $this->buildWhereClause($filter['I'], $IPList),
      'A' => $this->buildWhereClause($filter['A'], $IPList)
    );

    // build LIMIT clause
    $limit=$this->buildLimitClause($page, $nbItemsPage);


    $sql=$select['T']." FROM statcategories ".$where['T'].$groupBy;
    $sql.=' UNION '.$select['C']." FROM statcategories ".$where['C'].$groupBy;
    $sql.=' UNION '.$select['I']." FROM statimages ".$where['I'].$groupBy;
    $sql.=' UNION '.$select['A']." FROM statip ".$where['A'].$groupBy;
    $sql.=$orderBy;  //.$limit => removed because the limit have to be set on the total result

    $sqlStm=$this->dbHandle->prepare($sql);

    $result=$sqlStm->execute();
    if($result)
    {
      $sqlResult=array();
      while($row=$result->fetchArray(SQLITE3_ASSOC))
      {
        $sqlResult[]=$row;
      }

      /*
       * $returned have a row per period & info type T,C,I,A
       *
       * example:
       *   01 T 25
       *   01 C 10
       *   01 I 45
       *   01 A 4
       *   14 T 8
       *   14 C 6
       *   14 A 1
       *   28 T 1
       *   28 A 1
       *
       * the result is built as a table with:
       *  - info type in column
       *  - missing periods
       *
       * example:
       *   period   T   C   I   A
       *   01       25  10  45  4
       *   02       0   0   0   0
       *   03       0   0   0   0
       *   04       0   0   0   0
       *   ...
       *   14       8   6   0   1
       *   ...
       *   28       1   0   0   1
       */

      // first step, build a period list
      if(in_array('day', $fields))
      {
        for($d=1;$d<=$this->nbDaysMonth;$d++)
        {
          $returned[$d]=array(
            'T' => 0,
            'C' => 0,
            'I' => 0,
            'A' => 0
          );
        }
      }
      else
      {
        $returned=array(
          0 => array( // no day => 0
                'T' => 0,
                'C' => 0,
                'I' => 0,
                'A' => 0
            )
        );
      }

      // next step, fill the table
      foreach($sqlResult as $row)
      {
        if(isset($row['day']))
        {
          $d=$row['day'];
        }
        else
        {
          $d=0;
        }

        $returned[$d][$row['nfoType']]=$row['nbVisits'];
      }

    }
    return($returned);
  }


  /**
   * set country for IPadress in statip table
   *
   * @return Boolean: true if ok, otherwise false
   */
  public function buildIPCountry()
  {
    if($this->dbHandle==null or $this->ipCountryFile=='') return(false);

    $result=$this->dbHandle->exec("ATTACH '".$this->ipCountryFile."' AS ipCountry");
    if($result)
    {
      /* -----------------------------------------------------------------------
       * SQLite doesn't allow to update a table with data from another table
       * sub-query is very slow, so the method:
       *  1) create an indexed temp table with the same structure than statip
       *  2) fill the temp table with records from statip table where country is empty
       *  3) do a select, join the temp table and the IP country table; completed result
       *     replace the content of the temp table
       *  4) records from temp table replace the linked records of the statip table
       *  5) delete temp table
       * ----------------------------------------------------------------------- */

      $sql="
        -- start a transaction, if something is wrong database won't be poluted
        BEGIN TRANSACTION;

        --log build command
        REPLACE INTO main.info (domain, key, value)
          VALUES('log',
                 'buildIPCountry()',
                 '".date('Y-m-d H:i:s')."');

        UPDATE info
        SET value=value+1
        WHERE domain = 'log'
          AND key = 'buildIPCountry-count';

        -- drop temp table if exists
        DROP TABLE IF EXISTS tmpIp;

        -- create a temp table with records without country code
        CREATE TEMPORARY TABLE tmpIp AS
          SELECT * FROM statip WHERE country='';
        -- create index to optimize next requests
        CREATE INDEX iIpTmp ON tmpIp ('IPadress');
        CREATE UNIQUE INDEX iIpTmp2 ON tmpIp ('uaType', 'IPadress', 'catId', 'day');

        -- set country for each record of the temp table
        REPLACE INTO tmpIp (uaType, IPadress, catId, `day`, visits, country)
          SELECT si.uaType, si.IPadress, si.catId, si.day, si.visits, il.country
          FROM ipCountry.iplist il, tmpIp si
          WHERE si.IPadress BETWEEN il.rangeStart AND il.rangeEnd;

        -- set country for each record of the final table (from temp table)
        REPLACE INTO statip (uaType, IPadress, catId, `day`, visits, country)
          SELECT si.uaType, si.IPadress, si.catId, si.day, si.visits, si.country
          FROM tmpIp si;

        -- delete temp table
        DROP TABLE IF EXISTS tmpIp;

        -- update the logs table for rows with unset country
        -- use a simple sub select here, assuming that it's quick enough for the update
        UPDATE logs
        SET country=(SELECT sip.country FROM statip sip WHERE logs.IPadress = sip.IPadress)
        WHERE country='';

        -- commit changes
        COMMIT TRANSACTION;
      ";
      $returned=$this->dbHandle->exec($sql);

      if(!$this->dbHandle->exec("DETACH ipCountry")) $returned=false;

      return($returned);
    }
    return(false);
  }


  /**
   * check the database schema; update it if needed
   *
   * @return Boolean: true if OK, otherwise false
   */
  protected function checkSchema()
  {
    if(!parent::checkSchema()) return(false);

    $version=$this->getInfoProperty('nfo', 'version');

    switch($version)
    {
      case '00.00.00':
        // version 00.00.00
        //  file is just created...
        $result=$this->getDBInfo(ASDF_DB_TYPE_TABLE, 'logs', ASDF_DB_INFO);
        if(count($result)==0)
        {
          $this->dbHandle->exec(
              "CREATE TABLE 'logs' (
                  'id' INTEGER PRIMARY KEY,
                  'date' INTEGER,
                  'IPadress' BLOB,
                  'country' TEXT,
                  'userId' INTEGER,
                  'catId' INTEGER,
                  'imageId' INTEGER,
                  'tagsId' TEXT,
                  'section' TEXT,
                  'userAgent' TEXT,
                  'uaBrowser' INTEGER,
                  'uaBrowserVersion' TEXT,
                  'uaEngine' INTEGER,
                  'uaEngineVersion' TEXT,
                  'uaOS' INTEGER,
                  'uaOSVersion' TEXT,
                  'uaType' INTEGER,
                  'screenSizeW' INTEGER,
                  'screenSizeH' INTEGER
              );"
            );
          $this->dbHandle->exec("CREATE INDEX iDateIP ON logs ('date', 'IPadress');");
          $this->dbHandle->exec("CREATE INDEX iIPCountry ON logs ('IPadress', 'country');");
          $this->dbHandle->exec("CREATE INDEX iUAB ON logs ('uaType', 'uaBrowser', 'uaEngine');");
          $this->dbHandle->exec("CREATE INDEX iUAO ON logs ('uaOS');");
        }

        $result=$this->getDBInfo(ASDF_DB_TYPE_TABLE, 'statCategories', ASDF_DB_INFO);
        if(count($result)==0)
        {
          $this->dbHandle->exec(
              "CREATE TABLE 'statCategories' (
                  'catId' INTEGER,
                  'uaType' INTEGER,
                  'day' INTEGER,
                  'visits' INTEGER
              );"
            );
          $this->dbHandle->exec("CREATE UNIQUE INDEX iCat ON statCategories ('catId', 'uaType', 'day');");
        }

        $result=$this->getDBInfo(ASDF_DB_TYPE_TABLE, 'statImages', ASDF_DB_INFO);
        if(count($result)==0)
        {
          $this->dbHandle->exec(
              "CREATE TABLE 'statImages' (
                  'imageId' INTEGER,
                  'catId' INTEGER,
                  'uaType' INTEGER,
                  'day' INTEGER,
                  'visits' INTEGER
              );"
            );
          $this->dbHandle->exec("CREATE UNIQUE INDEX iImg ON statImages ('imageId', 'catId', 'uaType', 'day');");
        }

        $result=$this->getDBInfo(ASDF_DB_TYPE_TABLE, 'statIP', ASDF_DB_INFO);
        if(count($result)==0)
        {
          $this->dbHandle->exec(
              "CREATE TABLE 'statIP' (
                  'uaType' INTEGER,
                  'IPadress' BLOB,
                  'catId' INTEGER,
                  'day' INTEGER,
                  'visits' INTEGER,
                  'country' TEXT
              );"
            );
          $this->dbHandle->exec("CREATE UNIQUE INDEX iIP ON statIP ('uaType', 'IPadress', 'catId', 'day');");
          $this->dbHandle->exec("CREATE INDEX iIPAdress ON statIP ('IPadress');");
        }

        $result=$this->getDBInfo(ASDF_DB_TYPE_TABLE, 'statUA', ASDF_DB_INFO);
        if(count($result)==0)
        {
          $this->dbHandle->exec(
              "CREATE TABLE 'statUA' (
                  'uaData' INTEGER,
                  'uaValue' INTEGER,
                  'uaVersion' TEXT,
                  'day' INTEGER,
                  'visits' INTEGER
              );"
            );
          $this->dbHandle->exec("CREATE UNIQUE INDEX iUA ON statUA ('uaData', 'uaValue', 'uaVersion', 'day');");
        }

        if($this->getDBInfo(ASDF_DB_TYPE_TRIGGER, 'insertLogs')==0)
        {
          $this->dbHandle->exec("
            CREATE TRIGGER insertLogs
            AFTER INSERT ON logs
            BEGIN
              INSERT OR IGNORE INTO statCategories (catId, uaType, day, visits)
              VALUES (new.catId, new.uaType, strftime('%d',new.date, 'unixepoch', 'localtime'), 0);

              UPDATE statCategories
              SET visits = visits+1
              WHERE catId = new.catId
                AND uaType = new.uaType
                AND day = strftime('%d',new.date, 'unixepoch', 'localtime');

              INSERT OR IGNORE INTO statIP (uaType, IPadress, catId, day, visits, country)
              VALUES (new.uaType, new.IPadress, new.catId, strftime('%d',new.date, 'unixepoch', 'localtime'), 0, '');

              UPDATE statIP
              SET visits = visits+1
              WHERE uaType = new.uaType
                AND IPadress = new.IPadress
                AND catId = new.catId
                AND day = strftime('%d',new.date, 'unixepoch', 'localtime');

              -- multiple insert values doesn't work? then using select+union
              INSERT OR IGNORE INTO statUA (uaData, uaValue, uaVersion, day, visits)
                SELECT 1, new.uaBrowser, new.uaBrowserVersion, strftime('%d',new.date, 'unixepoch', 'localtime'), 0
                  UNION
                SELECT 2, new.uaEngine, new.uaEngineVersion, strftime('%d',new.date, 'unixepoch', 'localtime'), 0
                  UNION
                SELECT 3, new.uaOS, new.uaOSVersion, strftime('%d',new.date, 'unixepoch', 'localtime'), 0
                  UNION
                SELECT 49, new.uaType, '', strftime('%d',new.date, 'unixepoch', 'localtime'), 0;

              -- note: 49=0x0031=UA_DATA_BROWSER_TYPE value

              UPDATE statUA
              SET visits = visits+1
              WHERE uaData = 1 AND uaValue = new.uaBrowser AND uaVersion=new.uaBrowserVersion AND day = strftime('%d',new.date, 'unixepoch', 'localtime')
                OR uaData = 2 AND uaValue = new.uaEngine AND uaVersion=new.uaEngineVersion AND day = strftime('%d',new.date, 'unixepoch', 'localtime')
                OR uaData = 3 AND uaValue = new.uaOS AND uaVersion=new.uaOSVersion AND day = strftime('%d',new.date, 'unixepoch', 'localtime')
                OR uaData = 49 AND uaValue = new.uaType AND day = strftime('%d',new.date, 'unixepoch', 'localtime');

            END;
          ");
        }
        if($this->getDBInfo(ASDF_DB_TYPE_TRIGGER, 'insertLogsImg')==0)
        {
          $this->dbHandle->exec("
            CREATE TRIGGER insertLogsImg
            AFTER INSERT ON logs
            WHEN new.imageId > 0
            BEGIN
              INSERT OR IGNORE INTO statImages (imageId, catId, uaType, day, visits)
              VALUES (new.imageId, new.catId, new.uaType, strftime('%d',new.date, 'unixepoch', 'localtime'), 0);

              UPDATE statImages
              SET visits = visits+1
              WHERE imageId = new.imageId
                AND catId = new.catId
                AND uaType = new.uaType
                AND day = strftime('%d',new.date, 'unixepoch', 'localtime');
            END;
          ");
        }

        $this->setInfoProperty('nfo', 'version', '01.00.00');
        return(true);
        break;
      default:
        break;
    }
    return(false);
  }

} // class


?>