/**
* Compute all the actions along with their hierarchies.
*
* For each action we process the "interest statistics" :
* visits, unique visitors, bouce count, sum visit length.
*
*
*/
public function archiveDay($notification)
{
/* @var $archiveProcessing Piwik_ArchiveProcessing_Day */
$archiveProcessing = $notification->getNotificationObject();
if (!$archiveProcessing->shouldProcessReportsForPlugin($this->getPluginName())) {
return;
}
$this->actionsTablesByType = array(Piwik_Tracker_Action::TYPE_ACTION_URL => array(), Piwik_Tracker_Action::TYPE_DOWNLOAD => array(), Piwik_Tracker_Action::TYPE_OUTLINK => array(), Piwik_Tracker_Action::TYPE_ACTION_NAME => array());
// This row is used in the case where an action is know as an exit_action
// but this action was not properly recorded when it was hit in the first place
// so we add this fake row information to make sure there is a nb_hits, etc. column for every action
$this->defaultRow = new Piwik_DataTable_Row(array(Piwik_DataTable_Row::COLUMNS => array(Piwik_Archive::INDEX_NB_VISITS => 1, Piwik_Archive::INDEX_NB_UNIQ_VISITORS => 1, Piwik_Archive::INDEX_PAGE_NB_HITS => 1)));
/*
* Page URLs and Page names, general stats
*/
$select = "log_action.name,\n\t\t\t\tlog_action.type,\n\t\t\t\tlog_action.idaction,\n\t\t\t\tcount(distinct log_link_visit_action.idvisit) as `" . Piwik_Archive::INDEX_NB_VISITS . "`,\n\t\t\t\tcount(distinct log_link_visit_action.idvisitor) as `" . Piwik_Archive::INDEX_NB_UNIQ_VISITORS . "`,\n\t\t\t\tcount(*) as `" . Piwik_Archive::INDEX_PAGE_NB_HITS . "`";
$from = array("log_link_visit_action", array("table" => "log_action", "joinOn" => "log_link_visit_action.%s = log_action.idaction"));
$where = "log_link_visit_action.server_time >= ?\n\t\t\t\tAND log_link_visit_action.server_time <= ?\n\t\t\t\tAND log_link_visit_action.idsite = ?\n\t\t\t\tAND log_link_visit_action.%s IS NOT NULL";
$groupBy = "log_action.idaction";
$orderBy = "`" . Piwik_Archive::INDEX_PAGE_NB_HITS . "` DESC";
$this->archiveDayQueryProcess($select, $from, $where, $orderBy, $groupBy, "idaction_url", $archiveProcessing);
$this->archiveDayQueryProcess($select, $from, $where, $orderBy, $groupBy, "idaction_name", $archiveProcessing);
/*
* Entry actions for Page URLs and Page names
*/
$select = "log_visit.%s as idaction,\n\t\t\t\tcount(distinct log_visit.idvisitor) as `" . Piwik_Archive::INDEX_PAGE_ENTRY_NB_UNIQ_VISITORS . "`,\n\t\t\t\tcount(*) as `" . Piwik_Archive::INDEX_PAGE_ENTRY_NB_VISITS . "`,\n\t\t\t\tsum(log_visit.visit_total_actions) as `" . Piwik_Archive::INDEX_PAGE_ENTRY_NB_ACTIONS . "`,\n\t\t\t\tsum(log_visit.visit_total_time) as `" . Piwik_Archive::INDEX_PAGE_ENTRY_SUM_VISIT_LENGTH . "`,\n\t\t\t\tsum(case log_visit.visit_total_actions when 1 then 1 when 0 then 1 else 0 end) as `" . Piwik_Archive::INDEX_PAGE_ENTRY_BOUNCE_COUNT . "`";
$from = "log_visit";
$where = "log_visit.visit_last_action_time >= ?\n\t\t\t\tAND log_visit.visit_last_action_time <= ?\n\t\t\t\tAND log_visit.idsite = ?\n\t\t \t\tAND log_visit.%s > 0";
$groupBy = "log_visit.%s, idaction";
$this->archiveDayQueryProcess($select, $from, $where, $orderBy = false, $groupBy, "visit_entry_idaction_url", $archiveProcessing);
$this->archiveDayQueryProcess($select, $from, $where, $orderBy = false, $groupBy, "visit_entry_idaction_name", $archiveProcessing);
/*
* Exit actions
*/
$select = "log_visit.%s as idaction,\n\t\t\t\tcount(distinct log_visit.idvisitor) as `" . Piwik_Archive::INDEX_PAGE_EXIT_NB_UNIQ_VISITORS . "`,\n\t\t\t\tcount(*) as `" . Piwik_Archive::INDEX_PAGE_EXIT_NB_VISITS . "`";
$from = "log_visit";
$where = "log_visit.visit_last_action_time >= ?\n\t\t\t\tAND log_visit.visit_last_action_time <= ?\n\t\t \t\tAND log_visit.idsite = ?\n\t\t \t\tAND log_visit.%s > 0";
$groupBy = "log_visit.%s, idaction";
$this->archiveDayQueryProcess($select, $from, $where, $orderBy = false, $groupBy, "visit_exit_idaction_url", $archiveProcessing);
$this->archiveDayQueryProcess($select, $from, $where, $orderBy = false, $groupBy, "visit_exit_idaction_name", $archiveProcessing);
/*
* Time per action
*/
$select = "log_link_visit_action.%s as idaction,\n\t\t\t\tsum(log_link_visit_action.time_spent_ref_action) as `" . Piwik_Archive::INDEX_PAGE_SUM_TIME_SPENT . "`";
$from = "log_link_visit_action";
$where = "log_link_visit_action.server_time >= ?\n\t\t\t\tAND log_link_visit_action.server_time <= ?\n\t\t \t\tAND log_link_visit_action.idsite = ?\n\t\t \t\tAND log_link_visit_action.time_spent_ref_action > 0\n\t\t \t\tAND log_link_visit_action.%s > 0";
$groupBy = "log_link_visit_action.%s, idaction";
$this->archiveDayQueryProcess($select, $from, $where, $orderBy = false, $groupBy, "idaction_url_ref", $archiveProcessing);
$this->archiveDayQueryProcess($select, $from, $where, $orderBy = false, $groupBy, "idaction_name_ref", $archiveProcessing);
// Empty static cache
self::$cacheParsedAction = array();
// Record the final datasets
$this->archiveDayRecordInDatabase($archiveProcessing);
}
/**
* Compute all the actions along with their hierarchies.
*
* For each action we process the "interest statistics" :
* visits, unique visitors, bouce count, sum visit length.
*
*
*/
public function archiveDay($notification)
{
/* @var $archiveProcessing Piwik_ArchiveProcessing */
$archiveProcessing = $notification->getNotificationObject();
if (!$archiveProcessing->shouldProcessReportsForPlugin($this->getPluginName())) {
return;
}
$this->actionsTablesByType = array(Piwik_Tracker_Action::TYPE_ACTION_URL => array(), Piwik_Tracker_Action::TYPE_DOWNLOAD => array(), Piwik_Tracker_Action::TYPE_OUTLINK => array(), Piwik_Tracker_Action::TYPE_ACTION_NAME => array());
// This row is used in the case where an action is know as an exit_action
// but this action was not properly recorded when it was hit in the first place
// so we add this fake row information to make sure there is a nb_hits, etc. column for every action
$this->defaultRow = new Piwik_DataTable_Row(array(Piwik_DataTable_Row::COLUMNS => array(Piwik_Archive::INDEX_NB_VISITS => 1, Piwik_Archive::INDEX_NB_UNIQ_VISITORS => 1, Piwik_Archive::INDEX_PAGE_NB_HITS => 1)));
/*
* Handling a custom segment when processing Page reports
*/
$segment = $archiveProcessing->getSegment();
$segmentSql = $segment->getSql();
$sqlJoinVisitTable = $sqlSegmentWhere = '';
if (!$segment->isEmpty()) {
$sqlJoinVisitTable = "LEFT JOIN " . Piwik_Common::prefixTable('log_visit') . " as log_visit ON (log_visit.idvisit = log_link_visit_action.idvisit)";
$sqlSegmentWhere = ' AND ' . $segmentSql['sql'];
}
$sqlBind = $segmentSql['bind'];
/*
* Page URLs and Page names, general stats
*/
$queryString = "SELECT name,\n\t\t\t\t\t\t\ttype,\n\t\t\t\t\t\t\tidaction,\n\t\t\t\t\t\t\tcount(distinct log_link_visit_action.idvisit) as `" . Piwik_Archive::INDEX_NB_VISITS . "`, \n\t\t\t\t\t\t\tcount(distinct log_link_visit_action.idvisitor) as `" . Piwik_Archive::INDEX_NB_UNIQ_VISITORS . "`,\n\t\t\t\t\t\t\tcount(*) as `" . Piwik_Archive::INDEX_PAGE_NB_HITS . "`\t\t\t\t\t\t\t\n\t\t\t\t\tFROM " . Piwik_Common::prefixTable('log_link_visit_action') . " as log_link_visit_action\n\t\t\t\t\t\t\tLEFT JOIN " . Piwik_Common::prefixTable('log_action') . " as log_action ON (log_link_visit_action.%s = idaction)\n\t\t\t\t\t\t\t{$sqlJoinVisitTable}\n\t\t\t\t\tWHERE server_time >= ?\n\t\t\t\t\t\tAND server_time <= ?\n\t\t\t\t\t\tAND log_link_visit_action.idsite = ?\n\t\t\t\t \t\tAND %s > 0\n\t\t\t\t \t\t{$sqlSegmentWhere}\n\t\t\t\t\tGROUP BY idaction\n\t\t\t\t\tORDER BY `" . Piwik_Archive::INDEX_PAGE_NB_HITS . "` DESC";
$this->archiveDayQueryProcess($queryString, "idaction_url", $sqlBind, $archiveProcessing);
$this->archiveDayQueryProcess($queryString, "idaction_name", $sqlBind, $archiveProcessing);
/*
* Entry actions for Page URLs and Page names
*/
$queryString = "SELECT %s as idaction,\n\t\t\t\t\t\t\tcount(distinct idvisitor) as `" . Piwik_Archive::INDEX_PAGE_ENTRY_NB_UNIQ_VISITORS . "`, \n\t\t\t\t\t\t\tcount(*) as `" . Piwik_Archive::INDEX_PAGE_ENTRY_NB_VISITS . "`,\n\t\t\t\t\t\t\tsum(visit_total_actions) as `" . Piwik_Archive::INDEX_PAGE_ENTRY_NB_ACTIONS . "`,\n\t\t\t\t\t\t\tsum(visit_total_time) as `" . Piwik_Archive::INDEX_PAGE_ENTRY_SUM_VISIT_LENGTH . "`,\t\t\t\t\t\t\t\n\t\t\t\t\t\t\tsum(case visit_total_actions when 1 then 1 else 0 end) as `" . Piwik_Archive::INDEX_PAGE_ENTRY_BOUNCE_COUNT . "`\n\t\t\t\t\tFROM " . Piwik_Common::prefixTable('log_visit') . " \n\t\t\t\t\tWHERE visit_last_action_time >= ?\n\t\t\t\t\t\tAND visit_last_action_time <= ?\n\t\t\t\t\t\tAND idsite = ?\n\t\t\t\t \t\tAND %s > 0\n\t\t\t\t \t\t{$sqlSegmentWhere}\n\t\t\t\t\tGROUP BY %s, idaction";
$this->archiveDayQueryProcess($queryString, "visit_entry_idaction_url", $sqlBind, $archiveProcessing);
$this->archiveDayQueryProcess($queryString, "visit_entry_idaction_name", $sqlBind, $archiveProcessing);
/*
* Exit actions
*/
$queryString = "SELECT %s as idaction,\n\t\t\t\t\t\t\tcount(distinct idvisitor) as `" . Piwik_Archive::INDEX_PAGE_EXIT_NB_UNIQ_VISITORS . "`,\n\t\t\t\t\t\t\tcount(*) as `" . Piwik_Archive::INDEX_PAGE_EXIT_NB_VISITS . "`\n\t\t\t\t \tFROM " . Piwik_Common::prefixTable('log_visit') . " \n\t\t\t\t \tWHERE visit_last_action_time >= ?\n\t\t\t\t\t\tAND visit_last_action_time <= ?\n\t\t\t\t \t\tAND idsite = ?\n\t\t\t\t \t\tAND %s > 0\n\t\t\t\t \t\t{$sqlSegmentWhere}\n\t\t\t\t \tGROUP BY %s, idaction";
$this->archiveDayQueryProcess($queryString, "visit_exit_idaction_url", $sqlBind, $archiveProcessing);
$this->archiveDayQueryProcess($queryString, "visit_exit_idaction_name", $sqlBind, $archiveProcessing);
/*
* Time per action
*/
$queryString = "SELECT %s as idaction,\n\t\t\t\t\t\t\tsum(time_spent_ref_action) as `" . Piwik_Archive::INDEX_PAGE_SUM_TIME_SPENT . "`\n\t\t\t\t\tFROM " . Piwik_Common::prefixTable('log_link_visit_action') . " AS log_link_visit_action\n\t\t\t\t\t\t\t{$sqlJoinVisitTable}\n\t\t\t\t\tWHERE server_time >= ?\n\t\t\t\t\t\tAND server_time <= ?\n\t\t\t\t \t\tAND log_link_visit_action.idsite = ?\n\t\t\t\t \t\tAND time_spent_ref_action > 0\n\t\t\t\t \t\tAND %s > 0\n\t\t\t\t \t\t{$sqlSegmentWhere}\n\t\t\t\t \tGROUP BY %s, idaction";
$this->archiveDayQueryProcess($queryString, "idaction_url_ref", $sqlBind, $archiveProcessing);
$this->archiveDayQueryProcess($queryString, "idaction_name_ref", $sqlBind, $archiveProcessing);
// Empty static cache
self::$cacheParsedAction = array();
// Record the final datasets
$this->archiveDayRecordInDatabase($archiveProcessing);
}
