<?php
/*
 * -----------------------------------------------------------------------------
 * Plugin Name: EStat
 * -----------------------------------------------------------------------------
 * Author     : Grum
 *   email    : grum@piwigo.org
 *   website  : http://www.grum.fr
 *   PWG user : http://forum.piwigo.org/profile.php?id=3706
 *
 *   << May the Little SpaceFrog be with you ! >>
 *
 * -----------------------------------------------------------------------------
 *
 * See main.inc.php for release information
 *
 * manage all the ajax requests relative to file exports
 *   => very similar to EStat_Ajax functions for the following services:
 *          admin.export.history    <=>   admin.stat.history
 *          admin.export.ip         <=>   admin.stat.ip
 *          admin.export.category   <=>   admin.stat.category
 *          admin.export.image      <=>   admin.stat.image
 *
 *   the admin.export.loadFile service is used to download produced files
 * -----------------------------------------------------------------------------
 */

  // in this case, PHPWG_ROOT_PATH must be declared as an absolute path...
  define('PHPWG_ROOT_PATH',dirname(dirname(dirname(__FILE__))).'/');
  if(!defined('AJAX_CALL')) define('AJAX_CALL', true);

  /*
   * set ajax module in admin mode if request is used for admin interface
   */
  if(!isset($_REQUEST['ajaxfct'])) $_REQUEST['ajaxfct']='';
  if(preg_match('/^admin\./i', $_REQUEST['ajaxfct'])) define('IN_ADMIN', true);

  // the common.inc.php file loads all the main.inc.php plugins files
  include_once(PHPWG_ROOT_PATH.'include/common.inc.php' );
  include_once(PHPWG_PLUGINS_PATH.'GrumPluginClasses/classes/GPCAjax.class.inc.php');
  include_once(PHPWG_PLUGINS_PATH.'GrumPluginClasses/classes/GPCUserAgent.class.inc.php');
  include_once(PHPWG_PLUGINS_PATH.'GrumPluginClasses/classes/GPCExport.class.inc.php');
  include_once('estat_root.class.inc.php');

  load_language('plugin.lang', ESTAT_PATH);


  class EStat_AjaxExport extends EStat_root
  {
    /**
     * constructor
     */
    public function __construct($prefixeTable, $filelocation)
    {
      parent::__construct($prefixeTable, $filelocation);
      $this->loadConfig();
      $this->checkRequest();
      $this->returnAjaxContent();
    }

    /**
     * check the $_REQUEST values and set default values
     *
     */
    protected function checkRequest()
    {
      global $user;

      if(!isset($_REQUEST['errcode'])) $_REQUEST['errcode']='';
      GPCAjax::checkToken();

      // check if asked function is valid
      if(!($_REQUEST[GPC_AJAX]=='admin.export.history' or
           $_REQUEST[GPC_AJAX]=='admin.export.ip' or
           $_REQUEST[GPC_AJAX]=='admin.export.category' or
           $_REQUEST[GPC_AJAX]=='admin.export.image' or
           $_REQUEST[GPC_AJAX]=='admin.export.loadFile'
           )) $_REQUEST[GPC_AJAX]='';

      if(preg_match('/^admin\./i', $_REQUEST['ajaxfct']) and !is_admin()) $_REQUEST['ajaxfct']='';

      if($_REQUEST[GPC_AJAX]!='')
      {
        // name, format & compression common to all requests
        if(!isset($_REQUEST['name'])) $_REQUEST['name']='';
        if(!isset($_REQUEST['format'])) $_REQUEST['format']='csv';
        if(!isset($_REQUEST['compression'])) $_REQUEST['compression']='none';

        if(!($_REQUEST['format']=='csv' or
             $_REQUEST['format']=='ods' or
             $_REQUEST['format']=='sqlite')) $_REQUEST['format']='csv';

        if(!($_REQUEST['compression']=='none' or
             $_REQUEST['compression']=='zip' or
             $_REQUEST['compression']=='gz' or
             $_REQUEST['compression']=='bz2')) $_REQUEST['format']='none';

        if(preg_match('/^([a-z0-9\._]|-)+$/i', $_REQUEST['name'])!=1) $_REQUEST['name']='export_'.date('Ymd-His');

        switch($_REQUEST[GPC_AJAX])
        {
          case 'admin.export.history':
            if(!isset($_REQUEST['period'])) $_REQUEST['period']='';

            if(!isset($_REQUEST['sort']))
                $_REQUEST['sort']=array(
                    array('direction' => 'A', 'id' => 'date')
                );

            if(!isset($_REQUEST['filter'])) $_REQUEST['filter']=array();

            if($_REQUEST['period']=='') $_REQUEST['ajaxfct']='';
            break;
          case 'admin.export.ip':
            if(!isset($_REQUEST['period'])) $_REQUEST['period']='a';

            if(!isset($_REQUEST['group'])) $_REQUEST['group']=array();

            if(!isset($_REQUEST['sort']))
                $_REQUEST['sort']=array(
                    array('direction' => 'A', 'id' => 'ip')
                );

            if(!isset($_REQUEST['additionalFilter'])) $_REQUEST['additionalFilter']=array();
            if(!isset($_REQUEST['filter'])) $_REQUEST['filter']=array();

            if($_REQUEST['period']=='') $_REQUEST['ajaxfct']='';
            $_REQUEST['filter']['additionalFilter']=$_REQUEST['additionalFilter'];
            break;
          case 'admin.export.category':
          case 'admin.export.image':
            if(!isset($_REQUEST['period'])) $_REQUEST['period']='a';

            if(!isset($_REQUEST['group'])) $_REQUEST['group']=array();

            if(!isset($_REQUEST['sort']))
                $_REQUEST['sort']=array(
                    array('direction' => 'A', 'id' => 'nbVisits')
                );

            if(!isset($_REQUEST['additionalFilter'])) $_REQUEST['additionalFilter']=array();
            if(!isset($_REQUEST['filter'])) $_REQUEST['filter']=array();

            if($_REQUEST['period']=='') $_REQUEST['ajaxfct']='';
            $_REQUEST['filter']['additionalFilter']=$_REQUEST['additionalFilter'];
            break;
          case 'admin.export.loadFile':
            break;
          default:
            $_REQUEST[GPC_AJAX]='';
            break;
        }

      }
    } //checkRequest


    /**
     * return ajax content
     */
    protected function returnAjaxContent()
    {
      $result="KO";
      switch($_REQUEST[GPC_AJAX])
      {
        case 'admin.export.history':
          $result=$this->ajax_estat_export_admin_statHistory($_REQUEST['period'], $_REQUEST['filter'], $_REQUEST['sort'], $_REQUEST['format'], $_REQUEST['compression'], $_REQUEST['name']);
          break;
        case 'admin.export.ip':
          $result=$this->ajax_estat_export_admin_statIP($_REQUEST['period'], $_REQUEST['filter'], $_REQUEST['sort'], $_REQUEST['group'], $_REQUEST['format'], $_REQUEST['compression'], $_REQUEST['name']);
          break;
        case 'admin.export.category':
          $result=$this->ajax_estat_export_admin_statCategory($_REQUEST['period'], $_REQUEST['filter'], $_REQUEST['sort'], $_REQUEST['group'], $_REQUEST['format'], $_REQUEST['compression'], $_REQUEST['name']);
          break;
        case 'admin.export.image':
          $result=$this->ajax_estat_export_admin_statImage($_REQUEST['period'], $_REQUEST['filter'], $_REQUEST['sort'], $_REQUEST['group'], $_REQUEST['format'], $_REQUEST['compression'], $_REQUEST['name']);
          break;
        case 'admin.export.loadFile':
          $result=$this->ajax_estat_export_admin_loadFile($_REQUEST['name']);
          break;
      }
      if($result!='KO')
      {
        $result='OK:'.$this->getAdminLink('ajaxExport')."?ajaxfct=admin.export.loadFile&token=".get_pwg_token()."&name=".basename($result);
      }
      GPCAjax::returnResult($result);
    }


    /*------------------------------------------------------------------------*
     *
     * ADMIN FUNCTIONS
     *
     *----------------------------------------------------------------------- */

    /**
     * return the logs events from a period
     *
     * @param String $period : the period to be processed 'YYYYMM'
     * @param Array  $filter : filter options
     * @param Integer $pageNumber : page number to return
     * @return String : an array in json format
     *                    (Array) events properties
     *                      'date' => (String) date time in 'YYYY-MM-DD HH:II:SS' format
     *                      'IpUserId' => (String) IP or user login
     *                      'country' => (String) country code (ISO 3366-1)
     *                      'catId' => (String) name of the album
     *                      'imageId' => (String) name of image
     *                      'tags' => (String) tags list
     *                      'section' => (String) section name
     *                      'uaEngine' => (String) browser: engine+version
     *                      'uaBrowser' => (String) browser name+version
     *                      'uaOS' => (String) browser os: name+version
     *                      'uaType' => (String) browser type: name
     *                      'userAgent' => (String) user agent string
     */
    protected function ajax_estat_export_admin_statHistory($period, $filterPost, $sort, $format, $compression, $name)
    {
      global $lang;

      $year=substr($period,0,4);
      $month=substr($period,4,2);

      $filter=array();
      foreach($filterPost as $val)
      {
        $filter[$val['id']]=$val;
      }

      // open db file for the period
      $monthStatFile=new StatDBMonth($this->fileStatDir, self::FILE_MONTH);
      $monthStatFile->setPeriod($year, $month);
      $monthStatFile->open(ASDF_OPEN_READ);

      // get history & total number of items
      $result=$monthStatFile->getLogs(ASDF_GET_ROWS, 0, 0, $filter, $sort);
      $monthStatFile->close();

      // prepare an EStat_IdList to buid a list of unique Id
      $idList=new EStat_IdList(array('catId', 'imageId', 'tagsId', 'userId'));
      // $idAssoc will get the label associated to the id
      $idAssoc=array(
        'catId'=>array(),
        'imageId'=>array(),
        'tagsId'=>array(),
        'userId'=>array()
      );

      // build list of unique Id
      foreach($result as $row)
      {
        $idList->addItems(
          array(
            'catId' => $row['catId'],
            'imageId' => $row['imageId'],
            'tagsId' => $row['tagsId'],
            'userId' => $row['userId']
          )
        );
      }


      if(count($idList->getItems('catId')) > 0)
        $this->prepareIdList($idAssoc, 'catId', "SELECT id, name FROM ".CATEGORIES_TABLE." WHERE id IN (".implode(',', $idList->getItems('catId')).") ORDER BY id;");

      if(count($idList->getItems('imageId')) > 0)
        $this->prepareIdList($idAssoc, 'imageId', "SELECT id, file AS name, path FROM ".IMAGES_TABLE." WHERE id IN (".implode(',', $idList->getItems('imageId')).") ORDER BY id;");

      if(count($idList->getItems('tagsId')) > 0)
        $this->prepareIdList($idAssoc, 'tagsId', "SELECT id, name FROM ".TAGS_TABLE." WHERE id IN (".implode(',', $idList->getItems('tagsId')).") ORDER BY id;");

      if(count($idList->getItems('userId')) > 0)
        $this->prepareIdList($idAssoc, 'userId', "SELECT id, username AS name FROM ".USERS_TABLE." WHERE id IN (".implode(',', $idList->getItems('userId')).") ORDER BY id;");

      $data=array();
      // complete the data
      foreach($result as $row)
      {
        $userAgentNfo=GPCUserAgent::getProperties(
            array(
              UA_DATA_BROWSER => $row['uaBrowser'],
              UA_DATA_BROWSER_TYPE => $row['uaType'],
              UA_DATA_OS => $row['uaOS'],
              UA_DATA_ENGINE => $row['uaEngine']
            )
          );

        if($userAgentNfo[UA_DATA_ENGINE_NAME]=='Unknown') $userAgentNfo[UA_DATA_ENGINE_NAME]='ua_Unknown';
        if($userAgentNfo[UA_DATA_BROWSER_NAME]=='Unknown') $userAgentNfo[UA_DATA_BROWSER_NAME]='ua_Unknown';
        if($userAgentNfo[UA_DATA_OS_NAME]=='Unknown') $userAgentNfo[UA_DATA_OS_NAME]='ua_Unknown';

        $country=l10n('estat_country_'.strtoupper($row['country']));
        if(isset($lang[$country]))
        {
          $country=$lang[$country];
        }
        else
        {
          $country='';
        }

        //$userAgent = UserAgent::parse($row['userAgent']);
        $data[]=array(
          'date' => date('Y-m-d H:i:s', $row['date']),
          'IP' => $row['IPadress'],
          'UserId' => $row['userId'],
          'UserName' => $this->getId($idAssoc, 'userId', $row['userId'], '?', 'name'),
          'countryId' => $row['country'],
          'country' => $country,
          'catId' => $row['catId'],
          'catName' => GPCCore::getUserLanguageDesc($this->getId($idAssoc, 'catId', $row['catId'], ($row['catId']==0)?'-':'?')),
          'imageId' => $row['imageId'],
          'imageName' => $this->getId($idAssoc, 'imageId', $row['imageId'], ($row['imageId']==0)?'-':'?', 'name'),
          'tags'  => $this->getId($idAssoc, 'tagsId', $row['tagsId'], '', 'name'),
          'section' => l10n($row['section']),
          'uaEngineId' => $row['uaEngine'],
          'uaEngineName' => l10n($userAgentNfo[UA_DATA_ENGINE_NAME]),
          'uaEngineVersion' => $row['uaEngineVersion'],
          'uaBrowserId' => $row['uaBrowser'],
          'uaBrowserName' => l10n($userAgentNfo[UA_DATA_BROWSER_NAME]),
          'uaBrowserVersion' => $row['uaBrowserVersion'],
          'uaOSId' => $row['uaOS'],
          'uaOSName' => l10n($userAgentNfo[UA_DATA_OS_NAME]),
          'uaOSVersion' => $row['uaOSVersion'],
          'uaTypeId' => $row['uaType'],
          'uaType' => l10n('ua_'.$userAgentNfo[UA_DATA_BROWSER_TYPE_NAME]),
          'userAgent' => $row['userAgent']
        );
      }


      switch($format)
      {
        case 'csv':
          $extension='.csv';
          $export=new GPCCSVExport();
          $export->setOptions(
              array(
                'separator' => $this->config['export.csv.separator'],
                'decimalDot' => $this->config['export.csv.decimalDot'],
                'useQuotes' => ($this->config['export.csv.useQuotes']=='y')?true:false,
                'lineFeed' => $this->config['export.csv.lineFeed'],
              )
            );
          $export->setColumnsDef(
            array(
              array('name' => l10n('estat_datetime')),
              array('name' => l10n('estat_ipAdress')),
              array('name' => l10n('estat_userId'), 'type' => 'INTEGER'),
              array('name' => l10n('estat_userName')),
              array('name' => l10n('estat_countryId')),
              array('name' => l10n('estat_country')),
              array('name' => l10n('estat_categoryId'), 'type' => 'INTEGER'),
              array('name' => l10n('estat_category')),
              array('name' => l10n('estat_imageId'), 'type' => 'INTEGER'),
              array('name' => l10n('estat_image')),
              array('name' => l10n('estat_tags')),
              array('name' => l10n('estat_section')),
              array('name' => l10n('estat_engineId'), 'type' => 'INTEGER'),
              array('name' => l10n('estat_engine')),
              array('name' => l10n('estat_engineVersion')),
              array('name' => l10n('estat_browserId'), 'type' => 'INTEGER'),
              array('name' => l10n('estat_browser')),
              array('name' => l10n('estat_browserVersion')),
              array('name' => l10n('estat_osId'), 'type' => 'INTEGER'),
              array('name' => l10n('estat_os')),
              array('name' => l10n('estat_osVersion')),
              array('name' => l10n('estat_typeId'), 'type' => 'INTEGER'),
              array('name' => l10n('estat_type')),
              array('name' => l10n('estat_userAgentString'))
            )
          );
          break;
        case 'ods':
          $extension='.ods';
          $export=new GPCODSExport();
          $export->setOptions(
              array(
                'sheetName' => l10n('estat_history'),
                'fileTitle' => $this->config['export.ods.fileTitle'],
                'fileSubject' => sprintf(l10n('estat_fileODSSubject'), l10n('estat_history')),
                'keywords' => $this->config['export.ods.keywords'],
                'comments' => sprintf(l10n('estat_fileODSComments_1'), date('Y-m-d@H:i:s'), ESTAT_VERSION, PHPWG_VERSION)
              )
            );
          $export->setColumnsDef(
            array(
              array('name' => l10n('estat_datetime')),
              array('name' => l10n('estat_ipAdress')),
              array('name' => l10n('estat_userId'), 'type' => 'INTEGER'),
              array('name' => l10n('estat_userName')),
              array('name' => l10n('estat_countryId')),
              array('name' => l10n('estat_country')),
              array('name' => l10n('estat_categoryId'), 'type' => 'INTEGER'),
              array('name' => l10n('estat_category')),
              array('name' => l10n('estat_imageId'), 'type' => 'INTEGER'),
              array('name' => l10n('estat_image')),
              array('name' => l10n('estat_tags')),
              array('name' => l10n('estat_section')),
              array('name' => l10n('estat_engineId'), 'type' => 'INTEGER'),
              array('name' => l10n('estat_engine')),
              array('name' => l10n('estat_engineVersion')),
              array('name' => l10n('estat_browserId'), 'type' => 'INTEGER'),
              array('name' => l10n('estat_browser')),
              array('name' => l10n('estat_browserVersion')),
              array('name' => l10n('estat_osId'), 'type' => 'INTEGER'),
              array('name' => l10n('estat_os')),
              array('name' => l10n('estat_osVersion')),
              array('name' => l10n('estat_typeId'), 'type' => 'INTEGER'),
              array('name' => l10n('estat_type')),
              array('name' => l10n('estat_userAgentString'))
            )
          );
          break;
        case 'sqlite':
          $extension='.db';
          $export=new GPCSQLiteExport();
          $export->setOptions(
              array(
                'tableName' => 'history'
              )
            );
          $export->setColumnsDef(
            array(
              array('name' => 'datetime'),
              array('name' => 'ipAdress'),
              array('name' => 'userId', 'type' => 'INTEGER'),
              array('name' => 'userName'),
              array('name' => 'countryId'),
              array('name' => 'country'),
              array('name' => 'categoryId', 'type' => 'INTEGER'),
              array('name' => 'category'),
              array('name' => 'imageId', 'type' => 'INTEGER'),
              array('name' => 'image'),
              array('name' => 'tags'),
              array('name' => 'section'),
              array('name' => 'engineId', 'type' => 'INTEGER'),
              array('name' => 'engine'),
              array('name' => 'engineVersion'),
              array('name' => 'browserId', 'type' => 'INTEGER'),
              array('name' => 'browser'),
              array('name' => 'browserVersion'),
              array('name' => 'osId', 'type' => 'INTEGER'),
              array('name' => 'os'),
              array('name' => 'osVersion'),
              array('name' => 'typeId', 'type' => 'INTEGER'),
              array('name' => 'type'),
              array('name' => 'userAgentString')
            )
          );
          break;
      }
      $export->setFileName($name.$extension);
      $export->setFileDir($this->fileExportDir);

      if($export->write($data))
      {
        $file=$this->fileExportDir.$export->getFileName();

        if($compression!='none')
        {
          $compressedFile=$this->compress($file, $compression);
          unlink($file);
          $file=$compressedFile;
        }
        if(file_exists($file))
        {
          return($file);
        }
      }
      return('KO');
    }


    /**
     * return the detail for IP from a period
     *
     * @param String $period : the period to be processed
     *                          'allYear'    => process all year
     *                          'y-YYYY'     => process year YYYY
     *                          'ym-YYYYMM'  => process year/month YYYY/MM
     * @param Array  $filterPost : filter options
     * @param Array  $sort: sort options
     * @param Array  $group: grouped items; could be:
     *                        'IPadress'
     *                        'country'
     *                        'uaType'
     * @param Integer $pageNumber : page number to return
     * @return String : an array in json format
     *                    (Array)
     *                      'rows' => (Array)
     *                                  'IPadress' => (String)
     *                                  'country' => (String)
     *                                  'uaType' => (String)
     *                                  'nbVisits' => (Integer)
     *                      'total' => (Array)
     *                                  'IPadress' => (String) [empty]
     *                                  'country' => (String) [empty]
     *                                  'uaType' => (String) [empty]
     *                                  'nbVisits' => (Integer)
     *                      'nbItems' => (Integer)
     */
    protected function ajax_estat_export_admin_statIP($period, $filterPost, $sort, $group, $format, $compression, $name)
    {
      global $lang;

      $year=null;
      $month=null;

      $fields=array(
        'rows' => array_diff(array('uaType', 'IPadress', 'country'), $group),
        'total' => array()
      );
      $filter=array();

      if(substr($period,0,3)=='ym-')
      {
        $year=substr($period,3,4)*1;
        $month=substr($period,7,2)*1;
      }
      elseif(substr($period,0,2)=='y-')
      {
        $year=substr($period,2,4)*1;
      }

      foreach($filterPost as $key => $val)
      {
        if($key==='additionalFilter')
        {
          if(isset($val['catId']))
            $filter['catId']=$this->buildCatIdFilter($val['catId']);
        }
        else
        {
          $filter[$val['id']]=$val;
        }
      }

      if($year!=null)
        $filter['year']=array('operator' => '=', 'value' => $year);
      if($month!=null)
        $filter['month']=array('operator' => '=', 'value' => $month);

      // open db file for the period


      $statFile=new StatDBGlobal($this->fileStatDir, self::FILE_GLOBAL);

      $statFile->open(ASDF_OPEN_READ);

      // get stats
      $result=$statFile->getStatIP(ASDF_GET_ROWS, 0, 0, $fields['rows'], $filter, $sort);
      $statFile->close();

      $data=array();
      foreach($result as $row)
      {
        if(isset($row['uaType']))
        {
          $userAgentNfo=GPCUserAgent::getProperties(
              array(
                UA_DATA_BROWSER_TYPE => $row['uaType']
              )
            );
          $uaType=l10n('ua_'.$userAgentNfo[UA_DATA_BROWSER_TYPE_NAME]);
        }
        else
        {
          $row['uaType']='';
          $uaType='';
        }

        if(isset($row['country']))
        {
          $country='estat_country_'.strtoupper($row['country']);
          if(isset($lang[$country]))
          {
            $country=$lang[$country];
          }
          else
          {
            $country='';
          }
        }
        else
        {
          $country='';
          $row['country']='';
        }

        if(!isset($row['IPadress']))
        {
          $row['IPadress']='';
        }

        $data[]=array(
          $row['IPadress'],
          $row['country'],
          $country,
          $row['uaType'],
          $uaType,
          $row['nbVisits']
        );
      }


      switch($format)
      {
        case 'csv':
          $extension='.csv';
          $export=new GPCCSVExport();
          $export->setOptions(
              array(
                'separator' => $this->config['export.csv.separator'],
                'decimalDot' => $this->config['export.csv.decimalDot'],
                'useQuotes' => ($this->config['export.csv.useQuotes']=='y')?true:false,
                'lineFeed' => $this->config['export.csv.lineFeed'],
              )
            );
          $export->setColumnsDef(
            array(
              array('name' => l10n('estat_ipAdress')),
              array('name' => l10n('estat_countryID')),
              array('name' => l10n('estat_country')),
              array('name' => l10n('estat_typeId'), 'type' => 'INTEGER'),
              array('name' => l10n('estat_type')),
              array('name' => l10n('estat_visits'), 'type' => 'INTEGER')
            )
          );
          break;
        case 'ods':
          $extension='.ods';
          $export=new GPCODSExport();
          $export->setOptions(
              array(
                'sheetName' => l10n('estat_ipAdress'),
                'fileTitle' => $this->config['export.ods.fileTitle'],
                'fileSubject' => sprintf(l10n('estat_fileODSSubject'), l10n('estat_by_ip')),
                'keywords' => $this->config['export.ods.keywords'],
                'comments' => sprintf(l10n('estat_fileODSComments_1'), date('Y-m-d@H:i:s'), ESTAT_VERSION, PHPWG_VERSION)
              )
            );
          $export->setColumnsDef(
            array(
              array('name' => l10n('estat_ipAdress')),
              array('name' => l10n('estat_countryId')),
              array('name' => l10n('estat_country')),
              array('name' => l10n('estat_typeId'), 'type' => 'INTEGER'),
              array('name' => l10n('estat_type')),
              array('name' => l10n('estat_visits'), 'type' => 'INTEGER')
            )
          );
          break;
        case 'sqlite':
          $extension='.db';
          $export=new GPCSQLiteExport();
          $export->setOptions(
              array(
                'tableName' => 'ipAdress'
              )
            );
          $export->setColumnsDef(
            array(
              array('name' => 'ipAdress'),
              array('name' => 'countryId'),
              array('name' => 'country'),
              array('name' => 'typeId', 'type' => 'INTEGER'),
              array('name' => 'type'),
              array('name' => 'visits', 'type' => 'INTEGER')
            )
          );
          break;
      }
      $export->setFileName($name.$extension);
      $export->setFileDir($this->fileExportDir);

      if($export->write($data))
      {
        $file=$this->fileExportDir.$export->getFileName();

        if($compression!='none')
        {
          $compressedFile=$this->compress($file, $compression);
          unlink($file);
          $file=$compressedFile;
        }
        if(file_exists($file))
        {
          return($file);
        }
      }
      return('KO');
    }


    /**
     * return the detail for albums/categories from a period
     *
     * @param String $period : the period to be processed
     *                          'allYear'    => process all year
     *                          'y-YYYY'     => process year YYYY
     *                          'ym-YYYYMM'  => process year/month YYYY/MM
     * @param Array  $filterPost : filter options
     * @param Array  $sort: sort options
     * @param Array  $group: grouped items; could be:
     *                        'uaType'
     * @return String : an array in json format
     *                    (Array)
     *                      'rows' => (Array)
     *                                  'category' => (String)
     *                                  'uaType' => (String)
     *                                  'nbVisits' => (Integer)
     *                      'total' => (Array)
     *                                  'category' => (String) [empty]
     *                                  'uaType' => (String) [empty]
     *                                  'nbVisits' => (Integer)
     *                      'nbItems' => (Integer)
     */
    protected function ajax_estat_export_admin_statCategory($period, $filterPost, $sort, $group, $format, $compression, $name)
    {
      global $lang;

      $year=null;
      $month=null;

      $fields=array(
        'rows' => array_diff(array('catId', 'uaType'), $group),
        'total' => array()
      );
      $filter=array();

      if(substr($period,0,3)=='ym-')
      {
        $year=substr($period,3,4)*1;
        $month=substr($period,7,2)*1;
      }
      elseif(substr($period,0,2)=='y-')
      {
        $year=substr($period,2,4)*1;
      }

      foreach($filterPost as $key => $val)
      {
        if($key==='additionalFilter')
        {
          if(isset($val['catId']))
            $filter['catId']=$this->buildCatIdFilter($val['catId']);
        }
        else
        {
          $filter[$val['id']]=$val;
        }
      }

      if($year!=null)
        $filter['year']=array('operator' => '=', 'value' => $year);
      if($month!=null)
        $filter['month']=array('operator' => '=', 'value' => $month);

      // open db file for the period


      $statFile=new StatDBGlobal($this->fileStatDir, self::FILE_GLOBAL);

      $statFile->open(ASDF_OPEN_READ);

      // get stats
      $result=$statFile->getStatCat(ASDF_GET_ROWS, 0, 0, $fields['rows'], $filter, $sort);
      $statFile->close();

      // prepare categories&images properties
      $idList=new EStat_IdList(array('catId', 'imageId'));
      $idAssoc=array(
        'catId'=>array(),
        'imageId'=>array()
      );

      // first, build category_id list
      foreach($result as $row)
      {
        $idList->addItems(array('catId' => $row['catId']));
      }

      // get all needed properties for each category_id
      if(count($idList->getItems('catId')) > 0)
        $this->prepareIdList($idAssoc, 'catId', "SELECT id, name FROM ".CATEGORIES_TABLE." WHERE id IN (".implode(',', $idList->getItems('catId')).") ORDER BY id;");

      if(count($idList->getItems('imageId')) > 0)
        $this->prepareIdList($idAssoc, 'imageId', "SELECT id, file AS name, path FROM ".IMAGES_TABLE." WHERE id IN (".implode(',', $idList->getItems('imageId')).") ORDER BY id;");

      $data=array();
      foreach($result as $row)
      {
        if(isset($row['uaType']))
        {
          $userAgentNfo=GPCUserAgent::getProperties(
              array(
                UA_DATA_BROWSER_TYPE => $row['uaType']
              )
            );
          $uaType=l10n('ua_'.$userAgentNfo[UA_DATA_BROWSER_TYPE_NAME]);
        }
        else
        {
          $row['uaType']='';
          $uaType='';
        }


        if($row['catId']==0)
        {
          $categoryNfo=l10n('estat_special_category');
        }
        elseif($this->getId($idAssoc, 'catId', $row['catId'], '', 'name')=='')
        {
          $categoryNfo=l10n('estat_deleted_category');
        }
        else
        {
          $categoryNfo=$this->getId($idAssoc, 'catId', $row['catId'], '', 'name');
        }

        $data[]=array(
          $row['catId'],
          $categoryNfo,
          $row['uaType'],
          $uaType,
          $row['nbVisits']
        );
      }


      switch($format)
      {
        case 'csv':
          $extension='.csv';
          $export=new GPCCSVExport();
          $export->setOptions(
              array(
                'separator' => $this->config['export.csv.separator'],
                'decimalDot' => $this->config['export.csv.decimalDot'],
                'useQuotes' => ($this->config['export.csv.useQuotes']=='y')?true:false,
                'lineFeed' => $this->config['export.csv.lineFeed'],
              )
            );
          $export->setColumnsDef(
            array(
              array('name' => l10n('estat_categoryId')),
              array('name' => l10n('estat_category')),
              array('name' => l10n('estat_typeId'), 'type' => 'INTEGER'),
              array('name' => l10n('estat_type')),
              array('name' => l10n('estat_visits'), 'type' => 'INTEGER')
            )
          );
          break;
        case 'ods':
          $extension='.ods';
          $export=new GPCODSExport();
          $export->setOptions(
              array(
                'sheetName' => l10n('estat_category'),
                'fileTitle' => $this->config['export.ods.fileTitle'],
                'fileSubject' => sprintf(l10n('estat_fileODSSubject'), l10n('estat_by_category')),
                'keywords' => $this->config['export.ods.keywords'],
                'comments' => sprintf(l10n('estat_fileODSComments_1'), date('Y-m-d@H:i:s'), ESTAT_VERSION, PHPWG_VERSION)
              )
            );
          $export->setColumnsDef(
            array(
              array('name' => l10n('estat_categoryId')),
              array('name' => l10n('estat_category')),
              array('name' => l10n('estat_typeId'), 'type' => 'INTEGER'),
              array('name' => l10n('estat_type')),
              array('name' => l10n('estat_visits'), 'type' => 'INTEGER')
            )
          );
          break;
        case 'sqlite':
          $extension='.db';
          $export=new GPCSQLiteExport();
          $export->setOptions(
              array(
                'tableName' => 'album'
              )
            );
          $export->setColumnsDef(
            array(
              array('name' => 'albumId'),
              array('name' => 'album'),
              array('name' => 'typeId', 'type' => 'INTEGER'),
              array('name' => 'type'),
              array('name' => 'visits', 'type' => 'INTEGER')
            )
          );
          break;
      }
      $export->setFileName($name.$extension);
      $export->setFileDir($this->fileExportDir);

      if($export->write($data))
      {
        $file=$this->fileExportDir.$export->getFileName();

        if($compression!='none')
        {
          $compressedFile=$this->compress($file, $compression);
          unlink($file);
          $file=$compressedFile;
        }
        if(file_exists($file))
        {
          return($file);
        }
      }
      return('KO');
    }


    /**
     * return the detail for images from a period
     *
     * @param String $period : the period to be processed
     *                          'allYear'    => process all year
     *                          'y-YYYY'     => process year YYYY
     *                          'ym-YYYYMM'  => process year/month YYYY/MM
     * @param Array  $filterPost : filter options
     * @param Array  $sort: sort options
     * @param Array  $group: grouped items; could be:
     *                        'uaType'
     * @param Integer $pageNumber : page number to return
     * @return String : an array in json format
     *                    (Array)
     *                      'rows' => (Array)
     *                                  'category' => (String)
     *                                  'uaType' => (String)
     *                                  'nbVisits' => (Integer)
     *                      'total' => (Array)
     *                                  'category' => (String) [empty]
     *                                  'uaType' => (String) [empty]
     *                                  'nbVisits' => (Integer)
     *                      'nbItems' => (Integer)
     */
    protected function ajax_estat_export_admin_statImage($period, $filterPost, $sort, $group, $format, $compression, $name)
    {
      global $lang;

      $year=null;
      $month=null;

      $fields=array(
        'rows' => array_diff(array('imageId', 'catId', 'uaType'), $group),
        'total' => array()
      );
      $filter=array();

      if(substr($period,0,3)=='ym-')
      {
        $year=substr($period,3,4)*1;
        $month=substr($period,7,2)*1;
      }
      elseif(substr($period,0,2)=='y-')
      {
        $year=substr($period,2,4)*1;
      }

      foreach($filterPost as $key => $val)
      {
        if($key==='additionalFilter')
        {
          if(isset($val['catId']))
            $filter['catId']=$this->buildCatIdFilter($val['catId']);
        }
        else
        {
          $filter[$val['id']]=$val;
        }
      }

      if($year!=null)
        $filter['year']=array('operator' => '=', 'value' => $year);
      if($month!=null)
        $filter['month']=array('operator' => '=', 'value' => $month);

      // open db file for the period
      $statFile=new StatDBGlobal($this->fileStatDir, self::FILE_GLOBAL);
      $statFile->open(ASDF_OPEN_READ);

      // get stats
      $result=$statFile->getStatImages(ASDF_GET_ROWS, 0, 0, $fields['rows'], $filter, $sort);
      $statFile->close();

      // prepare categories&images properties
      $idList=new EStat_IdList(array('catId', 'imageId'));
      $idAssoc=array(
        'catId'=>array(),
        'imageId'=>array()
      );

      // first, build category_id list
      foreach($result as $row)
      {
        $idList->addItems(array('catId' => $row['catId']));
        $idList->addItems(array('imageId' => $row['imageId']));
      }

      // get all needed properties for each category_id
      if(count($idList->getItems('catId')) > 0)
        $this->prepareIdList($idAssoc, 'catId', "SELECT id, name FROM ".CATEGORIES_TABLE." WHERE id IN (".implode(',', $idList->getItems('catId')).") ORDER BY id;");

      if(count($idList->getItems('imageId')) > 0)
        $this->prepareIdList($idAssoc, 'imageId', "SELECT id, file AS name, path FROM ".IMAGES_TABLE." WHERE id IN (".implode(',', $idList->getItems('imageId')).") ORDER BY id;");

      $data=array();
      foreach($result as $row)
      {
        if(isset($row['uaType']))
        {
          $userAgentNfo=GPCUserAgent::getProperties(
              array(
                UA_DATA_BROWSER_TYPE => $row['uaType']
              )
            );
          $uaType=l10n('ua_'.$userAgentNfo[UA_DATA_BROWSER_TYPE_NAME]);
        }
        else
        {
          $row['uaType']='';
          $uaType='';
        }

        if($row['catId']==0)
        {
          $catName=l10n('estat_special_category');
        }
        elseif($this->getId($idAssoc, 'catId', $row['catId'], '', 'name')=='')
        {
          $catName=l10n('estat_deleted_category').' ['.$row['catId'].']';
        }
        else
        {
          $catName=$this->getId($idAssoc, 'catId', $row['catId'], '', 'name');
        }

        $imageNfo=$this->getId($idAssoc, 'imageId', $row['imageId'], '', 'name');
        if($imageNfo!='')
        {
          $imageName=$imageNfo;
        }
        else
        {
          $imageName=l10n('estat_deleted_image');
        }


        $data[]=array(
          $row['catId'],
          $catName,
          $row['imageId'],
          $imageName,
          $row['uaType'],
          $uaType,
          $row['nbVisits']
        );
      }


      switch($format)
      {
        case 'csv':
          $extension='.csv';
          $export=new GPCCSVExport();
          $export->setOptions(
              array(
                'separator' => $this->config['export.csv.separator'],
                'decimalDot' => $this->config['export.csv.decimalDot'],
                'useQuotes' => ($this->config['export.csv.useQuotes']=='y')?true:false,
                'lineFeed' => $this->config['export.csv.lineFeed'],
              )
            );
          $export->setColumnsDef(
            array(
              array('name' => l10n('estat_categoryId')),
              array('name' => l10n('estat_category')),
              array('name' => l10n('estat_imageId')),
              array('name' => l10n('estat_image')),
              array('name' => l10n('estat_typeId'), 'type' => 'INTEGER'),
              array('name' => l10n('estat_type')),
              array('name' => l10n('estat_visits'), 'type' => 'INTEGER')
            )
          );
          break;
        case 'ods':
          $extension='.ods';
          $export=new GPCODSExport();
          $export->setOptions(
              array(
                'sheetName' => l10n('estat_image'),
                'fileTitle' => $this->config['export.ods.fileTitle'],
                'fileSubject' => sprintf(l10n('estat_fileODSSubject'), l10n('estat_by_image')),
                'keywords' => $this->config['export.ods.keywords'],
                'comments' => sprintf(l10n('estat_fileODSComments_1'), date('Y-m-d@H:i:s'), ESTAT_VERSION, PHPWG_VERSION)
              )
            );
          $export->setColumnsDef(
            array(
              array('name' => l10n('estat_categoryId')),
              array('name' => l10n('estat_category')),
              array('name' => l10n('estat_imageId')),
              array('name' => l10n('estat_image')),
              array('name' => l10n('estat_typeId'), 'type' => 'INTEGER'),
              array('name' => l10n('estat_type')),
              array('name' => l10n('estat_visits'), 'type' => 'INTEGER')
            )
          );
          break;
        case 'sqlite':
          $extension='.db';
          $export=new GPCSQLiteExport();
          $export->setOptions(
              array(
                'tableName' => 'image'
              )
            );
          $export->setColumnsDef(
            array(
              array('name' => 'albumId'),
              array('name' => 'album'),
              array('name' => 'imageId'),
              array('name' => 'image'),
              array('name' => 'typeId', 'type' => 'INTEGER'),
              array('name' => 'type'),
              array('name' => 'visits', 'type' => 'INTEGER')
            )
          );
          break;
      }
      $export->setFileName($name.$extension);
      $export->setFileDir($this->fileExportDir);

      if($export->write($data))
      {
        $file=$this->fileExportDir.$export->getFileName();

        if($compression!='none')
        {
          $compressedFile=$this->compress($file, $compression);
          unlink($file);
          $file=$compressedFile;
        }
        if(file_exists($file))
        {
          return($file);
        }
      }
      return('KO');
    }


    /**
     * used for file download
     * stop the script and returns the file content
     *
     * @param String $name: file to upload
     * @param Booelan $deleteFile: is true, given file is deleted after content is returned
     * @return String: the file content with http header
     */
    private function ajax_estat_export_admin_loadFile($name, $deleteFile=true)
    {
      $file=$this->fileExportDir.$name;
      if(!file_exists($file))
        do_error(404, "Requested file not readable - $name");

      $gmtMtime=gmdate('D, d M Y H:i:s', filemtime($file)).' GMT';

      $infos=pathinfo($file, PATHINFO_EXTENSION);

      switch($infos)
      {
        case 'csv':
          $mimeType='text/csv';
          break;
        case 'ods':
          $mimeType='application/vnd.oasis.opendocument.spreadsheet';
          break;
        case 'db';
          $mimeType='application/octet-stream';
          break;
        case 'gz':
          $mimeType='application/gzip';
          break;
        case 'bz2':
          $mimeType='application/zip';
          break;
        case 'zip':
          $mimeType='application/zip';
          break;
        default:
          $mimeType='application/octet-stream';
          break;
      }

      $httpHeaders=array(
          'Content-Length: '.@filesize($file),
          'Last-Modified: '.$gmtMtime,
          'Content-Type: '.$mimeType,
          'Content-Disposition: attachment; filename="'.basename($file).'";',
          'Content-Transfer-Encoding: binary',
        );

      foreach ($httpHeaders as $header)
      {
        header($header);
      }

      $content=@readfile($file);
      if($deleteFile) unlink($file);
      die($content);
    } // ajax_estat_export_admin_loadFile


    /* ------------------------------------------------------------------------------------------------
       -- private method
       ------------------------------------------------------------------------------------------------ */

    /**
     * compress the file, according to the asked method
     * the file will be compressed in the same directory than the source file
     *
     * @param String $file: file to compress
     * @return String: compressed file name, or empty string if something wrong
     */
    private function compress($file, $method)
    {
      if(!file_exists($file)) return('');
      $infos=pathinfo($file);

      switch($method)
      {
        case 'zip':
          $compressedFile=$infos['dirname'].'/'.basename($infos['basename'], '.'.$infos['extension']).'.zip';
          if(GPCCompress::zip($file, $compressedFile, '', $infos['dirname']))
            return($compressedFile);
          break;
        case 'gz':
          $compressedFile=$file.'.gz';
          if(GPCCompress::gzip($file, $compressedFile))
            return($compressedFile);
          break;
        case 'bz2':
          $compressedFile=$file.'.bz2';
          if(GPCCompress::bzip2($file, $compressedFile))
            return($compressedFile);
          break;
      }
      return('');
    } //compress

  } //class EStat_AjaxExport


  $returned=new EStat_AjaxExport($prefixeTable, __FILE__);
?>