/plugins/Live/Visitor.php

https://github.com/CodeYellowBV/piwik · PHP · 992 lines · 814 code · 121 blank · 57 comment · 89 complexity · e6e1740373a2336d1fbd75a0c3a4a702 MD5 · raw file

  1. <?php
  2. /**
  3. * Piwik - free/libre analytics platform
  4. *
  5. * @link http://piwik.org
  6. * @license http://www.gnu.org/licenses/gpl-3.0.html GPL v3 or later
  7. *
  8. */
  9. namespace Piwik\Plugins\Live;
  10. use Piwik\Common;
  11. use Piwik\DataAccess\LogAggregator;
  12. use Piwik\DataTable\Filter\ColumnDelete;
  13. use Piwik\Date;
  14. use Piwik\Db;
  15. use Piwik\IP;
  16. use Piwik\Piwik;
  17. use Piwik\Plugins\API\API as APIMetadata;
  18. use Piwik\Plugins\CustomVariables\CustomVariables;
  19. use Piwik\Plugins\Referrers\API as APIReferrers;
  20. use Piwik\Plugins\UserCountry\LocationProvider\GeoIp;
  21. use Piwik\Tracker;
  22. use Piwik\Tracker\Action;
  23. use Piwik\Tracker\GoalManager;
  24. use Piwik\Tracker\Visit;
  25. use Piwik\UrlHelper;
  26. /**
  27. * @see plugins/Referrers/functions.php
  28. * @see plugins/UserCountry/functions.php
  29. * @see plugins/UserSettings/functions.php
  30. * @see plugins/Provider/functions.php
  31. */
  32. require_once PIWIK_INCLUDE_PATH . '/plugins/Referrers/functions.php';
  33. require_once PIWIK_INCLUDE_PATH . '/plugins/UserCountry/functions.php';
  34. require_once PIWIK_INCLUDE_PATH . '/plugins/UserSettings/functions.php';
  35. require_once PIWIK_INCLUDE_PATH . '/plugins/Provider/functions.php';
  36. /**
  37. */
  38. class Visitor
  39. {
  40. const DELIMITER_PLUGIN_NAME = ", ";
  41. const EVENT_VALUE_PRECISION = 3;
  42. function __construct($visitorRawData)
  43. {
  44. $this->details = $visitorRawData;
  45. }
  46. function getAllVisitorDetails()
  47. {
  48. return array(
  49. 'idSite' => $this->getIdSite(),
  50. 'idVisit' => $this->getIdVisit(),
  51. 'visitIp' => $this->getIp(),
  52. 'visitorId' => $this->getVisitorId(),
  53. 'visitorType' => $this->getVisitorReturning(),
  54. 'visitorTypeIcon' => $this->getVisitorReturningIcon(),
  55. 'visitConverted' => $this->isVisitorGoalConverted(),
  56. 'visitConvertedIcon' => $this->getVisitorGoalConvertedIcon(),
  57. 'visitEcommerceStatus' => $this->getVisitEcommerceStatus(),
  58. 'visitEcommerceStatusIcon' => $this->getVisitEcommerceStatusIcon(),
  59. 'searches' => $this->getNumberOfSearches(),
  60. 'events' => $this->getNumberOfEvents(),
  61. 'actions' => $this->getNumberOfActions(),
  62. // => false are placeholders to be filled in API later
  63. 'actionDetails' => false,
  64. 'customVariables' => $this->getCustomVariables(),
  65. 'goalConversions' => false,
  66. 'siteCurrency' => false,
  67. 'siteCurrencySymbol' => false,
  68. // all time entries
  69. 'serverDate' => $this->getServerDate(),
  70. 'visitLocalTime' => $this->getVisitLocalTime(),
  71. 'visitLocalHour' => $this->getVisitLocalHour(),
  72. 'visitServerHour' => $this->getVisitServerHour(),
  73. 'firstActionTimestamp' => $this->getTimestampFirstAction(),
  74. 'lastActionTimestamp' => $this->getTimestampLastAction(),
  75. 'lastActionDateTime' => $this->getDateTimeLastAction(),
  76. // standard attributes
  77. 'visitDuration' => $this->getVisitLength(),
  78. 'visitDurationPretty' => $this->getVisitLengthPretty(),
  79. 'visitCount' => $this->getVisitCount(),
  80. 'daysSinceLastVisit' => $this->getDaysSinceLastVisit(),
  81. 'daysSinceFirstVisit' => $this->getDaysSinceFirstVisit(),
  82. 'daysSinceLastEcommerceOrder' => $this->getDaysSinceLastEcommerceOrder(),
  83. 'continent' => $this->getContinent(),
  84. 'continentCode' => $this->getContinentCode(),
  85. 'country' => $this->getCountryName(),
  86. 'countryCode' => $this->getCountryCode(),
  87. 'countryFlag' => $this->getCountryFlag(),
  88. 'region' => $this->getRegionName(),
  89. 'regionCode' => $this->getRegionCode(),
  90. 'city' => $this->getCityName(),
  91. 'location' => $this->getPrettyLocation(),
  92. 'latitude' => $this->getLatitude(),
  93. 'longitude' => $this->getLongitude(),
  94. 'provider' => $this->getProvider(),
  95. 'providerName' => $this->getProviderName(),
  96. 'providerUrl' => $this->getProviderUrl(),
  97. 'referrerType' => $this->getReferrerType(),
  98. 'referrerTypeName' => $this->getReferrerTypeName(),
  99. 'referrerName' => $this->getReferrerName(),
  100. 'referrerKeyword' => $this->getKeyword(),
  101. 'referrerKeywordPosition' => $this->getKeywordPosition(),
  102. 'referrerUrl' => $this->getReferrerUrl(),
  103. 'referrerSearchEngineUrl' => $this->getSearchEngineUrl(),
  104. 'referrerSearchEngineIcon' => $this->getSearchEngineIcon(),
  105. 'operatingSystem' => $this->getOperatingSystem(),
  106. 'operatingSystemCode' => $this->getOperatingSystemCode(),
  107. 'operatingSystemShortName' => $this->getOperatingSystemShortName(),
  108. 'operatingSystemIcon' => $this->getOperatingSystemIcon(),
  109. 'browserFamily' => $this->getBrowserFamily(),
  110. 'browserFamilyDescription' => $this->getBrowserFamilyDescription(),
  111. 'browserName' => $this->getBrowser(),
  112. 'browserIcon' => $this->getBrowserIcon(),
  113. 'browserCode' => $this->getBrowserCode(),
  114. 'browserVersion' => $this->getBrowserVersion(),
  115. 'screenType' => $this->getScreenType(),
  116. 'deviceType' => $this->getDeviceType(),
  117. 'resolution' => $this->getResolution(),
  118. 'screenTypeIcon' => $this->getScreenTypeIcon(),
  119. 'plugins' => $this->getPlugins(),
  120. 'pluginsIcons' => $this->getPluginIcons(),
  121. );
  122. }
  123. function getVisitorId()
  124. {
  125. if (isset($this->details['idvisitor'])) {
  126. return bin2hex($this->details['idvisitor']);
  127. }
  128. return false;
  129. }
  130. function getVisitLocalTime()
  131. {
  132. return $this->details['visitor_localtime'];
  133. }
  134. function getVisitServerHour()
  135. {
  136. return date('G', strtotime($this->details['visit_last_action_time']));
  137. }
  138. function getVisitLocalHour()
  139. {
  140. return date('G', strtotime('2012-12-21 ' . $this->details['visitor_localtime']));
  141. }
  142. function getVisitCount()
  143. {
  144. return $this->details['visitor_count_visits'];
  145. }
  146. function getDaysSinceLastVisit()
  147. {
  148. return $this->details['visitor_days_since_last'];
  149. }
  150. function getDaysSinceLastEcommerceOrder()
  151. {
  152. return $this->details['visitor_days_since_order'];
  153. }
  154. function getDaysSinceFirstVisit()
  155. {
  156. return $this->details['visitor_days_since_first'];
  157. }
  158. function getServerDate()
  159. {
  160. return date('Y-m-d', strtotime($this->details['visit_last_action_time']));
  161. }
  162. function getIp()
  163. {
  164. if (isset($this->details['location_ip'])) {
  165. return IP::N2P($this->details['location_ip']);
  166. }
  167. return false;
  168. }
  169. function getIdVisit()
  170. {
  171. return $this->details['idvisit'];
  172. }
  173. function getIdSite()
  174. {
  175. return $this->details['idsite'];
  176. }
  177. function getNumberOfActions()
  178. {
  179. return $this->details['visit_total_actions'];
  180. }
  181. function getNumberOfEvents()
  182. {
  183. return $this->details['visit_total_events'];
  184. }
  185. function getNumberOfSearches()
  186. {
  187. return $this->details['visit_total_searches'];
  188. }
  189. function getVisitLength()
  190. {
  191. return $this->details['visit_total_time'];
  192. }
  193. function getVisitLengthPretty()
  194. {
  195. return \Piwik\MetricsFormatter::getPrettyTimeFromSeconds($this->details['visit_total_time']);
  196. }
  197. function getVisitorReturning()
  198. {
  199. $type = $this->details['visitor_returning'];
  200. return $type == 2
  201. ? 'returningCustomer'
  202. : ($type == 1
  203. ? 'returning'
  204. : 'new');
  205. }
  206. function getVisitorReturningIcon()
  207. {
  208. $type = $this->getVisitorReturning();
  209. if ($type == 'returning'
  210. || $type == 'returningCustomer'
  211. ) {
  212. return "plugins/Live/images/returningVisitor.gif";
  213. }
  214. return null;
  215. }
  216. function getTimestampFirstAction()
  217. {
  218. return strtotime($this->details['visit_first_action_time']);
  219. }
  220. function getTimestampLastAction()
  221. {
  222. return strtotime($this->details['visit_last_action_time']);
  223. }
  224. function getCountryCode()
  225. {
  226. return $this->details['location_country'];
  227. }
  228. function getCountryName()
  229. {
  230. return \Piwik\Plugins\UserCountry\countryTranslate($this->getCountryCode());
  231. }
  232. function getCountryFlag()
  233. {
  234. return \Piwik\Plugins\UserCountry\getFlagFromCode($this->getCountryCode());
  235. }
  236. function getContinent()
  237. {
  238. return \Piwik\Plugins\UserCountry\continentTranslate($this->getContinentCode());
  239. }
  240. function getContinentCode()
  241. {
  242. return Common::getContinent($this->details['location_country']);
  243. }
  244. function getCityName()
  245. {
  246. if (!empty($this->details['location_city'])) {
  247. return $this->details['location_city'];
  248. }
  249. return null;
  250. }
  251. public function getRegionName()
  252. {
  253. $region = $this->getRegionCode();
  254. if ($region != '' && $region != Visit::UNKNOWN_CODE) {
  255. return GeoIp::getRegionNameFromCodes(
  256. $this->details['location_country'], $region);
  257. }
  258. return null;
  259. }
  260. public function getRegionCode()
  261. {
  262. return $this->details['location_region'];
  263. }
  264. function getPrettyLocation()
  265. {
  266. $parts = array();
  267. $city = $this->getCityName();
  268. if (!empty($city)) {
  269. $parts[] = $city;
  270. }
  271. $region = $this->getRegionName();
  272. if (!empty($region)) {
  273. $parts[] = $region;
  274. }
  275. // add country & return concatenated result
  276. $parts[] = $this->getCountryName();
  277. return implode(', ', $parts);
  278. }
  279. function getLatitude()
  280. {
  281. if (!empty($this->details['location_latitude'])) {
  282. return $this->details['location_latitude'];
  283. }
  284. return null;
  285. }
  286. function getLongitude()
  287. {
  288. if (!empty($this->details['location_longitude'])) {
  289. return $this->details['location_longitude'];
  290. }
  291. return null;
  292. }
  293. function getCustomVariables()
  294. {
  295. $customVariables = array();
  296. $maxCustomVariables = CustomVariables::getMaxCustomVariables();
  297. for ($i = 1; $i <= $maxCustomVariables; $i++) {
  298. if (!empty($this->details['custom_var_k' . $i])) {
  299. $customVariables[$i] = array(
  300. 'customVariableName' . $i => $this->details['custom_var_k' . $i],
  301. 'customVariableValue' . $i => $this->details['custom_var_v' . $i],
  302. );
  303. }
  304. }
  305. return $customVariables;
  306. }
  307. function getReferrerType()
  308. {
  309. return \Piwik\Plugins\Referrers\getReferrerTypeFromShortName($this->details['referer_type']);
  310. }
  311. function getReferrerTypeName()
  312. {
  313. return \Piwik\Plugins\Referrers\getReferrerTypeLabel($this->details['referer_type']);
  314. }
  315. function getKeyword()
  316. {
  317. $keyword = $this->details['referer_keyword'];
  318. if (\Piwik\Plugin\Manager::getInstance()->isPluginActivated('Referrers')
  319. && $this->getReferrerType() == 'search'
  320. ) {
  321. $keyword = \Piwik\Plugins\Referrers\API::getCleanKeyword($keyword);
  322. }
  323. return urldecode($keyword);
  324. }
  325. function getReferrerUrl()
  326. {
  327. if ($this->getReferrerType() == 'search') {
  328. if (\Piwik\Plugin\Manager::getInstance()->isPluginActivated('Referrers')
  329. && $this->details['referer_keyword'] == APIReferrers::LABEL_KEYWORD_NOT_DEFINED
  330. ) {
  331. return 'http://piwik.org/faq/general/#faq_144';
  332. } // Case URL is google.XX/url.... then we rewrite to the search result page url
  333. elseif ($this->getReferrerName() == 'Google'
  334. && strpos($this->details['referer_url'], '/url')
  335. ) {
  336. $refUrl = @parse_url($this->details['referer_url']);
  337. if (isset($refUrl['host'])) {
  338. $url = \Piwik\Plugins\Referrers\getSearchEngineUrlFromUrlAndKeyword('http://google.com', $this->getKeyword());
  339. $url = str_replace('google.com', $refUrl['host'], $url);
  340. return $url;
  341. }
  342. }
  343. }
  344. if (\Piwik\UrlHelper::isLookLikeUrl($this->details['referer_url'])) {
  345. return $this->details['referer_url'];
  346. }
  347. return null;
  348. }
  349. function getKeywordPosition()
  350. {
  351. if ($this->getReferrerType() == 'search'
  352. && strpos($this->getReferrerName(), 'Google') !== false
  353. ) {
  354. $url = @parse_url($this->details['referer_url']);
  355. if (empty($url['query'])) {
  356. return null;
  357. }
  358. $position = UrlHelper::getParameterFromQueryString($url['query'], 'cd');
  359. if (!empty($position)) {
  360. return $position;
  361. }
  362. }
  363. return null;
  364. }
  365. function getReferrerName()
  366. {
  367. return urldecode($this->details['referer_name']);
  368. }
  369. function getSearchEngineUrl()
  370. {
  371. if ($this->getReferrerType() == 'search'
  372. && !empty($this->details['referer_name'])
  373. ) {
  374. return \Piwik\Plugins\Referrers\getSearchEngineUrlFromName($this->details['referer_name']);
  375. }
  376. return null;
  377. }
  378. function getSearchEngineIcon()
  379. {
  380. $searchEngineUrl = $this->getSearchEngineUrl();
  381. if (!is_null($searchEngineUrl)) {
  382. return \Piwik\Plugins\Referrers\getSearchEngineLogoFromUrl($searchEngineUrl);
  383. }
  384. return null;
  385. }
  386. function getPlugins()
  387. {
  388. $plugins = array(
  389. 'config_pdf',
  390. 'config_flash',
  391. 'config_java',
  392. 'config_director',
  393. 'config_quicktime',
  394. 'config_realplayer',
  395. 'config_windowsmedia',
  396. 'config_gears',
  397. 'config_silverlight',
  398. );
  399. $pluginShortNames = array();
  400. foreach ($plugins as $plugin) {
  401. if ($this->details[$plugin] == 1) {
  402. $pluginShortName = substr($plugin, 7);
  403. $pluginShortNames[] = $pluginShortName;
  404. }
  405. }
  406. return implode(self::DELIMITER_PLUGIN_NAME, $pluginShortNames);
  407. }
  408. function getPluginIcons()
  409. {
  410. $pluginNames = $this->getPlugins();
  411. if (!empty($pluginNames)) {
  412. $pluginNames = explode(self::DELIMITER_PLUGIN_NAME, $pluginNames);
  413. $pluginIcons = array();
  414. foreach ($pluginNames as $plugin) {
  415. $pluginIcons[] = array("pluginIcon" => \Piwik\Plugins\UserSettings\getPluginsLogo($plugin), "pluginName" => $plugin);
  416. }
  417. return $pluginIcons;
  418. }
  419. return null;
  420. }
  421. function getOperatingSystemCode()
  422. {
  423. return $this->details['config_os'];
  424. }
  425. function getOperatingSystem()
  426. {
  427. return \Piwik\Plugins\UserSettings\getOSLabel($this->details['config_os']);
  428. }
  429. function getOperatingSystemShortName()
  430. {
  431. return \Piwik\Plugins\UserSettings\getOSShortLabel($this->details['config_os']);
  432. }
  433. function getOperatingSystemIcon()
  434. {
  435. return \Piwik\Plugins\UserSettings\getOSLogo($this->details['config_os']);
  436. }
  437. function getBrowserFamilyDescription()
  438. {
  439. return \Piwik\Plugins\UserSettings\getBrowserTypeLabel($this->getBrowserFamily());
  440. }
  441. function getBrowserFamily()
  442. {
  443. return \Piwik\Plugins\UserSettings\getBrowserFamily($this->details['config_browser_name']);
  444. }
  445. function getBrowserCode()
  446. {
  447. return $this->details['config_browser_name'];
  448. }
  449. function getBrowserVersion()
  450. {
  451. return $this->details['config_browser_version'];
  452. }
  453. function getBrowser()
  454. {
  455. return \Piwik\Plugins\UserSettings\getBrowserLabel($this->details['config_browser_name'] . ";" . $this->details['config_browser_version']);
  456. }
  457. function getBrowserIcon()
  458. {
  459. return \Piwik\Plugins\UserSettings\getBrowsersLogo($this->details['config_browser_name'] . ";" . $this->details['config_browser_version']);
  460. }
  461. function getScreenType()
  462. {
  463. return \Piwik\Plugins\UserSettings\getScreenTypeFromResolution($this->details['config_resolution']);
  464. }
  465. function getDeviceType()
  466. {
  467. if (\Piwik\Plugin\Manager::getInstance()->isPluginActivated('DevicesDetection')) {
  468. return \Piwik\Plugins\DevicesDetection\getDeviceTypeLabel($this->details['config_device_type']);
  469. }
  470. return false;
  471. }
  472. function getResolution()
  473. {
  474. return $this->details['config_resolution'];
  475. }
  476. function getScreenTypeIcon()
  477. {
  478. return \Piwik\Plugins\UserSettings\getScreensLogo($this->getScreenType());
  479. }
  480. function getProvider()
  481. {
  482. if (isset($this->details['location_provider'])) {
  483. return $this->details['location_provider'];
  484. } else {
  485. return Piwik::translate('General_Unknown');
  486. }
  487. }
  488. function getProviderName()
  489. {
  490. return \Piwik\Plugins\Provider\getPrettyProviderName($this->getProvider());
  491. }
  492. function getProviderUrl()
  493. {
  494. return \Piwik\Plugins\Provider\getHostnameUrl(@$this->details['location_provider']);
  495. }
  496. function getDateTimeLastAction()
  497. {
  498. return date('Y-m-d H:i:s', strtotime($this->details['visit_last_action_time']));
  499. }
  500. function getVisitEcommerceStatusIcon()
  501. {
  502. $status = $this->getVisitEcommerceStatus();
  503. if (in_array($status, array('ordered', 'orderedThenAbandonedCart'))) {
  504. return "plugins/Morpheus/images/ecommerceOrder.gif";
  505. } elseif ($status == 'abandonedCart') {
  506. return "plugins/Morpheus/images/ecommerceAbandonedCart.gif";
  507. }
  508. return null;
  509. }
  510. function getVisitEcommerceStatus()
  511. {
  512. return APIMetadata::getVisitEcommerceStatusFromId($this->details['visit_goal_buyer']);
  513. }
  514. function getVisitorGoalConvertedIcon()
  515. {
  516. return $this->isVisitorGoalConverted()
  517. ? "plugins/Morpheus/images/goal.png"
  518. : null;
  519. }
  520. function isVisitorGoalConverted()
  521. {
  522. return $this->details['visit_goal_converted'];
  523. }
  524. /**
  525. * Removes fields that are not meant to be displayed (md5 config hash)
  526. * Or that the user should only access if he is Super User or admin (cookie, IP)
  527. *
  528. * @param array $visitorDetails
  529. * @return array
  530. */
  531. public static function cleanVisitorDetails($visitorDetails)
  532. {
  533. $toUnset = array('config_id');
  534. if (Piwik::isUserIsAnonymous()) {
  535. $toUnset[] = 'idvisitor';
  536. $toUnset[] = 'location_ip';
  537. }
  538. foreach ($toUnset as $keyName) {
  539. if (isset($visitorDetails[$keyName])) {
  540. unset($visitorDetails[$keyName]);
  541. }
  542. }
  543. return $visitorDetails;
  544. }
  545. /**
  546. * The &flat=1 feature is used by API.getSuggestedValuesForSegment
  547. *
  548. * @param $visitorDetailsArray
  549. * @return array
  550. */
  551. public static function flattenVisitorDetailsArray($visitorDetailsArray)
  552. {
  553. // NOTE: if you flatten more fields from the "actionDetails" array
  554. // ==> also update API/API.php getSuggestedValuesForSegment(), the $segmentsNeedActionsInfo array
  555. // flatten visit custom variables
  556. if (is_array($visitorDetailsArray['customVariables'])) {
  557. foreach ($visitorDetailsArray['customVariables'] as $thisCustomVar) {
  558. $visitorDetailsArray = array_merge($visitorDetailsArray, $thisCustomVar);
  559. }
  560. unset($visitorDetailsArray['customVariables']);
  561. }
  562. // flatten page views custom variables
  563. $count = 1;
  564. foreach ($visitorDetailsArray['actionDetails'] as $action) {
  565. if (!empty($action['customVariables'])) {
  566. foreach ($action['customVariables'] as $thisCustomVar) {
  567. foreach ($thisCustomVar as $cvKey => $cvValue) {
  568. $flattenedKeyName = $cvKey . ColumnDelete::APPEND_TO_COLUMN_NAME_TO_KEEP . $count;
  569. $visitorDetailsArray[$flattenedKeyName] = $cvValue;
  570. $count++;
  571. }
  572. }
  573. }
  574. }
  575. // Flatten Goals
  576. $count = 1;
  577. foreach ($visitorDetailsArray['actionDetails'] as $action) {
  578. if (!empty($action['goalId'])) {
  579. $flattenedKeyName = 'visitConvertedGoalId' . ColumnDelete::APPEND_TO_COLUMN_NAME_TO_KEEP . $count;
  580. $visitorDetailsArray[$flattenedKeyName] = $action['goalId'];
  581. $count++;
  582. }
  583. }
  584. // Flatten Page Titles/URLs
  585. $count = 1;
  586. foreach ($visitorDetailsArray['actionDetails'] as $action) {
  587. if (!empty($action['url'])) {
  588. $flattenedKeyName = 'pageUrl' . ColumnDelete::APPEND_TO_COLUMN_NAME_TO_KEEP . $count;
  589. $visitorDetailsArray[$flattenedKeyName] = $action['url'];
  590. }
  591. // API.getSuggestedValuesForSegment
  592. $flatten = array( 'pageTitle', 'siteSearchKeyword', 'eventCategory', 'eventAction', 'eventName', 'eventValue');
  593. foreach($flatten as $toFlatten) {
  594. if (!empty($action[$toFlatten])) {
  595. $flattenedKeyName = $toFlatten . ColumnDelete::APPEND_TO_COLUMN_NAME_TO_KEEP . $count;
  596. $visitorDetailsArray[$flattenedKeyName] = $action[$toFlatten];
  597. }
  598. }
  599. $count++;
  600. }
  601. // Entry/exit pages
  602. $firstAction = $lastAction = false;
  603. foreach ($visitorDetailsArray['actionDetails'] as $action) {
  604. if ($action['type'] == 'action') {
  605. if (empty($firstAction)) {
  606. $firstAction = $action;
  607. }
  608. $lastAction = $action;
  609. }
  610. }
  611. if (!empty($firstAction['pageTitle'])) {
  612. $visitorDetailsArray['entryPageTitle'] = $firstAction['pageTitle'];
  613. }
  614. if (!empty($firstAction['url'])) {
  615. $visitorDetailsArray['entryPageUrl'] = $firstAction['url'];
  616. }
  617. if (!empty($lastAction['pageTitle'])) {
  618. $visitorDetailsArray['exitPageTitle'] = $lastAction['pageTitle'];
  619. }
  620. if (!empty($lastAction['url'])) {
  621. $visitorDetailsArray['exitPageUrl'] = $lastAction['url'];
  622. }
  623. return $visitorDetailsArray;
  624. }
  625. /**
  626. * @param $visitorDetailsArray
  627. * @param $actionsLimit
  628. * @param $timezone
  629. * @return array
  630. */
  631. public static function enrichVisitorArrayWithActions($visitorDetailsArray, $actionsLimit, $timezone)
  632. {
  633. $idVisit = $visitorDetailsArray['idVisit'];
  634. $maxCustomVariables = CustomVariables::getMaxCustomVariables();
  635. $sqlCustomVariables = '';
  636. for ($i = 1; $i <= $maxCustomVariables; $i++) {
  637. $sqlCustomVariables .= ', custom_var_k' . $i . ', custom_var_v' . $i;
  638. }
  639. // The second join is a LEFT join to allow returning records that don't have a matching page title
  640. // eg. Downloads, Outlinks. For these, idaction_name is set to 0
  641. $sql = "
  642. SELECT
  643. COALESCE(log_action_event_category.type, log_action.type, log_action_title.type) AS type,
  644. log_action.name AS url,
  645. log_action.url_prefix,
  646. log_action_title.name AS pageTitle,
  647. log_action.idaction AS pageIdAction,
  648. log_link_visit_action.server_time as serverTimePretty,
  649. log_link_visit_action.time_spent_ref_action as timeSpentRef,
  650. log_link_visit_action.idlink_va AS pageId,
  651. log_link_visit_action.custom_float
  652. ". $sqlCustomVariables . ",
  653. log_action_event_category.name AS eventCategory,
  654. log_action_event_action.name as eventAction
  655. FROM " . Common::prefixTable('log_link_visit_action') . " AS log_link_visit_action
  656. LEFT JOIN " . Common::prefixTable('log_action') . " AS log_action
  657. ON log_link_visit_action.idaction_url = log_action.idaction
  658. LEFT JOIN " . Common::prefixTable('log_action') . " AS log_action_title
  659. ON log_link_visit_action.idaction_name = log_action_title.idaction
  660. LEFT JOIN " . Common::prefixTable('log_action') . " AS log_action_event_category
  661. ON log_link_visit_action.idaction_event_category = log_action_event_category.idaction
  662. LEFT JOIN " . Common::prefixTable('log_action') . " AS log_action_event_action
  663. ON log_link_visit_action.idaction_event_action = log_action_event_action.idaction
  664. WHERE log_link_visit_action.idvisit = ?
  665. ORDER BY server_time ASC
  666. LIMIT 0, $actionsLimit
  667. ";
  668. $actionDetails = Db::fetchAll($sql, array($idVisit));
  669. foreach ($actionDetails as $actionIdx => &$actionDetail) {
  670. $actionDetail =& $actionDetails[$actionIdx];
  671. $customVariablesPage = array();
  672. $maxCustomVariables = CustomVariables::getMaxCustomVariables();
  673. for ($i = 1; $i <= $maxCustomVariables; $i++) {
  674. if (!empty($actionDetail['custom_var_k' . $i])) {
  675. $cvarKey = $actionDetail['custom_var_k' . $i];
  676. $cvarKey = static::getCustomVariablePrettyKey($cvarKey);
  677. $customVariablesPage[$i] = array(
  678. 'customVariablePageName' . $i => $cvarKey,
  679. 'customVariablePageValue' . $i => $actionDetail['custom_var_v' . $i],
  680. );
  681. }
  682. unset($actionDetail['custom_var_k' . $i]);
  683. unset($actionDetail['custom_var_v' . $i]);
  684. }
  685. if (!empty($customVariablesPage)) {
  686. $actionDetail['customVariables'] = $customVariablesPage;
  687. }
  688. if($actionDetail['type'] == Action::TYPE_EVENT_CATEGORY) {
  689. // Handle Event
  690. if(strlen($actionDetail['pageTitle']) > 0) {
  691. $actionDetail['eventName'] = $actionDetail['pageTitle'];
  692. }
  693. unset($actionDetail['pageTitle']);
  694. } else if ($actionDetail['type'] == Action::TYPE_SITE_SEARCH) {
  695. // Handle Site Search
  696. $actionDetail['siteSearchKeyword'] = $actionDetail['pageTitle'];
  697. unset($actionDetail['pageTitle']);
  698. }
  699. // Event value / Generation time
  700. if($actionDetail['type'] == Action::TYPE_EVENT_CATEGORY) {
  701. if(strlen($actionDetail['custom_float']) > 0) {
  702. $actionDetail['eventValue'] = round($actionDetail['custom_float'], self::EVENT_VALUE_PRECISION);
  703. }
  704. } elseif ($actionDetail['custom_float'] > 0) {
  705. $actionDetail['generationTime'] = \Piwik\MetricsFormatter::getPrettyTimeFromSeconds($actionDetail['custom_float'] / 1000);
  706. }
  707. unset($actionDetail['custom_float']);
  708. if($actionDetail['type'] != Action::TYPE_EVENT_CATEGORY) {
  709. unset($actionDetail['eventCategory']);
  710. unset($actionDetail['eventAction']);
  711. }
  712. // Reconstruct url from prefix
  713. $actionDetail['url'] = Tracker\PageUrl::reconstructNormalizedUrl($actionDetail['url'], $actionDetail['url_prefix']);
  714. unset($actionDetail['url_prefix']);
  715. // Set the time spent for this action (which is the timeSpentRef of the next action)
  716. if (isset($actionDetails[$actionIdx + 1])) {
  717. $actionDetail['timeSpent'] = $actionDetails[$actionIdx + 1]['timeSpentRef'];
  718. $actionDetail['timeSpentPretty'] = \Piwik\MetricsFormatter::getPrettyTimeFromSeconds($actionDetail['timeSpent']);
  719. }
  720. unset($actionDetails[$actionIdx]['timeSpentRef']); // not needed after timeSpent is added
  721. }
  722. // If the visitor converted a goal, we shall select all Goals
  723. $sql = "
  724. SELECT
  725. 'goal' as type,
  726. goal.name as goalName,
  727. goal.idgoal as goalId,
  728. goal.revenue as revenue,
  729. log_conversion.idlink_va as goalPageId,
  730. log_conversion.server_time as serverTimePretty,
  731. log_conversion.url as url
  732. FROM " . Common::prefixTable('log_conversion') . " AS log_conversion
  733. LEFT JOIN " . Common::prefixTable('goal') . " AS goal
  734. ON (goal.idsite = log_conversion.idsite
  735. AND
  736. goal.idgoal = log_conversion.idgoal)
  737. AND goal.deleted = 0
  738. WHERE log_conversion.idvisit = ?
  739. AND log_conversion.idgoal > 0
  740. ORDER BY server_time ASC
  741. LIMIT 0, $actionsLimit
  742. ";
  743. $goalDetails = Db::fetchAll($sql, array($idVisit));
  744. $sql = "SELECT
  745. 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,
  746. idorder as orderId,
  747. " . LogAggregator::getSqlRevenue('revenue') . " as revenue,
  748. " . LogAggregator::getSqlRevenue('revenue_subtotal') . " as revenueSubTotal,
  749. " . LogAggregator::getSqlRevenue('revenue_tax') . " as revenueTax,
  750. " . LogAggregator::getSqlRevenue('revenue_shipping') . " as revenueShipping,
  751. " . LogAggregator::getSqlRevenue('revenue_discount') . " as revenueDiscount,
  752. items as items,
  753. log_conversion.server_time as serverTimePretty
  754. FROM " . Common::prefixTable('log_conversion') . " AS log_conversion
  755. WHERE idvisit = ?
  756. AND idgoal <= " . GoalManager::IDGOAL_ORDER . "
  757. ORDER BY server_time ASC
  758. LIMIT 0, $actionsLimit";
  759. $ecommerceDetails = Db::fetchAll($sql, array($idVisit));
  760. foreach ($ecommerceDetails as &$ecommerceDetail) {
  761. if ($ecommerceDetail['type'] == Piwik::LABEL_ID_GOAL_IS_ECOMMERCE_CART) {
  762. unset($ecommerceDetail['orderId']);
  763. unset($ecommerceDetail['revenueSubTotal']);
  764. unset($ecommerceDetail['revenueTax']);
  765. unset($ecommerceDetail['revenueShipping']);
  766. unset($ecommerceDetail['revenueDiscount']);
  767. }
  768. // 25.00 => 25
  769. foreach ($ecommerceDetail as $column => $value) {
  770. if (strpos($column, 'revenue') !== false) {
  771. if ($value == round($value)) {
  772. $ecommerceDetail[$column] = round($value);
  773. }
  774. }
  775. }
  776. }
  777. // Enrich ecommerce carts/orders with the list of products
  778. usort($ecommerceDetails, array('static', 'sortByServerTime'));
  779. foreach ($ecommerceDetails as $key => &$ecommerceConversion) {
  780. $sql = "SELECT
  781. log_action_sku.name as itemSKU,
  782. log_action_name.name as itemName,
  783. log_action_category.name as itemCategory,
  784. " . LogAggregator::getSqlRevenue('price') . " as price,
  785. quantity as quantity
  786. FROM " . Common::prefixTable('log_conversion_item') . "
  787. INNER JOIN " . Common::prefixTable('log_action') . " AS log_action_sku
  788. ON idaction_sku = log_action_sku.idaction
  789. LEFT JOIN " . Common::prefixTable('log_action') . " AS log_action_name
  790. ON idaction_name = log_action_name.idaction
  791. LEFT JOIN " . Common::prefixTable('log_action') . " AS log_action_category
  792. ON idaction_category = log_action_category.idaction
  793. WHERE idvisit = ?
  794. AND idorder = ?
  795. AND deleted = 0
  796. LIMIT 0, $actionsLimit
  797. ";
  798. $bind = array($idVisit, isset($ecommerceConversion['orderId'])
  799. ? $ecommerceConversion['orderId']
  800. : GoalManager::ITEM_IDORDER_ABANDONED_CART
  801. );
  802. $itemsDetails = Db::fetchAll($sql, $bind);
  803. foreach ($itemsDetails as &$detail) {
  804. if ($detail['price'] == round($detail['price'])) {
  805. $detail['price'] = round($detail['price']);
  806. }
  807. }
  808. $ecommerceConversion['itemDetails'] = $itemsDetails;
  809. }
  810. $actions = array_merge($actionDetails, $goalDetails, $ecommerceDetails);
  811. usort($actions, array('static', 'sortByServerTime'));
  812. $visitorDetailsArray['actionDetails'] = $actions;
  813. foreach ($visitorDetailsArray['actionDetails'] as &$details) {
  814. switch ($details['type']) {
  815. case 'goal':
  816. $details['icon'] = 'plugins/Morpheus/images/goal.png';
  817. break;
  818. case Piwik::LABEL_ID_GOAL_IS_ECOMMERCE_ORDER:
  819. case Piwik::LABEL_ID_GOAL_IS_ECOMMERCE_CART:
  820. $details['icon'] = 'plugins/Morpheus/images/' . $details['type'] . '.gif';
  821. break;
  822. case Action::TYPE_DOWNLOAD:
  823. $details['type'] = 'download';
  824. $details['icon'] = 'plugins/Morpheus/images/download.png';
  825. break;
  826. case Action::TYPE_OUTLINK:
  827. $details['type'] = 'outlink';
  828. $details['icon'] = 'plugins/Morpheus/images/link.gif';
  829. break;
  830. case Action::TYPE_SITE_SEARCH:
  831. $details['type'] = 'search';
  832. $details['icon'] = 'plugins/Morpheus/images/search_ico.png';
  833. break;
  834. case Action::TYPE_EVENT_CATEGORY:
  835. $details['type'] = 'event';
  836. $details['icon'] = 'plugins/Morpheus/images/event.png';
  837. break;
  838. default:
  839. $details['type'] = 'action';
  840. $details['icon'] = null;
  841. break;
  842. }
  843. // Convert datetimes to the site timezone
  844. $dateTimeVisit = Date::factory($details['serverTimePretty'], $timezone);
  845. $details['serverTimePretty'] = $dateTimeVisit->getLocalized(Piwik::translate('CoreHome_ShortDateFormat') . ' %time%');
  846. }
  847. $visitorDetailsArray['goalConversions'] = count($goalDetails);
  848. return $visitorDetailsArray;
  849. }
  850. private static function getCustomVariablePrettyKey($key)
  851. {
  852. $rename = array(
  853. Tracker\ActionSiteSearch::CVAR_KEY_SEARCH_CATEGORY => Piwik::translate('Actions_ColumnSearchCategory'),
  854. Tracker\ActionSiteSearch::CVAR_KEY_SEARCH_COUNT => Piwik::translate('Actions_ColumnSearchResultsCount'),
  855. );
  856. if (isset($rename[$key])) {
  857. return $rename[$key];
  858. }
  859. return $key;
  860. }
  861. private static function sortByServerTime($a, $b)
  862. {
  863. $ta = strtotime($a['serverTimePretty']);
  864. $tb = strtotime($b['serverTimePretty']);
  865. return $ta < $tb
  866. ? -1
  867. : ($ta == $tb
  868. ? 0
  869. : 1);
  870. }
  871. }