PageRenderTime 44ms CodeModel.GetById 17ms RepoModel.GetById 1ms app.codeStats 0ms

/plugins/Live/API.php

https://github.com/CodeYellowBV/piwik
PHP | 722 lines | 453 code | 108 blank | 161 comment | 79 complexity | e7d9e36e5a336393fdd552a0d9f48f0c MD5 | raw file
Possible License(s): LGPL-3.0, JSON, MIT, GPL-3.0, LGPL-2.1, GPL-2.0, AGPL-1.0, BSD-2-Clause, BSD-3-Clause
  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 Exception;
  11. use Piwik\Common;
  12. use Piwik\Config;
  13. use Piwik\DataTable;
  14. use Piwik\DataTable\Row;
  15. use Piwik\Date;
  16. use Piwik\Db;
  17. use Piwik\MetricsFormatter;
  18. use Piwik\Period\Range;
  19. use Piwik\Period;
  20. use Piwik\Piwik;
  21. use Piwik\Plugins\Referrers\API as APIReferrers;
  22. use Piwik\Plugins\SitesManager\API as APISitesManager;
  23. use Piwik\Segment;
  24. use Piwik\Site;
  25. use Piwik\Tracker;
  26. /**
  27. * @see plugins/Live/Visitor.php
  28. */
  29. require_once PIWIK_INCLUDE_PATH . '/plugins/Live/Visitor.php';
  30. /**
  31. * The Live! API lets you access complete visit level information about your visitors. Combined with the power of <a href='http://piwik.org/docs/analytics-api/segmentation/' target='_blank'>Segmentation</a>,
  32. * you will be able to request visits filtered by any criteria.
  33. *
  34. * The method "getLastVisitsDetails" will return extensive data for each visit, which includes: server time, visitId, visitorId,
  35. * visitorType (new or returning), number of pages, list of all pages (and events, file downloaded and outlinks clicked),
  36. * custom variables names and values set to this visit, number of goal conversions (and list of all Goal conversions for this visit,
  37. * with time of conversion, revenue, URL, etc.), but also other attributes such as: days since last visit, days since first visit,
  38. * country, continent, visitor IP,
  39. * provider, referrer used (referrer name, keyword if it was a search engine, full URL), campaign name and keyword, operating system,
  40. * browser, type of screen, resolution, supported browser plugins (flash, java, silverlight, pdf, etc.), various dates & times format to make
  41. * it easier for API users... and more!
  42. *
  43. * With the parameter <a href='http://piwik.org/docs/analytics-api/segmentation/' target='_blank'>'&segment='</a> you can filter the
  44. * returned visits by any criteria (visitor IP, visitor ID, country, keyword used, time of day, etc.).
  45. *
  46. * The method "getCounters" is used to return a simple counter: visits, number of actions, number of converted visits, in the last N minutes.
  47. *
  48. * See also the documentation about <a href='http://piwik.org/docs/real-time/' target='_blank'>Real time widget and visitor level reports</a> in Piwik.
  49. * @method static \Piwik\Plugins\Live\API getInstance()
  50. */
  51. class API extends \Piwik\Plugin\API
  52. {
  53. const VISITOR_PROFILE_MAX_VISITS_TO_AGGREGATE = 100;
  54. const VISITOR_PROFILE_MAX_VISITS_TO_SHOW = 10;
  55. const VISITOR_PROFILE_DATE_FORMAT = '%day% %shortMonth% %longYear%';
  56. /**
  57. * This will return simple counters, for a given website ID, for visits over the last N minutes
  58. *
  59. * @param int $idSite Id Site
  60. * @param int $lastMinutes Number of minutes to look back at
  61. * @param bool|string $segment
  62. * @return array( visits => N, actions => M, visitsConverted => P )
  63. */
  64. public function getCounters($idSite, $lastMinutes, $segment = false)
  65. {
  66. Piwik::checkUserHasViewAccess($idSite);
  67. $lastMinutes = (int)$lastMinutes;
  68. $select = "count(*) as visits,
  69. SUM(log_visit.visit_total_actions) as actions,
  70. SUM(log_visit.visit_goal_converted) as visitsConverted,
  71. COUNT(DISTINCT log_visit.idvisitor) as visitors";
  72. $from = "log_visit";
  73. list($whereIdSites, $idSites) = $this->getIdSitesWhereClause($idSite);
  74. $where = $whereIdSites . "AND log_visit.visit_last_action_time >= ?";
  75. $bind = $idSites;
  76. $bind[] = Date::factory(time() - $lastMinutes * 60)->toString('Y-m-d H:i:s');
  77. $segment = new Segment($segment, $idSite);
  78. $query = $segment->getSelectQuery($select, $from, $where, $bind);
  79. $data = Db::fetchAll($query['sql'], $query['bind']);
  80. // These could be unset for some reasons, ensure they are set to 0
  81. if (empty($data[0]['actions'])) {
  82. $data[0]['actions'] = 0;
  83. }
  84. if (empty($data[0]['visitsConverted'])) {
  85. $data[0]['visitsConverted'] = 0;
  86. }
  87. return $data;
  88. }
  89. /**
  90. * The same functionnality can be obtained using segment=visitorId==$visitorId with getLastVisitsDetails
  91. *
  92. * @deprecated
  93. * @ignore
  94. * @param int $visitorId
  95. * @param int $idSite
  96. * @param int $filter_limit
  97. * @param bool $flat Whether to flatten the visitor details array
  98. *
  99. * @return DataTable
  100. */
  101. public function getLastVisitsForVisitor($visitorId, $idSite, $filter_limit = 10, $flat = false)
  102. {
  103. Piwik::checkUserHasViewAccess($idSite);
  104. $countVisitorsToFetch = $filter_limit;
  105. $table = $this->loadLastVisitorDetailsFromDatabase($idSite, $period = false, $date = false, $segment = false, $countVisitorsToFetch, $visitorId);
  106. $this->addFilterToCleanVisitors($table, $idSite, $flat);
  107. return $table;
  108. }
  109. /**
  110. * Returns the last visits tracked in the specified website
  111. * You can define any number of filters: none, one, many or all parameters can be defined
  112. *
  113. * @param int $idSite Site ID
  114. * @param bool|string $period Period to restrict to when looking at the logs
  115. * @param bool|string $date Date to restrict to
  116. * @param bool|int $segment (optional) Number of visits rows to return
  117. * @param bool|int $countVisitorsToFetch (optional) Only return the last X visits. By default the last GET['filter_offset']+GET['filter_limit'] are returned.
  118. * @param bool|int $minTimestamp (optional) Minimum timestamp to restrict the query to (useful when paginating or refreshing visits)
  119. * @param bool $flat
  120. * @param bool $doNotFetchActions
  121. * @return DataTable
  122. */
  123. public function getLastVisitsDetails($idSite, $period = false, $date = false, $segment = false, $countVisitorsToFetch = false, $minTimestamp = false, $flat = false, $doNotFetchActions = false)
  124. {
  125. if (false === $countVisitorsToFetch) {
  126. $filter_limit = Common::getRequestVar('filter_limit', 10, 'int');
  127. $filter_offset = Common::getRequestVar('filter_offset', 0, 'int');
  128. $countVisitorsToFetch = $filter_limit + $filter_offset;
  129. }
  130. $filterSortOrder = Common::getRequestVar('filter_sort_order', false, 'string');
  131. Piwik::checkUserHasViewAccess($idSite);
  132. $dataTable = $this->loadLastVisitorDetailsFromDatabase($idSite, $period, $date, $segment, $countVisitorsToFetch, $visitorId = false, $minTimestamp, $filterSortOrder);
  133. $this->addFilterToCleanVisitors($dataTable, $idSite, $flat, $doNotFetchActions);
  134. return $dataTable;
  135. }
  136. /**
  137. * Returns an array describing a visitor using her last visits (uses a maximum of 100).
  138. *
  139. * @param int $idSite Site ID
  140. * @param bool|false|string $visitorId The ID of the visitor whose profile to retrieve.
  141. * @param bool|false|string $segment
  142. * @param bool $checkForLatLong If true, hasLatLong will appear in the output and be true if
  143. * one of the first 100 visits has a latitude/longitude.
  144. * @return array
  145. */
  146. public function getVisitorProfile($idSite, $visitorId = false, $segment = false, $checkForLatLong = false)
  147. {
  148. Piwik::checkUserHasViewAccess($idSite);
  149. if ($visitorId === false) {
  150. $visitorId = $this->getMostRecentVisitorId($idSite, $segment);
  151. }
  152. $newSegment = ($segment === false ? '' : $segment . ';') . 'visitorId==' . $visitorId;
  153. $visits = $this->loadLastVisitorDetailsFromDatabase($idSite, $period = false, $date = false, $newSegment,
  154. $numVisitorsToFetch = self::VISITOR_PROFILE_MAX_VISITS_TO_AGGREGATE,
  155. $overrideVisitorId = false,
  156. $minTimestamp = false);
  157. $this->addFilterToCleanVisitors($visits, $idSite, $flat = false, $doNotFetchActions = false, $filterNow = true);
  158. if ($visits->getRowsCount() == 0) {
  159. return array();
  160. }
  161. $isEcommerceEnabled = Site::isEcommerceEnabledFor($idSite);
  162. $result = array();
  163. $result['totalVisits'] = 0;
  164. $result['totalVisitDuration'] = 0;
  165. $result['totalActions'] = 0;
  166. $result['totalSearches'] = 0;
  167. $result['totalPageViews'] = 0;
  168. $result['totalGoalConversions'] = 0;
  169. $result['totalConversionsByGoal'] = array();
  170. if ($isEcommerceEnabled) {
  171. $result['totalEcommerceConversions'] = 0;
  172. $result['totalEcommerceRevenue'] = 0;
  173. $result['totalEcommerceItems'] = 0;
  174. $result['totalAbandonedCarts'] = 0;
  175. $result['totalAbandonedCartsRevenue'] = 0;
  176. $result['totalAbandonedCartsItems'] = 0;
  177. }
  178. $countries = array();
  179. $continents = array();
  180. $cities = array();
  181. $siteSearchKeywords = array();
  182. $pageGenerationTimeTotal = 0;
  183. // aggregate all requested visits info for total_* info
  184. foreach ($visits->getRows() as $visit) {
  185. ++$result['totalVisits'];
  186. $result['totalVisitDuration'] += $visit->getColumn('visitDuration');
  187. $result['totalActions'] += $visit->getColumn('actions');
  188. $result['totalGoalConversions'] += $visit->getColumn('goalConversions');
  189. // individual goal conversions are stored in action details
  190. foreach ($visit->getColumn('actionDetails') as $action) {
  191. if ($action['type'] == 'goal') {
  192. // handle goal conversion
  193. $idGoal = $action['goalId'];
  194. $idGoalKey = 'idgoal=' . $idGoal;
  195. if (!isset($result['totalConversionsByGoal'][$idGoalKey])) {
  196. $result['totalConversionsByGoal'][$idGoalKey] = 0;
  197. }
  198. ++$result['totalConversionsByGoal'][$idGoalKey];
  199. if (!empty($action['revenue'])) {
  200. if (!isset($result['totalRevenueByGoal'][$idGoalKey])) {
  201. $result['totalRevenueByGoal'][$idGoalKey] = 0;
  202. }
  203. $result['totalRevenueByGoal'][$idGoalKey] += $action['revenue'];
  204. }
  205. } else if ($action['type'] == Piwik::LABEL_ID_GOAL_IS_ECOMMERCE_ORDER // handle ecommerce order
  206. && $isEcommerceEnabled
  207. ) {
  208. ++$result['totalEcommerceConversions'];
  209. $result['totalEcommerceRevenue'] += $action['revenue'];
  210. $result['totalEcommerceItems'] += $action['items'];
  211. } else if ($action['type'] == Piwik::LABEL_ID_GOAL_IS_ECOMMERCE_CART // handler abandoned cart
  212. && $isEcommerceEnabled
  213. ) {
  214. ++$result['totalAbandonedCarts'];
  215. $result['totalAbandonedCartsRevenue'] += $action['revenue'];
  216. $result['totalAbandonedCartsItems'] += $action['items'];
  217. }
  218. if (isset($action['siteSearchKeyword'])) {
  219. $keyword = $action['siteSearchKeyword'];
  220. if (!isset($siteSearchKeywords[$keyword])) {
  221. $siteSearchKeywords[$keyword] = 0;
  222. ++$result['totalSearches'];
  223. }
  224. ++$siteSearchKeywords[$keyword];
  225. }
  226. if (isset($action['generationTime'])) {
  227. $pageGenerationTimeTotal += $action['generationTime'];
  228. ++$result['totalPageViews'];
  229. }
  230. }
  231. $countryCode = $visit->getColumn('countryCode');
  232. if (!isset($countries[$countryCode])) {
  233. $countries[$countryCode] = 0;
  234. }
  235. ++$countries[$countryCode];
  236. $continentCode = $visit->getColumn('continentCode');
  237. if (!isset($continents[$continentCode])) {
  238. $continents[$continentCode] = 0;
  239. }
  240. ++$continents[$continentCode];
  241. if (!array_key_exists($countryCode, $cities)) {
  242. $cities[$countryCode] = array();
  243. }
  244. $city = $visit->getColumn('city');
  245. if(!empty($city)) {
  246. $cities[$countryCode][] = $city;
  247. }
  248. }
  249. // sort countries/continents/search keywords by visit/action
  250. asort($countries);
  251. asort($continents);
  252. arsort($siteSearchKeywords);
  253. // transform country/continents/search keywords into something that will look good in XML
  254. $result['countries'] = $result['continents'] = $result['searches'] = array();
  255. foreach ($countries as $countryCode => $nbVisits) {
  256. $countryInfo = array('country' => $countryCode,
  257. 'nb_visits' => $nbVisits,
  258. 'flag' => \Piwik\Plugins\UserCountry\getFlagFromCode($countryCode),
  259. 'prettyName' => \Piwik\Plugins\UserCountry\countryTranslate($countryCode));
  260. if(!empty($cities[$countryCode])) {
  261. $countryInfo['cities'] = array_unique($cities[$countryCode]);
  262. }
  263. $result['countries'][] = $countryInfo;
  264. }
  265. foreach ($continents as $continentCode => $nbVisits) {
  266. $result['continents'][] = array('continent' => $continentCode,
  267. 'nb_visits' => $nbVisits,
  268. 'prettyName' => \Piwik\Plugins\UserCountry\continentTranslate($continentCode));
  269. }
  270. foreach ($siteSearchKeywords as $keyword => $searchCount) {
  271. $result['searches'][] = array('keyword' => $keyword,
  272. 'searches' => $searchCount);
  273. }
  274. if ($result['totalPageViews']) {
  275. $result['averagePageGenerationTime'] =
  276. round($pageGenerationTimeTotal / $result['totalPageViews'], $precision = 2);
  277. }
  278. $result['totalVisitDurationPretty'] = MetricsFormatter::getPrettyTimeFromSeconds($result['totalVisitDuration']);
  279. // use requested visits for first/last visit info
  280. $rows = $visits->getRows();
  281. $result['firstVisit'] = $this->getVisitorProfileVisitSummary(end($rows));
  282. $result['lastVisit'] = $this->getVisitorProfileVisitSummary(reset($rows));
  283. // check if requested visits have lat/long
  284. if ($checkForLatLong) {
  285. $result['hasLatLong'] = false;
  286. foreach ($rows as $visit) {
  287. if ($visit->getColumn('latitude') !== false) { // realtime map only checks for latitude
  288. $result['hasLatLong'] = true;
  289. break;
  290. }
  291. }
  292. }
  293. // save count of visits we queries
  294. $result['visitsAggregated'] = count($rows);
  295. // use N most recent visits for last_visits
  296. $visits->deleteRowsOffset(self::VISITOR_PROFILE_MAX_VISITS_TO_SHOW);
  297. $result['lastVisits'] = $visits;
  298. // use the right date format for the pretty server date
  299. $timezone = Site::getTimezoneFor($idSite);
  300. foreach ($result['lastVisits']->getRows() as $visit) {
  301. $dateTimeVisitFirstAction = Date::factory($visit->getColumn('firstActionTimestamp'), $timezone);
  302. $datePretty = $dateTimeVisitFirstAction->getLocalized(self::VISITOR_PROFILE_DATE_FORMAT);
  303. $visit->setColumn('serverDatePrettyFirstAction', $datePretty);
  304. $dateTimePretty = $datePretty . ' ' . $visit->getColumn('serverTimePrettyFirstAction');
  305. $visit->setColumn('serverDateTimePrettyFirstAction', $dateTimePretty);
  306. }
  307. // get visitor IDs that are adjacent to this one in log_visit
  308. // TODO: make sure order of visitor ids is not changed if a returning visitor visits while the user is
  309. // looking at the popup.
  310. $latestVisitTime = reset($rows)->getColumn('lastActionDateTime');
  311. $result['nextVisitorId'] = $this->getAdjacentVisitorId($idSite, $visitorId, $latestVisitTime, $segment, $getNext = true);
  312. $result['previousVisitorId'] = $this->getAdjacentVisitorId($idSite, $visitorId, $latestVisitTime, $segment, $getNext = false);
  313. /**
  314. * Triggered in the Live.getVisitorProfile API method. Plugins can use this event
  315. * to discover and add extra data to visitor profiles.
  316. *
  317. * For example, if an email address is found in a custom variable, a plugin could load the
  318. * gravatar for the email and add it to the visitor profile, causing it to display in the
  319. * visitor profile popup.
  320. *
  321. * The following visitor profile elements can be set to augment the visitor profile popup:
  322. *
  323. * - **visitorAvatar**: A URL to an image to display in the top left corner of the popup.
  324. * - **visitorDescription**: Text to be used as the tooltip of the avatar image.
  325. *
  326. * @param array &$visitorProfile The unaugmented visitor profile info.
  327. */
  328. Piwik::postEvent('Live.getExtraVisitorDetails', array(&$result));
  329. return $result;
  330. }
  331. /**
  332. * Returns the visitor ID of the most recent visit.
  333. *
  334. * @param int $idSite
  335. * @param bool|string $segment
  336. * @return string
  337. */
  338. public function getMostRecentVisitorId($idSite, $segment = false)
  339. {
  340. Piwik::checkUserHasViewAccess($idSite);
  341. $dataTable = $this->loadLastVisitorDetailsFromDatabase(
  342. $idSite, $period = false, $date = false, $segment, $numVisitorsToFetch = 1,
  343. $visitorId = false, $minTimestamp = false
  344. );
  345. if (0 >= $dataTable->getRowsCount()) {
  346. return false;
  347. }
  348. $visitDetails = $dataTable->getFirstRow()->getColumns();
  349. $visitor = new Visitor($visitDetails);
  350. return $visitor->getVisitorId();
  351. }
  352. /**
  353. * Returns the ID of a visitor that is adjacent to another visitor (by time of last action)
  354. * in the log_visit table.
  355. *
  356. * @param int $idSite The ID of the site whose visits should be looked at.
  357. * @param string $visitorId The ID of the visitor to get an adjacent visitor for.
  358. * @param string $visitLastActionTime The last action time of the latest visit for $visitorId.
  359. * @param string $segment
  360. * @param bool $getNext Whether to retrieve the next visitor or the previous visitor. The next
  361. * visitor will be the visitor that appears chronologically later in the
  362. * log_visit table. The previous visitor will be the visitor that appears
  363. * earlier.
  364. * @return string The hex visitor ID.
  365. */
  366. private function getAdjacentVisitorId($idSite, $visitorId, $visitLastActionTime, $segment, $getNext)
  367. {
  368. if ($getNext) {
  369. $visitLastActionTimeCondition = "sub.visit_last_action_time <= ?";
  370. $orderByDir = "DESC";
  371. } else {
  372. $visitLastActionTimeCondition = "sub.visit_last_action_time >= ?";
  373. $orderByDir = "ASC";
  374. }
  375. $visitLastActionDate = Date::factory($visitLastActionTime);
  376. $dateOneDayAgo = $visitLastActionDate->subDay(1);
  377. $dateOneDayInFuture = $visitLastActionDate->addDay(1);
  378. $select = "log_visit.idvisitor, MAX(log_visit.visit_last_action_time) as visit_last_action_time";
  379. $from = "log_visit";
  380. $where = "log_visit.idsite = ? AND log_visit.idvisitor <> ? AND visit_last_action_time >= ? and visit_last_action_time <= ?";
  381. $whereBind = array($idSite, @Common::hex2bin($visitorId), $dateOneDayAgo->toString('Y-m-d H:i:s'), $dateOneDayInFuture->toString('Y-m-d H:i:s'));
  382. $orderBy = "MAX(log_visit.visit_last_action_time) $orderByDir";
  383. $groupBy = "log_visit.idvisitor";
  384. $segment = new Segment($segment, $idSite);
  385. $queryInfo = $segment->getSelectQuery($select, $from, $where, $whereBind, $orderBy, $groupBy);
  386. $sql = "SELECT sub.idvisitor, sub.visit_last_action_time
  387. FROM ({$queryInfo['sql']}) as sub
  388. WHERE $visitLastActionTimeCondition
  389. LIMIT 1";
  390. $bind = array_merge($queryInfo['bind'], array($visitLastActionTime));
  391. $visitorId = Db::fetchOne($sql, $bind);
  392. if (!empty($visitorId)) {
  393. $visitorId = bin2hex($visitorId);
  394. }
  395. return $visitorId;
  396. }
  397. /**
  398. * Returns a summary for an important visit. Used to describe the first & last visits of a visitor.
  399. *
  400. * @param Row $visit
  401. * @return array
  402. */
  403. private function getVisitorProfileVisitSummary($visit)
  404. {
  405. $today = Date::today();
  406. $serverDate = $visit->getColumn('firstActionTimestamp');
  407. return array(
  408. 'date' => $serverDate,
  409. 'prettyDate' => Date::factory($serverDate)->getLocalized(self::VISITOR_PROFILE_DATE_FORMAT),
  410. 'daysAgo' => (int)Date::secondsToDays($today->getTimestamp() - Date::factory($serverDate)->getTimestamp()),
  411. 'referrerType' => $visit->getColumn('referrerType'),
  412. 'referralSummary' => self::getReferrerSummaryForVisit($visit),
  413. );
  414. }
  415. /**
  416. * Returns a summary for a visit's referral.
  417. *
  418. * @param Row $visit
  419. * @return bool|mixed|string
  420. * @ignore
  421. */
  422. public static function getReferrerSummaryForVisit($visit)
  423. {
  424. $referrerType = $visit->getColumn('referrerType');
  425. if ($referrerType === false
  426. || $referrerType == 'direct'
  427. ) {
  428. $result = Piwik::translate('Referrers_DirectEntry');
  429. } else if ($referrerType == 'search') {
  430. $result = $visit->getColumn('referrerName');
  431. $keyword = $visit->getColumn('referrerKeyword');
  432. if ($keyword !== false
  433. && $keyword != APIReferrers::getKeywordNotDefinedString()
  434. ) {
  435. $result .= ' (' . $keyword . ')';
  436. }
  437. } else if ($referrerType == 'campaign') {
  438. $result = Piwik::translate('Referrers_ColumnCampaign') . ' (' . $visit->getColumn('referrerName') . ')';
  439. } else {
  440. $result = $visit->getColumn('referrerName');
  441. }
  442. return $result;
  443. }
  444. /**
  445. * @deprecated
  446. */
  447. public function getLastVisits($idSite, $filter_limit = 10, $minTimestamp = false)
  448. {
  449. return $this->getLastVisitsDetails($idSite, $period = false, $date = false, $segment = false, $countVisitorsToFetch = $filter_limit, $minTimestamp, $flat = false);
  450. }
  451. /**
  452. * For an array of visits, query the list of pages for this visit
  453. * as well as make the data human readable
  454. * @param DataTable $dataTable
  455. * @param int $idSite
  456. * @param bool $flat whether to flatten the array (eg. 'customVariables' names/values will appear in the root array rather than in 'customVariables' key
  457. * @param bool $doNotFetchActions If set to true, we only fetch visit info and not actions (much faster)
  458. * @param bool $filterNow If true, the visitors will be cleaned immediately
  459. */
  460. private function addFilterToCleanVisitors(DataTable $dataTable, $idSite, $flat = false, $doNotFetchActions = false, $filterNow = false)
  461. {
  462. $filter = 'queueFilter';
  463. if ($filterNow) {
  464. $filter = 'filter';
  465. }
  466. $dataTable->$filter(function ($table) use ($idSite, $flat, $doNotFetchActions) {
  467. /** @var DataTable $table */
  468. $actionsLimit = (int)Config::getInstance()->General['visitor_log_maximum_actions_per_visit'];
  469. $website = new Site($idSite);
  470. $timezone = $website->getTimezone();
  471. $currency = $website->getCurrency();
  472. $currencies = APISitesManager::getInstance()->getCurrencySymbols();
  473. // live api is not summable, prevents errors like "Unexpected ECommerce status value"
  474. $table->deleteRow(DataTable::ID_SUMMARY_ROW);
  475. foreach ($table->getRows() as $visitorDetailRow) {
  476. $visitorDetailsArray = Visitor::cleanVisitorDetails($visitorDetailRow->getColumns());
  477. $visitor = new Visitor($visitorDetailsArray);
  478. $visitorDetailsArray = $visitor->getAllVisitorDetails();
  479. $visitorDetailsArray['siteCurrency'] = $currency;
  480. $visitorDetailsArray['siteCurrencySymbol'] = @$currencies[$visitorDetailsArray['siteCurrency']];
  481. $visitorDetailsArray['serverTimestamp'] = $visitorDetailsArray['lastActionTimestamp'];
  482. $dateTimeVisit = Date::factory($visitorDetailsArray['lastActionTimestamp'], $timezone);
  483. if($dateTimeVisit) {
  484. $visitorDetailsArray['serverTimePretty'] = $dateTimeVisit->getLocalized('%time%');
  485. $visitorDetailsArray['serverDatePretty'] = $dateTimeVisit->getLocalized(Piwik::translate('CoreHome_ShortDateFormat'));
  486. }
  487. $dateTimeVisitFirstAction = Date::factory($visitorDetailsArray['firstActionTimestamp'], $timezone);
  488. $visitorDetailsArray['serverDatePrettyFirstAction'] = $dateTimeVisitFirstAction->getLocalized(Piwik::translate('CoreHome_ShortDateFormat'));
  489. $visitorDetailsArray['serverTimePrettyFirstAction'] = $dateTimeVisitFirstAction->getLocalized('%time%');
  490. $visitorDetailsArray['actionDetails'] = array();
  491. if (!$doNotFetchActions) {
  492. $visitorDetailsArray = Visitor::enrichVisitorArrayWithActions($visitorDetailsArray, $actionsLimit, $timezone);
  493. }
  494. if ($flat) {
  495. $visitorDetailsArray = Visitor::flattenVisitorDetailsArray($visitorDetailsArray);
  496. }
  497. $visitorDetailRow->setColumns($visitorDetailsArray);
  498. }
  499. });
  500. }
  501. private function loadLastVisitorDetailsFromDatabase($idSite, $period, $date, $segment = false, $countVisitorsToFetch = 100, $visitorId = false, $minTimestamp = false, $filterSortOrder = false)
  502. {
  503. $where = $whereBind = array();
  504. list($whereClause, $idSites) = $this->getIdSitesWhereClause($idSite);
  505. $where[] = $whereClause;
  506. $whereBind = $idSites;
  507. if (strtolower($filterSortOrder) !== 'asc') {
  508. $filterSortOrder = 'DESC';
  509. }
  510. $orderBy = "idsite, visit_last_action_time " . $filterSortOrder;
  511. $orderByParent = "sub.visit_last_action_time " . $filterSortOrder;
  512. if (!empty($visitorId)) {
  513. $where[] = "log_visit.idvisitor = ? ";
  514. $whereBind[] = @Common::hex2bin($visitorId);
  515. }
  516. if (!empty($minTimestamp)) {
  517. $where[] = "log_visit.visit_last_action_time > ? ";
  518. $whereBind[] = date("Y-m-d H:i:s", $minTimestamp);
  519. }
  520. // If no other filter, only look at the last 24 hours of stats
  521. if (empty($visitorId)
  522. && empty($countVisitorsToFetch)
  523. && empty($period)
  524. && empty($date)
  525. ) {
  526. $period = 'day';
  527. $date = 'yesterdaySameTime';
  528. }
  529. // SQL Filter with provided period
  530. if (!empty($period) && !empty($date)) {
  531. $currentSite = new Site($idSite);
  532. $currentTimezone = $currentSite->getTimezone();
  533. $dateString = $date;
  534. if ($period == 'range') {
  535. $processedPeriod = new Range('range', $date);
  536. if ($parsedDate = Range::parseDateRange($date)) {
  537. $dateString = $parsedDate[2];
  538. }
  539. } else {
  540. $processedDate = Date::factory($date);
  541. if ($date == 'today'
  542. || $date == 'now'
  543. || $processedDate->toString() == Date::factory('now', $currentTimezone)->toString()
  544. ) {
  545. $processedDate = $processedDate->subDay(1);
  546. }
  547. $processedPeriod = Period\Factory::build($period, $processedDate);
  548. }
  549. $dateStart = $processedPeriod->getDateStart()->setTimezone($currentTimezone);
  550. $where[] = "log_visit.visit_last_action_time >= ?";
  551. $whereBind[] = $dateStart->toString('Y-m-d H:i:s');
  552. if (!in_array($date, array('now', 'today', 'yesterdaySameTime'))
  553. && strpos($date, 'last') === false
  554. && strpos($date, 'previous') === false
  555. && Date::factory($dateString)->toString('Y-m-d') != Date::factory('now', $currentTimezone)->toString()
  556. ) {
  557. $dateEnd = $processedPeriod->getDateEnd()->setTimezone($currentTimezone);
  558. $where[] = " log_visit.visit_last_action_time <= ?";
  559. $dateEndString = $dateEnd->addDay(1)->toString('Y-m-d H:i:s');
  560. $whereBind[] = $dateEndString;
  561. }
  562. }
  563. if (count($where) > 0) {
  564. $where = join("
  565. AND ", $where);
  566. } else {
  567. $where = false;
  568. }
  569. $segment = new Segment($segment, $idSite);
  570. // Subquery to use the indexes for ORDER BY
  571. $select = "log_visit.*";
  572. $from = "log_visit";
  573. $subQuery = $segment->getSelectQuery($select, $from, $where, $whereBind, $orderBy);
  574. $sqlLimit = $countVisitorsToFetch >= 1 ? " LIMIT 0, " . (int)$countVisitorsToFetch : "";
  575. // Group by idvisit so that a visitor converting 2 goals only appears once
  576. $sql = "
  577. SELECT sub.*
  578. FROM (
  579. " . $subQuery['sql'] . "
  580. $sqlLimit
  581. ) AS sub
  582. GROUP BY sub.idvisit
  583. ORDER BY $orderByParent
  584. ";
  585. try {
  586. $data = Db::fetchAll($sql, $subQuery['bind']);
  587. } catch (Exception $e) {
  588. echo $e->getMessage();
  589. exit;
  590. }
  591. $dataTable = new DataTable();
  592. $dataTable->addRowsFromSimpleArray($data);
  593. // $dataTable->disableFilter('Truncate');
  594. if (!empty($data[0])) {
  595. $columnsToNotAggregate = array_map(function () {
  596. return 'skip';
  597. }, $data[0]);
  598. $dataTable->setMetadata(DataTable::COLUMN_AGGREGATION_OPS_METADATA_NAME, $columnsToNotAggregate);
  599. }
  600. return $dataTable;
  601. }
  602. /**
  603. * @param $idSite
  604. * @return array
  605. */
  606. private function getIdSitesWhereClause($idSite)
  607. {
  608. $idSites = array($idSite);
  609. Piwik::postEvent('Live.API.getIdSitesString', array(&$idSites));
  610. $idSitesBind = Common::getSqlStringFieldsArray($idSites);
  611. $whereClause = "log_visit.idsite in ($idSitesBind) ";
  612. return array($whereClause, $idSites);
  613. }
  614. }