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!='') { // packed file was unpacked, pack file only if file was already packed if($fmPeriod->pack()) $fmPeriod->delete(ASDF_DELETE_UNPACKED); } 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 ?>