<?php

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

/**
 * specific class for SQLite global stat managment
 *
 * statDBMonth is needed to connect monthly stat DB and make consolidation
 */
class StatDBGlobal extends StatDB
{
  /**
   * constructor
   *
   * @param String $directory: directory where the sqlite file is saved
   * @param String $fileName: file name (without extension)
   */
  public function __construct($directory='', $fileName='')
  {
    parent::__construct($directory, $fileName);
    $this->fileRootName=ASDF_FILE_ROOT_GLOBAL;
  }

  /**
   * check if a year/month exists in the file list
   * if not, create it
   *
   * return true if the an update was done, otherwise false
   *
   * @param String $year:
   * @param String $month:
   * @return Boolean
   */
  public function checkFilePeriod($year, $month)
  {
    if($this->dbHandle==null) return(false);

    $sql="SELECT COUNT(*)
          FROM files
          WHERE `year` = $year
            AND `month` = $month;";
    $returned=$this->dbHandle->querySingle($sql);

    if($returned==0)
    {
      $sql="REPLACE INTO files (year, month, logs, packed, unpacked, lastBuilt, needBuild)
          VALUES ($year, $month, 0, 0, 0, '', 'Y');";
      return($this->dbHandle->exec($sql));
    }
    return(false);
  }

  /**
   * return the list of monthly files
   * can be filtered to return packed or unpacked only
   *
   * for each file, return the following informations:
   *  - year
   *  - month
   *  - logs (number of logs)
   *  - packed (packed size)
   *  - unpacked (unpacked size)
   *  - lastBuilt (last date the consolidation was done)
   *  - needBuild (need to be built or not?)
   *
   * @param Integer $packed: combination of ASDF_EXIST_PACKED and ASDF_EXIST_UNPACKED
   * @param Integer $build: combination of ASDF_BUILD_DONE and ASDF_BUILD_MISSING
   * @return Array
   */
  public function getFilesList($packed=ASDF_EXIST_ALL, $build=ASDF_BUILD_ALL)
  {
    $returned=array();
    if($this->dbHandle)
    {
      $where=array();

      $sql="SELECT `year`, `month`, `logs`, `packed`, `unpacked`, `lastBuilt`, `needBuild`
            FROM files";

      if($packed!=(ASDF_EXIST_PACKED|ASDF_EXIST_UNPACKED))
        switch($packed)
        {
          case ASDF_EXIST_PACKED:
            $where[]=" packed=0 ";
            break;
          case ASDF_EXIST_UNPACKED:
            $where[]=" packed>0 ";
            break;
        }

      if($build!=(ASDF_BUILD_DONE|ASDF_BUILD_MISSING ))
        switch($build)
        {
          case ASDF_BUILD_DONE:
            $where[]=" needBuild='N' ";
            break;
          case ASDF_BUILD_MISSING:
            $where[]=" needBuild='Y' ";
            break;
        }

      if(count($where)>0)
        $sql.=" WHERE ".implode(' AND ', $where);

      $sql.=" ORDER BY `year`, `month`";

      $result=$this->dbHandle->query($sql);
      if($result)
      {
        while($row=$result->fetchArray(SQLITE3_ASSOC))
        {
          $returned[]=$row;
        }
      }
    }
    return($returned);
  }

  /**
   * set packed size for a file year, month and packed file size have to be
   * provided
   *
   * @param Integer $year
   * @param Integer $month
   * @param Integer $fileSize
   * @return Boolean
   */
  public function updatePackedSize($year, $month, $fileSize)
  {
    if($this->dbHandle)
    {
      $sql="UPDATE files
            SET packed=$fileSize
            WHERE `year`=$year
              AND `month`=$month;";
      return($this->dbHandle->exec($sql));
    }
    return(false);
  }


  /**
   * build stats for a year/month
   *
   * @param String $fileDir: directory where file is saved
   * @param String $fileName: root name of the file
   * @param Integer $year
   * @param Integer $month
   * @return Boolean
   */
  public function buildStatPeriod($fileDir, $fileName, $year, $month)
  {
    if($this->dbHandle==null) return(false);
    $packed='';
    $returned=true;

    // test if a file exist for the period
    $fmPeriod=new StatDBMonth($fileDir, $fileName, $year, $month);

    $unpacked=$fmPeriod->getFileName(ASDF_EXIST_UNPACKED, true);
    if($unpacked=='')
    {
      $packed=$fmPeriod->getFileName(ASDF_EXIST_PACKED, true);
      if($packed=='') return(false);
      $fmPeriod->unpack();
    }

    $unpacked=$fmPeriod->getFileName(ASDF_EXIST_UNPACKED, true);
    if($unpacked=='') return(false); //not unpacked?

    $fmPeriod->setIpCountryFile($this->ipCountryFile);
    $fmPeriod->open(ASDF_OPEN_WRITE);
    $fmPeriod->buildIPCountry();
    $fmInfos=$fmPeriod->getInfo();
    $fmPeriod->close();

    $result=$this->dbHandle->exec("ATTACH '$unpacked' AS period");
    if($result)
    {
      $needBuild=(date('Ym')==$year.$month)?'Y':'N';

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

        REPLACE INTO main.info (domain, key, value)
          VALUES('log',
                 'buildStatPeriod($year, $month)',
                 '".date('Y-m-d H:i:s')."');

        REPLACE INTO period.info (domain, key, value)
          VALUES('log',
                 'buildStatPeriod($year, $month)',
                 '".date('Y-m-d H:i:s')."');

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

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


        REPLACE INTO main.files (year, month, logs, packed, unpacked, lastBuilt, needBuild)
          VALUES ($year, $month, ".$fmInfos['nbRecords']['logs'].", ".$fmInfos['packedFile']['fileSize'].", ".$fmInfos['unpackedFile']['fileSize'].", '".date('Y-m-d H:i:s')."', '".$needBuild."');

        REPLACE INTO main.statCategories (catId, uaType, year, month, visits)
            SELECT catId, uaType, $year, $month, SUM(visits)
            FROM period.statCategories
            GROUP BY catId, uaType;

        REPLACE INTO main.statImages (imageId, catId, uaType, year, month, visits)
            SELECT imageId, catId, uaType, $year, $month, SUM(visits)
            FROM period.statImages
            GROUP BY imageId, catId, uaType;

        REPLACE INTO main.statIP (uaType, IPadress, catId, year, month, visits, country)
            SELECT uaType, IPadress, catId, $year, $month, SUM(visits), country
            FROM period.statIP
            GROUP BY uaType, IPadress, catId, country;

        REPLACE INTO main.statUA (uaData, uaValue, uaVersion, year, month, visits)
            SELECT uaData, uaValue, uaVersion, $year, $month, SUM(visits)
            FROM period.statUA
            GROUP BY uaData, uaValue, uaVersion;

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

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

    if($packed!='')
    {
      if($fmPeriod->pack())
        $fmPeriod->delete(ASDF_DELETE_UNPACKED); //packed file was unpacked, keep only packed file
    }
    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)
   *
   *
   * only asked 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
   *   'uaValue'   => return detailled stat per type of data
   *   'year'      => return detailled stat per year
   *   'month'     => return detailled stat per month
   *   '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=='year' or
         $field=='month' 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['year'])) $filter['year']=null;
    if(!isset($filter['month'])) $filter['month']=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['year']=$this->getOperator($filter['year'], 'integer');
    $filter['month']=$this->getOperator($filter['month'], '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']=='year' or
             $sort['id']=='month' or
             $sort['id']=='uaData' 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('year', $select))
        $orderBy[]=array('id'=>'year', 'direction'=>'D');
      if(in_array('month', $select))
        $orderBy[]=array('id'=>'month', '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:
   *   'NumIPadress' => return the number of distinct IP address
   *   'IPadress'    => return the IP address
   *   'year'        => return detailled stat per year
   *   'month'       => return detailled stat per month
   *   '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=='year' or
         $field=='month' or
         $field=='catId' or
         $field=='country' or
         $field=='uaType')
      {
        $select[]=$field;
        $groupBy[]=$field;
      }
      if($field=='NumIPadress')
          $select[]='COUNT(DISTINCT IPadress) AS NumIPadress';
    }

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

    if(!isset($filter['IPadress'])) $filter['IPadress']=null;
    if(!isset($filter['year'])) $filter['year']=null;
    if(!isset($filter['month'])) $filter['month']=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['catId']=$this->getOperator($filter['catId'], 'integer');
    $filter['country']=$this->getOperator($filter['country'], 'string');
    $filter['year']=$this->getOperator($filter['year'], 'integer');
    $filter['month']=$this->getOperator($filter['month'], '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']=='IPadress' or
             $sort['id']=='country' or
             $sort['id']=='catId' or
             $sort['id']=='year' or
             $sort['id']=='month' 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('year', $select))
        $orderBy[]=array('id'=>'year', 'direction'=>'D');
      if(in_array('month', $select))
        $orderBy[]=array('id'=>'month', '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 (SELECT * FROM statip $where $groupBy);";

          $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:
   *  'nbVisits' => number of visits
   *
   * all fields are filterable, except 'nbVisits' that can't be filtered
   *
   * available fields values are:
   *   'year'      => return detailled stat per year
   *   'month'     => return detailled stat per month
   *   '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('SUM(visits) AS nbVisits');
    $groupBy=array();
    // initialize fields list
    foreach($fields as $field)
    {
      if($field=='year' or
         $field=='month' or
         $field=='catId' or
         $field=='uaType')
      {
        $select[]=$field;
        $groupBy[]=$field;
      }
    }

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

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

    // check filter values - getOperator check and 'clean' the filter
    $filter['catId']=$this->getOperator($filter['catId'], 'integer');
    $filter['year']=$this->getOperator($filter['year'], 'integer');
    $filter['month']=$this->getOperator($filter['month'], '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']=='catId' or
             $sort['id']=='year' or
             $sort['id']=='month' 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('year', $select))
        $orderBy[]=array('id'=>'year', 'direction'=>'D');
      if(in_array('month', $select))
        $orderBy[]=array('id'=>'month', '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 (SELECT * FROM statcategories $where $groupBy);";

          $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:
   *  'nbVisits' => number of visits
   *
   * all fields are filterable, except 'nbVisits' that can't be filtered
   *
   * available fields values are:
   *   'year'      => return detailled stat per year
   *   'month'     => return detailled stat per month
   *   '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('SUM(visits) AS nbVisits');
    $groupBy=array();
    // initialize fields list
    foreach($fields as $field)
    {
      if($field=='year' or
         $field=='month' or
         $field=='catId' or
         $field=='imageId' or
         $field=='uaType')
      {
        $select[]=$field;
        $groupBy[]=$field;
      }
    }

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

    if(!isset($filter['imageId'])) $filter['imageId']=null;
    if(!isset($filter['year'])) $filter['year']=null;
    if(!isset($filter['month'])) $filter['month']=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['year']=$this->getOperator($filter['year'], 'integer');
    $filter['month']=$this->getOperator($filter['month'], 'integer');
    $filter['imageId']=$this->getOperator($filter['imageId'], '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']=='year' or
             $sort['id']=='month' or
             $sort['id']=='imageId' 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('year', $select))
        $orderBy[]=array('id'=>'year', 'direction'=>'D');
      if(in_array('month', $select))
        $orderBy[]=array('id'=>'month', '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 (SELECT * FROM statimages $where $groupBy);";

          $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:
   *   'year'      => return detailled stat per year
   *   'month'     => return detailled stat per month
   *   '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=='year' or
         $field=='month')
      {
        $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['year'])) $filter['year']=null;
    if(!isset($filter['month'])) $filter['month']=null;
    if(!isset($filter['catId'])) $filter['catId']=null;

    foreach(array('T', 'C', 'I', 'A') as $key)
    {
      $filter[$key]['year']=$this->getOperator($filter['year'], 'integer');
      $filter[$key]['month']=$this->getOperator($filter['month'], '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']=='year' or
             $sort['id']=='month') 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('year', $select['T']))
        $orderBy[]=array('id'=>'year', 'direction'=>'D');
      if(in_array('month', $select['T']))
        $orderBy[]=array('id'=>'month', '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;

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

    if($result)
    {
      $year=array(
        'min' => 9999,
        'max' => 0
      );
      $sqlResult=array();
      while($row=$result->fetchArray(SQLITE3_ASSOC))
      {
        if(isset($row['year']))
        {
          if($row['year']>$year['max']) $year['max']=$row['year'];
          if($row['year']<$year['min']) $year['min']=$row['year'];
        }
        $sqlResult[]=$row;
      }

      if($year['min']==9999 and $year['max']==0) $year['min']=0;

      /*
       * $returned have a row per period & info type T,C,I,A
       *
       * example:
       *   2010 09 T 25
       *   2010 09 C 10
       *   2010 09 I 45
       *   2010 09 A 4
       *   2010 11 T 8
       *   2010 11 C 6
       *   2010 11 A 1
       *   2010 12 T 1
       *   2010 12 A 1
       *
       * the result is built as a table with:
       *  - info type in column
       *  - missing periods
       *
       * example:
       *   period   T   C   I   A
       *   2010 09  25  10  45  4
       *   2010 10  0   0   0   0
       *   2010 11  8   6   0   1
       *   2010 12  1   0   0   1
       */

      // first step, build a period list
      if(in_array('year', $fields) or in_array('month', $fields))
      {
        for($y=$year['min'];$y<=$year['max'];$y++)
        {
          if(in_array('month', $fields))
          {
            $returned[$y]=array();
            for($m=1;$m<=12;$m++)
            {
              $returned[$y][$m]=array(
                'T' => 0,
                'C' => 0,
                'I' => 0,
                'A' => 0
              );
            }
          }
          else
          {
            $returned[$y]=array(
              0 => array( // no month => 0
                    'T' => 0,
                    'C' => 0,
                    'I' => 0,
                    'A' => 0
              )
            );
          }
        }
      }
      else
      {
        $returned=array(
          0 => array( // no year => 0
              0 => array( // no month => 0
                    'T' => 0,
                    'C' => 0,
                    'I' => 0,
                    'A' => 0
                )
            )
        );
      }

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

        if(isset($row['month']))
        {
          $m=$row['month'];
        }
        else
        {
          $m=0;
        }

        $returned[$y][$m][$row['nfoType']]=$row['nbVisits'];
      }

    }
    return($returned);
  }


  /**
   * 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, 'files', ASDF_DB_INFO);
        if(count($result)==0)
        {
          $this->dbHandle->exec(
              "CREATE TABLE 'files' (
                  'year' INTEGER,
                  'month' INTEGER,
                  'logs' INTEGER,
                  'packed' INTEGER,
                  'unpacked' INTEGER,
                  'lastBuilt' TEXT,
                  'needBuild' TEXT
              );"
            );
          $this->dbHandle->exec("CREATE UNIQUE INDEX iPeriod ON files ('year', 'month');");
        }

        $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,
                  'year' INTEGER,
                  'month' INTEGER,
                  'visits' INTEGER
              );"
            );
          $this->dbHandle->exec("CREATE UNIQUE INDEX iCat ON statCategories ('catId', 'uaType', 'year', 'month');");
          $this->dbHandle->exec("CREATE UNIQUE INDEX iCatPeriod ON statCategories ('year', 'month', 'uaType', 'catId');");
        }

        $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,
                  'year' INTEGER,
                  'month' INTEGER,
                  'visits' INTEGER
              );"
            );
          $this->dbHandle->exec("CREATE UNIQUE INDEX iImg ON statImages ('imageId', 'catId', 'uaType', 'year', 'month');");
          $this->dbHandle->exec("CREATE UNIQUE INDEX iImgPeriod ON statImages ('year', 'month', 'uaType', 'imageId');");
        }

        $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,
                  'year' INTEGER,
                  'month' INTEGER,
                  'visits' INTEGER,
                  'country' TEXT
              );"
            );
          $this->dbHandle->exec("CREATE UNIQUE INDEX iIP ON statIP ('uaType', 'IPadress', 'catId', 'year', 'month');");
          $this->dbHandle->exec("CREATE INDEX iIPPeriod ON statIP ('year', 'month', 'IPadress', 'uaType');");
        }

        $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,
                  'year' INTEGER,
                  'month' INTEGER,
                  'visits' INTEGER
              );"
            );
          $this->dbHandle->exec("CREATE UNIQUE INDEX iUA ON statUA ('uaData', 'uaValue', 'uaVersion', 'year', 'month');");
          $this->dbHandle->exec("CREATE INDEX iUAPeriod ON statUA ('year', 'month', 'uaData', 'uaValue');");
        }

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

} // class


?>