diff options
author | Matthieu Aubry <matt@piwik.org> | 2014-12-06 04:10:51 +0300 |
---|---|---|
committer | Matthieu Aubry <matt@piwik.org> | 2014-12-06 04:10:51 +0300 |
commit | 0bb5558f8a97e1bace18e87741b37837d5954d47 (patch) | |
tree | f1a2c5f4ea5ece208456d0d7878b03fe01019ec3 | |
parent | 5c51746f627f2cffa5bb15a339f8083a34b5ba69 (diff) | |
parent | 8f079b21b1f635a6bb9bbbdfd8396001fc2bdfa3 (diff) |
Merge pull request #6817 from piwik/6786_refactor2.10.0-b4
Live API refactoring and hopefully Fixing the performance
23 files changed, 1386 insertions, 843 deletions
diff --git a/core/DataAccess/LogQueryBuilder.php b/core/DataAccess/LogQueryBuilder.php new file mode 100644 index 0000000000..a5b2fcf986 --- /dev/null +++ b/core/DataAccess/LogQueryBuilder.php @@ -0,0 +1,284 @@ +<?php +/** + * Piwik - free/libre analytics platform + * + * @link http://piwik.org + * @license http://www.gnu.org/licenses/gpl-3.0.html GPL v3 or later + * + */ + +namespace Piwik\DataAccess; + + +use Exception; +use Piwik\Common; +use Piwik\Segment\SegmentExpression; + +class LogQueryBuilder +{ + public function __construct(SegmentExpression $segmentExpression) + { + $this->segmentExpression = $segmentExpression; + } + + public function getSelectQueryString($select, $from, $where, $bind, $groupBy, $orderBy, $limit) + { + if (!is_array($from)) { + $from = array($from); + } + + if(!$this->segmentExpression->isEmpty()) { + $this->segmentExpression->parseSubExpressionsIntoSqlExpressions($from); + $segmentSql = $this->segmentExpression->getSql(); + $where = $this->getWhereMatchBoth($where, $segmentSql['where']); + $bind = array_merge($bind, $segmentSql['bind']); + } + + $joins = $this->generateJoinsString($from); + $joinWithSubSelect = $joins['joinWithSubSelect']; + $from = $joins['sql']; + + if ($joinWithSubSelect) { + $sql = $this->buildWrappedSelectQuery($select, $from, $where, $groupBy, $orderBy, $limit); + } else { + $sql = $this->buildSelectQuery($select, $from, $where, $groupBy, $orderBy, $limit); + } + return array( + 'sql' => $sql, + 'bind' => $bind + ); + } + + + /** + * Generate the join sql based on the needed tables + * @param array $tables tables to join + * @throws Exception if tables can't be joined + * @return array + */ + private function generateJoinsString($tables) + { + $knownTables = array("log_visit", "log_link_visit_action", "log_conversion", "log_conversion_item"); + $visitsAvailable = $actionsAvailable = $conversionsAvailable = $conversionItemAvailable = false; + $joinWithSubSelect = false; + $sql = ''; + + // make sure the tables are joined in the right order + // base table first, then action before conversion + // this way, conversions can be joined on idlink_va + $actionIndex = array_search("log_link_visit_action", $tables); + $conversionIndex = array_search("log_conversion", $tables); + if ($actionIndex > 0 && $conversionIndex > 0 && $actionIndex > $conversionIndex) { + $tables[$actionIndex] = "log_conversion"; + $tables[$conversionIndex] = "log_link_visit_action"; + } + + // same as above: action before visit + $actionIndex = array_search("log_link_visit_action", $tables); + $visitIndex = array_search("log_visit", $tables); + if ($actionIndex > 0 && $visitIndex > 0 && $actionIndex > $visitIndex) { + $tables[$actionIndex] = "log_visit"; + $tables[$visitIndex] = "log_link_visit_action"; + } + + foreach ($tables as $i => $table) { + if (is_array($table)) { + // join condition provided + $alias = isset($table['tableAlias']) ? $table['tableAlias'] : $table['table']; + $sql .= " + LEFT JOIN " . Common::prefixTable($table['table']) . " AS " . $alias + . " ON " . $table['joinOn']; + continue; + } + + if (!in_array($table, $knownTables)) { + throw new Exception("Table '$table' can't be used for segmentation"); + } + + $tableSql = Common::prefixTable($table) . " AS $table"; + + if ($i == 0) { + // first table + $sql .= $tableSql; + } else { + if ($actionsAvailable && $table == "log_conversion") { + // have actions, need conversions => join on idlink_va + $join = "log_conversion.idlink_va = log_link_visit_action.idlink_va " + . "AND log_conversion.idsite = log_link_visit_action.idsite"; + } else if ($actionsAvailable && $table == "log_visit") { + // have actions, need visits => join on idvisit + $join = "log_visit.idvisit = log_link_visit_action.idvisit"; + } else if ($visitsAvailable && $table == "log_link_visit_action") { + // have visits, need actions => we have to use a more complex join + // we don't hande this here, we just return joinWithSubSelect=true in this case + $joinWithSubSelect = true; + $join = "log_link_visit_action.idvisit = log_visit.idvisit"; + } else if ($conversionsAvailable && $table == "log_link_visit_action") { + // have conversions, need actions => join on idlink_va + $join = "log_conversion.idlink_va = log_link_visit_action.idlink_va"; + } else if (($visitsAvailable && $table == "log_conversion") + || ($conversionsAvailable && $table == "log_visit") + ) { + // have visits, need conversion (or vice versa) => join on idvisit + // notice that joining conversions on visits has lower priority than joining it on actions + $join = "log_conversion.idvisit = log_visit.idvisit"; + + // if conversions are joined on visits, we need a complex join + if ($table == "log_conversion") { + $joinWithSubSelect = true; + } + } elseif ($conversionItemAvailable && $table === 'log_visit') { + $join = "log_conversion_item.idvisit = log_visit.idvisit"; + } elseif ($conversionItemAvailable && $table === 'log_link_visit_action') { + $join = "log_conversion_item.idvisit = log_link_visit_action.idvisit"; + } elseif ($conversionItemAvailable && $table === 'log_conversion') { + $join = "log_conversion_item.idvisit = log_conversion.idvisit"; + } else { + throw new Exception("Table '$table' can't be joined for segmentation"); + } + + // the join sql the default way + $sql .= " + LEFT JOIN $tableSql ON $join"; + } + + // remember which tables are available + $visitsAvailable = ($visitsAvailable || $table == "log_visit"); + $actionsAvailable = ($actionsAvailable || $table == "log_link_visit_action"); + $conversionsAvailable = ($conversionsAvailable || $table == "log_conversion"); + $conversionItemAvailable = ($conversionItemAvailable || $table == "log_conversion_item"); + } + + $return = array( + 'sql' => $sql, + 'joinWithSubSelect' => $joinWithSubSelect + ); + return $return; + + } + + + /** + * Build a select query where actions have to be joined on visits (or conversions) + * In this case, the query gets wrapped in another query so that grouping by visit is possible + * @param string $select + * @param string $from + * @param string $where + * @param string $groupBy + * @param string $orderBy + * @param string $limit + * @throws Exception + * @return string + */ + private function buildWrappedSelectQuery($select, $from, $where, $groupBy, $orderBy, $limit) + { + $matchTables = "(log_visit|log_conversion_item|log_conversion|log_action)"; + preg_match_all("/". $matchTables ."\.[a-z0-9_\*]+/", $select, $matches); + $neededFields = array_unique($matches[0]); + + if (count($neededFields) == 0) { + throw new Exception("No needed fields found in select expression. " + . "Please use a table prefix."); + } + + $innerSelect = implode(", \n", $neededFields); + $innerFrom = $from; + $innerWhere = $where; + + $innerLimit = $limit; + $innerGroupBy = "log_visit.idvisit"; + $innerOrderBy = "NULL"; + if($innerLimit && $orderBy) { + // only When LIMITing we can apply to the inner query the same ORDER BY as the parent query + $innerOrderBy = $orderBy; + } + if($innerLimit) { + // When LIMITing, no need to GROUP BY (GROUPing by is done before the LIMIT which is super slow when large amount of rows is matched) + $innerGroupBy = false; + } + + $innerQuery = $this->buildSelectQuery($innerSelect, $innerFrom, $innerWhere, $innerGroupBy, $innerOrderBy, $innerLimit); + + $select = preg_replace('/'.$matchTables.'\./', 'log_inner.', $select); + $from = " + ( + $innerQuery + ) AS log_inner"; + $where = false; + $orderBy = preg_replace('/'.$matchTables.'\./', 'log_inner.', $orderBy); + $groupBy = preg_replace('/'.$matchTables.'\./', 'log_inner.', $groupBy); + $query = $this->buildSelectQuery($select, $from, $where, $groupBy, $orderBy, $limit); + return $query; + } + + + /** + * Build select query the normal way + * + * @param string $select fieldlist to be selected + * @param string $from tablelist to select from + * @param string $where where clause + * @param string $groupBy group by clause + * @param string $orderBy order by clause + * @param string $limit limit by clause + * @return string + */ + private function buildSelectQuery($select, $from, $where, $groupBy, $orderBy, $limit) + { + $sql = " + SELECT + $select + FROM + $from"; + + if ($where) { + $sql .= " + WHERE + $where"; + } + + if ($groupBy) { + $sql .= " + GROUP BY + $groupBy"; + } + + if ($orderBy) { + $sql .= " + ORDER BY + $orderBy"; + } + + $limit = (int)$limit; + if ($limit >= 1) { + $sql .= " + LIMIT + $limit"; + } + + return $sql; + } + + /** + * @param $where + * @param $segmentWhere + * @return string + * @throws + */ + protected function getWhereMatchBoth($where, $segmentWhere) + { + if (empty($segmentWhere) && empty($where)) { + throw new \Exception("Segment where clause should be non empty."); + } + if (empty($segmentWhere)) { + return $where; + } + if (empty($where)) { + return $segmentWhere; + } + return "( $where ) + AND + ($segmentWhere)"; + } + +}
\ No newline at end of file diff --git a/core/RankingQuery.php b/core/RankingQuery.php index f44845f075..cd4f830669 100644 --- a/core/RankingQuery.php +++ b/core/RankingQuery.php @@ -214,7 +214,7 @@ class RankingQuery */ public function execute($innerQuery, $bind = array()) { - $query = $this->generateQuery($innerQuery); + $query = $this->generateRankingQuery($innerQuery); $data = Db::fetchAll($query, $bind); if ($this->columnToMarkExcludedRows !== false) { @@ -268,7 +268,7 @@ class RankingQuery * itself. * @return string The entire ranking query SQL. */ - public function generateQuery($innerQuery) + public function generateRankingQuery($innerQuery) { // +1 to include "Others" $limit = $this->limit + 1; diff --git a/core/Segment.php b/core/Segment.php index 420946794d..7fe4c061fd 100644 --- a/core/Segment.php +++ b/core/Segment.php @@ -9,7 +9,9 @@ namespace Piwik; use Exception; +use Piwik\DataAccess\LogQueryBuilder; use Piwik\Plugins\API\API; +use Piwik\Segment\SegmentExpression; /** * Limits the set of visits Piwik uses when aggregating analytics data. @@ -57,7 +59,17 @@ class Segment /** * @var SegmentExpression */ - protected $segment = null; + protected $segmentExpression = null; + + /** + * @var string + */ + protected $string = null; + + /** + * @var array + */ + protected $idSites = null; /** * Truncate the Segments to 8k @@ -68,9 +80,8 @@ class Segment * Constructor. * * @param string $segmentCondition The segment condition, eg, `'browserCode=ff;countryCode=CA'`. - * @param array $idSites The list of sites the segment will be used with. Some segments are - * dependent on the site, such as goal segments. - * @throws Exception + * @param array $idSites The list of sites the st + * @throws */ public function __construct($segmentCondition, $idSites) { @@ -103,7 +114,7 @@ class Segment $this->string = $string; $this->idSites = $idSites; $segment = new SegmentExpression($string); - $this->segment = $segment; + $this->segmentExpression = $segment; // parse segments $expressions = $segment->parseSubExpressions(); @@ -127,7 +138,7 @@ class Segment */ public function isEmpty() { - return empty($this->string); + return $this->segmentExpression->isEmpty(); } protected $availableSegments = array(); @@ -222,236 +233,16 @@ class Segment * @param array|string $bind (optional) Bind parameters, eg, `array($col1Value, $col2Value)`. * @param false|string $orderBy (optional) Order by clause, eg, `"t1.col1 ASC"`. * @param false|string $groupBy (optional) Group by clause, eg, `"t2.col2"`. + * @param int $limit Limit by clause + * @param int If set to value >= 1 then the Select query (and All inner queries) will be LIMIT'ed by this value. + * Use only when you're not aggregating or it will sample the data. * @return string The entire select query. */ - public function getSelectQuery($select, $from, $where = false, $bind = array(), $orderBy = false, $groupBy = false) - { - if (!is_array($from)) { - $from = array($from); - } - - if (!$this->isEmpty()) { - $this->segment->parseSubExpressionsIntoSqlExpressions($from); - - $joins = $this->generateJoins($from); - $from = $joins['sql']; - $joinWithSubSelect = $joins['joinWithSubSelect']; - - $segmentSql = $this->segment->getSql(); - $segmentWhere = $segmentSql['where']; - if (!empty($segmentWhere)) { - if (!empty($where)) { - $where = "( $where ) - AND - ($segmentWhere)"; - } else { - $where = $segmentWhere; - } - } - - $bind = array_merge($bind, $segmentSql['bind']); - } else { - $joins = $this->generateJoins($from); - $from = $joins['sql']; - $joinWithSubSelect = $joins['joinWithSubSelect']; - } - - if ($joinWithSubSelect) { - $sql = $this->buildWrappedSelectQuery($select, $from, $where, $orderBy, $groupBy); - } else { - $sql = $this->buildSelectQuery($select, $from, $where, $orderBy, $groupBy); - } - return array( - 'sql' => $sql, - 'bind' => $bind - ); - } - - /** - * Generate the join sql based on the needed tables - * @param array $tables tables to join - * @throws Exception if tables can't be joined - * @return array - */ - private function generateJoins($tables) - { - $knownTables = array("log_visit", "log_link_visit_action", "log_conversion", "log_conversion_item"); - $visitsAvailable = $actionsAvailable = $conversionsAvailable = $conversionItemAvailable = false; - $joinWithSubSelect = false; - $sql = ''; - - // make sure the tables are joined in the right order - // base table first, then action before conversion - // this way, conversions can be joined on idlink_va - $actionIndex = array_search("log_link_visit_action", $tables); - $conversionIndex = array_search("log_conversion", $tables); - if ($actionIndex > 0 && $conversionIndex > 0 && $actionIndex > $conversionIndex) { - $tables[$actionIndex] = "log_conversion"; - $tables[$conversionIndex] = "log_link_visit_action"; - } - - // same as above: action before visit - $actionIndex = array_search("log_link_visit_action", $tables); - $visitIndex = array_search("log_visit", $tables); - if ($actionIndex > 0 && $visitIndex > 0 && $actionIndex > $visitIndex) { - $tables[$actionIndex] = "log_visit"; - $tables[$visitIndex] = "log_link_visit_action"; - } - - foreach ($tables as $i => $table) { - if (is_array($table)) { - // join condition provided - $alias = isset($table['tableAlias']) ? $table['tableAlias'] : $table['table']; - $sql .= " - LEFT JOIN " . Common::prefixTable($table['table']) . " AS " . $alias - . " ON " . $table['joinOn']; - continue; - } - - if (!in_array($table, $knownTables)) { - throw new Exception("Table '$table' can't be used for segmentation"); - } - - $tableSql = Common::prefixTable($table) . " AS $table"; - - if ($i == 0) { - // first table - $sql .= $tableSql; - } else { - if ($actionsAvailable && $table == "log_conversion") { - // have actions, need conversions => join on idlink_va - $join = "log_conversion.idlink_va = log_link_visit_action.idlink_va " - . "AND log_conversion.idsite = log_link_visit_action.idsite"; - } else if ($actionsAvailable && $table == "log_visit") { - // have actions, need visits => join on idvisit - $join = "log_visit.idvisit = log_link_visit_action.idvisit"; - } else if ($visitsAvailable && $table == "log_link_visit_action") { - // have visits, need actions => we have to use a more complex join - // we don't hande this here, we just return joinWithSubSelect=true in this case - $joinWithSubSelect = true; - $join = "log_link_visit_action.idvisit = log_visit.idvisit"; - } else if ($conversionsAvailable && $table == "log_link_visit_action") { - // have conversions, need actions => join on idlink_va - $join = "log_conversion.idlink_va = log_link_visit_action.idlink_va"; - } else if (($visitsAvailable && $table == "log_conversion") - || ($conversionsAvailable && $table == "log_visit") - ) { - // have visits, need conversion (or vice versa) => join on idvisit - // notice that joining conversions on visits has lower priority than joining it on actions - $join = "log_conversion.idvisit = log_visit.idvisit"; - - // if conversions are joined on visits, we need a complex join - if ($table == "log_conversion") { - $joinWithSubSelect = true; - } - } elseif ($conversionItemAvailable && $table === 'log_visit') { - $join = "log_conversion_item.idvisit = log_visit.idvisit"; - } elseif ($conversionItemAvailable && $table === 'log_link_visit_action') { - $join = "log_conversion_item.idvisit = log_link_visit_action.idvisit"; - } elseif ($conversionItemAvailable && $table === 'log_conversion') { - $join = "log_conversion_item.idvisit = log_conversion.idvisit"; - } else { - throw new Exception("Table '$table' can't be joined for segmentation"); - } - - // the join sql the default way - $sql .= " - LEFT JOIN $tableSql ON $join"; - } - - // remember which tables are available - $visitsAvailable = ($visitsAvailable || $table == "log_visit"); - $actionsAvailable = ($actionsAvailable || $table == "log_link_visit_action"); - $conversionsAvailable = ($conversionsAvailable || $table == "log_conversion"); - $conversionItemAvailable = ($conversionItemAvailable || $table == "log_conversion_item"); - } - - $return = array( - 'sql' => $sql, - 'joinWithSubSelect' => $joinWithSubSelect - ); - return $return; - - } - - /** - * Build select query the normal way - * @param string $select fieldlist to be selected - * @param string $from tablelist to select from - * @param string $where where clause - * @param string $orderBy order by clause - * @param string $groupBy group by clause - * @return string - */ - private function buildSelectQuery($select, $from, $where, $orderBy, $groupBy) - { - $sql = " - SELECT - $select - FROM - $from"; - - if ($where) { - $sql .= " - WHERE - $where"; - } - - if ($groupBy) { - $sql .= " - GROUP BY - $groupBy"; - } - - if ($orderBy) { - $sql .= " - ORDER BY - $orderBy"; - } - - return $sql; - } - - /** - * Build a select query where actions have to be joined on visits (or conversions) - * In this case, the query gets wrapped in another query so that grouping by visit is possible - * @param string $select - * @param string $from - * @param string $where - * @param string $orderBy - * @param string $groupBy - * @throws Exception - * @return string - */ - private function buildWrappedSelectQuery($select, $from, $where, $orderBy, $groupBy) + public function getSelectQuery($select, $from, $where = false, $bind = array(), $orderBy = false, $groupBy = false, $limit = 0) { - $matchTables = "(log_visit|log_conversion_item|log_conversion|log_action)"; - preg_match_all("/". $matchTables ."\.[a-z0-9_\*]+/", $select, $matches); - $neededFields = array_unique($matches[0]); - - if (count($neededFields) == 0) { - throw new Exception("No needed fields found in select expression. " - . "Please use a table prefix."); - } - - $select = preg_replace('/'.$matchTables.'\./', 'log_inner.', $select); - $orderBy = preg_replace('/'.$matchTables.'\./', 'log_inner.', $orderBy); - $groupBy = preg_replace('/'.$matchTables.'\./', 'log_inner.', $groupBy); - - $from = "( - SELECT - " . implode(", - ", $neededFields) . " - FROM - $from - WHERE - $where - GROUP BY log_visit.idvisit - ) AS log_inner"; - - $where = false; - $query = $this->buildSelectQuery($select, $from, $where, $orderBy, $groupBy); - return $query; + $segmentExpression = $this->segmentExpression; + $segmentQuery = new LogQueryBuilder($segmentExpression); + return $segmentQuery->getSelectQueryString($select, $from, $where, $bind, $groupBy, $orderBy, $limit); } /** @@ -463,4 +254,5 @@ class Segment { return (string) $this->getString(); } + }
\ No newline at end of file diff --git a/core/SegmentExpression.php b/core/Segment/SegmentExpression.php index cbd2a3eb9e..10cf0294e3 100644 --- a/core/SegmentExpression.php +++ b/core/Segment/SegmentExpression.php @@ -7,7 +7,7 @@ * */ -namespace Piwik; +namespace Piwik\Segment; use Exception; @@ -46,6 +46,11 @@ class SegmentExpression $this->tree = $this->parseTree(); } + public function isEmpty() + { + return count($this->tree) == 0; + } + protected $joins = array(); protected $valuesBind = array(); protected $parsedTree = array(); @@ -337,7 +342,7 @@ class SegmentExpression */ public function getSql() { - if (count($this->tree) == 0) { + if ($this->isEmpty()) { throw new Exception("Invalid segment, please specify a valid segment."); } $sql = ''; diff --git a/core/Tracker/TableLogAction.php b/core/Tracker/TableLogAction.php index 709936f2a5..fe620035d7 100644 --- a/core/Tracker/TableLogAction.php +++ b/core/Tracker/TableLogAction.php @@ -10,7 +10,7 @@ namespace Piwik\Tracker; use Piwik\Common; -use Piwik\SegmentExpression; +use Piwik\Segment\SegmentExpression; use Piwik\Tracker; /** diff --git a/plugins/API/API.php b/plugins/API/API.php index f9f60dd261..f7aa20d08b 100644 --- a/plugins/API/API.php +++ b/plugins/API/API.php @@ -23,7 +23,7 @@ use Piwik\Period\Range; use Piwik\Piwik; use Piwik\Plugin\Dimension\VisitDimension; use Piwik\Plugins\CoreAdminHome\CustomLogo; -use Piwik\SegmentExpression; +use Piwik\Segment\SegmentExpression; use Piwik\Translate; use Piwik\Version; diff --git a/plugins/Actions/Archiver.php b/plugins/Actions/Archiver.php index 67408ced64..199438445e 100644 --- a/plugins/Actions/Archiver.php +++ b/plugins/Actions/Archiver.php @@ -244,9 +244,9 @@ class Archiver extends \Piwik\Plugin\Archiver $this->updateQuerySelectFromForSiteSearch($select, $from); } - $this->archiveDayQueryProcess($select, $from, $where, $orderBy, $groupBy, "idaction_name", $rankingQuery); + $this->archiveDayQueryProcess($select, $from, $where, $groupBy, $orderBy, "idaction_name", $rankingQuery); - $this->archiveDayQueryProcess($select, $from, $where, $orderBy, $groupBy, "idaction_url", $rankingQuery); + $this->archiveDayQueryProcess($select, $from, $where, $groupBy, $orderBy, "idaction_url", $rankingQuery); } protected function isSiteSearchEnabled() @@ -254,7 +254,7 @@ class Archiver extends \Piwik\Plugin\Archiver return $this->isSiteSearchEnabled; } - protected function archiveDayQueryProcess($select, $from, $where, $orderBy, $groupBy, $sprintfField, $rankingQuery = false) + protected function archiveDayQueryProcess($select, $from, $where, $groupBy, $orderBy, $sprintfField, RankingQuery $rankingQuery = null) { $select = sprintf($select, $sprintfField); @@ -266,7 +266,7 @@ class Archiver extends \Piwik\Plugin\Archiver // apply ranking query if ($rankingQuery) { - $querySql = $rankingQuery->generateQuery($querySql); + $querySql = $rankingQuery->generateRankingQuery($querySql); } // get result @@ -321,9 +321,9 @@ class Archiver extends \Piwik\Plugin\Archiver $groupBy = "log_visit.%s, idaction"; - $this->archiveDayQueryProcess($select, $from, $where, $orderBy, $groupBy, "visit_entry_idaction_url", $rankingQuery); + $this->archiveDayQueryProcess($select, $from, $where, $groupBy, $orderBy, "visit_entry_idaction_url", $rankingQuery); - $this->archiveDayQueryProcess($select, $from, $where, $orderBy, $groupBy, "visit_entry_idaction_name", $rankingQuery); + $this->archiveDayQueryProcess($select, $from, $where, $groupBy, $orderBy, "visit_entry_idaction_name", $rankingQuery); } /** @@ -366,9 +366,9 @@ class Archiver extends \Piwik\Plugin\Archiver $groupBy = "log_visit.%s, idaction"; - $this->archiveDayQueryProcess($select, $from, $where, $orderBy, $groupBy, "visit_exit_idaction_url", $rankingQuery); + $this->archiveDayQueryProcess($select, $from, $where, $groupBy, $orderBy, "visit_exit_idaction_url", $rankingQuery); - $this->archiveDayQueryProcess($select, $from, $where, $orderBy, $groupBy, "visit_exit_idaction_name", $rankingQuery); + $this->archiveDayQueryProcess($select, $from, $where, $groupBy, $orderBy, "visit_exit_idaction_name", $rankingQuery); return array($rankingQuery, $extraSelects, $from, $orderBy, $select, $where, $groupBy); } @@ -412,9 +412,9 @@ class Archiver extends \Piwik\Plugin\Archiver $groupBy = "log_link_visit_action.%s, idaction"; - $this->archiveDayQueryProcess($select, $from, $where, $orderBy, $groupBy, "idaction_url_ref", $rankingQuery); + $this->archiveDayQueryProcess($select, $from, $where, $groupBy, $orderBy, "idaction_url_ref", $rankingQuery); - $this->archiveDayQueryProcess($select, $from, $where, $orderBy, $groupBy, "idaction_name_ref", $rankingQuery); + $this->archiveDayQueryProcess($select, $from, $where, $groupBy, $orderBy, "idaction_name_ref", $rankingQuery); } /** diff --git a/plugins/Contents/Archiver.php b/plugins/Contents/Archiver.php index 55668b4c3a..6d3a2e05ce 100644 --- a/plugins/Contents/Archiver.php +++ b/plugins/Contents/Archiver.php @@ -117,7 +117,7 @@ class Archiver extends \Piwik\Plugin\Archiver $rankingQuery->addColumn(array(Metrics::INDEX_CONTENT_NB_IMPRESSIONS, Metrics::INDEX_NB_VISITS), 'sum'); } - $resultSet = $this->archiveDayQueryProcess($select, $from, $where, $orderBy, $groupBy, $rankingQuery); + $resultSet = $this->archiveDayQueryProcess($select, $from, $where, $groupBy, $orderBy, $rankingQuery); while ($row = $resultSet->fetch()) { $this->aggregateImpressionRow($row); @@ -174,21 +174,21 @@ class Archiver extends \Piwik\Plugin\Archiver $rankingQuery->addColumn(array(Metrics::INDEX_CONTENT_NB_INTERACTIONS), 'sum'); } - $resultSet = $this->archiveDayQueryProcess($select, $from, $where, $orderBy, $groupBy, $rankingQuery); + $resultSet = $this->archiveDayQueryProcess($select, $from, $where, $groupBy, $orderBy, $rankingQuery); while ($row = $resultSet->fetch()) { $this->aggregateInteractionRow($row); } } - private function archiveDayQueryProcess($select, $from, $where, $orderBy, $groupBy, RankingQuery $rankingQuery) + private function archiveDayQueryProcess($select, $from, $where, $groupBy, $orderBy, RankingQuery $rankingQuery) { // get query with segmentation $query = $this->getLogAggregator()->generateQuery($select, $from, $where, $groupBy, $orderBy); // apply ranking query if ($rankingQuery) { - $query['sql'] = $rankingQuery->generateQuery($query['sql']); + $query['sql'] = $rankingQuery->generateRankingQuery($query['sql']); } // get result diff --git a/plugins/Events/Archiver.php b/plugins/Events/Archiver.php index eb33d4899f..5a5160bdb2 100644 --- a/plugins/Events/Archiver.php +++ b/plugins/Events/Archiver.php @@ -179,17 +179,17 @@ class Archiver extends \Piwik\Plugin\Archiver $rankingQuery->addColumn(Metrics::INDEX_EVENT_MAX_EVENT_VALUE, 'max'); } - $this->archiveDayQueryProcess($select, $from, $where, $orderBy, $groupBy, $rankingQuery); + $this->archiveDayQueryProcess($select, $from, $where, $groupBy, $orderBy, $rankingQuery); } - protected function archiveDayQueryProcess($select, $from, $where, $orderBy, $groupBy, RankingQuery $rankingQuery) + protected function archiveDayQueryProcess($select, $from, $where, $groupBy, $orderBy, RankingQuery $rankingQuery) { // get query with segmentation $query = $this->getLogAggregator()->generateQuery($select, $from, $where, $groupBy, $orderBy); // apply ranking query if ($rankingQuery) { - $query['sql'] = $rankingQuery->generateQuery($query['sql']); + $query['sql'] = $rankingQuery->generateRankingQuery($query['sql']); } // get result diff --git a/plugins/Live/API.php b/plugins/Live/API.php index 7a36822534..ed11c6c277 100644 --- a/plugins/Live/API.php +++ b/plugins/Live/API.php @@ -16,10 +16,8 @@ use Piwik\DataTable\Row; use Piwik\Date; use Piwik\Db; use Piwik\Metrics\Formatter; -use Piwik\Period\Range; use Piwik\Period; use Piwik\Piwik; -use Piwik\Plugins\Referrers\API as APIReferrers; use Piwik\Plugins\SitesManager\API as APISitesManager; use Piwik\Segment; use Piwik\Site; @@ -55,8 +53,6 @@ require_once PIWIK_INCLUDE_PATH . '/plugins/UserCountry/functions.php'; class API extends \Piwik\Plugin\API { const VISITOR_PROFILE_MAX_VISITS_TO_AGGREGATE = 100; - const VISITOR_PROFILE_MAX_VISITS_TO_SHOW = 10; - const VISITOR_PROFILE_DATE_FORMAT = '%day% %shortMonth% %longYear%'; /** * This will return simple counters, for a given website ID, for visits over the last N minutes @@ -69,49 +65,8 @@ class API extends \Piwik\Plugin\API public function getCounters($idSite, $lastMinutes, $segment = false) { Piwik::checkUserHasViewAccess($idSite); - $lastMinutes = (int) $lastMinutes; - - $counters = array( - 'visits' => 0, - 'actions' => 0, - 'visitors' => 0, - 'visitsConverted' => 0, - ); - - if (empty($lastMinutes)) { - return array($counters); - } - - list($whereIdSites, $idSites) = $this->getIdSitesWhereClause($idSite); - - $select = "count(*) as visits, COUNT(DISTINCT log_visit.idvisitor) as visitors"; - $where = $whereIdSites . "AND log_visit.visit_last_action_time >= ?"; - $bind = $idSites; - $bind[] = Date::factory(time() - $lastMinutes * 60)->toString('Y-m-d H:i:s'); - - $segment = new Segment($segment, $idSite); - $query = $segment->getSelectQuery($select, 'log_visit', $where, $bind); - - $data = Db::fetchAll($query['sql'], $query['bind']); - - $counters['visits'] = $data[0]['visits']; - $counters['visitors'] = $data[0]['visitors']; - - $select = "count(*)"; - $from = 'log_link_visit_action'; - list($whereIdSites) = $this->getIdSitesWhereClause($idSite, $from); - $where = $whereIdSites . "AND log_link_visit_action.server_time >= ?"; - $query = $segment->getSelectQuery($select, $from, $where, $bind); - $counters['actions'] = Db::fetchOne($query['sql'], $query['bind']); - - $select = "count(*)"; - $from = 'log_conversion'; - list($whereIdSites) = $this->getIdSitesWhereClause($idSite, $from); - $where = $whereIdSites . "AND log_conversion.server_time >= ?"; - $query = $segment->getSelectQuery($select, $from, $where, $bind); - $counters['visitsConverted'] = Db::fetchOne($query['sql'], $query['bind']); - - return array($counters); + $model = new Model(); + return $model->queryCounters($idSite, $lastMinutes, $segment); } /** @@ -183,11 +138,9 @@ class API extends \Piwik\Plugin\API * @param int $idSite Site ID * @param bool|false|string $visitorId The ID of the visitor whose profile to retrieve. * @param bool|false|string $segment - * @param bool $checkForLatLong If true, hasLatLong will appear in the output and be true if - * one of the first 100 visits has a latitude/longitude. * @return array */ - public function getVisitorProfile($idSite, $visitorId = false, $segment = false, $checkForLatLong = false) + public function getVisitorProfile($idSite, $visitorId = false, $segment = false) { Piwik::checkUserHasViewAccess($idSite); @@ -207,190 +160,8 @@ class API extends \Piwik\Plugin\API return array(); } - $isEcommerceEnabled = Site::isEcommerceEnabledFor($idSite); - - $result = array(); - $result['totalVisits'] = 0; - $result['totalVisitDuration'] = 0; - $result['totalActions'] = 0; - $result['totalSearches'] = 0; - $result['totalPageViews'] = 0; - $result['totalGoalConversions'] = 0; - $result['totalConversionsByGoal'] = array(); - - if ($isEcommerceEnabled) { - $result['totalEcommerceConversions'] = 0; - $result['totalEcommerceRevenue'] = 0; - $result['totalEcommerceItems'] = 0; - $result['totalAbandonedCarts'] = 0; - $result['totalAbandonedCartsRevenue'] = 0; - $result['totalAbandonedCartsItems'] = 0; - } - - $countries = array(); - $continents = array(); - $cities = array(); - $siteSearchKeywords = array(); - - $pageGenerationTimeTotal = 0; - - // aggregate all requested visits info for total_* info - foreach ($visits->getRows() as $visit) { - ++$result['totalVisits']; - - $result['totalVisitDuration'] += $visit->getColumn('visitDuration'); - $result['totalActions'] += $visit->getColumn('actions'); - $result['totalGoalConversions'] += $visit->getColumn('goalConversions'); - - // individual goal conversions are stored in action details - foreach ($visit->getColumn('actionDetails') as $action) { - if ($action['type'] == 'goal') { - // handle goal conversion - $idGoal = $action['goalId']; - $idGoalKey = 'idgoal=' . $idGoal; - - if (!isset($result['totalConversionsByGoal'][$idGoalKey])) { - $result['totalConversionsByGoal'][$idGoalKey] = 0; - } - ++$result['totalConversionsByGoal'][$idGoalKey]; - - if (!empty($action['revenue'])) { - if (!isset($result['totalRevenueByGoal'][$idGoalKey])) { - $result['totalRevenueByGoal'][$idGoalKey] = 0; - } - $result['totalRevenueByGoal'][$idGoalKey] += $action['revenue']; - } - } else if ($action['type'] == Piwik::LABEL_ID_GOAL_IS_ECOMMERCE_ORDER // handle ecommerce order - && $isEcommerceEnabled - ) { - ++$result['totalEcommerceConversions']; - $result['totalEcommerceRevenue'] += $action['revenue']; - $result['totalEcommerceItems'] += $action['items']; - } else if ($action['type'] == Piwik::LABEL_ID_GOAL_IS_ECOMMERCE_CART // handler abandoned cart - && $isEcommerceEnabled - ) { - ++$result['totalAbandonedCarts']; - $result['totalAbandonedCartsRevenue'] += $action['revenue']; - $result['totalAbandonedCartsItems'] += $action['items']; - } - - if (isset($action['siteSearchKeyword'])) { - $keyword = $action['siteSearchKeyword']; - - if (!isset($siteSearchKeywords[$keyword])) { - $siteSearchKeywords[$keyword] = 0; - ++$result['totalSearches']; - } - ++$siteSearchKeywords[$keyword]; - } - - if (isset($action['generationTime'])) { - $pageGenerationTimeTotal += $action['generationTime']; - ++$result['totalPageViews']; - } - } - - $countryCode = $visit->getColumn('countryCode'); - if (!isset($countries[$countryCode])) { - $countries[$countryCode] = 0; - } - ++$countries[$countryCode]; - - $continentCode = $visit->getColumn('continentCode'); - if (!isset($continents[$continentCode])) { - $continents[$continentCode] = 0; - } - ++$continents[$continentCode]; - - if ($countryCode && !array_key_exists($countryCode, $cities)) { - $cities[$countryCode] = array(); - } - $city = $visit->getColumn('city'); - if (!empty($city)) { - $cities[$countryCode][] = $city; - } - } - - // sort countries/continents/search keywords by visit/action - asort($countries); - asort($continents); - arsort($siteSearchKeywords); - - // transform country/continents/search keywords into something that will look good in XML - $result['countries'] = $result['continents'] = $result['searches'] = array(); - - foreach ($countries as $countryCode => $nbVisits) { - - $countryInfo = array('country' => $countryCode, - 'nb_visits' => $nbVisits, - 'flag' => \Piwik\Plugins\UserCountry\getFlagFromCode($countryCode), - 'prettyName' => \Piwik\Plugins\UserCountry\countryTranslate($countryCode)); - if (!empty($cities[$countryCode])) { - $countryInfo['cities'] = array_unique($cities[$countryCode]); - } - $result['countries'][] = $countryInfo; - } - foreach ($continents as $continentCode => $nbVisits) { - $result['continents'][] = array('continent' => $continentCode, - 'nb_visits' => $nbVisits, - 'prettyName' => \Piwik\Plugins\UserCountry\continentTranslate($continentCode)); - } - foreach ($siteSearchKeywords as $keyword => $searchCount) { - $result['searches'][] = array('keyword' => $keyword, - 'searches' => $searchCount); - } - - if ($result['totalPageViews']) { - $result['averagePageGenerationTime'] = - round($pageGenerationTimeTotal / $result['totalPageViews'], $precision = 2); - } - - $formatter = new Formatter(); - $result['totalVisitDurationPretty'] = $formatter->getPrettyTimeFromSeconds($result['totalVisitDuration'], true); - - // use requested visits for first/last visit info - $rows = $visits->getRows(); - $result['firstVisit'] = $this->getVisitorProfileVisitSummary(end($rows)); - $result['lastVisit'] = $this->getVisitorProfileVisitSummary(reset($rows)); - - // check if requested visits have lat/long - if ($checkForLatLong) { - $result['hasLatLong'] = false; - foreach ($rows as $visit) { - if ($visit->getColumn('latitude') !== false) { // realtime map only checks for latitude - $result['hasLatLong'] = true; - break; - } - } - } - - // save count of visits we queries - $result['visitsAggregated'] = count($rows); - - // use N most recent visits for last_visits - $visits->deleteRowsOffset(self::VISITOR_PROFILE_MAX_VISITS_TO_SHOW); - $result['lastVisits'] = $visits; - - // use the right date format for the pretty server date - $timezone = Site::getTimezoneFor($idSite); - foreach ($result['lastVisits']->getRows() as $visit) { - $dateTimeVisitFirstAction = Date::factory($visit->getColumn('firstActionTimestamp'), $timezone); - - $datePretty = $dateTimeVisitFirstAction->getLocalized(self::VISITOR_PROFILE_DATE_FORMAT); - $visit->setColumn('serverDatePrettyFirstAction', $datePretty); - - $dateTimePretty = $datePretty . ' ' . $visit->getColumn('serverTimePrettyFirstAction'); - $visit->setColumn('serverDateTimePrettyFirstAction', $dateTimePretty); - } - - $result['userId'] = $visit->getColumn('userId'); - - // get visitor IDs that are adjacent to this one in log_visit - // TODO: make sure order of visitor ids is not changed if a returning visitor visits while the user is - // looking at the popup. - $latestVisitTime = reset($rows)->getColumn('lastActionDateTime'); - $result['nextVisitorId'] = $this->getAdjacentVisitorId($idSite, $visitorId, $latestVisitTime, $segment, $getNext = true); - $result['previousVisitorId'] = $this->getAdjacentVisitorId($idSite, $visitorId, $latestVisitTime, $segment, $getNext = false); + $profile = new VisitorProfile($idSite); + $result = $profile->makeVisitorProfile($visits, $visitorId, $segment); /** * Triggered in the Live.getVisitorProfile API method. Plugins can use this event @@ -439,107 +210,6 @@ class API extends \Piwik\Plugin\API return $visitor->getVisitorId(); } - /** - * Returns the ID of a visitor that is adjacent to another visitor (by time of last action) - * in the log_visit table. - * - * @param int $idSite The ID of the site whose visits should be looked at. - * @param string $visitorId The ID of the visitor to get an adjacent visitor for. - * @param string $visitLastActionTime The last action time of the latest visit for $visitorId. - * @param string $segment - * @param bool $getNext Whether to retrieve the next visitor or the previous visitor. The next - * visitor will be the visitor that appears chronologically later in the - * log_visit table. The previous visitor will be the visitor that appears - * earlier. - * @return string The hex visitor ID. - */ - private function getAdjacentVisitorId($idSite, $visitorId, $visitLastActionTime, $segment, $getNext) - { - if ($getNext) { - $visitLastActionTimeCondition = "sub.visit_last_action_time <= ?"; - $orderByDir = "DESC"; - } else { - $visitLastActionTimeCondition = "sub.visit_last_action_time >= ?"; - $orderByDir = "ASC"; - } - - $visitLastActionDate = Date::factory($visitLastActionTime); - $dateOneDayAgo = $visitLastActionDate->subDay(1); - $dateOneDayInFuture = $visitLastActionDate->addDay(1); - - $select = "log_visit.idvisitor, MAX(log_visit.visit_last_action_time) as visit_last_action_time"; - $from = "log_visit"; - $where = "log_visit.idsite = ? AND log_visit.idvisitor <> ? AND visit_last_action_time >= ? and visit_last_action_time <= ?"; - $whereBind = array($idSite, @Common::hex2bin($visitorId), $dateOneDayAgo->toString('Y-m-d H:i:s'), $dateOneDayInFuture->toString('Y-m-d H:i:s')); - $orderBy = "MAX(log_visit.visit_last_action_time) $orderByDir"; - $groupBy = "log_visit.idvisitor"; - - $segment = new Segment($segment, $idSite); - $queryInfo = $segment->getSelectQuery($select, $from, $where, $whereBind, $orderBy, $groupBy); - - $sql = "SELECT sub.idvisitor, sub.visit_last_action_time FROM ({$queryInfo['sql']}) as sub - WHERE $visitLastActionTimeCondition - LIMIT 1"; - $bind = array_merge($queryInfo['bind'], array($visitLastActionTime)); - - $visitorId = Db::fetchOne($sql, $bind); - if (!empty($visitorId)) { - $visitorId = bin2hex($visitorId); - } - return $visitorId; - } - - /** - * Returns a summary for an important visit. Used to describe the first & last visits of a visitor. - * - * @param Row $visit - * @return array - */ - private function getVisitorProfileVisitSummary($visit) - { - $today = Date::today(); - - $serverDate = $visit->getColumn('firstActionTimestamp'); - return array( - 'date' => $serverDate, - 'prettyDate' => Date::factory($serverDate)->getLocalized(self::VISITOR_PROFILE_DATE_FORMAT), - 'daysAgo' => (int)Date::secondsToDays($today->getTimestamp() - Date::factory($serverDate)->getTimestamp()), - 'referrerType' => $visit->getColumn('referrerType'), - 'referralSummary' => self::getReferrerSummaryForVisit($visit), - ); - } - - /** - * Returns a summary for a visit's referral. - * - * @param Row $visit - * @return bool|mixed|string - * @ignore - */ - public static function getReferrerSummaryForVisit($visit) - { - $referrerType = $visit->getColumn('referrerType'); - if ($referrerType === false - || $referrerType == 'direct' - ) { - $result = Piwik::translate('Referrers_DirectEntry'); - } else if ($referrerType == 'search') { - $result = $visit->getColumn('referrerName'); - - $keyword = $visit->getColumn('referrerKeyword'); - if ($keyword !== false - && $keyword != APIReferrers::getKeywordNotDefinedString() - ) { - $result .= ' (' . $keyword . ')'; - } - } else if ($referrerType == 'campaign') { - $result = Piwik::translate('Referrers_ColumnCampaign') . ' (' . $visit->getColumn('referrerName') . ')'; - } else { - $result = $visit->getColumn('referrerName'); - } - - return $result; - } /** * @deprecated @@ -614,112 +284,20 @@ class API extends \Piwik\Plugin\API private function loadLastVisitorDetailsFromDatabase($idSite, $period, $date, $segment = false, $countVisitorsToFetch = 100, $visitorId = false, $minTimestamp = false, $filterSortOrder = false) { - $where = $whereBind = array(); - - list($whereClause, $idSites) = $this->getIdSitesWhereClause($idSite); - - $where[] = $whereClause; - $whereBind = $idSites; - - if (strtolower($filterSortOrder) !== 'asc') { - $filterSortOrder = 'DESC'; - } - - $orderBy = "idsite, visit_last_action_time " . $filterSortOrder; - $orderByParent = "sub.visit_last_action_time " . $filterSortOrder; - - if (!empty($visitorId)) { - $where[] = "log_visit.idvisitor = ? "; - $whereBind[] = @Common::hex2bin($visitorId); - } - - if (!empty($minTimestamp)) { - $where[] = "log_visit.visit_last_action_time > ? "; - $whereBind[] = date("Y-m-d H:i:s", $minTimestamp); - } - - // If no other filter, only look at the last 24 hours of stats - if (empty($visitorId) - && empty($countVisitorsToFetch) - && empty($period) - && empty($date) - ) { - $period = 'day'; - $date = 'yesterdaySameTime'; - } - - // SQL Filter with provided period - if (!empty($period) && !empty($date)) { - $currentSite = new Site($idSite); - $currentTimezone = $currentSite->getTimezone(); - - $dateString = $date; - if ($period == 'range') { - $processedPeriod = new Range('range', $date); - if ($parsedDate = Range::parseDateRange($date)) { - $dateString = $parsedDate[2]; - } - } else { - $processedDate = Date::factory($date); - if ($date == 'today' - || $date == 'now' - || $processedDate->toString() == Date::factory('now', $currentTimezone)->toString() - ) { - $processedDate = $processedDate->subDay(1); - } - $processedPeriod = Period\Factory::build($period, $processedDate); - } - $dateStart = $processedPeriod->getDateStart()->setTimezone($currentTimezone); - $where[] = "log_visit.visit_last_action_time >= ?"; - $whereBind[] = $dateStart->toString('Y-m-d H:i:s'); - - if (!in_array($date, array('now', 'today', 'yesterdaySameTime')) - && strpos($date, 'last') === false - && strpos($date, 'previous') === false - && Date::factory($dateString)->toString('Y-m-d') != Date::factory('now', $currentTimezone)->toString() - ) { - $dateEnd = $processedPeriod->getDateEnd()->setTimezone($currentTimezone); - $where[] = " log_visit.visit_last_action_time <= ?"; - $dateEndString = $dateEnd->addDay(1)->toString('Y-m-d H:i:s'); - $whereBind[] = $dateEndString; - } - } - - if (count($where) > 0) { - $where = join(" - AND ", $where); - } else { - $where = false; - } - - $segment = new Segment($segment, $idSite); - - // Subquery to use the indexes for ORDER BY - $select = "log_visit.*"; - $from = "log_visit"; - $subQuery = $segment->getSelectQuery($select, $from, $where, $whereBind, $orderBy); - - $sqlLimit = $countVisitorsToFetch >= 1 ? " LIMIT 0, " . (int)$countVisitorsToFetch : ""; - - // Group by idvisit so that a visitor converting 2 goals only appears once - $sql = " - SELECT sub.* FROM ( - " . $subQuery['sql'] . " - $sqlLimit - ) AS sub - GROUP BY sub.idvisit - ORDER BY $orderByParent - "; - try { - $data = Db::fetchAll($sql, $subQuery['bind']); - } catch (Exception $e) { - echo $e->getMessage(); - exit; - } + $model = new Model(); + $data = $model->queryLogVisits($idSite, $period, $date, $segment, $countVisitorsToFetch, $visitorId, $minTimestamp, $filterSortOrder); + return $this->makeVisitorTableFromArray($data); + } + /** + * @param $data + * @return DataTable + * @throws Exception + */ + private function makeVisitorTableFromArray($data) + { $dataTable = new DataTable(); $dataTable->addRowsFromSimpleArray($data); - // $dataTable->disableFilter('Truncate'); if (!empty($data[0])) { $columnsToNotAggregate = array_map(function () { @@ -732,18 +310,5 @@ class API extends \Piwik\Plugin\API return $dataTable; } - /** - * @param $idSite - * @param string $table - * @return array - */ - private function getIdSitesWhereClause($idSite, $table = 'log_visit') - { - $idSites = array($idSite); - Piwik::postEvent('Live.API.getIdSitesString', array(&$idSites)); - $idSitesBind = Common::getSqlStringFieldsArray($idSites); - $whereClause = $table . ".idsite in ($idSitesBind) "; - return array($whereClause, $idSites); - } } diff --git a/plugins/Live/Controller.php b/plugins/Live/Controller.php index 9df3e26ff8..82dccb92bc 100644 --- a/plugins/Live/Controller.php +++ b/plugins/Live/Controller.php @@ -109,7 +109,7 @@ class Controller extends \Piwik\Plugin\Controller $view = new View('@Live/getVisitorProfilePopup.twig'); $view->idSite = $idSite; $view->goals = APIGoals::getInstance()->getGoals($idSite); - $view->visitorData = Request::processRequest('Live.getVisitorProfile', array('checkForLatLong' => true)); + $view->visitorData = Request::processRequest('Live.getVisitorProfile'); $view->exportLink = $this->getVisitorProfileExportLink(); if (Common::getRequestVar('showMap', 1) == 1 @@ -133,7 +133,7 @@ class Controller extends \Piwik\Plugin\Controller 'date' => false )); $view->visitData = $visits->getFirstRow()->getColumns(); - $view->visitReferralSummary = API::getReferrerSummaryForVisit($visits->getFirstRow()); + $view->visitReferralSummary = VisitorProfile::getReferrerSummaryForVisit($visits->getFirstRow()); $view->showLocation = true; $this->setWidgetizedVisitorProfileUrl($view); $view->exportLink = $this->getVisitorProfileExportLink(); diff --git a/plugins/Live/Model.php b/plugins/Live/Model.php new file mode 100644 index 0000000000..81215f864c --- /dev/null +++ b/plugins/Live/Model.php @@ -0,0 +1,454 @@ +<?php +/** + * Piwik - free/libre analytics platform + * + * @link http://piwik.org + * @license http://www.gnu.org/licenses/gpl-3.0.html GPL v3 or later + * + */ + +namespace Piwik\Plugins\Live; + +use Exception; +use Piwik\Common; +use Piwik\DataAccess\LogAggregator; +use Piwik\Date; +use Piwik\Db; +use Piwik\Period; +use Piwik\Period\Range; +use Piwik\Piwik; +use Piwik\Plugins\CustomVariables\CustomVariables; +use Piwik\Segment; +use Piwik\Site; +use Piwik\Tracker\GoalManager; + +class Model +{ + + /** + * @param $idVisit + * @param $actionsLimit + * @return array + * @throws \Exception + */ + public function queryActionsForVisit($idVisit, $actionsLimit) + { + $maxCustomVariables = CustomVariables::getMaxCustomVariables(); + + $sqlCustomVariables = ''; + for ($i = 1; $i <= $maxCustomVariables; $i++) { + $sqlCustomVariables .= ', custom_var_k' . $i . ', custom_var_v' . $i; + } + // The second join is a LEFT join to allow returning records that don't have a matching page title + // eg. Downloads, Outlinks. For these, idaction_name is set to 0 + $sql = " + SELECT + COALESCE(log_action_event_category.type, log_action.type, log_action_title.type) AS type, + log_action.name AS url, + log_action.url_prefix, + log_action_title.name AS pageTitle, + log_action.idaction AS pageIdAction, + log_link_visit_action.server_time as serverTimePretty, + log_link_visit_action.time_spent_ref_action as timeSpentRef, + log_link_visit_action.idlink_va AS pageId, + log_link_visit_action.custom_float + " . $sqlCustomVariables . ", + log_action_event_category.name AS eventCategory, + log_action_event_action.name as eventAction + FROM " . Common::prefixTable('log_link_visit_action') . " AS log_link_visit_action + LEFT JOIN " . Common::prefixTable('log_action') . " AS log_action + ON log_link_visit_action.idaction_url = log_action.idaction + LEFT JOIN " . Common::prefixTable('log_action') . " AS log_action_title + ON log_link_visit_action.idaction_name = log_action_title.idaction + LEFT JOIN " . Common::prefixTable('log_action') . " AS log_action_event_category + ON log_link_visit_action.idaction_event_category = log_action_event_category.idaction + LEFT JOIN " . Common::prefixTable('log_action') . " AS log_action_event_action + ON log_link_visit_action.idaction_event_action = log_action_event_action.idaction + WHERE log_link_visit_action.idvisit = ? + ORDER BY server_time ASC + LIMIT 0, $actionsLimit + "; + $actionDetails = Db::fetchAll($sql, array($idVisit)); + return $actionDetails; + } + + /** + * @param $idVisit + * @param $limit + * @return array + * @throws \Exception + */ + public function queryGoalConversionsForVisit($idVisit, $limit) + { + $sql = " + SELECT + 'goal' as type, + goal.name as goalName, + goal.idgoal as goalId, + goal.revenue as revenue, + log_conversion.idlink_va as goalPageId, + log_conversion.server_time as serverTimePretty, + log_conversion.url as url + FROM " . Common::prefixTable('log_conversion') . " AS log_conversion + LEFT JOIN " . Common::prefixTable('goal') . " AS goal + ON (goal.idsite = log_conversion.idsite + AND + goal.idgoal = log_conversion.idgoal) + AND goal.deleted = 0 + WHERE log_conversion.idvisit = ? + AND log_conversion.idgoal > 0 + ORDER BY server_time ASC + LIMIT 0, $limit + "; + $goalDetails = Db::fetchAll($sql, array($idVisit)); + return $goalDetails; + } + + /** + * @param $idVisit + * @param $limit + * @return array + * @throws \Exception + */ + public function queryEcommerceConversionsForVisit($idVisit, $limit) + { + $sql = "SELECT + case idgoal when " . GoalManager::IDGOAL_CART + . " then '" . Piwik::LABEL_ID_GOAL_IS_ECOMMERCE_CART + . "' else '" . Piwik::LABEL_ID_GOAL_IS_ECOMMERCE_ORDER . "' end as type, + idorder as orderId, + " . LogAggregator::getSqlRevenue('revenue') . " as revenue, + " . LogAggregator::getSqlRevenue('revenue_subtotal') . " as revenueSubTotal, + " . LogAggregator::getSqlRevenue('revenue_tax') . " as revenueTax, + " . LogAggregator::getSqlRevenue('revenue_shipping') . " as revenueShipping, + " . LogAggregator::getSqlRevenue('revenue_discount') . " as revenueDiscount, + items as items, + log_conversion.server_time as serverTimePretty + FROM " . Common::prefixTable('log_conversion') . " AS log_conversion + WHERE idvisit = ? + AND idgoal <= " . GoalManager::IDGOAL_ORDER . " + ORDER BY server_time ASC + LIMIT 0, $limit"; + $ecommerceDetails = Db::fetchAll($sql, array($idVisit)); + return $ecommerceDetails; + } + + + /** + * @param $idVisit + * @param $idOrder + * @param $actionsLimit + * @return array + * @throws \Exception + */ + public function queryEcommerceItemsForOrder($idVisit, $idOrder, $actionsLimit) + { + $sql = "SELECT + log_action_sku.name as itemSKU, + log_action_name.name as itemName, + log_action_category.name as itemCategory, + " . LogAggregator::getSqlRevenue('price') . " as price, + quantity as quantity + FROM " . Common::prefixTable('log_conversion_item') . " + INNER JOIN " . Common::prefixTable('log_action') . " AS log_action_sku + ON idaction_sku = log_action_sku.idaction + LEFT JOIN " . Common::prefixTable('log_action') . " AS log_action_name + ON idaction_name = log_action_name.idaction + LEFT JOIN " . Common::prefixTable('log_action') . " AS log_action_category + ON idaction_category = log_action_category.idaction + WHERE idvisit = ? + AND idorder = ? + AND deleted = 0 + LIMIT 0, $actionsLimit + "; + + $bind = array($idVisit, $idOrder); + + $itemsDetails = Db::fetchAll($sql, $bind); + return $itemsDetails; + } + + /** + * @param $idSite + * @param $period + * @param $date + * @param $segment + * @param $countVisitorsToFetch + * @param $visitorId + * @param $minTimestamp + * @param $filterSortOrder + * @return array + * @throws Exception + */ + public function queryLogVisits($idSite, $period, $date, $segment, $countVisitorsToFetch, $visitorId, $minTimestamp, $filterSortOrder) + { + list($sql, $bind) = $this->makeLogVisitsQueryString($idSite, $period, $date, $segment, $countVisitorsToFetch, $visitorId, $minTimestamp, $filterSortOrder); + + try { + $data = Db::fetchAll($sql, $bind); + return $data; + } catch (Exception $e) { + echo $e->getMessage(); + exit; + } + return $data; + } + + /** + * @param $idSite + * @param $lastMinutes + * @param $segment + * @return array + * @throws Exception + */ + public function queryCounters($idSite, $lastMinutes, $segment) + { + $lastMinutes = (int)$lastMinutes; + + $counters = array( + 'visits' => 0, + 'actions' => 0, + 'visitors' => 0, + 'visitsConverted' => 0, + ); + + if (empty($lastMinutes)) { + return array($counters); + } + + list($whereIdSites, $idSites) = $this->getIdSitesWhereClause($idSite); + + $select = "count(*) as visits, COUNT(DISTINCT log_visit.idvisitor) as visitors"; + $where = $whereIdSites . "AND log_visit.visit_last_action_time >= ?"; + $bind = $idSites; + $bind[] = Date::factory(time() - $lastMinutes * 60)->toString('Y-m-d H:i:s'); + + $segment = new Segment($segment, $idSite); + $query = $segment->getSelectQuery($select, 'log_visit', $where, $bind); + + $data = Db::fetchAll($query['sql'], $query['bind']); + + $counters['visits'] = $data[0]['visits']; + $counters['visitors'] = $data[0]['visitors']; + + $select = "count(*)"; + $from = 'log_link_visit_action'; + list($whereIdSites) = $this->getIdSitesWhereClause($idSite, $from); + $where = $whereIdSites . "AND log_link_visit_action.server_time >= ?"; + $query = $segment->getSelectQuery($select, $from, $where, $bind); + $counters['actions'] = Db::fetchOne($query['sql'], $query['bind']); + + $select = "count(*)"; + $from = 'log_conversion'; + list($whereIdSites) = $this->getIdSitesWhereClause($idSite, $from); + $where = $whereIdSites . "AND log_conversion.server_time >= ?"; + $query = $segment->getSelectQuery($select, $from, $where, $bind); + $counters['visitsConverted'] = Db::fetchOne($query['sql'], $query['bind']); + + return array($counters); + } + + + + /** + * @param $idSite + * @param string $table + * @return array + */ + private function getIdSitesWhereClause($idSite, $table = 'log_visit') + { + $idSites = array($idSite); + Piwik::postEvent('Live.API.getIdSitesString', array(&$idSites)); + + $idSitesBind = Common::getSqlStringFieldsArray($idSites); + $whereClause = $table . ".idsite in ($idSitesBind) "; + return array($whereClause, $idSites); + } + + + /** + * Returns the ID of a visitor that is adjacent to another visitor (by time of last action) + * in the log_visit table. + * + * @param int $idSite The ID of the site whose visits should be looked at. + * @param string $visitorId The ID of the visitor to get an adjacent visitor for. + * @param string $visitLastActionTime The last action time of the latest visit for $visitorId. + * @param string $segment + * @param bool $getNext Whether to retrieve the next visitor or the previous visitor. The next + * visitor will be the visitor that appears chronologically later in the + * log_visit table. The previous visitor will be the visitor that appears + * earlier. + * @return string The hex visitor ID. + * @throws Exception + */ + public function queryAdjacentVisitorId($idSite, $visitorId, $visitLastActionTime, $segment, $getNext) + { + if ($getNext) { + $visitLastActionTimeCondition = "sub.visit_last_action_time <= ?"; + $orderByDir = "DESC"; + } else { + $visitLastActionTimeCondition = "sub.visit_last_action_time >= ?"; + $orderByDir = "ASC"; + } + + $visitLastActionDate = Date::factory($visitLastActionTime); + $dateOneDayAgo = $visitLastActionDate->subDay(1); + $dateOneDayInFuture = $visitLastActionDate->addDay(1); + + $select = "log_visit.idvisitor, MAX(log_visit.visit_last_action_time) as visit_last_action_time"; + $from = "log_visit"; + $where = "log_visit.idsite = ? AND log_visit.idvisitor <> ? AND visit_last_action_time >= ? and visit_last_action_time <= ?"; + $whereBind = array($idSite, @Common::hex2bin($visitorId), $dateOneDayAgo->toString('Y-m-d H:i:s'), $dateOneDayInFuture->toString('Y-m-d H:i:s')); + $orderBy = "MAX(log_visit.visit_last_action_time) $orderByDir"; + $groupBy = "log_visit.idvisitor"; + + $segment = new Segment($segment, $idSite); + $queryInfo = $segment->getSelectQuery($select, $from, $where, $whereBind, $orderBy, $groupBy); + + $sql = "SELECT sub.idvisitor, sub.visit_last_action_time FROM ({$queryInfo['sql']}) as sub + WHERE $visitLastActionTimeCondition + LIMIT 1"; + $bind = array_merge($queryInfo['bind'], array($visitLastActionTime)); + + $visitorId = Db::fetchOne($sql, $bind); + if (!empty($visitorId)) { + $visitorId = bin2hex($visitorId); + } + return $visitorId; + } + + /** + * @param $idSite + * @param $period + * @param $date + * @param $segment + * @param $countVisitorsToFetch + * @param $visitorId + * @param $minTimestamp + * @param $filterSortOrder + * @return array + * @throws Exception + */ + public function makeLogVisitsQueryString($idSite, $period, $date, $segment, $countVisitorsToFetch, $visitorId, $minTimestamp, $filterSortOrder) + { + // If no other filter, only look at the last 24 hours of stats + if (empty($visitorId) + && empty($countVisitorsToFetch) + && empty($period) + && empty($date) + ) { + $period = 'day'; + $date = 'yesterdaySameTime'; + } + + list($whereBind, $where) = $this->getWhereClauseAndBind($idSite, $period, $date, $visitorId, $minTimestamp); + + if (strtolower($filterSortOrder) !== 'asc') { + $filterSortOrder = 'DESC'; + } + $segment = new Segment($segment, $idSite); + + // Subquery to use the indexes for ORDER BY + $select = "log_visit.*"; + $from = "log_visit"; + $groupBy = false; + $limit = $countVisitorsToFetch >= 1 ? (int)$countVisitorsToFetch : 0; + $orderBy = "idsite, visit_last_action_time " . $filterSortOrder; + $orderByParent = "sub.visit_last_action_time " . $filterSortOrder; + + $subQuery = $segment->getSelectQuery($select, $from, $where, $whereBind, $orderBy, $groupBy, $limit); + + $bind = $subQuery['bind']; + // Group by idvisit so that a visitor converting 2 goals only appears once + $sql = " + SELECT sub.* FROM ( + " . $subQuery['sql'] . " + ) AS sub + GROUP BY sub.idvisit + ORDER BY $orderByParent + "; + return array($sql, $bind); + } + + /** + * @param $idSite + * @return Site + */ + protected function makeSite($idSite) + { + return new Site($idSite); + } + + /** + * @param $idSite + * @param $period + * @param $date + * @param $visitorId + * @param $minTimestamp + * @return array + * @throws Exception + */ + private function getWhereClauseAndBind($idSite, $period, $date, $visitorId, $minTimestamp) + { + list($whereClause, $bindIdSites) = $this->getIdSitesWhereClause($idSite); + + $where = array(); + $where[] = $whereClause; + $whereBind = $bindIdSites; + + if (!empty($visitorId)) { + $where[] = "log_visit.idvisitor = ? "; + $whereBind[] = @Common::hex2bin($visitorId); + } + + if (!empty($minTimestamp)) { + $where[] = "log_visit.visit_last_action_time > ? "; + $whereBind[] = date("Y-m-d H:i:s", $minTimestamp); + } + + // SQL Filter with provided period + if (!empty($period) && !empty($date)) { + $currentSite = $this->makeSite($idSite); + $currentTimezone = $currentSite->getTimezone(); + + $dateString = $date; + if ($period == 'range') { + $processedPeriod = new Range('range', $date); + if ($parsedDate = Range::parseDateRange($date)) { + $dateString = $parsedDate[2]; + } + } else { + $processedDate = Date::factory($date); + if ($date == 'today' + || $date == 'now' + || $processedDate->toString() == Date::factory('now', $currentTimezone)->toString() + ) { + $processedDate = $processedDate->subDay(1); + } + $processedPeriod = Period\Factory::build($period, $processedDate); + } + $dateStart = $processedPeriod->getDateStart()->setTimezone($currentTimezone); + $where[] = "log_visit.visit_last_action_time >= ?"; + $whereBind[] = $dateStart->toString('Y-m-d H:i:s'); + + if (!in_array($date, array('now', 'today', 'yesterdaySameTime')) + && strpos($date, 'last') === false + && strpos($date, 'previous') === false + && Date::factory($dateString)->toString('Y-m-d') != Date::factory('now', $currentTimezone)->toString() + ) { + $dateEnd = $processedPeriod->getDateEnd()->setTimezone($currentTimezone); + $where[] = " log_visit.visit_last_action_time <= ?"; + $dateEndString = $dateEnd->addDay(1)->toString('Y-m-d H:i:s'); + $whereBind[] = $dateEndString; + } + } + + if (count($where) > 0) { + $where = join(" + AND ", $where); + } else { + $where = false; + } + return array($whereBind, $where); + } +}
\ No newline at end of file diff --git a/plugins/Live/Visitor.php b/plugins/Live/Visitor.php index 5cbe823cd9..eec1ecb6ce 100644 --- a/plugins/Live/Visitor.php +++ b/plugins/Live/Visitor.php @@ -246,44 +246,11 @@ class Visitor implements VisitorInterface { $idVisit = $visitorDetailsArray['idVisit']; - $maxCustomVariables = CustomVariables::getMaxCustomVariables(); - - $sqlCustomVariables = ''; - for ($i = 1; $i <= $maxCustomVariables; $i++) { - $sqlCustomVariables .= ', custom_var_k' . $i . ', custom_var_v' . $i; - } - // The second join is a LEFT join to allow returning records that don't have a matching page title - // eg. Downloads, Outlinks. For these, idaction_name is set to 0 - $sql = " - SELECT - COALESCE(log_action_event_category.type, log_action.type, log_action_title.type) AS type, - log_action.name AS url, - log_action.url_prefix, - log_action_title.name AS pageTitle, - log_action.idaction AS pageIdAction, - log_link_visit_action.server_time as serverTimePretty, - log_link_visit_action.time_spent_ref_action as timeSpentRef, - log_link_visit_action.idlink_va AS pageId, - log_link_visit_action.custom_float - ". $sqlCustomVariables . ", - log_action_event_category.name AS eventCategory, - log_action_event_action.name as eventAction - FROM " . Common::prefixTable('log_link_visit_action') . " AS log_link_visit_action - LEFT JOIN " . Common::prefixTable('log_action') . " AS log_action - ON log_link_visit_action.idaction_url = log_action.idaction - LEFT JOIN " . Common::prefixTable('log_action') . " AS log_action_title - ON log_link_visit_action.idaction_name = log_action_title.idaction - LEFT JOIN " . Common::prefixTable('log_action') . " AS log_action_event_category - ON log_link_visit_action.idaction_event_category = log_action_event_category.idaction - LEFT JOIN " . Common::prefixTable('log_action') . " AS log_action_event_action - ON log_link_visit_action.idaction_event_action = log_action_event_action.idaction - WHERE log_link_visit_action.idvisit = ? - ORDER BY server_time ASC - LIMIT 0, $actionsLimit - "; - $actionDetails = Db::fetchAll($sql, array($idVisit)); + $model = new Model(); + $actionDetails = $model->queryActionsForVisit($idVisit, $actionsLimit); $formatter = new Formatter(); + $maxCustomVariables = CustomVariables::getMaxCustomVariables(); foreach ($actionDetails as $actionIdx => &$actionDetail) { $actionDetail =& $actionDetails[$actionIdx]; @@ -353,46 +320,9 @@ class Visitor implements VisitorInterface } // If the visitor converted a goal, we shall select all Goals - $sql = " - SELECT - 'goal' as type, - goal.name as goalName, - goal.idgoal as goalId, - goal.revenue as revenue, - log_conversion.idlink_va as goalPageId, - log_conversion.server_time as serverTimePretty, - log_conversion.url as url - FROM " . Common::prefixTable('log_conversion') . " AS log_conversion - LEFT JOIN " . Common::prefixTable('goal') . " AS goal - ON (goal.idsite = log_conversion.idsite - AND - goal.idgoal = log_conversion.idgoal) - AND goal.deleted = 0 - WHERE log_conversion.idvisit = ? - AND log_conversion.idgoal > 0 - ORDER BY server_time ASC - LIMIT 0, $actionsLimit - "; - $goalDetails = Db::fetchAll($sql, array($idVisit)); - - $sql = "SELECT - case idgoal when " . GoalManager::IDGOAL_CART . " then '" . Piwik::LABEL_ID_GOAL_IS_ECOMMERCE_CART . "' else '" . Piwik::LABEL_ID_GOAL_IS_ECOMMERCE_ORDER . "' end as type, - idorder as orderId, - " . LogAggregator::getSqlRevenue('revenue') . " as revenue, - " . LogAggregator::getSqlRevenue('revenue_subtotal') . " as revenueSubTotal, - " . LogAggregator::getSqlRevenue('revenue_tax') . " as revenueTax, - " . LogAggregator::getSqlRevenue('revenue_shipping') . " as revenueShipping, - " . LogAggregator::getSqlRevenue('revenue_discount') . " as revenueDiscount, - items as items, - - log_conversion.server_time as serverTimePretty - FROM " . Common::prefixTable('log_conversion') . " AS log_conversion - WHERE idvisit = ? - AND idgoal <= " . GoalManager::IDGOAL_ORDER . " - ORDER BY server_time ASC - LIMIT 0, $actionsLimit"; - $ecommerceDetails = Db::fetchAll($sql, array($idVisit)); + $goalDetails = $model->queryGoalConversionsForVisit($idVisit, $actionsLimit); + $ecommerceDetails = $model->queryEcommerceConversionsForVisit($idVisit, $actionsLimit); foreach ($ecommerceDetails as &$ecommerceDetail) { if ($ecommerceDetail['type'] == Piwik::LABEL_ID_GOAL_IS_ECOMMERCE_CART) { unset($ecommerceDetail['orderId']); @@ -415,30 +345,9 @@ class Visitor implements VisitorInterface // Enrich ecommerce carts/orders with the list of products usort($ecommerceDetails, array('static', 'sortByServerTime')); foreach ($ecommerceDetails as &$ecommerceConversion) { - $sql = "SELECT - log_action_sku.name as itemSKU, - log_action_name.name as itemName, - log_action_category.name as itemCategory, - " . LogAggregator::getSqlRevenue('price') . " as price, - quantity as quantity - FROM " . Common::prefixTable('log_conversion_item') . " - INNER JOIN " . Common::prefixTable('log_action') . " AS log_action_sku - ON idaction_sku = log_action_sku.idaction - LEFT JOIN " . Common::prefixTable('log_action') . " AS log_action_name - ON idaction_name = log_action_name.idaction - LEFT JOIN " . Common::prefixTable('log_action') . " AS log_action_category - ON idaction_category = log_action_category.idaction - WHERE idvisit = ? - AND idorder = ? - AND deleted = 0 - LIMIT 0, $actionsLimit - "; - $bind = array($idVisit, isset($ecommerceConversion['orderId']) - ? $ecommerceConversion['orderId'] - : GoalManager::ITEM_IDORDER_ABANDONED_CART - ); - - $itemsDetails = Db::fetchAll($sql, $bind); + $idOrder = isset($ecommerceConversion['orderId']) ? $ecommerceConversion['orderId'] : GoalManager::ITEM_IDORDER_ABANDONED_CART; + + $itemsDetails = $model->queryEcommerceItemsForOrder($idVisit, $idOrder, $actionsLimit); foreach ($itemsDetails as &$detail) { if ($detail['price'] == round($detail['price'])) { $detail['price'] = round($detail['price']); diff --git a/plugins/Live/VisitorProfile.php b/plugins/Live/VisitorProfile.php new file mode 100644 index 0000000000..d52adbf076 --- /dev/null +++ b/plugins/Live/VisitorProfile.php @@ -0,0 +1,361 @@ +<?php +/** + * Piwik - free/libre analytics platform + * + * @link http://piwik.org + * @license http://www.gnu.org/licenses/gpl-3.0.html GPL v3 or later + * + */ + +namespace Piwik\Plugins\Live; + +use Exception; +use Piwik\DataTable; +use Piwik\Date; +use Piwik\Metrics\Formatter; +use Piwik\Piwik; +use Piwik\Site; +use Piwik\Plugins\Referrers\API as APIReferrers; + +class VisitorProfile +{ + const VISITOR_PROFILE_MAX_VISITS_TO_SHOW = 10; + const VISITOR_PROFILE_DATE_FORMAT = '%day% %shortMonth% %longYear%'; + + protected $profile = array(); + private $siteSearchKeywords = array(); + private $continents = array(); + private $countries = array(); + private $cities = array(); + private $pageGenerationTimeTotal = 0; + + public function __construct($idSite) + { + $this->idSite = $idSite; + $this->isEcommerceEnabled = Site::isEcommerceEnabledFor($this->idSite); + } + + /** + * @param $visits + * @param $visitorId + * @param $segment + * @return array + * @throws Exception + */ + public function makeVisitorProfile(DataTable $visits, $visitorId, $segment) + { + $this->initVisitorProfile(); + + /** @var DataTable\Row $visit */ + foreach ($visits->getRows() as $visit) { + ++$this->profile['totalVisits']; + + $this->profile['totalVisitDuration'] += $visit->getColumn('visitDuration'); + $this->profile['totalActions'] += $visit->getColumn('actions'); + $this->profile['totalGoalConversions'] += $visit->getColumn('goalConversions'); + + // individual goal conversions are stored in action details + foreach ($visit->getColumn('actionDetails') as $action) { + $this->handleIfGoalAction($action); + $this->handleIfEcommerceAction($action); + $this->handleIfSiteSearchAction($action); + $this->handleIfPageGenerationTime($action); + } + $this->handleGeoLocation($visit); + } + + $this->handleGeoLocationCountries(); + $this->handleGeoLocationContinents(); + $this->handleSiteSearches(); + $this->handleAveragePageGenerationTime(); + + $formatter = new Formatter(); + $this->profile['totalVisitDurationPretty'] = $formatter->getPrettyTimeFromSeconds($this->profile['totalVisitDuration'], true); + + $this->handleVisitsSummary($visits); + $this->handleAdjacentVisitorIds($visits, $visitorId, $segment); + + // use N most recent visits for last_visits + $visits->deleteRowsOffset(self::VISITOR_PROFILE_MAX_VISITS_TO_SHOW); + + $this->enrichVisitsWithFirstActionDatetime($visits); + + $this->profile['lastVisits'] = $visits; + + $this->profile['userId'] = $visit->getColumn('userId'); + + return $this->profile; + } + + /** + * Returns a summary for an important visit. Used to describe the first & last visits of a visitor. + * + * @param DataTable\Row $visit + * @return array + */ + private function getVisitorProfileVisitSummary($visit) + { + $today = Date::today(); + + $serverDate = $visit->getColumn('firstActionTimestamp'); + return array( + 'date' => $serverDate, + 'prettyDate' => Date::factory($serverDate)->getLocalized(self::VISITOR_PROFILE_DATE_FORMAT), + 'daysAgo' => (int)Date::secondsToDays($today->getTimestamp() - Date::factory($serverDate)->getTimestamp()), + 'referrerType' => $visit->getColumn('referrerType'), + 'referralSummary' => self::getReferrerSummaryForVisit($visit), + ); + } + + + /** + * Returns a summary for a visit's referral. + * + * @param DataTable\Row $visit + * @return bool|mixed|string + */ + public static function getReferrerSummaryForVisit($visit) + { + $referrerType = $visit->getColumn('referrerType'); + if ($referrerType === false + || $referrerType == 'direct' + ) { + return Piwik::translate('Referrers_DirectEntry'); + } + + if ($referrerType == 'search') { + $referrerName = $visit->getColumn('referrerName'); + + $keyword = $visit->getColumn('referrerKeyword'); + if ($keyword !== false + && $keyword != APIReferrers::getKeywordNotDefinedString() + ) { + $referrerName .= ' (' . $keyword . ')'; + } + return $referrerName; + } + + if ($referrerType == 'campaign') { + return Piwik::translate('Referrers_ColumnCampaign') . ' (' . $visit->getColumn('referrerName') . ')'; + } + + return $visit->getColumn('referrerName'); + } + + private function isEcommerceEnabled() + { + return $this->isEcommerceEnabled; + } + + /** + * @param $action + */ + private function handleIfEcommerceAction($action) + { + if (!$this->isEcommerceEnabled()) { + return; + } + if ($action['type'] == Piwik::LABEL_ID_GOAL_IS_ECOMMERCE_ORDER) { + ++$this->profile['totalEcommerceConversions']; + $this->profile['totalEcommerceRevenue'] += $action['revenue']; + $this->profile['totalEcommerceItems'] += $action['items']; + } else if ($action['type'] == Piwik::LABEL_ID_GOAL_IS_ECOMMERCE_CART) { + ++$this->profile['totalAbandonedCarts']; + $this->profile['totalAbandonedCartsRevenue'] += $action['revenue']; + $this->profile['totalAbandonedCartsItems'] += $action['items']; + } + } + + private function handleIfGoalAction($action) + { + if ($action['type'] != 'goal') { + return; + } + $idGoal = $action['goalId']; + $idGoalKey = 'idgoal=' . $idGoal; + + if (!isset($this->profile['totalConversionsByGoal'][$idGoalKey])) { + $this->profile['totalConversionsByGoal'][$idGoalKey] = 0; + } + ++$this->profile['totalConversionsByGoal'][$idGoalKey]; + + if (!empty($action['revenue'])) { + if (!isset($this->profile['totalRevenueByGoal'][$idGoalKey])) { + $this->profile['totalRevenueByGoal'][$idGoalKey] = 0; + } + $this->profile['totalRevenueByGoal'][$idGoalKey] += $action['revenue']; + } + } + + private function handleIfSiteSearchAction($action) + { + if (!isset($action['siteSearchKeyword'])) { + return; + } + $keyword = $action['siteSearchKeyword']; + + if (!isset($this->siteSearchKeywords[$keyword])) { + $this->siteSearchKeywords[$keyword] = 0; + ++$this->profile['totalSearches']; + } + ++$this->siteSearchKeywords[$keyword]; + } + + private function handleGeoLocation(DataTable\Row $visit) + { + // realtime map only checks for latitude + $hasLatitude = $visit->getColumn('latitude') !== false; + if ($hasLatitude) { + $this->profile['hasLatLong'] = true; + } + + $countryCode = $visit->getColumn('countryCode'); + if (!isset($this->countries[$countryCode])) { + $this->countries[$countryCode] = 0; + } + ++$this->countries[$countryCode]; + + $continentCode = $visit->getColumn('continentCode'); + if (!isset($this->continents[$continentCode])) { + $this->continents[$continentCode] = 0; + } + ++$this->continents[$continentCode]; + + if ($countryCode && !array_key_exists($countryCode, $this->cities)) { + $this->cities[$countryCode] = array(); + } + $city = $visit->getColumn('city'); + if (!empty($city)) { + $this->cities[$countryCode][] = $city; + } + } + + private function handleSiteSearches() + { + // sort by visit/action + arsort($this->siteSearchKeywords); + + foreach ($this->siteSearchKeywords as $keyword => $searchCount) { + $this->profile['searches'][] = array('keyword' => $keyword, + 'searches' => $searchCount); + } + } + + private function handleGeoLocationContinents() + { + // sort by visit/action + asort($this->continents); + foreach ($this->continents as $continentCode => $nbVisits) { + $this->profile['continents'][] = array('continent' => $continentCode, + 'nb_visits' => $nbVisits, + 'prettyName' => \Piwik\Plugins\UserCountry\continentTranslate($continentCode)); + } + } + + private function handleGeoLocationCountries() + { + // sort by visit/action + asort($this->countries); + + // transform country/continents/search keywords into something that will look good in XML + $this->profile['countries'] = $this->profile['continents'] = $this->profile['searches'] = array(); + + foreach ($this->countries as $countryCode => $nbVisits) { + + $countryInfo = array('country' => $countryCode, + 'nb_visits' => $nbVisits, + 'flag' => \Piwik\Plugins\UserCountry\getFlagFromCode($countryCode), + 'prettyName' => \Piwik\Plugins\UserCountry\countryTranslate($countryCode)); + if (!empty($this->cities[$countryCode])) { + $countryInfo['cities'] = array_unique($this->cities[$countryCode]); + } + $this->profile['countries'][] = $countryInfo; + } + } + + private function initVisitorProfile() + { + $this->profile['totalVisits'] = 0; + $this->profile['totalVisitDuration'] = 0; + $this->profile['totalActions'] = 0; + $this->profile['totalSearches'] = 0; + $this->profile['totalPageViewsWithTiming'] = 0; + $this->profile['totalGoalConversions'] = 0; + $this->profile['totalConversionsByGoal'] = array(); + $this->profile['hasLatLong'] = false; + + if ($this->isEcommerceEnabled()) { + $this->profile['totalEcommerceConversions'] = 0; + $this->profile['totalEcommerceRevenue'] = 0; + $this->profile['totalEcommerceItems'] = 0; + $this->profile['totalAbandonedCarts'] = 0; + $this->profile['totalAbandonedCartsRevenue'] = 0; + $this->profile['totalAbandonedCartsItems'] = 0; + } + } + + private function handleAveragePageGenerationTime() + { + if ($this->profile['totalPageViewsWithTiming']) { + $this->profile['averagePageGenerationTime'] = + round($this->pageGenerationTimeTotal / $this->profile['totalPageViewsWithTiming'], $precision = 2); + } + } + + private function handleIfPageGenerationTime($action) + { + if (isset($action['generationTime'])) { + $this->pageGenerationTimeTotal += $action['generationTime']; + ++$this->profile['totalPageViewsWithTiming']; + } + } + + /** + * @param DataTable $visits + * @param $visitorId + * @param $segment + */ + private function handleAdjacentVisitorIds(DataTable $visits, $visitorId, $segment) + { + // get visitor IDs that are adjacent to this one in log_visit + // TODO: make sure order of visitor ids is not changed if a returning visitor visits while the user is + // looking at the popup. + $rows = $visits->getRows(); + $latestVisitTime = reset($rows)->getColumn('lastActionDateTime'); + + $model = new Model(); + $this->profile['nextVisitorId'] = $model->queryAdjacentVisitorId($this->idSite, $visitorId, $latestVisitTime, $segment, $getNext = true); + $this->profile['previousVisitorId'] = $model->queryAdjacentVisitorId($this->idSite, $visitorId, $latestVisitTime, $segment, $getNext = false); + } + + /** + * @param DataTable $visits + */ + private function handleVisitsSummary(DataTable $visits) + { + $rows = $visits->getRows(); + $this->profile['firstVisit'] = $this->getVisitorProfileVisitSummary(end($rows)); + $this->profile['lastVisit'] = $this->getVisitorProfileVisitSummary(reset($rows)); + $this->profile['visitsAggregated'] = count($rows); + } + + /** + * @param DataTable $visits + * @return DataTable\Row + * @throws Exception + */ + private function enrichVisitsWithFirstActionDatetime(DataTable $visits) + { + $timezone = Site::getTimezoneFor($this->idSite); + foreach ($visits->getRows() as $visit) { + $dateTimeVisitFirstAction = Date::factory($visit->getColumn('firstActionTimestamp'), $timezone); + + $datePretty = $dateTimeVisitFirstAction->getLocalized(self::VISITOR_PROFILE_DATE_FORMAT); + $visit->setColumn('serverDatePrettyFirstAction', $datePretty); + + $dateTimePretty = $datePretty . ' ' . $visit->getColumn('serverTimePrettyFirstAction'); + $visit->setColumn('serverDateTimePrettyFirstAction', $dateTimePretty); + } + } + +}
\ No newline at end of file diff --git a/plugins/Live/templates/getVisitorProfilePopup.twig b/plugins/Live/templates/getVisitorProfilePopup.twig index 0a25de6a39..afb9551321 100644 --- a/plugins/Live/templates/getVisitorProfilePopup.twig +++ b/plugins/Live/templates/getVisitorProfilePopup.twig @@ -61,7 +61,7 @@ </p> {% endif %} {% if visitorData.averagePageGenerationTime is defined %} - <p title="{{ 'Live_CalculatedOverNPageViews'|translate(visitorData.totalPageViews) }}"> + <p title="{{ 'Live_CalculatedOverNPageViews'|translate(visitorData.totalPageViewsWithTiming) }}"> {{ 'Live_AveragePageGenerationTime'|translate('<strong>' ~ visitorData.averagePageGenerationTime ~ 's</strong>')|raw }} </p> {% endif %} diff --git a/plugins/Live/tests/Integration/APITest.php b/plugins/Live/tests/System/APITest.php index 2c85088a19..2c85088a19 100644 --- a/plugins/Live/tests/Integration/APITest.php +++ b/plugins/Live/tests/System/APITest.php diff --git a/plugins/Live/tests/System/ModelTest.php b/plugins/Live/tests/System/ModelTest.php new file mode 100644 index 0000000000..80f1c8d86f --- /dev/null +++ b/plugins/Live/tests/System/ModelTest.php @@ -0,0 +1,123 @@ +<?php +/** + * Piwik - free/libre analytics platform + * + * @link http://piwik.org + * @license http://www.gnu.org/licenses/gpl-3.0.html GPL v3 or later + */ + +namespace Piwik\Plugins\Live\tests\Integration; + +use Piwik\Access; +use Piwik\Common; +use Piwik\Plugins\Live\Model; +use Piwik\Tests\Framework\Fixture; +use Piwik\Tests\Framework\Mock\FakeAccess; +use Piwik\Tests\Framework\TestCase\SystemTestCase; +use Piwik\Tests\Integration\SegmentTest; + +/** + * @group Live + * @group ModelTest + * @group Plugins + */ +class ModelTest extends SystemTestCase +{ + function setUp() + { + $this->setSuperUser(); + Fixture::createWebsite('2010-01-01'); + } + + public function test_makeLogVisitsQueryString() + { + $model = new Model(); + list($sql, $bind) = $model->makeLogVisitsQueryString( + $idSite = 1, + $period = 'month', + $date = '2010-01-01', + $segment = false, + $countVisitorsToFetch = 100, + $visitorId = false, + $minTimestamp = false, + $filterSortOrder = false + ); + $expectedSql = ' SELECT sub.* FROM + ( + SELECT log_visit.* + FROM ' . Common::prefixTable('log_visit') . ' AS log_visit + WHERE log_visit.idsite in (?) + AND log_visit.visit_last_action_time >= ? + AND log_visit.visit_last_action_time <= ? + ORDER BY idsite, visit_last_action_time DESC + LIMIT 100 + ) AS sub + GROUP BY sub.idvisit + ORDER BY sub.visit_last_action_time DESC + '; + $expectedBind = array( + '1', + '2010-01-01 00:00:00', + '2010-02-01 00:00:00', + ); + $this->assertEquals(SegmentTest::removeExtraWhiteSpaces($expectedSql), SegmentTest::removeExtraWhiteSpaces($sql)); + $this->assertEquals(SegmentTest::removeExtraWhiteSpaces($expectedBind), SegmentTest::removeExtraWhiteSpaces($bind)); + } + + + public function test_makeLogVisitsQueryString_whenSegment() + { + $model = new Model(); + list($sql, $bind) = $model->makeLogVisitsQueryString( + $idSite = 1, + $period = 'month', + $date = '2010-01-01', + $segment = 'customVariablePageName1==Test', + $countVisitorsToFetch = 100, + $visitorId = 'abc', + $minTimestamp = false, + $filterSortOrder = false + ); + $expectedSql = ' SELECT sub.* FROM + ( + + SELECT log_inner.* + FROM ( + SELECT log_visit.* + FROM ' . Common::prefixTable('log_visit') . ' AS log_visit + LEFT JOIN ' . Common::prefixTable('log_link_visit_action') . ' AS log_link_visit_action + ON log_link_visit_action.idvisit = log_visit.idvisit + WHERE ( log_visit.idsite in (?) + AND log_visit.idvisitor = ? + AND log_visit.visit_last_action_time >= ? + AND log_visit.visit_last_action_time <= ? ) + AND ( log_link_visit_action.custom_var_k1 = ? ) + ORDER BY idsite, visit_last_action_time DESC + LIMIT 100 + ) AS log_inner + ORDER BY idsite, visit_last_action_time DESC + LIMIT 100 + ) AS sub + GROUP BY sub.idvisit + ORDER BY sub.visit_last_action_time DESC + '; + $expectedBind = array( + '1', + Common::hex2bin('abc'), + '2010-01-01 00:00:00', + '2010-02-01 00:00:00', + 'Test', + ); + $this->assertEquals(SegmentTest::removeExtraWhiteSpaces($expectedSql), SegmentTest::removeExtraWhiteSpaces($sql)); + $this->assertEquals(SegmentTest::removeExtraWhiteSpaces($expectedBind), SegmentTest::removeExtraWhiteSpaces($bind)); + } + + + protected function setSuperUser() + { + $pseudoMockAccess = new FakeAccess(); + FakeAccess::$superUser = true; + Access::setSingletonInstance($pseudoMockAccess); + } + +}
\ No newline at end of file diff --git a/plugins/Transitions/API.php b/plugins/Transitions/API.php index 32abffdc06..44a39fdcf5 100644 --- a/plugins/Transitions/API.php +++ b/plugins/Transitions/API.php @@ -24,7 +24,7 @@ use Piwik\Plugins\Actions\Actions; use Piwik\Plugins\Actions\ArchivingHelper; use Piwik\RankingQuery; use Piwik\Segment; -use Piwik\SegmentExpression; +use Piwik\Segment\SegmentExpression; use Piwik\Site; use Piwik\Tracker\Action; use Piwik\Tracker\PageUrl; diff --git a/plugins/VisitFrequency/API.php b/plugins/VisitFrequency/API.php index 97a98f27f8..a6f9971480 100644 --- a/plugins/VisitFrequency/API.php +++ b/plugins/VisitFrequency/API.php @@ -13,7 +13,7 @@ use Piwik\Archive; use Piwik\DataTable; use Piwik\Piwik; use Piwik\Plugins\VisitsSummary\API as APIVisitsSummary; -use Piwik\SegmentExpression; +use Piwik\Segment\SegmentExpression; /** * VisitFrequency API lets you access a list of metrics related to Returning Visitors. diff --git a/tests/PHPUnit/Integration/SegmentTest.php b/tests/PHPUnit/Integration/SegmentTest.php index 889f4a8e04..91be56af12 100644 --- a/tests/PHPUnit/Integration/SegmentTest.php +++ b/tests/PHPUnit/Integration/SegmentTest.php @@ -36,11 +36,11 @@ class SegmentTest extends IntegrationTestCase parent::tearDown(); } - protected function _filterWhitsSpaces($valueToFilter) + static public function removeExtraWhiteSpaces($valueToFilter) { if (is_array($valueToFilter)) { foreach ($valueToFilter as $key => $value) { - $valueToFilter[$key] = $this->_filterWhitsSpaces($value); + $valueToFilter[$key] = self::removeExtraWhiteSpaces($value); } return $valueToFilter; } else { @@ -117,16 +117,16 @@ class SegmentTest extends IntegrationTestCase $segment = new Segment($segment, $idSites = array()); $sql = $segment->getSelectQuery($select, $from, false); - $this->assertEquals($this->_filterWhitsSpaces($expected), $this->_filterWhitsSpaces($sql)); + $this->assertEquals($this->removeExtraWhiteSpaces($expected), $this->removeExtraWhiteSpaces($sql)); // calling twice should give same results $sql = $segment->getSelectQuery($select, array($from)); - $this->assertEquals($this->_filterWhitsSpaces($expected), $this->_filterWhitsSpaces($sql)); + $this->assertEquals($this->removeExtraWhiteSpaces($expected), $this->removeExtraWhiteSpaces($sql)); $this->assertEquals(32, strlen($segment->getHash())); } - public function testGetSelectQueryNoJoin() + public function test_getSelectQuery_whenNoJoin() { $select = '*'; $from = 'log_visit'; @@ -150,10 +150,10 @@ class SegmentTest extends IntegrationTestCase ( log_visit.custom_var_k1 = ? AND log_visit.visitor_returning = ? )", "bind" => array(1, 'Test', 0)); - $this->assertEquals($this->_filterWhitsSpaces($expected), $this->_filterWhitsSpaces($query)); + $this->assertEquals($this->removeExtraWhiteSpaces($expected), $this->removeExtraWhiteSpaces($query)); } - public function testGetSelectQueryJoinVisitOnAction() + public function test_getSelectQuery_whenJoinVisitOnAction() { $select = '*'; $from = 'log_link_visit_action'; @@ -178,10 +178,10 @@ class SegmentTest extends IntegrationTestCase ( log_link_visit_action.custom_var_k1 = ? AND log_visit.visitor_returning = ? )", "bind" => array(1, 'Test', 0)); - $this->assertEquals($this->_filterWhitsSpaces($expected), $this->_filterWhitsSpaces($query)); + $this->assertEquals($this->removeExtraWhiteSpaces($expected), $this->removeExtraWhiteSpaces($query)); } - public function testGetSelectQueryJoinActionOnVisit() + public function test_getSelectQuery_whenJoinActionOnVisit() { $select = 'sum(log_visit.visit_total_actions) as nb_actions, max(log_visit.visit_total_actions) as max_actions, sum(log_visit.visit_total_time) as sum_visit_length'; $from = 'log_visit'; @@ -210,13 +210,14 @@ class SegmentTest extends IntegrationTestCase AND ( log_link_visit_action.custom_var_k1 = ? AND log_visit.visitor_returning = ? ) GROUP BY log_visit.idvisit + ORDER BY NULL ) AS log_inner", "bind" => array(1, 'Test', 0)); - $this->assertEquals($this->_filterWhitsSpaces($expected), $this->_filterWhitsSpaces($query)); + $this->assertEquals($this->removeExtraWhiteSpaces($expected), $this->removeExtraWhiteSpaces($query)); } - public function testGetSelectQueryJoinConversionOnAction() + public function test_getSelectQuery_whenJoinConversionOnAction() { $select = '*'; $from = 'log_link_visit_action'; @@ -241,10 +242,10 @@ class SegmentTest extends IntegrationTestCase ( log_link_visit_action.custom_var_k1 = ? AND log_conversion.idgoal = ? AND log_link_visit_action.custom_var_k2 = ? )", "bind" => array(1, 'Test', 1, 'Test2')); - $this->assertEquals($this->_filterWhitsSpaces($expected), $this->_filterWhitsSpaces($query)); + $this->assertEquals($this->removeExtraWhiteSpaces($expected), $this->removeExtraWhiteSpaces($query)); } - public function testGetSelectQueryJoinActionOnConversion() + public function test_getSelectQuery_whenJoinActionOnConversion() { $select = '*'; $from = 'log_conversion'; @@ -269,10 +270,10 @@ class SegmentTest extends IntegrationTestCase ( ( log_conversion.idgoal IS NULL OR log_conversion.idgoal <> ? ) AND log_link_visit_action.custom_var_k1 = ? AND log_conversion.idgoal = ? )", "bind" => array(1, 2, 'Test', 1)); - $this->assertEquals($this->_filterWhitsSpaces($expected), $this->_filterWhitsSpaces($query)); + $this->assertEquals($this->removeExtraWhiteSpaces($expected), $this->removeExtraWhiteSpaces($query)); } - public function testGetSelectQueryJoinConversionOnVisit() + public function test_getSelectQuery_whenJoinConversionOnVisit() { $select = 'log_visit.*'; $from = 'log_visit'; @@ -300,13 +301,14 @@ class SegmentTest extends IntegrationTestCase AND ( log_conversion.idgoal = ? ) GROUP BY log_visit.idvisit + ORDER BY NULL ) AS log_inner", "bind" => array(1, 1)); - $this->assertEquals($this->_filterWhitsSpaces($expected), $this->_filterWhitsSpaces($query)); + $this->assertEquals($this->removeExtraWhiteSpaces($expected), $this->removeExtraWhiteSpaces($query)); } - public function testGetSelectQueryConversionOnly() + public function test_getSelectQuery_whenJoinConversionOnly() { $select = 'log_conversion.*'; $from = 'log_conversion'; @@ -330,10 +332,10 @@ class SegmentTest extends IntegrationTestCase ( log_conversion.idgoal = ? )", "bind" => array(1, 1)); - $this->assertEquals($this->_filterWhitsSpaces($expected), $this->_filterWhitsSpaces($query)); + $this->assertEquals($this->removeExtraWhiteSpaces($expected), $this->removeExtraWhiteSpaces($query)); } - public function testGetSelectQueryJoinVisitOnConversion() + public function test_getSelectQuery_whenJoinVisitOnConversion() { $select = '*'; $from = 'log_conversion'; @@ -358,14 +360,14 @@ class SegmentTest extends IntegrationTestCase ( (log_conversion.idgoal = ? OR HOUR(log_visit.visit_last_action_time) = ? ))", "bind" => array(1, 1, 12)); - $this->assertEquals($this->_filterWhitsSpaces($expected), $this->_filterWhitsSpaces($query)); + $this->assertEquals($this->removeExtraWhiteSpaces($expected), $this->removeExtraWhiteSpaces($query)); } /** * visit is joined on action, then conversion is joined * make sure that conversion is joined on action not visit */ - public function testGetSelectQueryJoinVisitAndConversionOnAction() + public function test_getSelectQuery_whenJoinVisitAndConversionOnAction() { $select = '*'; $from = 'log_link_visit_action'; @@ -389,21 +391,21 @@ class SegmentTest extends IntegrationTestCase HOUR(log_visit.visit_last_action_time) = ? AND log_conversion.idgoal = ? ", "bind" => array(12, 1)); - $this->assertEquals($this->_filterWhitsSpaces($expected), $this->_filterWhitsSpaces($query)); + $this->assertEquals($this->removeExtraWhiteSpaces($expected), $this->removeExtraWhiteSpaces($query)); } /** * join conversion on visit, then actions * make sure actions are joined before conversions */ - public function testGetSelectQueryJoinConversionAndActionOnVisit() + public function test_getSelectQuery_whenJoinConversionAndActionOnVisit_andPageUrlSet() { $select = 'log_visit.*'; $from = 'log_visit'; $where = false; $bind = array(); - $segment = 'visitConvertedGoalId==1;visitServerHour==12;customVariablePageName1==Test'; + $segment = 'visitConvertedGoalId==1;visitServerHour==12;customVariablePageName1==Test;pageUrl!='; $segment = new Segment($segment, $idSites = array()); $query = $segment->getSelectQuery($select, $from, $where, $bind); @@ -422,15 +424,21 @@ class SegmentTest extends IntegrationTestCase LEFT JOIN " . Common::prefixTable('log_conversion') . " AS log_conversion ON log_conversion.idlink_va = log_link_visit_action.idlink_va AND log_conversion.idsite = log_link_visit_action.idsite WHERE log_conversion.idgoal = ? AND HOUR(log_visit.visit_last_action_time) = ? AND log_link_visit_action.custom_var_k1 = ? + AND ( + log_link_visit_action.idaction_url IS NOT NULL + AND (log_link_visit_action.idaction_url <> '' + OR log_link_visit_action.idaction_url = 0) + ) GROUP BY log_visit.idvisit + ORDER BY NULL ) AS log_inner", "bind" => array(1, 12, 'Test')); - $this->assertEquals($this->_filterWhitsSpaces($expected), $this->_filterWhitsSpaces($query)); + $this->assertEquals($this->removeExtraWhiteSpaces($expected), $this->removeExtraWhiteSpaces($query)); } /** - * Dataprovider for testBogusSegmentThrowsException + * Dataprovider for test_bogusSegment_shouldThrowException */ public function getBogusSegments() { @@ -444,7 +452,7 @@ class SegmentTest extends IntegrationTestCase /** * @dataProvider getBogusSegments */ - public function testBogusSegmentThrowsException($segment) + public function test_bogusSegment_shouldThrowException($segment) { try { new Segment($segment, $idSites = array()); @@ -453,4 +461,45 @@ class SegmentTest extends IntegrationTestCase } $this->fail('Expected exception not raised'); } + + + public function test_getSelectQuery_whenLimit_innerQueryShouldHaveLimitAndNoGroupBy() + { + $select = 'sum(log_visit.visit_total_time) as sum_visit_length'; + $from = 'log_visit'; + $where = 'log_visit.idvisit = ?'; + $bind = array(1); + + $segment = 'customVariablePageName1==Test'; + $segment = new Segment($segment, $idSites = array()); + + $orderBy = false; + $groupBy = false; + $limit = 33; + + $query = $segment->getSelectQuery($select, $from, $where, $bind, $orderBy, $groupBy, $limit); + + $expected = array( + "sql" => " + SELECT + sum(log_inner.visit_total_time) as sum_visit_length + FROM + ( + SELECT + log_visit.visit_total_time + FROM + " . Common::prefixTable('log_visit') . " AS log_visit + LEFT JOIN " . Common::prefixTable('log_link_visit_action') . " AS log_link_visit_action ON log_link_visit_action.idvisit = log_visit.idvisit + WHERE + ( log_visit.idvisit = ? ) + AND + ( log_link_visit_action.custom_var_k1 = ? ) + ORDER BY NULL + LIMIT 33 + ) AS log_inner + LIMIT 33", + "bind" => array(1, 'Test')); + + $this->assertEquals($this->removeExtraWhiteSpaces($expected), $this->removeExtraWhiteSpaces($query)); + } } diff --git a/tests/PHPUnit/System/expected/test_periodIsRange_dateIsLastN_MetadataAndNormalAPI__Live.getVisitorProfile.xml b/tests/PHPUnit/System/expected/test_periodIsRange_dateIsLastN_MetadataAndNormalAPI__Live.getVisitorProfile.xml index 60e389c7ab..845a1a23a9 100644 --- a/tests/PHPUnit/System/expected/test_periodIsRange_dateIsLastN_MetadataAndNormalAPI__Live.getVisitorProfile.xml +++ b/tests/PHPUnit/System/expected/test_periodIsRange_dateIsLastN_MetadataAndNormalAPI__Live.getVisitorProfile.xml @@ -4,11 +4,12 @@ <totalVisitDuration>361</totalVisitDuration> <totalActions>2</totalActions> <totalSearches>0</totalSearches> - <totalPageViews>0</totalPageViews> + <totalPageViewsWithTiming>0</totalPageViewsWithTiming> <totalGoalConversions>1</totalGoalConversions> <totalConversionsByGoal> <row idgoal="1">1</row> </totalConversionsByGoal> + <hasLatLong>0</hasLatLong> <searches> </searches> <continents> @@ -42,6 +43,8 @@ <referralSummary>Direct Entry</referralSummary> </lastVisit> <visitsAggregated>2</visitsAggregated> + + <lastVisits> <row> <idSite>1</idSite> @@ -263,6 +266,4 @@ </row> </lastVisits> <userId>0</userId> - - </result>
\ No newline at end of file diff --git a/tests/PHPUnit/Unit/RankingQueryTest.php b/tests/PHPUnit/Unit/RankingQueryTest.php index 49c24e56be..c242732ee7 100644 --- a/tests/PHPUnit/Unit/RankingQueryTest.php +++ b/tests/PHPUnit/Unit/RankingQueryTest.php @@ -139,7 +139,7 @@ class RankingQueryTest extends \PHPUnit_Framework_TestCase */ private function checkQuery($rankingQuery, $innerQuerySql, $expected) { - $query = $rankingQuery->generateQuery($innerQuerySql); + $query = $rankingQuery->generateRankingQuery($innerQuerySql); $queryNoWhitespace = preg_replace("/\s+/", "", $query); $expectedNoWhitespace = preg_replace("/\s+/", "", $expected); diff --git a/tests/PHPUnit/Unit/SegmentExpressionTest.php b/tests/PHPUnit/Unit/Segment/SegmentExpressionTest.php index aadecc8f0e..ede256707b 100644 --- a/tests/PHPUnit/Unit/SegmentExpressionTest.php +++ b/tests/PHPUnit/Unit/Segment/SegmentExpressionTest.php @@ -8,7 +8,7 @@ namespace Piwik\Tests\Unit; -use Piwik\SegmentExpression; +use Piwik\Segment\SegmentExpression; class SegmentExpressionTest extends \PHPUnit_Framework_TestCase { |