Piwik\Plugins\Live\Model::queryActionsForVisit PHP Method

queryActionsForVisit() public method

public queryActionsForVisit ( $idVisit, $actionsLimit ) : array
$idVisit
$actionsLimit
return array
    public function queryActionsForVisit($idVisit, $actionsLimit)
    {
        $maxCustomVariables = CustomVariables::getNumUsableCustomVariables();
        $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.idlink_va,\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\tlog_link_visit_action.interaction_position\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));
        return $actionDetails;
    }

Usage Example

Esempio n. 1
0
 /**
  * @param $visitorDetailsArray
  * @param $actionsLimit
  * @param $timezone
  * @return array
  */
 public static function enrichVisitorArrayWithActions($visitorDetailsArray, $actionsLimit, $timezone)
 {
     $idVisit = $visitorDetailsArray['idVisit'];
     $model = new Model();
     $actionDetails = $model->queryActionsForVisit($idVisit, $actionsLimit);
     $formatter = new Formatter();
     $maxCustomVariables = CustomVariables::getNumUsableCustomVariables();
     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) {
             // 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) {
             if (strlen($actionDetail['custom_float']) > 0) {
                 $actionDetail['eventValue'] = round($actionDetail['custom_float'], self::EVENT_VALUE_PRECISION);
             }
         } elseif ($actionDetail['custom_float'] > 0) {
             $actionDetail['generationTime'] = $formatter->getPrettyTimeFromSeconds($actionDetail['custom_float'] / 1000, true);
         }
         unset($actionDetail['custom_float']);
         if ($actionDetail['type'] != Action::TYPE_EVENT) {
             unset($actionDetail['eventCategory']);
             unset($actionDetail['eventAction']);
         }
         // Reconstruct url from prefix
         $url = Tracker\PageUrl::reconstructNormalizedUrl($actionDetail['url'], $actionDetail['url_prefix']);
         $url = Common::unsanitizeInputValue($url);
         $actionDetail['url'] = $url;
         unset($actionDetail['url_prefix']);
     }
     // If the visitor converted a goal, we shall select all Goals
     $goalDetails = $model->queryGoalConversionsForVisit($idVisit, $actionsLimit);
     $ecommerceDetails = $model->queryEcommerceConversionsForVisit($idVisit, $actionsLimit);
     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) {
         $idOrder = isset($ecommerceConversion['orderId']) ? $ecommerceConversion['orderId'] : GoalManager::ITEM_IDORDER_ABANDONED_CART;
         $itemsDetails = $model->queryEcommerceItemsForOrder($idVisit, $idOrder, $actionsLimit);
         foreach ($itemsDetails as &$detail) {
             if ($detail['price'] == round($detail['price'])) {
                 $detail['price'] = round($detail['price']);
             }
         }
         $ecommerceConversion['itemDetails'] = $itemsDetails;
     }
     // Enrich with time spent per action
     foreach ($actionDetails as $actionIdx => &$actionDetail) {
         // Set the time spent for this action (which is the timeSpentRef of the next action)
         $nextActionFound = isset($actionDetails[$actionIdx + 1]);
         if ($nextActionFound) {
             $actionDetail['timeSpent'] = $actionDetails[$actionIdx + 1]['timeSpentRef'];
         } else {
             // Last action of a visit.
             // By default, Piwik does not know how long the user stayed on the page
             // If enableHeartBeatTimer() is used in piwik.js then we can find the accurate time on page for the last pageview
             $timeOfLastActionOrPingInVisitRow = $visitorDetailsArray['lastActionTimestamp'];
             $timeOfLastAction = Date::factory($actionDetail['serverTimePretty'])->getTimestamp();
             $timeSpentOnPage = $timeOfLastActionOrPingInVisitRow - $timeOfLastAction;
             // Safe net, we assume the time is correct when it's more than 10 seconds
             if ($timeSpentOnPage > 10) {
                 $actionDetail['timeSpent'] = $timeSpentOnPage;
             }
         }
         if (isset($actionDetail['timeSpent'])) {
             $actionDetail['timeSpentPretty'] = $formatter->getPrettyTimeFromSeconds($actionDetail['timeSpent'], true);
         }
         unset($actionDetails[$actionIdx]['timeSpentRef']);
         // not needed after timeSpent is added
     }
     $actions = array_merge($actionDetails, $goalDetails, $ecommerceDetails);
     usort($actions, array('static', 'sortByServerTime'));
     foreach ($actions as &$action) {
         unset($action['idlink_va']);
     }
     $visitorDetailsArray['goalConversions'] = count($goalDetails);
     $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:
                 $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(Date::DATETIME_FORMAT_SHORT);
         $details['timestamp'] = $dateTimeVisit->getTimestamp();
     }
     return $visitorDetailsArray;
 }