/** * @param Zend_Db_Statement|PDOStatement $query * @param string|bool $fieldQueried * @param array $actionsTablesByType * @return int */ public static function updateActionsTableWithRowQuery($query, $fieldQueried, &$actionsTablesByType) { $rowsProcessed = 0; while ($row = $query->fetch()) { // var_dump($row); if (empty($row['idaction'])) { $row['type'] = $fieldQueried == 'idaction_url' ? Piwik_Tracker_Action::TYPE_ACTION_URL : Piwik_Tracker_Action::TYPE_ACTION_NAME; // This will be replaced with 'X not defined' later $row['name'] = ''; // Yes, this is kind of a hack, so we don't mix 'page url not defined' with 'page title not defined' etc. $row['idaction'] = -$row['type']; } if ($row['type'] != Piwik_Tracker_Action::TYPE_SITE_SEARCH) { unset($row[Piwik_Archive::INDEX_SITE_SEARCH_HAS_NO_RESULT]); } // This will appear as <url /> in the API, which is actually very important to keep // eg. When there's at least one row in a report that does not have a URL, not having this <url/> would break HTML/PDF reports. $url = ''; if ($row['type'] == Piwik_Tracker_Action::TYPE_SITE_SEARCH || $row['type'] == Piwik_Tracker_Action::TYPE_ACTION_NAME) { $url = null; } elseif (!empty($row['name']) && $row['name'] != Piwik_DataTable::LABEL_SUMMARY_ROW) { $url = Piwik_Tracker_Action::reconstructNormalizedUrl((string) $row['name'], $row['url_prefix']); } if (isset($row['name']) && isset($row['type'])) { $actionName = $row['name']; $actionType = $row['type']; $urlPrefix = $row['url_prefix']; $idaction = $row['idaction']; // in some unknown case, the type field is NULL, as reported in #1082 - we ignore this page view if (empty($actionType)) { if ($idaction != Piwik_DataTable::LABEL_SUMMARY_ROW) { self::setCachedActionRow($idaction, $actionType, false); } continue; } $actionRow = self::getActionRow($actionName, $actionType, $urlPrefix, $actionsTablesByType); self::setCachedActionRow($idaction, $actionType, $actionRow); } else { $actionRow = self::getCachedActionRow($row['idaction'], $row['type']); // Action processed as "to skip" for some reasons if ($actionRow === false) { continue; } } if (is_null($actionRow)) { continue; } // Here we do ensure that, the Metadata URL set for a given row, is the one from the Pageview with the most hits. // This is to ensure that when, different URLs are loaded with the same page name. // For example http://piwik.org and http://id.piwik.org are reported in Piwik > Actions > Pages with /index // But, we must make sure http://piwik.org is used to link & for transitions // Note: this code is partly duplicated from Piwik_DataTable_Row->sumRowMetadata() if (!is_null($url) && !$actionRow->isSummaryRow()) { if (($existingUrl = $actionRow->getMetadata('url')) !== false) { if (!empty($row[Piwik_Archive::INDEX_PAGE_NB_HITS]) && $row[Piwik_Archive::INDEX_PAGE_NB_HITS] > $actionRow->maxVisitsSummed) { $actionRow->setMetadata('url', $url); $actionRow->maxVisitsSummed = $row[Piwik_Archive::INDEX_PAGE_NB_HITS]; } } else { $actionRow->setMetadata('url', $url); $actionRow->maxVisitsSummed = !empty($row[Piwik_Archive::INDEX_PAGE_NB_HITS]) ? $row[Piwik_Archive::INDEX_PAGE_NB_HITS] : 0; } } unset($row['name']); unset($row['type']); unset($row['idaction']); unset($row['url_prefix']); foreach ($row as $name => $value) { // in some edge cases, we have twice the same action name with 2 different idaction // - this happens when 2 visitors visit the same new page at the same time, and 2 actions get recorded for the same name // - this could also happen when 2 URLs end up having the same label (eg. 2 subdomains get aggregated to the "/index" page name) if (($alreadyValue = $actionRow->getColumn($name)) !== false) { $actionRow->setColumn($name, $alreadyValue + $value); } else { $actionRow->addColumn($name, $value); } } // if the exit_action was not recorded properly in the log_link_visit_action // there would be an error message when getting the nb_hits column // we must fake the record and add the columns if ($actionRow->getColumn(Piwik_Archive::INDEX_PAGE_NB_HITS) === false) { // to test this code: delete the entries in log_link_action_visit for // a given exit_idaction_url foreach (self::getDefaultRow()->getColumns() as $name => $value) { $actionRow->addColumn($name, $value); } } $rowsProcessed++; } // just to make sure php copies the last $actionRow in the $parentTable array $actionRow =& $actionsTablesByType; return $rowsProcessed; }
private function getPageLabel(&$pageRecord, $isTitle) { if ($isTitle) { $label = $pageRecord['name']; if (empty($label)) { $label = Piwik_Actions_ArchivingHelper::getUnknownActionName(Piwik_Tracker_Action::TYPE_ACTION_NAME); } return $label; } else { if ($this->returnNormalizedUrls) { return $pageRecord['name']; } else { return Piwik_Tracker_Action::reconstructNormalizedUrl($pageRecord['name'], $pageRecord['url_prefix']); } } }
/** * Test removing hash tag * @group Core * @group Tracker * @group Tracker_Action * @dataProvider getTestUrlsHashtag */ public function testRemoveTrailingHashtag($url, $expectedUrl) { $this->assertEquals(Piwik_Tracker_Action::reconstructNormalizedUrl($url, Piwik_Tracker_Action::$urlPrefixMap['http://']), $expectedUrl); }
/** * Get information about the following actions (following pages, outlinks, downloads) * * @param $idaction * @param Piwik_ArchiveProcessing_Day $archiveProcessing * @return array(followingPages:Piwik_DataTable, outlinks:Piwik_DataTable, downloads:Piwik_DataTable) */ public function queryFollowingActions($idaction, Piwik_ArchiveProcessing_Day $archiveProcessing, $limitBeforeGrouping = false) { static $types = array(Piwik_Tracker_Action::TYPE_ACTION_URL => 'followingPages', Piwik_Tracker_Action::TYPE_OUTLINK => 'outlinks', Piwik_Tracker_Action::TYPE_DOWNLOAD => 'downloads'); $dimension = 'idaction_url'; $rankingQuery = new Piwik_RankingQuery($limitBeforeGrouping ? $limitBeforeGrouping : $this->limitBeforeGrouping); $rankingQuery->addLabelColumn(array('name', 'url_prefix')); $rankingQuery->partitionResultIntoMultipleGroups('type', array_keys($types)); $addSelect = 'log_action.name, log_action.url_prefix, log_action.type'; $where = ' log_link_visit_action.idaction_url_ref = ' . intval($idaction) . ' AND log_link_visit_action.idaction_url != ' . intval($idaction); $orderBy = '`' . Piwik_Archive::INDEX_NB_ACTIONS . '` DESC'; $metrics = array(Piwik_Archive::INDEX_NB_ACTIONS); $data = $archiveProcessing->queryActionsByDimension(array($dimension), $where, $metrics, $orderBy, $rankingQuery, $dimension, $addSelect); $dataTables = array(); foreach ($types as $type => $recordName) { $dataTable = new Piwik_DataTable(); if (isset($data[$type])) { foreach ($data[$type] as &$record) { $dataTable->addRow(new Piwik_DataTable_Row(array(Piwik_DataTable_Row::COLUMNS => array('label' => $type == Piwik_Tracker_Action::TYPE_ACTION_URL ? Piwik_Tracker_Action::reconstructNormalizedUrl($record['name'], $record['url_prefix']) : $record['name'], Piwik_Archive::INDEX_NB_ACTIONS => intval($record[Piwik_Archive::INDEX_NB_ACTIONS]))))); } } $dataTables[$recordName] = $dataTable; } return $dataTables; }
/** * For an array of visits, query the list of pages for this visit * as well as make the data human readable * @param array $visitorDetails * @param int $idSite * @return Piwik_DataTable */ private function getCleanedVisitorsFromDetails($visitorDetails, $idSite) { $table = new Piwik_DataTable(); $site = new Piwik_Site($idSite); $timezone = $site->getTimezone(); $currencies = Piwik_SitesManager_API::getInstance()->getCurrencySymbols(); foreach ($visitorDetails as $visitorDetail) { $this->cleanVisitorDetails($visitorDetail, $idSite); $visitor = new Piwik_Live_Visitor($visitorDetail); $visitorDetailsArray = $visitor->getAllVisitorDetails(); $visitorDetailsArray['siteCurrency'] = $site->getCurrency(); $visitorDetailsArray['siteCurrencySymbol'] = @$currencies[$site->getCurrency()]; $visitorDetailsArray['serverTimestamp'] = $visitorDetailsArray['lastActionTimestamp']; $dateTimeVisit = Piwik_Date::factory($visitorDetailsArray['lastActionTimestamp'], $timezone); $visitorDetailsArray['serverTimePretty'] = $dateTimeVisit->getLocalized('%time%'); $visitorDetailsArray['serverDatePretty'] = $dateTimeVisit->getLocalized(Piwik_Translate('CoreHome_ShortDateFormat')); $dateTimeVisitFirstAction = Piwik_Date::factory($visitorDetailsArray['firstActionTimestamp'], $timezone); $visitorDetailsArray['serverDatePrettyFirstAction'] = $dateTimeVisitFirstAction->getLocalized(Piwik_Translate('CoreHome_ShortDateFormat')); $visitorDetailsArray['serverTimePrettyFirstAction'] = $dateTimeVisitFirstAction->getLocalized('%time%'); $idvisit = $visitorDetailsArray['idVisit']; $sqlCustomVariables = ''; for ($i = 1; $i <= Piwik_Tracker::MAX_CUSTOM_VARIABLES; $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 = "\n\t\t\t\tSELECT\n\t\t\t\t\tlog_action.type AS type,\n\t\t\t\t\tlog_action.name AS url,\n\t\t\t\t\tlog_action.url_prefix,\n\t\t\t\t\tlog_action_title.name AS pageTitle,\n\t\t\t\t\tlog_action.idaction AS pageIdAction,\n\t\t\t\t\tlog_link_visit_action.idlink_va AS pageId,\n\t\t\t\t\tlog_link_visit_action.server_time as serverTimePretty,\n\t\t\t\t\tlog_link_visit_action.time_spent_ref_action as timeSpentRef\n\t\t\t\t\t{$sqlCustomVariables}\n\t\t\t\tFROM " . Piwik_Common::prefixTable('log_link_visit_action') . " AS log_link_visit_action\n\t\t\t\t\tINNER JOIN " . Piwik_Common::prefixTable('log_action') . " AS log_action\n\t\t\t\t\tON log_link_visit_action.idaction_url = log_action.idaction\n\t\t\t\t\tLEFT JOIN " . Piwik_Common::prefixTable('log_action') . " AS log_action_title\n\t\t\t\t\tON log_link_visit_action.idaction_name = log_action_title.idaction\n\t\t\t\tWHERE log_link_visit_action.idvisit = ?\n\t\t\t\t "; $actionDetails = Piwik_FetchAll($sql, array($idvisit)); foreach ($actionDetails as $actionIdx => &$actionDetail) { $customVariablesPage = array(); for ($i = 1; $i <= Piwik_Tracker::MAX_CUSTOM_VARIABLES; $i++) { if (!empty($actionDetail['custom_var_k' . $i]) && !empty($actionDetail['custom_var_v' . $i])) { $customVariablesPage[$i] = array('customVariableName' . $i => $actionDetail['custom_var_k' . $i], 'customVariableValue' . $i => $actionDetail['custom_var_v' . $i]); } unset($actionDetail['custom_var_k' . $i]); unset($actionDetail['custom_var_v' . $i]); } if (!empty($customVariablesPage)) { $actionDetail['customVariables'] = $customVariablesPage; } // reconstruct url from prefix $actionDetail['url'] = Piwik_Tracker_Action::reconstructNormalizedUrl($actionDetail['url'], $actionDetail['url_prefix']); unset($actionDetail['url_prefix']); // set the time spent for this action (which is the timeSpentRef of the next action) if (isset($actionDetails[$actionIdx + 1])) { $actionDetail['timeSpent'] = $actionDetails[$actionIdx + 1]['timeSpentRef']; $actionDetail['timeSpentPretty'] = Piwik::getPrettyTimeFromSeconds($actionDetail['timeSpent']); } unset($actionDetails[$actionIdx]['timeSpentRef']); // not needed after timeSpent is added } // If the visitor converted a goal, we shall select all Goals $sql = "\n\t\t\t\tSELECT \n\t\t\t\t\t\t'goal' as type,\n\t\t\t\t\t\tgoal.name as goalName,\n\t\t\t\t\t\tgoal.revenue as revenue,\n\t\t\t\t\t\tlog_conversion.idlink_va as goalPageId,\n\t\t\t\t\t\tlog_conversion.server_time as serverTimePretty,\n\t\t\t\t\t\tlog_conversion.url as url\n\t\t\t\tFROM " . Piwik_Common::prefixTable('log_conversion') . " AS log_conversion\n\t\t\t\tLEFT JOIN " . Piwik_Common::prefixTable('goal') . " AS goal \n\t\t\t\t\tON (goal.idsite = log_conversion.idsite\n\t\t\t\t\t\tAND \n\t\t\t\t\t\tgoal.idgoal = log_conversion.idgoal)\n\t\t\t\t\tAND goal.deleted = 0\n\t\t\t\tWHERE log_conversion.idvisit = ?\n\t\t\t\t\tAND log_conversion.idgoal > 0\n\t\t\t"; $goalDetails = Piwik_FetchAll($sql, array($idvisit)); $sql = "SELECT \n\t\t\t\t\t\tcase idgoal when " . Piwik_Tracker_GoalManager::IDGOAL_CART . " then '" . Piwik_Archive::LABEL_ECOMMERCE_CART . "' else '" . Piwik_Archive::LABEL_ECOMMERCE_ORDER . "' end as type,\n\t\t\t\t\t\tidorder as orderId,\n\t\t\t\t\t\t" . Piwik_ArchiveProcessing_Day::getSqlRevenue('revenue') . " as revenue,\n\t\t\t\t\t\t" . Piwik_ArchiveProcessing_Day::getSqlRevenue('revenue_subtotal') . " as revenueSubTotal,\n\t\t\t\t\t\t" . Piwik_ArchiveProcessing_Day::getSqlRevenue('revenue_tax') . " as revenueTax,\n\t\t\t\t\t\t" . Piwik_ArchiveProcessing_Day::getSqlRevenue('revenue_shipping') . " as revenueShipping,\n\t\t\t\t\t\t" . Piwik_ArchiveProcessing_Day::getSqlRevenue('revenue_discount') . " as revenueDiscount,\n\t\t\t\t\t\titems as items,\n\t\t\t\t\t\t\n\t\t\t\t\t\tlog_conversion.server_time as serverTimePretty\n\t\t\t\t\tFROM " . Piwik_Common::prefixTable('log_conversion') . " AS log_conversion\n\t\t\t\t\tWHERE idvisit = ?\n\t\t\t\t\t\tAND idgoal <= " . Piwik_Tracker_GoalManager::IDGOAL_ORDER; $ecommerceDetails = Piwik_FetchAll($sql, array($idvisit)); foreach ($ecommerceDetails as &$ecommerceDetail) { if ($ecommerceDetail['type'] == Piwik_Archive::LABEL_ECOMMERCE_CART) { unset($ecommerceDetail['orderId']); unset($ecommerceDetail['revenueSubTotal']); unset($ecommerceDetail['revenueTax']); unset($ecommerceDetail['revenueShipping']); unset($ecommerceDetail['revenueDiscount']); } // 25.00 => 25 foreach ($ecommerceDetail as $column => $value) { if (strpos($column, 'revenue') !== false) { if ($value == round($value)) { $ecommerceDetail[$column] = round($value); } } } } // Enrich ecommerce carts/orders with the list of products usort($ecommerceDetails, array($this, 'sortByServerTime')); foreach ($ecommerceDetails as $key => &$ecommerceConversion) { $sql = "SELECT \n\t\t\t\t\t\t\tlog_action_sku.name as itemSKU,\n\t\t\t\t\t\t\tlog_action_name.name as itemName,\n\t\t\t\t\t\t\tlog_action_category.name as itemCategory,\n\t\t\t\t\t\t\t" . Piwik_ArchiveProcessing_Day::getSqlRevenue('price') . " as price,\n\t\t\t\t\t\t\tquantity as quantity\n\t\t\t\t\t\tFROM " . Piwik_Common::prefixTable('log_conversion_item') . "\n\t\t\t\t\t\t\tINNER JOIN " . Piwik_Common::prefixTable('log_action') . " AS log_action_sku\n\t\t\t\t\t\t\tON idaction_sku = log_action_sku.idaction\n\t\t\t\t\t\t\tLEFT JOIN " . Piwik_Common::prefixTable('log_action') . " AS log_action_name\n\t\t\t\t\t\t\tON idaction_name = log_action_name.idaction\n\t\t\t\t\t\t\tLEFT JOIN " . Piwik_Common::prefixTable('log_action') . " AS log_action_category\n\t\t\t\t\t\t\tON idaction_category = log_action_category.idaction\n\t\t\t\t\t\tWHERE idvisit = ? \n\t\t\t\t\t\t\tAND idorder = ?\n\t\t\t\t\t\t\tAND deleted = 0\n\t\t\t\t"; $bind = array($idvisit, isset($ecommerceConversion['orderId']) ? $ecommerceConversion['orderId'] : Piwik_Tracker_GoalManager::ITEM_IDORDER_ABANDONED_CART); $itemsDetails = Piwik_FetchAll($sql, $bind); foreach ($itemsDetails as &$detail) { if ($detail['price'] == round($detail['price'])) { $detail['price'] = round($detail['price']); } } $ecommerceConversion['itemDetails'] = $itemsDetails; } $actions = array_merge($actionDetails, $goalDetails, $ecommerceDetails); usort($actions, array($this, 'sortByServerTime')); $visitorDetailsArray['actionDetails'] = $actions; // Convert datetimes to the site timezone foreach ($visitorDetailsArray['actionDetails'] as &$details) { switch ($details['type']) { case 'goal': $details['icon'] = 'themes/default/images/goal.png'; break; case Piwik_Archive::LABEL_ECOMMERCE_ORDER: case Piwik_Archive::LABEL_ECOMMERCE_CART: $details['icon'] = 'themes/default/images/' . $details['type'] . '.gif'; break; case Piwik_Tracker_Action_Interface::TYPE_DOWNLOAD: $details['type'] = 'download'; $details['icon'] = 'themes/default/images/download.png'; break; case Piwik_Tracker_Action_Interface::TYPE_OUTLINK: $details['type'] = 'outlink'; $details['icon'] = 'themes/default/images/link.gif'; break; default: $details['type'] = 'action'; $details['icon'] = null; break; } $dateTimeVisit = Piwik_Date::factory($details['serverTimePretty'], $timezone); $details['serverTimePretty'] = $dateTimeVisit->getLocalized(Piwik_Translate('CoreHome_ShortDateFormat') . ' %time%'); } $visitorDetailsArray['goalConversions'] = count($goalDetails); $table->addRowFromArray(array(Piwik_DataTable_Row::COLUMNS => $visitorDetailsArray)); } return $table; }
/** * Given a page name and type, builds a recursive datatable where * each level of the tree is a category, based on the page name split by a delimiter (slash / by default) * * @param string $actionName * @param int $actionType * @param int $urlPrefix * @return Piwik_DataTable */ protected function parseActionNameCategoriesInDataTable($actionName, $actionType, $urlPrefix = null) { // we work on the root table of the given TYPE (either ACTION_URL or DOWNLOAD or OUTLINK etc.) $currentTable =& $this->actionsTablesByType[$actionType]; // go to the level of the subcategory $actionExplodedNames = $this->getActionExplodedNames($actionName, $actionType, $urlPrefix); $end = count($actionExplodedNames) - 1; for ($level = 0; $level < $end; $level++) { $actionCategory = $actionExplodedNames[$level]; $currentTable =& $currentTable[$actionCategory]; } $actionShortName = $actionExplodedNames[$end]; // currentTable is now the array element corresponding the the action // at this point we may be for example at the 4th level of depth in the hierarchy $currentTable =& $currentTable[$actionShortName]; // add the row to the matching sub category subtable if (!$currentTable instanceof Piwik_DataTable_Row) { $defaultColumnsNewRow = array('label' => (string) $actionShortName, Piwik_Archive::INDEX_NB_VISITS => 0, Piwik_Archive::INDEX_NB_UNIQ_VISITORS => 0, Piwik_Archive::INDEX_PAGE_NB_HITS => 0, Piwik_Archive::INDEX_PAGE_SUM_TIME_SPENT => 0); if ($actionType == Piwik_Tracker_Action::TYPE_ACTION_NAME) { $currentTable = new Piwik_DataTable_Row(array(Piwik_DataTable_Row::COLUMNS => $defaultColumnsNewRow)); } else { $currentTable = new Piwik_DataTable_Row(array(Piwik_DataTable_Row::COLUMNS => $defaultColumnsNewRow, Piwik_DataTable_Row::METADATA => array('url' => Piwik_Tracker_Action::reconstructNormalizedUrl((string) $actionName, $urlPrefix)))); } } return $currentTable; }