You can use the methods in this class within {@link Piwik\Plugin\Archiver Archiver} descendants to aggregate log data without having to write SQL queries. ### Aggregation Dimension All aggregation methods accept a **dimension** parameter. These parameters are important as they control how rows in a table are aggregated together. A **_dimension_** is just a table column. Rows that have the same values for these columns are aggregated together. The result of these aggregations is a set of metrics for every recorded value of a **dimension**. _Note: A dimension is essentially the same as a **GROUP BY** field._ ### Examples **Aggregating visit data** $archiveProcessor = // ... $logAggregator = $archiveProcessor->getLogAggregator(); get metrics for every used browser language of all visits by returning visitors $query = $logAggregator->queryVisitsByDimension( $dimensions = array('log_visit.location_browser_lang'), $where = 'log_visit.visitor_returning = 1', also count visits for each browser language that are not located in the US $additionalSelects = array('sum(case when log_visit.location_country <> 'us' then 1 else 0 end) as nonus'), we're only interested in visits, unique visitors & actions, so don't waste time calculating anything else $metrics = array(Metrics::INDEX_NB_UNIQ_VISITORS, Metrics::INDEX_NB_VISITS, Metrics::INDEX_NB_ACTIONS), ); if ($query === false) { return; } while ($row = $query->fetch()) { $uniqueVisitors = $row[Metrics::INDEX_NB_UNIQ_VISITORS]; $visits = $row[Metrics::INDEX_NB_VISITS]; $actions = $row[Metrics::INDEX_NB_ACTIONS]; ... do something w/ calculated metrics ... } **Aggregating conversion data** $archiveProcessor = // ... $logAggregator = $archiveProcessor->getLogAggregator(); get metrics for ecommerce conversions for each country $query = $logAggregator->queryConversionsByDimension( $dimensions = array('log_conversion.location_country'), $where = 'log_conversion.idgoal = 0', // 0 is the special ecommerceOrder idGoal value in the table also calculate average tax and max shipping per country $additionalSelects = array( 'AVG(log_conversion.revenue_tax) as avg_tax', 'MAX(log_conversion.revenue_shipping) as max_shipping' ) ); if ($query === false) { return; } while ($row = $query->fetch()) { $country = $row['location_country']; $numEcommerceSales = $row[Metrics::INDEX_GOAL_NB_CONVERSIONS]; $numVisitsWithEcommerceSales = $row[Metrics::INDEX_GOAL_NB_VISITS_CONVERTED]; $avgTaxForCountry = $row['avg_tax']; $maxShippingForCountry = $row['max_shipping']; ... do something with aggregated data ... }
コード例 #1
0
    public function test_generateQuery_WithQueryHint_ShouldAddQueryHintAsComment()
    {
        $this->logAggregator->setQueryOriginHint('MyPluginName');
        $query = $this->logAggregator->generateQuery('test, test2', 'log_visit', '1=1', false, '5');
        $expected = array('sql' => 'SELECT /* MyPluginName */
				test, test2
			FROM
				log_visit AS log_visit
			WHERE
				1=1
			ORDER BY
				5', 'bind' => array(0 => '2012-01-01 00:00:00', 1 => '2012-01-31 23:59:59', 2 => 1));
        $this->assertSame($expected, $query);
    }
コード例 #2
0
ファイル: Model.php プロジェクト: piwik/piwik
 /**
  * @param $idVisit
  * @param $idOrder
  * @param $actionsLimit
  * @return array
  * @throws \Exception
  */
 public function queryEcommerceItemsForOrder($idVisit, $idOrder, $actionsLimit)
 {
     $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" . LogAggregator::getSqlRevenue('price') . " as price,\n\t\t\t\t\t\t\tquantity as quantity\n\t\t\t\t\t\tFROM " . Common::prefixTable('log_conversion_item') . "\n\t\t\t\t\t\t\tINNER JOIN " . 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 " . 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 " . 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\t\tLIMIT 0, {$actionsLimit}\n\t\t\t\t";
     $bind = array($idVisit, $idOrder);
     $itemsDetails = Db::fetchAll($sql, $bind);
     return $itemsDetails;
 }
コード例 #3
0
ファイル: PluginsArchiver.php プロジェクト: halabuda/piwik
 /**
  * Instantiates the Archiver class in each plugin that defines it,
  * and triggers Aggregation processing on these plugins.
  */
 public function callAggregateAllPlugins($visits, $visitsConverted)
 {
     Log::debug("PluginsArchiver::%s: Initializing archiving process for all plugins [visits = %s, visits converted = %s]", __FUNCTION__, $visits, $visitsConverted);
     $this->archiveProcessor->setNumberOfVisits($visits, $visitsConverted);
     $archivers = $this->getPluginArchivers();
     foreach ($archivers as $pluginName => $archiverClass) {
         // We clean up below all tables created during this function call (and recursive calls)
         $latestUsedTableId = Manager::getInstance()->getMostRecentTableId();
         /** @var Archiver $archiver */
         $archiver = new $archiverClass($this->archiveProcessor);
         if (!$archiver->isEnabled()) {
             Log::debug("PluginsArchiver::%s: Skipping archiving for plugin '%s'.", __FUNCTION__, $pluginName);
             continue;
         }
         if ($this->shouldProcessReportsForPlugin($pluginName)) {
             $this->logAggregator->setQueryOriginHint($pluginName);
             $timer = new Timer();
             if ($this->isSingleSiteDayArchive) {
                 Log::debug("PluginsArchiver::%s: Archiving day reports for plugin '%s'.", __FUNCTION__, $pluginName);
                 $archiver->aggregateDayReport();
             } else {
                 Log::debug("PluginsArchiver::%s: Archiving period reports for plugin '%s'.", __FUNCTION__, $pluginName);
                 $archiver->aggregateMultipleReports();
             }
             $this->logAggregator->setQueryOriginHint('');
             Log::debug("PluginsArchiver::%s: %s while archiving %s reports for plugin '%s'.", __FUNCTION__, $timer->getMemoryLeak(), $this->params->getPeriod()->getLabel(), $pluginName);
         } else {
             Log::debug("PluginsArchiver::%s: Not archiving reports for plugin '%s'.", __FUNCTION__, $pluginName);
         }
         Manager::getInstance()->deleteAll($latestUsedTableId);
         unset($archiver);
     }
 }
コード例 #4
0
ファイル: Archiver.php プロジェクト: FluentDevelopment/piwik
 protected function aggregateByPlugin()
 {
     $selects = array("sum(case log_visit.config_pdf when 1 then 1 else 0 end) as pdf", "sum(case log_visit.config_flash when 1 then 1 else 0 end) as flash", "sum(case log_visit.config_java when 1 then 1 else 0 end) as java", "sum(case log_visit.config_director when 1 then 1 else 0 end) as director", "sum(case log_visit.config_quicktime when 1 then 1 else 0 end) as quicktime", "sum(case log_visit.config_realplayer when 1 then 1 else 0 end) as realplayer", "sum(case log_visit.config_windowsmedia when 1 then 1 else 0 end) as windowsmedia", "sum(case log_visit.config_gears when 1 then 1 else 0 end) as gears", "sum(case log_visit.config_silverlight when 1 then 1 else 0 end) as silverlight", "sum(case log_visit.config_cookie when 1 then 1 else 0 end) as cookie");
     $query = $this->getLogAggregator()->queryVisitsByDimension(array(), false, $selects, $metrics = array());
     $data = $query->fetch();
     $cleanRow = LogAggregator::makeArrayOneColumn($data, Metrics::INDEX_NB_VISITS);
     $table = DataTable::makeFromIndexedArray($cleanRow);
     $this->insertTable(self::PLUGIN_RECORD_NAME, $table);
 }
コード例 #5
0
ファイル: Archiver.php プロジェクト: a4tunado/piwik
 protected function aggregateGeneralGoalMetrics()
 {
     $prefixes = array(self::VISITS_UNTIL_RECORD_NAME => 'vcv', self::DAYS_UNTIL_CONV_RECORD_NAME => 'vdsf');
     $selects = array();
     $selects = array_merge($selects, LogAggregator::getSelectsFromRangedColumn(self::VISITS_COUNT_FIELD, self::$visitCountRanges, self::LOG_CONVERSION_TABLE, $prefixes[self::VISITS_UNTIL_RECORD_NAME]));
     $selects = array_merge($selects, LogAggregator::getSelectsFromRangedColumn(self::DAYS_SINCE_FIRST_VISIT_FIELD, self::$daysToConvRanges, self::LOG_CONVERSION_TABLE, $prefixes[self::DAYS_UNTIL_CONV_RECORD_NAME]));
     $query = $this->getLogAggregator()->queryConversionsByDimension(array(), false, $selects);
     if ($query === false) {
         return;
     }
     $totalConversions = $totalRevenue = 0;
     $goals = new DataArray();
     $visitsToConversions = $daysToConversions = array();
     $conversionMetrics = $this->getLogAggregator()->getConversionsMetricFields();
     while ($row = $query->fetch()) {
         $idGoal = $row['idgoal'];
         unset($row['idgoal']);
         unset($row['label']);
         $values = array();
         foreach ($conversionMetrics as $field => $statement) {
             $values[$field] = $row[$field];
         }
         $goals->sumMetrics($idGoal, $values);
         if (empty($visitsToConversions[$idGoal])) {
             $visitsToConversions[$idGoal] = new DataTable();
         }
         $array = LogAggregator::makeArrayOneColumn($row, Metrics::INDEX_NB_CONVERSIONS, $prefixes[self::VISITS_UNTIL_RECORD_NAME]);
         $visitsToConversions[$idGoal]->addDataTable(DataTable::makeFromIndexedArray($array));
         if (empty($daysToConversions[$idGoal])) {
             $daysToConversions[$idGoal] = new DataTable();
         }
         $array = LogAggregator::makeArrayOneColumn($row, Metrics::INDEX_NB_CONVERSIONS, $prefixes[self::DAYS_UNTIL_CONV_RECORD_NAME]);
         $daysToConversions[$idGoal]->addDataTable(DataTable::makeFromIndexedArray($array));
         // We don't want to sum Abandoned cart metrics in the overall revenue/conversions/converted visits
         // since it is a "negative conversion"
         if ($idGoal != GoalManager::IDGOAL_CART) {
             $totalConversions += $row[Metrics::INDEX_GOAL_NB_CONVERSIONS];
             $totalRevenue += $row[Metrics::INDEX_GOAL_REVENUE];
         }
     }
     // Stats by goal, for all visitors
     $numericRecords = $this->getConversionsNumericMetrics($goals);
     $this->getProcessor()->insertNumericRecords($numericRecords);
     $this->insertReports(self::VISITS_UNTIL_RECORD_NAME, $visitsToConversions);
     $this->insertReports(self::DAYS_UNTIL_CONV_RECORD_NAME, $daysToConversions);
     // Stats for all goals
     $nbConvertedVisits = $this->getProcessor()->getNumberOfVisitsConverted();
     $metrics = array(self::getRecordName('conversion_rate') => $this->getConversionRate($nbConvertedVisits), self::getRecordName('nb_conversions') => $totalConversions, self::getRecordName('nb_visits_converted') => $nbConvertedVisits, self::getRecordName('revenue') => $totalRevenue);
     $this->getProcessor()->insertNumericRecords($metrics);
 }
コード例 #6
0
ファイル: Archiver.php プロジェクト: carriercomm/piwik
 public function aggregateDayReport()
 {
     // 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.
     $prefixes = array(self::TIME_SPENT_RECORD_NAME => 'tg', self::PAGES_VIEWED_RECORD_NAME => 'pg', self::VISITS_COUNT_RECORD_NAME => 'vbvn', self::DAYS_SINCE_LAST_RECORD_NAME => 'dslv');
     // collect our extra aggregate select fields
     $selects = array();
     $selects = array_merge($selects, LogAggregator::getSelectsFromRangedColumn('visit_total_time', self::getSecondsGap(), 'log_visit', $prefixes[self::TIME_SPENT_RECORD_NAME]));
     $selects = array_merge($selects, LogAggregator::getSelectsFromRangedColumn('visit_total_actions', self::$pageGap, 'log_visit', $prefixes[self::PAGES_VIEWED_RECORD_NAME]));
     $selects = array_merge($selects, LogAggregator::getSelectsFromRangedColumn('visitor_count_visits', self::$visitNumberGap, 'log_visit', $prefixes[self::VISITS_COUNT_RECORD_NAME]));
     $selects = array_merge($selects, LogAggregator::getSelectsFromRangedColumn('visitor_days_since_last', self::$daysSinceLastVisitGap, 'log_visit', $prefixes[self::DAYS_SINCE_LAST_RECORD_NAME], $restrictToReturningVisitors = true));
     $query = $this->getLogAggregator()->queryVisitsByDimension(array(), $where = false, $selects, array());
     $row = $query->fetch();
     foreach ($prefixes as $recordName => $selectAsPrefix) {
         $cleanRow = LogAggregator::makeArrayOneColumn($row, Metrics::INDEX_NB_VISITS, $selectAsPrefix);
         $dataTable = DataTable::makeFromIndexedArray($cleanRow);
         $this->getProcessor()->insertBlobRecord($recordName, $dataTable->getSerialized());
     }
 }
コード例 #7
0
ファイル: Visitor.php プロジェクト: KingNoosh/Teknik
 /**
  * @param $visitorDetailsArray
  * @param $actionsLimit
  * @param $timezone
  * @return array
  */
 public static function enrichVisitorArrayWithActions($visitorDetailsArray, $actionsLimit, $timezone)
 {
     $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 = "\n\t\t\t\tSELECT\n\t\t\t\t\tCOALESCE(log_action_event_category.type, log_action.type, log_action_title.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.server_time as serverTimePretty,\n\t\t\t\t\tlog_link_visit_action.time_spent_ref_action as timeSpentRef,\n\t\t\t\t\tlog_link_visit_action.idlink_va AS pageId,\n\t\t\t\t\tlog_link_visit_action.custom_float\n\t\t\t\t\t" . $sqlCustomVariables . ",\n\t\t\t\t\tlog_action_event_category.name AS eventCategory,\n\t\t\t\t\tlog_action_event_action.name as eventAction\n\t\t\t\tFROM " . Common::prefixTable('log_link_visit_action') . " AS log_link_visit_action\n\t\t\t\t\tLEFT JOIN " . 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 " . 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\t\tLEFT JOIN " . Common::prefixTable('log_action') . " AS log_action_event_category\n\t\t\t\t\tON  log_link_visit_action.idaction_event_category = log_action_event_category.idaction\n\t\t\t\t\tLEFT JOIN " . Common::prefixTable('log_action') . " AS log_action_event_action\n\t\t\t\t\tON  log_link_visit_action.idaction_event_action = log_action_event_action.idaction\n\t\t\t\tWHERE log_link_visit_action.idvisit = ?\n\t\t\t\tORDER BY server_time ASC\n\t\t\t\tLIMIT 0, {$actionsLimit}\n\t\t\t\t ";
     $actionDetails = Db::fetchAll($sql, array($idVisit));
     foreach ($actionDetails as $actionIdx => &$actionDetail) {
         $actionDetail =& $actionDetails[$actionIdx];
         $customVariablesPage = array();
         for ($i = 1; $i <= $maxCustomVariables; $i++) {
             if (!empty($actionDetail['custom_var_k' . $i])) {
                 $cvarKey = $actionDetail['custom_var_k' . $i];
                 $cvarKey = static::getCustomVariablePrettyKey($cvarKey);
                 $customVariablesPage[$i] = array('customVariablePageName' . $i => $cvarKey, 'customVariablePageValue' . $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 ($actionDetail['type'] == Action::TYPE_CONTENT) {
             unset($actionDetails[$actionIdx]);
             continue;
         } elseif ($actionDetail['type'] == Action::TYPE_EVENT_CATEGORY) {
             // Handle Event
             if (strlen($actionDetail['pageTitle']) > 0) {
                 $actionDetail['eventName'] = $actionDetail['pageTitle'];
             }
             unset($actionDetail['pageTitle']);
         } else {
             if ($actionDetail['type'] == Action::TYPE_SITE_SEARCH) {
                 // Handle Site Search
                 $actionDetail['siteSearchKeyword'] = $actionDetail['pageTitle'];
                 unset($actionDetail['pageTitle']);
             }
         }
         // Event value / Generation time
         if ($actionDetail['type'] == Action::TYPE_EVENT_CATEGORY) {
             if (strlen($actionDetail['custom_float']) > 0) {
                 $actionDetail['eventValue'] = round($actionDetail['custom_float'], self::EVENT_VALUE_PRECISION);
             }
         } elseif ($actionDetail['custom_float'] > 0) {
             $actionDetail['generationTime'] = \Piwik\MetricsFormatter::getPrettyTimeFromSeconds($actionDetail['custom_float'] / 1000);
         }
         unset($actionDetail['custom_float']);
         if ($actionDetail['type'] != Action::TYPE_EVENT_CATEGORY) {
             unset($actionDetail['eventCategory']);
             unset($actionDetail['eventAction']);
         }
         // Reconstruct url from prefix
         $actionDetail['url'] = Tracker\PageUrl::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\MetricsFormatter::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.idgoal as goalId,\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 " . Common::prefixTable('log_conversion') . " AS log_conversion\n\t\t\t\tLEFT JOIN " . 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                ORDER BY server_time ASC\n\t\t\t\tLIMIT 0, {$actionsLimit}\n\t\t\t";
     $goalDetails = Db::fetchAll($sql, array($idVisit));
     $sql = "SELECT\n\t\t\t\t\t\tcase idgoal when " . GoalManager::IDGOAL_CART . " then '" . Piwik::LABEL_ID_GOAL_IS_ECOMMERCE_CART . "' else '" . Piwik::LABEL_ID_GOAL_IS_ECOMMERCE_ORDER . "' end as type,\n\t\t\t\t\t\tidorder as orderId,\n\t\t\t\t\t\t" . LogAggregator::getSqlRevenue('revenue') . " as revenue,\n\t\t\t\t\t\t" . LogAggregator::getSqlRevenue('revenue_subtotal') . " as revenueSubTotal,\n\t\t\t\t\t\t" . LogAggregator::getSqlRevenue('revenue_tax') . " as revenueTax,\n\t\t\t\t\t\t" . LogAggregator::getSqlRevenue('revenue_shipping') . " as revenueShipping,\n\t\t\t\t\t\t" . LogAggregator::getSqlRevenue('revenue_discount') . " as revenueDiscount,\n\t\t\t\t\t\titems as items,\n\n\t\t\t\t\t\tlog_conversion.server_time as serverTimePretty\n\t\t\t\t\tFROM " . Common::prefixTable('log_conversion') . " AS log_conversion\n\t\t\t\t\tWHERE idvisit = ?\n\t\t\t\t\t\tAND idgoal <= " . GoalManager::IDGOAL_ORDER . "\n\t\t\t\t\tORDER BY server_time ASC\n\t\t\t\t\tLIMIT 0, {$actionsLimit}";
     $ecommerceDetails = Db::fetchAll($sql, array($idVisit));
     foreach ($ecommerceDetails as &$ecommerceDetail) {
         if ($ecommerceDetail['type'] == Piwik::LABEL_ID_GOAL_IS_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('static', 'sortByServerTime'));
     foreach ($ecommerceDetails as &$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" . LogAggregator::getSqlRevenue('price') . " as price,\n\t\t\t\t\t\t\tquantity as quantity\n\t\t\t\t\t\tFROM " . Common::prefixTable('log_conversion_item') . "\n\t\t\t\t\t\t\tINNER JOIN " . 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 " . 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 " . 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\t\tLIMIT 0, {$actionsLimit}\n\t\t\t\t";
         $bind = array($idVisit, isset($ecommerceConversion['orderId']) ? $ecommerceConversion['orderId'] : GoalManager::ITEM_IDORDER_ABANDONED_CART);
         $itemsDetails = Db::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('static', 'sortByServerTime'));
     $visitorDetailsArray['actionDetails'] = $actions;
     foreach ($visitorDetailsArray['actionDetails'] as &$details) {
         switch ($details['type']) {
             case 'goal':
                 $details['icon'] = 'plugins/Morpheus/images/goal.png';
                 break;
             case Piwik::LABEL_ID_GOAL_IS_ECOMMERCE_ORDER:
             case Piwik::LABEL_ID_GOAL_IS_ECOMMERCE_CART:
                 $details['icon'] = 'plugins/Morpheus/images/' . $details['type'] . '.gif';
                 break;
             case Action::TYPE_DOWNLOAD:
                 $details['type'] = 'download';
                 $details['icon'] = 'plugins/Morpheus/images/download.png';
                 break;
             case Action::TYPE_OUTLINK:
                 $details['type'] = 'outlink';
                 $details['icon'] = 'plugins/Morpheus/images/link.gif';
                 break;
             case Action::TYPE_SITE_SEARCH:
                 $details['type'] = 'search';
                 $details['icon'] = 'plugins/Morpheus/images/search_ico.png';
                 break;
             case Action::TYPE_EVENT_CATEGORY:
                 $details['type'] = 'event';
                 $details['icon'] = 'plugins/Morpheus/images/event.png';
                 break;
             default:
                 $details['type'] = 'action';
                 $details['icon'] = null;
                 break;
         }
         // Convert datetimes to the site timezone
         $dateTimeVisit = Date::factory($details['serverTimePretty'], $timezone);
         $details['serverTimePretty'] = $dateTimeVisit->getLocalized(Piwik::translate('CoreHome_ShortDateFormat') . ' %time%');
     }
     $visitorDetailsArray['goalConversions'] = count($goalDetails);
     return $visitorDetailsArray;
 }
コード例 #8
0
ファイル: API.php プロジェクト: a4tunado/piwik
 /**
  * Get information about internal referrers (previous pages & loops, i.e. page refreshes)
  *
  * @param $idaction
  * @param $actionType
  * @param LogAggregator $logAggregator
  * @param $limitBeforeGrouping
  * @return array(previousPages:DataTable, loops:integer)
  */
 protected function queryInternalReferrers($idaction, $actionType, $logAggregator, $limitBeforeGrouping = false)
 {
     $keyIsOther = 0;
     $keyIsPageUrlAction = 1;
     $keyIsSiteSearchAction = 2;
     $rankingQuery = new RankingQuery($limitBeforeGrouping ? $limitBeforeGrouping : $this->limitBeforeGrouping);
     $rankingQuery->addLabelColumn(array('name', 'url_prefix'));
     $rankingQuery->setColumnToMarkExcludedRows('is_self');
     $rankingQuery->partitionResultIntoMultipleGroups('action_partition', array($keyIsOther, $keyIsPageUrlAction, $keyIsSiteSearchAction));
     $type = $this->getColumnTypeSuffix($actionType);
     $mainActionType = Action::TYPE_PAGE_URL;
     $dimension = 'idaction_url_ref';
     if ($actionType == 'title') {
         $mainActionType = Action::TYPE_PAGE_TITLE;
         $dimension = 'idaction_name_ref';
     }
     $selects = array('log_action.name', 'log_action.url_prefix', 'CASE WHEN log_link_visit_action.idaction_' . $type . '_ref = ' . intval($idaction) . ' THEN 1 ELSE 0 END AS `is_self`', 'CASE
             WHEN log_action.type = ' . $mainActionType . ' THEN ' . $keyIsPageUrlAction . '
                     WHEN log_action.type = ' . Action::TYPE_SITE_SEARCH . ' THEN ' . $keyIsSiteSearchAction . '
                     ELSE ' . $keyIsOther . '
                 END AS `action_partition`');
     $where = ' log_link_visit_action.idaction_' . $type . ' = ' . intval($idaction);
     if ($dimension == 'idaction_url_ref') {
         // site search referrers are logged with url_ref=NULL
         // when we find one, we have to join on name_ref
         $dimension = 'IF( idaction_url_ref IS NULL, idaction_name_ref, idaction_url_ref )';
         $joinLogActionOn = $dimension;
     } else {
         $joinLogActionOn = $dimension;
     }
     $metrics = array(Metrics::INDEX_NB_ACTIONS);
     $data = $logAggregator->queryActionsByDimension(array($dimension), $where, $selects, $metrics, $rankingQuery, $joinLogActionOn);
     $loops = 0;
     $nbPageviews = 0;
     $previousPagesDataTable = new DataTable();
     if (isset($data['result'][$keyIsPageUrlAction])) {
         foreach ($data['result'][$keyIsPageUrlAction] as &$page) {
             $nbActions = intval($page[Metrics::INDEX_NB_ACTIONS]);
             $previousPagesDataTable->addRow(new Row(array(Row::COLUMNS => array('label' => $this->getPageLabel($page, Action::TYPE_PAGE_URL), Metrics::INDEX_NB_ACTIONS => $nbActions))));
             $nbPageviews += $nbActions;
         }
     }
     $previousSearchesDataTable = new DataTable();
     if (isset($data['result'][$keyIsSiteSearchAction])) {
         foreach ($data['result'][$keyIsSiteSearchAction] as &$search) {
             $nbActions = intval($search[Metrics::INDEX_NB_ACTIONS]);
             $previousSearchesDataTable->addRow(new Row(array(Row::COLUMNS => array('label' => $search['name'], Metrics::INDEX_NB_ACTIONS => $nbActions))));
             $nbPageviews += $nbActions;
         }
     }
     if (isset($data['result'][0])) {
         foreach ($data['result'][0] as &$referrer) {
             $nbPageviews += intval($referrer[Metrics::INDEX_NB_ACTIONS]);
         }
     }
     if (count($data['excludedFromLimit'])) {
         $loops += intval($data['excludedFromLimit'][0][Metrics::INDEX_NB_ACTIONS]);
         $nbPageviews += $loops;
     }
     return array('pageviews' => $nbPageviews, 'previousPages' => $previousPagesDataTable, 'previousSiteSearches' => $previousSearchesDataTable, 'loops' => $loops);
 }
コード例 #9
0
ファイル: Archiver.php プロジェクト: TensorWrenchOSS/piwik
 protected function getSelectAveragePrice()
 {
     $field = "custom_var_v" . \PiwikTracker::CVAR_INDEX_ECOMMERCE_ITEM_PRICE;
     return LogAggregator::getSqlRevenue("AVG(log_link_visit_action." . $field . ")") . " as `" . Metrics::INDEX_ECOMMERCE_ITEM_PRICE_VIEWED . "`";
 }