/** * 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; }