| <?php |
|
|
| |
| |
| |
| |
| |
| |
|
|
| namespace Piwik\Plugins\Ecommerce; |
|
|
| use Piwik\Common; |
| use Piwik\Config; |
| use Piwik\DataAccess\LogAggregator; |
| use Piwik\Date; |
| use Piwik\DbHelper; |
| use Piwik\Piwik; |
| use Piwik\Plugins\Ecommerce\Columns\ProductCategory; |
| use Piwik\Plugins\Live\Model; |
| use Piwik\Plugins\Live\VisitorDetailsAbstract; |
| use Piwik\Site; |
| use Piwik\Tracker\GoalManager; |
| use Piwik\View; |
|
|
| use function Piwik\Plugins\Referrers\getReferrerTypeFromShortName; |
|
|
| class VisitorDetails extends VisitorDetailsAbstract |
| { |
| public const CATEGORY_COUNT = 5; |
| public const DEFAULT_LIFETIME_STAT = array( |
| 'lifeTimeRevenue' => 0, |
| 'lifeTimeConversions' => 0, |
| 'lifeTimeEcommerceItems' => 0); |
|
|
| public function extendVisitorDetails(&$visitor) |
| { |
| if (Site::isEcommerceEnabledFor($visitor['idSite'])) { |
| $ecommerceMetrics = $this->queryEcommerceConversionsVisitorLifeTimeMetricsForVisitor( |
| $visitor['idSite'], |
| $visitor['visitorId'] |
| ); |
| $visitor['totalEcommerceRevenue'] = $ecommerceMetrics['totalEcommerceRevenue']; |
| $visitor['totalEcommerceConversions'] = $ecommerceMetrics['totalEcommerceConversions']; |
| $visitor['totalEcommerceItems'] = $ecommerceMetrics['totalEcommerceItems']; |
|
|
| $visitor['totalAbandonedCartsRevenue'] = $ecommerceMetrics['totalAbandonedCartsRevenue']; |
| $visitor['totalAbandonedCarts'] = $ecommerceMetrics['totalAbandonedCarts']; |
| $visitor['totalAbandonedCartsItems'] = $ecommerceMetrics['totalAbandonedCartsItems']; |
| } |
| } |
|
|
| public function extendActionDetails(&$action, $nextAction, $visitorDetails) |
| { |
| if (empty($action['productViewName'])) { |
| unset($action['productViewName']); |
| } |
| if (empty($action['productViewSku'])) { |
| unset($action['productViewSku']); |
| } |
| if (empty($action['productViewPrice'])) { |
| unset($action['productViewPrice']); |
| } |
|
|
| $categories = []; |
| for ($i = 1; $i <= ProductCategory::PRODUCT_CATEGORY_COUNT; $i++) { |
| if (!empty($action['productViewCategory' . $i])) { |
| $categories[] = $action['productViewCategory' . $i]; |
| } |
|
|
| unset($action['productViewCategory' . $i]); |
| } |
| if (!empty($categories)) { |
| $action['productViewCategories'] = $categories; |
| } |
| } |
|
|
| public function renderActionTooltip($action, $visitInfo) |
| { |
| if ( |
| !isset($action['productViewName']) && !isset($action['productViewSku']) && |
| !isset($action['productViewPrice']) && !isset($action['productViewCategories']) |
| ) { |
| return []; |
| } |
|
|
| $view = new View('@Ecommerce/_actionTooltip'); |
| $view->sendHeadersWhenRendering = false; |
| $view->action = $action; |
| $view->visitInfo = $visitInfo; |
| return [[ 15, $view->render() ]]; |
| } |
|
|
| public function provideActionsForVisitIds(&$actions, $idVisits) |
| { |
| $ecommerceDetails = $this->queryEcommerceConversionsForVisits($idVisits); |
|
|
| |
| while (is_array($ecommerceDetails) && count($ecommerceDetails)) { |
| $ecommerceDetail = array_shift($ecommerceDetails); |
|
|
| $idVisit = $ecommerceDetail['idvisit']; |
|
|
| unset($ecommerceDetail['idvisit']); |
|
|
| 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']); |
| } |
|
|
| $ecommerceDetail['referrerType'] = $this->getReferrerType($ecommerceDetail['referrerType']); |
|
|
| |
| foreach ($ecommerceDetail as $column => $value) { |
| if (strpos($column, 'revenue') !== false) { |
| if (!is_numeric($value)) { |
| $ecommerceDetail[$column] = 0; |
| } elseif ($value == round($value)) { |
| $ecommerceDetail[$column] = round($value); |
| } |
| } |
| } |
|
|
| $idOrder = isset($ecommerceDetail['orderId']) ? $ecommerceDetail['orderId'] : GoalManager::ITEM_IDORDER_ABANDONED_CART; |
|
|
| $itemsDetails = $this->queryEcommerceItemsForOrder($idVisit, $idOrder); |
| foreach ($itemsDetails as &$detail) { |
| if ($detail['price'] == round($detail['price'])) { |
| $detail['price'] = round($detail['price']); |
| } |
| } |
| $ecommerceDetail['itemDetails'] = $itemsDetails; |
|
|
| $actions[$idVisit][] = $ecommerceDetail; |
| } |
| } |
|
|
| |
| |
| |
| |
| |
| |
| protected function queryEcommerceConversionsVisitorLifeTimeMetricsForVisitor($idSite, $idVisitor) |
| { |
| $sql = $this->getSqlEcommerceConversionsLifeTimeMetricsForIdGoal(); |
| $lifeTimeStats = $this->getDb()->fetchAll($sql, array($idSite, @Common::hex2bin($idVisitor))); |
|
|
| $defaultStats = array_fill_keys([GoalManager::IDGOAL_CART, GoalManager::IDGOAL_ORDER], self::DEFAULT_LIFETIME_STAT); |
|
|
| $lifeTimeStatsByGoal = array_reduce($lifeTimeStats, function ($carry, $statRow) { |
| $idgoal = $statRow['idgoal']; |
| $carry[$idgoal] = array_merge($carry[$idgoal], $statRow); |
| return $carry; |
| }, $defaultStats); |
|
|
| $ecommerceOrders = $lifeTimeStatsByGoal[GoalManager::IDGOAL_ORDER]; |
| $abandonedCarts = $lifeTimeStatsByGoal[GoalManager::IDGOAL_CART]; |
|
|
| return array( |
| 'totalEcommerceRevenue' => $ecommerceOrders['lifeTimeRevenue'], |
| 'totalEcommerceConversions' => $ecommerceOrders['lifeTimeConversions'], |
| 'totalEcommerceItems' => $ecommerceOrders['lifeTimeEcommerceItems'], |
| 'totalAbandonedCartsRevenue' => $abandonedCarts['lifeTimeRevenue'], |
| 'totalAbandonedCarts' => $abandonedCarts['lifeTimeConversions'], |
| 'totalAbandonedCartsItems' => $abandonedCarts['lifeTimeEcommerceItems'], |
| ); |
| } |
|
|
|
|
| |
| |
| |
| |
| |
| protected function getSqlEcommerceConversionsLifeTimeMetricsForIdGoal() |
| { |
| $sql = "SELECT |
| idgoal, |
| COALESCE(SUM(" . LogAggregator::getSqlRevenue('revenue') . "), 0) as lifeTimeRevenue, |
| COUNT(*) as lifeTimeConversions, |
| COALESCE(SUM(" . LogAggregator::getSqlRevenue('items') . "), 0) as lifeTimeEcommerceItems |
| FROM " . Common::prefixTable('log_visit') . " AS log_visit |
| STRAIGHT_JOIN " . Common::prefixTable('log_conversion') . " AS log_conversion |
| ON log_visit.idvisit = log_conversion.idvisit |
| WHERE |
| log_visit.idsite = ? |
| AND log_visit.idvisitor = ? |
| AND log_conversion.idgoal IN ( " . GoalManager::IDGOAL_CART . ", " . GoalManager::IDGOAL_ORDER . " ) |
| GROUP BY idgoal |
| "; |
| return $sql; |
| } |
|
|
| |
| |
| |
| |
| |
| |
| protected function queryEcommerceConversionsForVisits($idVisits) |
| { |
| $sql = "SELECT |
| log_conversion.idvisit, |
| case idgoal when " . GoalManager::IDGOAL_CART |
| . " then '" . Piwik::LABEL_ID_GOAL_IS_ECOMMERCE_CART |
| . "' else '" . Piwik::LABEL_ID_GOAL_IS_ECOMMERCE_ORDER . "' end as type, |
| idorder as orderId, |
| " . LogAggregator::getSqlRevenue('revenue') . " as revenue, |
| " . LogAggregator::getSqlRevenue('revenue_subtotal') . " as revenueSubTotal, |
| " . LogAggregator::getSqlRevenue('revenue_tax') . " as revenueTax, |
| " . LogAggregator::getSqlRevenue('revenue_shipping') . " as revenueShipping, |
| " . LogAggregator::getSqlRevenue('revenue_discount') . " as revenueDiscount, |
| items as items, |
| log_conversion.server_time as serverTimePretty, |
| log_conversion.idlink_va, |
| log_link_visit_action.idpageview, |
| log_conversion.referer_type as referrerType, |
| log_conversion.referer_name as referrerName, |
| log_conversion.referer_keyword as referrerKeyword |
| FROM " . Common::prefixTable('log_conversion') . " AS log_conversion |
| LEFT JOIN " . Common::prefixTable('log_link_visit_action') . " AS log_link_visit_action |
| ON log_link_visit_action.idlink_va = log_conversion.idlink_va |
| WHERE log_conversion.idvisit IN ('" . implode("','", $idVisits) . "') |
| AND idgoal <= " . GoalManager::IDGOAL_ORDER . " |
| ORDER BY log_conversion.idvisit, log_conversion.server_time ASC"; |
|
|
| $sql = DbHelper::addMaxExecutionTimeHintToQuery($sql, $this->getLiveQueryMaxExecutionTime()); |
|
|
| try { |
| $ecommerceDetails = $this->getDb()->fetchAll($sql); |
| } catch (\Exception $e) { |
| $now = Date::now(); |
| Model::handleMaxExecutionTimeError($this->getDb(), $e, '', $now, $now, null, 0, ['sql' => $sql]); |
| throw $e; |
| } |
|
|
| return $ecommerceDetails; |
| } |
|
|
| |
| |
| |
| |
| |
| |
| |
| protected function queryEcommerceItemsForOrder($idVisit, $idOrder) |
| { |
| $categorySelects = []; |
| $categoryJoins = []; |
| for ($i = 0; $i < self::CATEGORY_COUNT; ++$i) { |
| $suffix = $i === 0 ? '' : $i; |
| $column = $i === 0 ? 'idaction_category' : ('idaction_category' . ($i + 1)); |
| $categorySelects[] = 'log_action_category' . $suffix . '.name as itemCategory' . $suffix; |
| $categoryJoins[] = 'LEFT JOIN ' . Common::prefixTable('log_action') . " AS log_action_category$suffix |
| ON $column = log_action_category$suffix.idaction"; |
| } |
| $categorySelects = implode(',', $categorySelects); |
| $categoryJoins = implode("\n", $categoryJoins); |
|
|
| $sql = "SELECT |
| log_action_sku.name as itemSKU, |
| log_action_name.name as itemName, |
| $categorySelects, |
| " . LogAggregator::getSqlRevenue('price') . " as price, |
| quantity as quantity |
| FROM " . Common::prefixTable('log_conversion_item') . " |
| INNER JOIN " . Common::prefixTable('log_action') . " AS log_action_sku |
| ON idaction_sku = log_action_sku.idaction |
| LEFT JOIN " . Common::prefixTable('log_action') . " AS log_action_name |
| ON idaction_name = log_action_name.idaction |
| $categoryJoins |
| WHERE idvisit = ? |
| AND idorder = ? |
| AND deleted = 0 |
| "; |
|
|
| $bind = array($idVisit, $idOrder); |
|
|
| $itemsDetails = $this->getDb()->fetchAll($sql, $bind); |
|
|
| |
| foreach ($itemsDetails as &$item) { |
| $categories = []; |
| for ($i = 0; $i < self::CATEGORY_COUNT; ++$i) { |
| $suffix = $i === 0 ? '' : $i; |
| if (empty($item['itemCategory' . $suffix])) { |
| continue; |
| } |
|
|
| $categories[] = trim($item['itemCategory' . $suffix]); |
| } |
| $item['categories'] = array_filter($categories); |
|
|
| |
| for ($i = 1; $i < self::CATEGORY_COUNT; ++$i) { |
| unset($item['itemCategory' . $i]); |
| } |
| } |
|
|
| return $itemsDetails; |
| } |
|
|
| public function initProfile($visits, &$profile) |
| { |
| if (Site::isEcommerceEnabledFor($visits->getFirstRow()->getColumn('idSite'))) { |
| $profile['totalEcommerceRevenue'] = 0; |
| $profile['totalEcommerceConversions'] = 0; |
| $profile['totalEcommerceItems'] = 0; |
| $profile['totalAbandonedCarts'] = 0; |
| $profile['totalAbandonedCartsRevenue'] = 0; |
| $profile['totalAbandonedCartsItems'] = 0; |
| } |
| } |
|
|
| public function finalizeProfile($visits, &$profile) |
| { |
| $lastVisit = $visits->getLastRow(); |
| if ($lastVisit && Site::isEcommerceEnabledFor($lastVisit->getColumn('idSite'))) { |
| $profile['totalEcommerceRevenue'] = $lastVisit->getColumn('totalEcommerceRevenue'); |
| $profile['totalEcommerceConversions'] = $lastVisit->getColumn('totalEcommerceConversions'); |
| $profile['totalEcommerceItems'] = $lastVisit->getColumn('totalEcommerceItems'); |
| $profile['totalAbandonedCartsRevenue'] = $lastVisit->getColumn('totalAbandonedCartsRevenue'); |
| $profile['totalAbandonedCarts'] = $lastVisit->getColumn('totalAbandonedCarts'); |
| $profile['totalAbandonedCartsItems'] = $lastVisit->getColumn('totalAbandonedCartsItems'); |
| } |
| } |
|
|
| protected function getReferrerType($referrerTypeId) |
| { |
| try { |
| $referrerType = getReferrerTypeFromShortName($referrerTypeId); |
| } catch (\Exception $e) { |
| $referrerType = ''; |
| } |
|
|
| return $referrerType; |
| } |
|
|
| private function getLiveQueryMaxExecutionTime() |
| { |
| return Config::getInstance()->General['live_query_max_execution_time']; |
| } |
| } |
|
|
