/**
* General method to get transitions for an action
*
* @param $actionName
* @param $actionType "url"|"title"
* @param $idSite
* @param $period
* @param $date
* @param bool $segment
* @param bool $limitBeforeGrouping
* @param string $parts
* @param bool $returnNormalizedUrls
* @return array
* @throws Exception
*/
public function getTransitionsForAction($actionName, $actionType, $idSite, $period, $date, $segment = false, $limitBeforeGrouping = false, $parts = 'all', $returnNormalizedUrls = false)
{
Piwik::checkUserHasViewAccess($idSite);
// get idaction of the requested action
$idaction = $this->deriveIdAction($actionName, $actionType);
if ($idaction < 0) {
throw new Exception('NoDataForAction');
}
// prepare archive processing that can be used by the archiving code
$archiveProcessing = new Piwik_ArchiveProcessing_Day();
$archiveProcessing->setSite(new Piwik_Site($idSite));
$archiveProcessing->setPeriod(Piwik_Period::advancedFactory($period, $date));
$archiveProcessing->setSegment(new Piwik_Segment($segment, $idSite));
$archiveProcessing->initForLiveUsage();
// prepare the report
$report = array('date' => Piwik_Period_Day::advancedFactory($period, $date)->getLocalizedShortString());
// add data to the report
$transitionsArchiving = new Piwik_Transitions();
if ($returnNormalizedUrls) {
$transitionsArchiving->returnNormalizedUrls();
}
$partsArray = explode(',', $parts);
if ($parts == 'all' || in_array('internalReferrers', $partsArray)) {
$this->addInternalReferrers($transitionsArchiving, $archiveProcessing, $report, $idaction, $actionType, $limitBeforeGrouping);
}
if ($parts == 'all' || in_array('followingActions', $partsArray)) {
$includeLoops = $parts != 'all' && !in_array('internalReferrers', $partsArray);
$this->addFollowingActions($transitionsArchiving, $archiveProcessing, $report, $idaction, $actionType, $limitBeforeGrouping, $includeLoops);
}
if ($parts == 'all' || in_array('externalReferrers', $partsArray)) {
$this->addExternalReferrers($transitionsArchiving, $archiveProcessing, $report, $idaction, $actionType, $limitBeforeGrouping);
}
// derive the number of exits from the other metrics
if ($parts == 'all') {
$report['pageMetrics']['exits'] = $report['pageMetrics']['pageviews'] - $transitionsArchiving->getTotalTransitionsToFollowingActions() - $report['pageMetrics']['loops'];
}
// replace column names in the data tables
$reportNames = array('previousPages' => true, 'previousSiteSearches' => false, 'followingPages' => true, 'followingSiteSearches' => false, 'outlinks' => true, 'downloads' => true);
foreach ($reportNames as $reportName => $replaceLabel) {
if (isset($report[$reportName])) {
$columnNames = array(Piwik_Archive::INDEX_NB_ACTIONS => 'referrals');
if ($replaceLabel) {
$columnNames[Piwik_Archive::INDEX_NB_ACTIONS] = 'referrals';
}
$report[$reportName]->filter('ReplaceColumnNames', array($columnNames));
}
}
return $report;
}
/**
* @param Piwik_ArchiveProcessing_Day $archiveProcessing
*/
protected function archiveDayRecordInDatabase($archiveProcessing)
{
$tableCountry = $archiveProcessing->getDataTableFromArray($this->interestByCountry);
$archiveProcessing->insertBlobRecord('UserCountry_country', $tableCountry->getSerialized());
$archiveProcessing->insertNumericRecord('UserCountry_distinctCountries', $tableCountry->getRowsCount());
destroy($tableCountry);
$tableContinent = $archiveProcessing->getDataTableFromArray($this->interestByContinent);
$archiveProcessing->insertBlobRecord('UserCountry_continent', $tableContinent->getSerialized());
destroy($tableContinent);
}
protected function archiveDayRecordInDatabase($archiveProcessing)
{
$dataTable = Piwik_ArchiveProcessing_Day::generateDataTable($this->actionsTablesByType[Piwik_Tracker_Action::TYPE_ACTION_URL]);
$this->deleteInvalidSummedColumnsFromDataTable($dataTable);
$s = $dataTable->getSerialized($this->maximumRowsInDataTableLevelZero, $this->maximumRowsInSubDataTable, $this->columnToSortByBeforeTruncation);
$archiveProcessing->insertBlobRecord('Actions_actions_url', $s);
destroy($dataTable);
$dataTable = Piwik_ArchiveProcessing_Day::generateDataTable($this->actionsTablesByType[Piwik_Tracker_Action::TYPE_DOWNLOAD]);
$this->deleteInvalidSummedColumnsFromDataTable($dataTable);
$s = $dataTable->getSerialized($this->maximumRowsInDataTableLevelZero, $this->maximumRowsInSubDataTable, $this->columnToSortByBeforeTruncation);
$archiveProcessing->insertBlobRecord('Actions_downloads', $s);
destroy($dataTable);
$dataTable = Piwik_ArchiveProcessing_Day::generateDataTable($this->actionsTablesByType[Piwik_Tracker_Action::TYPE_OUTLINK]);
$this->deleteInvalidSummedColumnsFromDataTable($dataTable);
$s = $dataTable->getSerialized($this->maximumRowsInDataTableLevelZero, $this->maximumRowsInSubDataTable, $this->columnToSortByBeforeTruncation);
$archiveProcessing->insertBlobRecord('Actions_outlink', $s);
destroy($dataTable);
$dataTable = Piwik_ArchiveProcessing_Day::generateDataTable($this->actionsTablesByType[Piwik_Tracker_Action::TYPE_ACTION_NAME]);
$this->deleteInvalidSummedColumnsFromDataTable($dataTable);
$s = $dataTable->getSerialized($this->maximumRowsInDataTableLevelZero, $this->maximumRowsInSubDataTable, $this->columnToSortByBeforeTruncation);
$archiveProcessing->insertBlobRecord('Actions_actions', $s);
destroy($dataTable);
unset($this->actionsTablesByType);
}
/**
* Daily archive: processes all Referers reports, eg. Visits by Keyword,
* Visits by websites, etc.
*
* @param Piwik_ArchiveProcessing $archiveProcessing
* @throws Exception
* @return void
*/
protected function archiveDayAggregateVisits(Piwik_ArchiveProcessing_Day $archiveProcessing)
{
$dimension = array("referer_type", "referer_name", "referer_keyword", "referer_url");
$query = $archiveProcessing->queryVisitsByDimension($dimension);
$this->interestBySearchEngine = $this->interestByKeyword = $this->interestBySearchEngineAndKeyword = $this->interestByKeywordAndSearchEngine = $this->interestByWebsite = $this->interestByWebsiteAndUrl = $this->interestByCampaignAndKeyword = $this->interestByCampaign = $this->interestByType = $this->distinctUrls = array();
while ($row = $query->fetch()) {
if (empty($row['referer_type'])) {
$row['referer_type'] = Piwik_Common::REFERER_TYPE_DIRECT_ENTRY;
} else {
switch ($row['referer_type']) {
case Piwik_Common::REFERER_TYPE_SEARCH_ENGINE:
if (empty($row['referer_keyword'])) {
$row['referer_keyword'] = self::LABEL_KEYWORD_NOT_DEFINED;
}
if (!isset($this->interestBySearchEngine[$row['referer_name']])) {
$this->interestBySearchEngine[$row['referer_name']] = $archiveProcessing->getNewInterestRow();
}
if (!isset($this->interestByKeyword[$row['referer_keyword']])) {
$this->interestByKeyword[$row['referer_keyword']] = $archiveProcessing->getNewInterestRow();
}
if (!isset($this->interestBySearchEngineAndKeyword[$row['referer_name']][$row['referer_keyword']])) {
$this->interestBySearchEngineAndKeyword[$row['referer_name']][$row['referer_keyword']] = $archiveProcessing->getNewInterestRow();
}
if (!isset($this->interestByKeywordAndSearchEngine[$row['referer_keyword']][$row['referer_name']])) {
$this->interestByKeywordAndSearchEngine[$row['referer_keyword']][$row['referer_name']] = $archiveProcessing->getNewInterestRow();
}
$archiveProcessing->updateInterestStats($row, $this->interestBySearchEngine[$row['referer_name']]);
$archiveProcessing->updateInterestStats($row, $this->interestByKeyword[$row['referer_keyword']]);
$archiveProcessing->updateInterestStats($row, $this->interestBySearchEngineAndKeyword[$row['referer_name']][$row['referer_keyword']]);
$archiveProcessing->updateInterestStats($row, $this->interestByKeywordAndSearchEngine[$row['referer_keyword']][$row['referer_name']]);
break;
case Piwik_Common::REFERER_TYPE_WEBSITE:
if (!isset($this->interestByWebsite[$row['referer_name']])) {
$this->interestByWebsite[$row['referer_name']] = $archiveProcessing->getNewInterestRow();
}
$archiveProcessing->updateInterestStats($row, $this->interestByWebsite[$row['referer_name']]);
if (!isset($this->interestByWebsiteAndUrl[$row['referer_name']][$row['referer_url']])) {
$this->interestByWebsiteAndUrl[$row['referer_name']][$row['referer_url']] = $archiveProcessing->getNewInterestRow();
}
$archiveProcessing->updateInterestStats($row, $this->interestByWebsiteAndUrl[$row['referer_name']][$row['referer_url']]);
if (!isset($this->distinctUrls[$row['referer_url']])) {
$this->distinctUrls[$row['referer_url']] = true;
}
break;
case Piwik_Common::REFERER_TYPE_CAMPAIGN:
if (!empty($row['referer_keyword'])) {
if (!isset($this->interestByCampaignAndKeyword[$row['referer_name']][$row['referer_keyword']])) {
$this->interestByCampaignAndKeyword[$row['referer_name']][$row['referer_keyword']] = $archiveProcessing->getNewInterestRow();
}
$archiveProcessing->updateInterestStats($row, $this->interestByCampaignAndKeyword[$row['referer_name']][$row['referer_keyword']]);
}
if (!isset($this->interestByCampaign[$row['referer_name']])) {
$this->interestByCampaign[$row['referer_name']] = $archiveProcessing->getNewInterestRow();
}
$archiveProcessing->updateInterestStats($row, $this->interestByCampaign[$row['referer_name']]);
break;
case Piwik_Common::REFERER_TYPE_DIRECT_ENTRY:
// direct entry are aggregated below in $this->interestByType array
break;
default:
throw new Exception("Non expected referer_type = " . $row['referer_type']);
break;
}
}
if (!isset($this->interestByType[$row['referer_type']])) {
$this->interestByType[$row['referer_type']] = $archiveProcessing->getNewInterestRow();
}
$archiveProcessing->updateInterestStats($row, $this->interestByType[$row['referer_type']]);
}
}
/**
* @param Piwik_ArchiveProcessing_Day $archiveProcessing
*/
function archiveGeneralGoalMetrics($archiveProcessing)
{
// extra aggregate selects for the visits to conversion report
$visitToConvExtraCols = Piwik_ArchiveProcessing_Day::buildReduceByRangeSelect('visitor_count_visits', self::$visitCountRanges, 'log_conversion', 'vcv');
// extra aggregate selects for the days to conversion report
$daysToConvExtraCols = Piwik_ArchiveProcessing_Day::buildReduceByRangeSelect('visitor_days_since_first', self::$daysToConvRanges, 'log_conversion', 'vdsf');
$query = $archiveProcessing->queryConversionsByDimension(array(), '', array_merge($visitToConvExtraCols, $daysToConvExtraCols));
if ($query === false) {
return;
}
$goals = array();
$visitsToConvReport = array();
$daysToConvReport = array();
// Get a standard empty goal row
$overall = $archiveProcessing->getNewGoalRow($idGoal = 1);
while ($row = $query->fetch()) {
$idgoal = $row['idgoal'];
if (!isset($goals[$idgoal])) {
$goals[$idgoal] = $archiveProcessing->getNewGoalRow($idgoal);
$visitsToConvReport[$idgoal] = new Piwik_DataTable();
$daysToConvReport[$idgoal] = new Piwik_DataTable();
}
$archiveProcessing->updateGoalStats($row, $goals[$idgoal]);
// We don't want to sum Abandoned cart metrics in the overall revenue/conversions/converted visits
// since it is a "negative conversion"
if ($idgoal != Piwik_Tracker_GoalManager::IDGOAL_CART) {
$archiveProcessing->updateGoalStats($row, $overall);
}
// map the goal + visit number of a visitor with the # of conversions that happened on that visit
$table = $archiveProcessing->getSimpleDataTableFromRow($row, Piwik_Archive::INDEX_NB_CONVERSIONS, 'vcv');
$visitsToConvReport[$idgoal]->addDataTable($table);
// map the goal + day number of a visit with the # of conversion that happened on that day
$table = $archiveProcessing->getSimpleDataTableFromRow($row, Piwik_Archive::INDEX_NB_CONVERSIONS, 'vdsf');
$daysToConvReport[$idgoal]->addDataTable($table);
}
// these data tables hold reports for every goal of a site
$visitsToConvOverview = new Piwik_DataTable();
$daysToConvOverview = new Piwik_DataTable();
// Stats by goal, for all visitors
foreach ($goals as $idgoal => $values) {
foreach ($values as $metricId => $value) {
$metricName = Piwik_Archive::$mappingFromIdToNameGoal[$metricId];
$recordName = self::getRecordName($metricName, $idgoal);
$archiveProcessing->insertNumericRecord($recordName, $value);
}
$conversion_rate = $this->getConversionRate($values[Piwik_Archive::INDEX_GOAL_NB_VISITS_CONVERTED], $archiveProcessing);
$recordName = self::getRecordName('conversion_rate', $idgoal);
$archiveProcessing->insertNumericRecord($recordName, $conversion_rate);
// if the goal is not a special goal (like ecommerce) add it to the overview report
if ($idgoal !== Piwik_Tracker_GoalManager::IDGOAL_CART && $idgoal !== Piwik_Tracker_GoalManager::IDGOAL_ORDER) {
$visitsToConvOverview->addDataTable($visitsToConvReport[$idgoal]);
$daysToConvOverview->addDataTable($daysToConvReport[$idgoal]);
}
// visit count until conversion stats
$archiveProcessing->insertBlobRecord(self::getRecordName(self::VISITS_UNTIL_RECORD_NAME, $idgoal), $visitsToConvReport[$idgoal]->getSerialized());
// day count until conversion stats
$archiveProcessing->insertBlobRecord(self::getRecordName(self::DAYS_UNTIL_CONV_RECORD_NAME, $idgoal), $daysToConvReport[$idgoal]->getSerialized());
}
// archive overview reports
$archiveProcessing->insertBlobRecord(self::getRecordName(self::VISITS_UNTIL_RECORD_NAME), $visitsToConvOverview->getSerialized());
$archiveProcessing->insertBlobRecord(self::getRecordName(self::DAYS_UNTIL_CONV_RECORD_NAME), $daysToConvOverview->getSerialized());
// Stats for all goals
$totalAllGoals = array(self::getRecordName('conversion_rate') => $this->getConversionRate($archiveProcessing->getNumberOfVisitsConverted(), $archiveProcessing), self::getRecordName('nb_conversions') => $overall[Piwik_Archive::INDEX_GOAL_NB_CONVERSIONS], self::getRecordName('nb_visits_converted') => $archiveProcessing->getNumberOfVisitsConverted(), self::getRecordName('revenue') => $overall[Piwik_Archive::INDEX_GOAL_REVENUE]);
foreach ($totalAllGoals as $recordName => $value) {
$archiveProcessing->insertNumericRecord($recordName, $value);
}
}
/**
* For an array of visits, query the list of pages for this visit
* as well as make the data human readable
*/
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('%shortDay% %day% %shortMonth%');
$dateTimeVisitFirstAction = Piwik_Date::factory($visitorDetailsArray['firstActionTimestamp'], $timezone);
$visitorDetailsArray['serverDatePrettyFirstAction'] = $dateTimeVisitFirstAction->getLocalized('%shortDay% %day% %shortMonth%');
$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 = "
SELECT
log_action.type as type,
log_action.name AS url,
log_action_title.name AS pageTitle,
log_action.idaction AS pageIdAction,
log_link_visit_action.idlink_va AS pageId,
log_link_visit_action.server_time as serverTimePretty
$sqlCustomVariables
FROM " .Piwik_Common::prefixTable('log_link_visit_action')." AS log_link_visit_action
INNER JOIN " .Piwik_Common::prefixTable('log_action')." AS log_action
ON log_link_visit_action.idaction_url = log_action.idaction
LEFT JOIN " .Piwik_Common::prefixTable('log_action')." AS log_action_title
ON log_link_visit_action.idaction_name = log_action_title.idaction
WHERE log_link_visit_action.idvisit = ?
";
$actionDetails = Piwik_FetchAll($sql, array($idvisit));
foreach($actionDetails as &$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;
}
}
// If the visitor converted a goal, we shall select all Goals
$sql = "
SELECT
'goal' as type,
goal.name as goalName,
goal.revenue as revenue,
log_conversion.idlink_va as goalPageId,
log_conversion.server_time as serverTimePretty,
log_conversion.url as url
FROM ".Piwik_Common::prefixTable('log_conversion')." AS log_conversion
LEFT JOIN ".Piwik_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
";
$goalDetails = Piwik_FetchAll($sql, array($idvisit));
$sql = "SELECT
case idgoal when ".Piwik_Tracker_GoalManager::IDGOAL_CART." then '".Piwik_Archive::LABEL_ECOMMERCE_CART."' else '".Piwik_Archive::LABEL_ECOMMERCE_ORDER."' end as type,
idorder as orderId,
".Piwik_ArchiveProcessing_Day::getSqlRevenue('revenue')." as revenue,
".Piwik_ArchiveProcessing_Day::getSqlRevenue('revenue_subtotal')." as revenueSubTotal,
".Piwik_ArchiveProcessing_Day::getSqlRevenue('revenue_tax')." as revenueTax,
".Piwik_ArchiveProcessing_Day::getSqlRevenue('revenue_shipping')." as revenueShipping,
".Piwik_ArchiveProcessing_Day::getSqlRevenue('revenue_discount')." as revenueDiscount,
items as items,
log_conversion.server_time as serverTimePretty
FROM ".Piwik_Common::prefixTable('log_conversion')." AS log_conversion
WHERE idvisit = ?
AND 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);
}
}
}
}
$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('%shortDay% %day% %shortMonth% %time%');
}
$visitorDetailsArray['goalConversions'] = count($goalDetails);
// Enrich ecommerce carts/orders with the list of products
usort($ecommerceDetails, array($this, 'sortByServerTime'));
foreach($ecommerceDetails as $key => &$ecommerceConversion)
{
$sql = "SELECT
log_action_sku.name as itemSKU,
log_action_name.name as itemName,
log_action_category.name as itemCategory,
".Piwik_ArchiveProcessing_Day::getSqlRevenue('price')." as price,
quantity as quantity
FROM ".Piwik_Common::prefixTable('log_conversion_item')."
INNER JOIN " .Piwik_Common::prefixTable('log_action')." AS log_action_sku
ON idaction_sku = log_action_sku.idaction
LEFT JOIN " .Piwik_Common::prefixTable('log_action')." AS log_action_name
ON idaction_name = log_action_name.idaction
LEFT JOIN " .Piwik_Common::prefixTable('log_action')." AS log_action_category
ON idaction_category = log_action_category.idaction
WHERE idvisit = ?
AND idorder = ?
AND deleted = 0
";
$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;
}
$table->addRowFromArray( array(Piwik_DataTable_Row::COLUMNS => $visitorDetailsArray));
}
return $table;
}
/**
* @param Piwik_ArchiveProcessing_Day $archiveProcessing
* @return void
*/
protected function archiveDayAggregate(Piwik_ArchiveProcessing_Day $archiveProcessing)
{
for ($i = 1; $i <= Piwik_Tracker::MAX_CUSTOM_VARIABLES; $i++) {
$keyField = "custom_var_k" . $i;
$valueField = "custom_var_v" . $i;
$dimensions = array($keyField, $valueField);
$where = "%s.{$keyField} != ''";
// Custom Vars names and values metrics for visits
$query = $archiveProcessing->queryVisitsByDimension($dimensions, $where);
while ($row = $query->fetch()) {
// Handle case custom var value is empty
$row[$valueField] = $this->cleanCustomVarValue($row[$valueField]);
// Aggregate
if (!isset($this->interestByCustomVariables[$row[$keyField]])) {
$this->interestByCustomVariables[$row[$keyField]] = $archiveProcessing->getNewInterestRow();
}
if (!isset($this->interestByCustomVariablesAndValue[$row[$keyField]][$row[$valueField]])) {
$this->interestByCustomVariablesAndValue[$row[$keyField]][$row[$valueField]] = $archiveProcessing->getNewInterestRow();
}
$archiveProcessing->updateInterestStats($row, $this->interestByCustomVariables[$row[$keyField]]);
$archiveProcessing->updateInterestStats($row, $this->interestByCustomVariablesAndValue[$row[$keyField]][$row[$valueField]]);
}
// Custom Vars names and values metrics for page views
$query = $archiveProcessing->queryActionsByDimension($dimensions, $where);
$onlyMetricsAvailableInActionsTable = true;
while ($row = $query->fetch()) {
// Handle case custom var value is empty
$row[$valueField] = $this->cleanCustomVarValue($row[$valueField]);
$label = $row[$valueField];
// Remove price tracked if it's zero or we if we are not currently tracking an ecommerce var
if (!in_array($row[$keyField], array('_pks', '_pkn', '_pkc'))) {
unset($row[Piwik_Archive::INDEX_ECOMMERCE_ITEM_PRICE_VIEWED]);
}
// when custom variable value is a JSON array of categories
// possibly JSON value
$mustInsertCustomVariableValue = true;
if ($row[$keyField] == '_pkc' && $label[0] == '[' && $label[1] == '"') {
// In case categories were truncated, try closing the array
if (substr($label, -2) != '"]') {
$label .= '"]';
}
$decoded = @Piwik_Common::json_decode($label);
if (is_array($decoded)) {
$count = 0;
foreach ($decoded as $category) {
if (empty($category) || $count >= Piwik_Tracker_GoalManager::MAXIMUM_PRODUCT_CATEGORIES) {
continue;
}
if (!isset($this->interestByCustomVariablesAndValue[$row[$keyField]][$category])) {
$this->interestByCustomVariablesAndValue[$row[$keyField]][$category] = $archiveProcessing->getNewInterestRow($onlyMetricsAvailableInActionsTable);
}
$archiveProcessing->updateInterestStats($row, $this->interestByCustomVariablesAndValue[$row[$keyField]][$category], $onlyMetricsAvailableInActionsTable);
$mustInsertCustomVariableValue = false;
$count++;
}
}
}
// end multi categories hack
if ($mustInsertCustomVariableValue) {
if (!isset($this->interestByCustomVariablesAndValue[$row[$keyField]][$row[$valueField]])) {
$this->interestByCustomVariablesAndValue[$row[$keyField]][$row[$valueField]] = $archiveProcessing->getNewInterestRow($onlyMetricsAvailableInActionsTable);
}
$archiveProcessing->updateInterestStats($row, $this->interestByCustomVariablesAndValue[$row[$keyField]][$row[$valueField]], $onlyMetricsAvailableInActionsTable);
}
// Do not report on Price viewed for the Custom Variable names
unset($row[Piwik_Archive::INDEX_ECOMMERCE_ITEM_PRICE_VIEWED]);
// When tracking Custom Variables with scope=page we do not add up visits numbers
// as it is incorrect to sum visits this way
// for scope=visit this is allowed, since there is supposed to be one custom var value per custom variable name for a given visit
$doNotSumVisits = true;
if (!isset($this->interestByCustomVariables[$row[$keyField]])) {
$this->interestByCustomVariables[$row[$keyField]] = $archiveProcessing->getNewInterestRow($onlyMetricsAvailableInActionsTable, $doNotSumVisits);
}
$archiveProcessing->updateInterestStats($row, $this->interestByCustomVariables[$row[$keyField]], $onlyMetricsAvailableInActionsTable, $doNotSumVisits);
}
// Custom Vars names and values metrics for Goals
$query = $archiveProcessing->queryConversionsByDimension($dimensions, $where);
if ($query !== false) {
while ($row = $query->fetch()) {
// Handle case custom var value is empty
$row[$valueField] = $this->cleanCustomVarValue($row[$valueField]);
if (!isset($this->interestByCustomVariables[$row[$keyField]][Piwik_Archive::INDEX_GOALS][$row['idgoal']])) {
$this->interestByCustomVariables[$row[$keyField]][Piwik_Archive::INDEX_GOALS][$row['idgoal']] = $archiveProcessing->getNewGoalRow($row['idgoal']);
}
if (!isset($this->interestByCustomVariablesAndValue[$row[$keyField]][$row[$valueField]][Piwik_Archive::INDEX_GOALS][$row['idgoal']])) {
$this->interestByCustomVariablesAndValue[$row[$keyField]][$row[$valueField]][Piwik_Archive::INDEX_GOALS][$row['idgoal']] = $archiveProcessing->getNewGoalRow($row['idgoal']);
}
$archiveProcessing->updateGoalStats($row, $this->interestByCustomVariables[$row[$keyField]][Piwik_Archive::INDEX_GOALS][$row['idgoal']]);
$archiveProcessing->updateGoalStats($row, $this->interestByCustomVariablesAndValue[$row[$keyField]][$row[$valueField]][Piwik_Archive::INDEX_GOALS][$row['idgoal']]);
}
}
}
$archiveProcessing->enrichConversionsByLabelArray($this->interestByCustomVariables);
$archiveProcessing->enrichConversionsByLabelArrayHasTwoLevels($this->interestByCustomVariablesAndValue);
// var_dump($this->interestByCustomVariables);
//var_dump($this->interestByCustomVariablesAndValue);
}
function test_generateDataTable_1row4level()
{
$table = new Piwik_DataTable();
$rowpagecat3 = new Piwik_DataTable_Row(array(Piwik_DataTable_Row::COLUMNS => array('label' => '123123', 'visits' => 3, 'actions' => 5)));
$rowcat3 = new Piwik_DataTable_Row(array(Piwik_DataTable_Row::COLUMNS => array('label' => '789.654', 'visits' => 3, 'actions' => 5)));
$rowcat2 = new Piwik_DataTable_Row(array(Piwik_DataTable_Row::COLUMNS => array('label' => 'cat2', 'visits' => 3, 'actions' => 5)));
$rowcat1 = new Piwik_DataTable_Row(array(Piwik_DataTable_Row::COLUMNS => array('label' => '&*()', 'visits' => 3, 'actions' => 5)));
$subtablerowpagecat3 = new Piwik_DataTable();
$subtablerowpagecat3->addRow($rowpagecat3);
$rowcat3->addSubtable($subtablerowpagecat3);
$subtablecat2 = new Piwik_DataTable();
$subtablecat2->addRow($rowcat3);
$rowcat2->addSubtable($subtablecat2);
$subtablecat1 = new Piwik_DataTable();
$subtablecat1->addRow($rowcat2);
$rowcat1->addSubtable($subtablecat1);
//-- add
$table->addRow($rowcat1);
// WHAT WE TEST
$input = array('&*()' => array('cat2' => array('789.654' => array('123123' => $rowpagecat3))));
$tableGenerated = Piwik_ArchiveProcessing_Day::generateDataTable($input);
$r1 = new Piwik_DataTable_Renderer_Console();
$r1->setTable($table);
$r2 = new Piwik_DataTable_Renderer_Console();
$r2->setTable($tableGenerated);
$this->assertTrue(Piwik_DataTable::isEqual($table, $tableGenerated));
}
/**
* Add transitions data to the report.
* Fake ArchiveProcessing to do the queries live.
*/
private function addLiveTransitionsDataToReport(&$report, $pageUrl, $idSite, $period, $date, $segment, $limitBeforeGrouping)
{
// get idaction of page url
$actionsPlugin = new Piwik_Actions();
$idaction = $actionsPlugin->getIdActionFromSegment($pageUrl, 'idaction');
// prepare archive processing that can be reused by the archiving code
$archiveProcessing = new Piwik_ArchiveProcessing_Day();
$archiveProcessing->setSite(new Piwik_Site($idSite));
$archiveProcessing->setPeriod(Piwik_Period::advancedFactory($period, $date));
$archiveProcessing->setSegment(new Piwik_Segment($segment, $idSite));
$archiveProcessing->initForLiveUsage();
// launch the archiving code - but live
$transitionsArchiving = new Piwik_Transitions();
$data = $transitionsArchiving->queryInternalReferrers($idaction, $archiveProcessing, $limitBeforeGrouping);
$report['previousPages'] =& $data['previousPages'];
$report['pageMetrics']['loops'] = intval($data['loops']);
$data = $transitionsArchiving->queryFollowingActions($idaction, $archiveProcessing, $limitBeforeGrouping);
foreach ($data as $tableName => $table) {
$report[$tableName] = $table;
}
$data = $transitionsArchiving->queryExternalReferrers($idaction, $archiveProcessing, $limitBeforeGrouping);
$report['pageMetrics']['entries'] = 0;
$report['referrers'] = array();
foreach ($data->getRows() as $row) {
$referrerId = $row->getColumn('label');
$visits = $row->getColumn(Piwik_Archive::INDEX_NB_VISITS);
if ($visits) {
// load details (i.e. subtables)
$details = array();
if ($idSubTable = $row->getIdSubDataTable()) {
$subTable = Piwik_DataTable_Manager::getInstance()->getTable($idSubTable);
foreach ($subTable->getRows() as $subRow) {
$details[] = array('label' => $subRow->getColumn('label'), 'referrals' => $subRow->getColumn(Piwik_Archive::INDEX_NB_VISITS));
}
}
$report['referrers'][] = array('label' => $this->getReferrerLabel($referrerId), 'shortName' => Piwik_getRefererTypeFromShortName($referrerId), 'visits' => $visits, 'details' => $details);
$report['pageMetrics']['entries'] += $visits;
}
}
// if there's no data for referrers, Piwik_API_ResponseBuilder::handleMultiDimensionalArray
// does not detect the multi dimensional array and the data is rendered differently, which
// causes an exception.
if (count($report['referrers']) == 0) {
$report['referrers'][] = array('label' => $this->getReferrerLabel(Piwik_Common::REFERER_TYPE_DIRECT_ENTRY), 'shortName' => Piwik_getRefererTypeLabel(Piwik_Common::REFERER_TYPE_DIRECT_ENTRY), 'visits' => 0);
}
}
/**
* @param Piwik_ArchiveProcessing_Day $archiveProcessing
* @return void
*/
protected function archiveDayAggregate(Piwik_ArchiveProcessing_Day $archiveProcessing)
{
for($i = 1; $i <= Piwik_Tracker::MAX_CUSTOM_VARIABLES; $i++ )
{
$keyField = "custom_var_k".$i;
$valueField = "custom_var_v".$i;
$dimensions = array($keyField, $valueField);
$where = "%s.$keyField != '' AND %s.$valueField != ''";
// Custom Vars names and values metrics for visits
$query = $archiveProcessing->queryVisitsByDimension($dimensions, $where);
while($row = $query->fetch() )
{
if(!isset($this->interestByCustomVariables[$row[$keyField]])) $this->interestByCustomVariables[$row[$keyField]]= $archiveProcessing->getNewInterestRow();
if(!isset($this->interestByCustomVariablesAndValue[$row[$keyField]][$row[$valueField]])) $this->interestByCustomVariablesAndValue[$row[$keyField]][$row[$valueField]] = $archiveProcessing->getNewInterestRow();
$archiveProcessing->updateInterestStats( $row, $this->interestByCustomVariables[$row[$keyField]]);
$archiveProcessing->updateInterestStats( $row, $this->interestByCustomVariablesAndValue[$row[$keyField]][$row[$valueField]]);
}
// Custom Vars names and values metrics for page views
$query = $archiveProcessing->queryActionsByDimension($dimensions, $where);
$onlyMetricsAvailableInActionsTable = true;
while($row = $query->fetch() )
{
if(!isset($this->interestByCustomVariables[$row[$keyField]])) $this->interestByCustomVariables[$row[$keyField]]= $archiveProcessing->getNewInterestRow($onlyMetricsAvailableInActionsTable);
if(!isset($this->interestByCustomVariablesAndValue[$row[$keyField]][$row[$valueField]])) $this->interestByCustomVariablesAndValue[$row[$keyField]][$row[$valueField]] = $archiveProcessing->getNewInterestRow($onlyMetricsAvailableInActionsTable);
$archiveProcessing->updateInterestStats( $row, $this->interestByCustomVariables[$row[$keyField]], $onlyMetricsAvailableInActionsTable);
$archiveProcessing->updateInterestStats( $row, $this->interestByCustomVariablesAndValue[$row[$keyField]][$row[$valueField]], $onlyMetricsAvailableInActionsTable);
}
// Custom Vars names and values metrics for Goals
$query = $archiveProcessing->queryConversionsByDimension($dimensions, $where);
if($query !== false)
{
while($row = $query->fetch() )
{
if(!isset($this->interestByCustomVariables[$row[$keyField]][Piwik_Archive::INDEX_GOALS][$row['idgoal']])) $this->interestByCustomVariables[$row[$keyField]][Piwik_Archive::INDEX_GOALS][$row['idgoal']] = $archiveProcessing->getNewGoalRow($row['idgoal']);
if(!isset($this->interestByCustomVariablesAndValue[$row[$keyField]][$row[$valueField]][Piwik_Archive::INDEX_GOALS][$row['idgoal']])) $this->interestByCustomVariablesAndValue[$row[$keyField]][$row[$valueField]][Piwik_Archive::INDEX_GOALS][$row['idgoal']] = $archiveProcessing->getNewGoalRow($row['idgoal']);
$archiveProcessing->updateGoalStats( $row, $this->interestByCustomVariables[$row[$keyField]][Piwik_Archive::INDEX_GOALS][$row['idgoal']]);
$archiveProcessing->updateGoalStats( $row, $this->interestByCustomVariablesAndValue[$row[$keyField]][$row[$valueField]][Piwik_Archive::INDEX_GOALS][$row['idgoal']]);
}
}
}
$archiveProcessing->enrichConversionsByLabelArray($this->interestByCustomVariables);
$archiveProcessing->enrichConversionsByLabelArrayHasTwoLevels($this->interestByCustomVariablesAndValue);
// var_dump($this->interestByCustomVariables);
// var_dump($this->interestByCustomVariablesAndValue);
}
/**
* Get information about the following actions (following pages, site searches, outlinks, downloads)
*
* @param $idaction
* @param $actionType
* @param Piwik_ArchiveProcessing_Day $archiveProcessing
* @param $limitBeforeGrouping
* @param $includeLoops
* @return array(followingPages:Piwik_DataTable, outlinks:Piwik_DataTable, downloads:Piwik_DataTable)
*/
public function queryFollowingActions($idaction, $actionType, Piwik_ArchiveProcessing_Day $archiveProcessing, $limitBeforeGrouping = false, $includeLoops = false)
{
$types = array();
$isTitle = $actionType == 'title';
if (!$isTitle) {
// specific setup for page urls
$types[Piwik_Tracker_Action::TYPE_ACTION_URL] = 'followingPages';
$dimension = 'IF( idaction_url IS NULL, idaction_name, idaction_url )';
// site search referrers are logged with url=NULL
// when we find one, we have to join on name
$joinLogActionColumn = $dimension;
$addSelect = 'log_action.name, log_action.url_prefix, log_action.type';
} else {
// specific setup for page titles:
$types[Piwik_Tracker_Action::TYPE_ACTION_NAME] = 'followingPages';
// join log_action on name and url and pick depending on url type
// the table joined on url is log_action1
$joinLogActionColumn = array('idaction_url', 'idaction_name');
$dimension = '
CASE
' . '
WHEN log_link_visit_action.idaction_url IS NULL THEN log_action2.idaction
' . '
WHEN log_action1.type = ' . Piwik_Tracker_Action::TYPE_ACTION_URL . ' THEN log_action2.idaction
' . '
ELSE log_action1.idaction
END
';
$addSelect = '
CASE
' . '
WHEN log_link_visit_action.idaction_url IS NULL THEN log_action2.name
' . '
WHEN log_action1.type = ' . Piwik_Tracker_Action::TYPE_ACTION_URL . ' THEN log_action2.name
' . '
ELSE log_action1.name
END AS name,
CASE
' . '
WHEN log_link_visit_action.idaction_url IS NULL THEN log_action2.type
' . '
WHEN log_action1.type = ' . Piwik_Tracker_Action::TYPE_ACTION_URL . ' THEN log_action2.type
' . '
ELSE log_action1.type
END AS type,
NULL AS url_prefix
';
}
// these types are available for both titles and urls
$types[Piwik_Tracker_Action::TYPE_SITE_SEARCH] = 'followingSiteSearches';
$types[Piwik_Tracker_Action::TYPE_OUTLINK] = 'outlinks';
$types[Piwik_Tracker_Action::TYPE_DOWNLOAD] = 'downloads';
$rankingQuery = new Piwik_RankingQuery($limitBeforeGrouping ? $limitBeforeGrouping : $this->limitBeforeGrouping);
$rankingQuery->addLabelColumn(array('name', 'url_prefix'));
$rankingQuery->partitionResultIntoMultipleGroups('type', array_keys($types));
$type = $this->getColumnTypeSuffix($actionType);
$where = 'log_link_visit_action.idaction_' . $type . '_ref = ' . intval($idaction);
if (!$includeLoops) {
$where .= ' AND (log_link_visit_action.idaction_' . $type . ' IS NULL OR ' . 'log_link_visit_action.idaction_' . $type . ' != ' . intval($idaction) . ')';
}
$orderBy = '`' . Piwik_Archive::INDEX_NB_ACTIONS . '` DESC';
$metrics = array(Piwik_Archive::INDEX_NB_ACTIONS);
$data = $archiveProcessing->queryActionsByDimension($dimension, $where, $metrics, $orderBy, $rankingQuery, $joinLogActionColumn, $addSelect);
$this->totalTransitionsToFollowingActions = 0;
$dataTables = array();
foreach ($types as $type => $recordName) {
$dataTable = new Piwik_DataTable();
if (isset($data[$type])) {
foreach ($data[$type] as &$record) {
$actions = intval($record[Piwik_Archive::INDEX_NB_ACTIONS]);
$dataTable->addRow(new Piwik_DataTable_Row(array(Piwik_DataTable_Row::COLUMNS => array('label' => $this->getPageLabel($record, $isTitle), Piwik_Archive::INDEX_NB_ACTIONS => $actions))));
$this->totalTransitionsToFollowingActions += $actions;
}
}
$dataTables[$recordName] = $dataTable;
}
return $dataTables;
}
public function archiveDay($notification)
{
$this->archiveProcessing = $notification->getNotificationObject();
if (!$this->archiveProcessing->shouldProcessReportsForPlugin($this->getPluginName())) {
return;
}
// these prefixes are prepended to the 'SELECT as' parts of each SELECT expression. detecting
// these prefixes allows us to get all the data in one query.
$timeGapPrefix = 'tg';
$pageGapPrefix = 'pg';
$visitsByVisitNumPrefix = 'vbvn';
$daysSinceLastVisitPrefix = 'dslv';
// extra condition for the SQL SELECT that makes sure only returning visits are counted
// when creating the 'days since last visit' report. the SELECT expression below it
// is used to count all new visits.
$daysSinceLastExtraCondition = 'and log_visit.visitor_returning = 1';
$selectAs = $daysSinceLastVisitPrefix . 'General_NewVisits';
$newVisitCountSelect = "sum(case when log_visit.visitor_returning = 0 then 1 else 0 end) as `{$selectAs}`";
// create the select expressions to use
$timeGapSelects = Piwik_ArchiveProcessing_Day::buildReduceByRangeSelect('visit_total_time', self::getSecondsGap(), 'log_visit', $timeGapPrefix);
$pageGapSelects = Piwik_ArchiveProcessing_Day::buildReduceByRangeSelect('visit_total_actions', self::$pageGap, 'log_visit', $pageGapPrefix);
$visitsByVisitNumSelects = Piwik_ArchiveProcessing_Day::buildReduceByRangeSelect('visitor_count_visits', self::$visitNumberGap, 'log_visit', $visitsByVisitNumPrefix);
$daysSinceLastVisitSelects = Piwik_ArchiveProcessing_Day::buildReduceByRangeSelect('visitor_days_since_last', self::$daysSinceLastVisitGap, 'log_visit', $daysSinceLastVisitPrefix, $daysSinceLastExtraCondition);
array_unshift($daysSinceLastVisitSelects, $newVisitCountSelect);
$selects = array_merge($timeGapSelects, $pageGapSelects, $visitsByVisitNumSelects, $daysSinceLastVisitSelects);
// select data for every report
$row = $this->archiveProcessing->queryVisitsSimple(implode(',', $selects));
// archive visits by total time report
$recordName = 'VisitorInterest_timeGap';
$this->archiveRangeStats($recordName, $row, Piwik_Archive::INDEX_NB_VISITS, $timeGapPrefix);
// archive visits by total actions report
$recordName = 'VisitorInterest_pageGap';
$this->archiveRangeStats($recordName, $row, Piwik_Archive::INDEX_NB_VISITS, $pageGapPrefix);
// archive visits by visit number report
$recordName = 'VisitorInterest_visitsByVisitCount';
$this->archiveRangeStats($recordName, $row, Piwik_Archive::INDEX_NB_VISITS, $visitsByVisitNumPrefix);
// archive days since last visit report
$recordName = 'VisitorInterest_daysSinceLastVisit';
$this->archiveRangeStats($recordName, $row, Piwik_Archive::INDEX_NB_VISITS, $daysSinceLastVisitPrefix);
}
/**
* 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;
}
/**
* @param Piwik_ArchiveProcessing_Day $archiveProcessing
*/
protected function archiveDayRecordInDatabase($archiveProcessing)
{
$maximumRows = Piwik_Config::getInstance()->General['datatable_archiving_maximum_rows_standard'];
$tableCountry = Piwik_ArchiveProcessing_Day::getDataTableFromArray($this->interestTables['location_country']);
$archiveProcessing->insertBlobRecord(self::VISITS_BY_COUNTRY_RECORD_NAME, $tableCountry->getSerialized());
$archiveProcessing->insertNumericRecord(self::DISTINCT_COUNTRIES_METRIC, $tableCountry->getRowsCount());
destroy($tableCountry);
$tableRegion = Piwik_ArchiveProcessing_Day::getDataTableFromArray($this->interestTables['location_region']);
$serialized = $tableRegion->getSerialized($maximumRows, $maximumRows, Piwik_Archive::INDEX_NB_VISITS);
$archiveProcessing->insertBlobRecord(self::VISITS_BY_REGION_RECORD_NAME, $serialized);
destroy($tableRegion);
$tableCity = Piwik_ArchiveProcessing_Day::getDataTableFromArray($this->interestTables['location_city']);
$this->setLatitudeLongitude($tableCity);
$serialized = $tableCity->getSerialized($maximumRows, $maximumRows, Piwik_Archive::INDEX_NB_VISITS);
$archiveProcessing->insertBlobRecord(self::VISITS_BY_CITY_RECORD_NAME, $serialized);
destroy($tableCity);
}
/**
* @param Piwik_ArchiveProcessing_Day $archiveProcessing
*/
function archiveGeneralGoalMetrics($archiveProcessing)
{
$query = $archiveProcessing->queryConversionsByDimension('');
if($query === false) { return; }
$goals = array();
// Get a standard empty goal row
$overall = $archiveProcessing->getNewGoalRow( $idGoal = 1);
while($row = $query->fetch() )
{
if(!isset($goals[$row['idgoal']])) $goals[$row['idgoal']] = $archiveProcessing->getNewGoalRow($row['idgoal']);
$archiveProcessing->updateGoalStats($row, $goals[$row['idgoal']]);
// We don't want to sum Abandoned cart metrics in the overall revenue/conversions/converted visits
// since it is a "negative conversion"
if($row['idgoal'] != Piwik_Tracker_GoalManager::IDGOAL_CART)
{
$archiveProcessing->updateGoalStats($row, $overall);
}
}
// Stats by goal, for all visitors
foreach($goals as $idgoal => $values)
{
foreach($values as $metricId => $value)
{
$metricName = Piwik_Archive::$mappingFromIdToNameGoal[$metricId];
$recordName = self::getRecordName($metricName, $idgoal);
$archiveProcessing->insertNumericRecord($recordName, $value);
}
$conversion_rate = $this->getConversionRate($values[Piwik_Archive::INDEX_GOAL_NB_VISITS_CONVERTED], $archiveProcessing);
$recordName = self::getRecordName('conversion_rate', $idgoal);
$archiveProcessing->insertNumericRecord($recordName, $conversion_rate);
}
// Stats for all goals
$totalAllGoals = array(
self::getRecordName('conversion_rate') => $this->getConversionRate($archiveProcessing->getNumberOfVisitsConverted(), $archiveProcessing),
self::getRecordName('nb_conversions') => $overall[Piwik_Archive::INDEX_GOAL_NB_CONVERSIONS],
self::getRecordName('nb_visits_converted') => $archiveProcessing->getNumberOfVisitsConverted(),
self::getRecordName('revenue') => $overall[Piwik_Archive::INDEX_GOAL_REVENUE],
);
foreach($totalAllGoals as $recordName => $value)
{
$archiveProcessing->insertNumericRecord($recordName, $value);
}
}
/**
* Ordnet f�r den gegebenen Archivzeitraum allen Digitalisat-URLs Kollektionen zu,
* und archiviert diese.
* @param Piwik_ArchiveProcessing_Day $archiveProcessing
* @return Piwik_DataTable
*/
private function getCollectionData($archiveProcessing)
{
Piwik_cdebug::clog('getCollectionData');
$collections = array();
$query = $archiveProcessing->queryActionsByDimension('idaction_url');
while ($row = $query->fetch()) {
$url = Piwik_FetchOne("SELECT name FROM " . Piwik_Common::prefixTable("log_action") . " WHERE idaction = ?", $row["label"]);
$idcollections = $this->getCollectionsForURL($url);
if (!is_array($idcollections) || count($idcollections) < 1) {
continue;
}
foreach ($idcollections as $c) {
if (!array_key_exists($c, $collections)) {
$collections[$c] = array(self::$valuefield => 1);
} else {
$collections[$c][self::$valuefield]++;
}
}
}
return $collections;
}
