public function test_bogusSegment_ThrowsException() { $tests = array('referrerType==not', 'someRandomSegment==not', 'A=B'); foreach ($tests as $segment) { try { $segment = new Piwik_Segment($segment, $idSites = array()); $sql = $segment->getSelectQuery(); $this->fail(); } catch (Exception $e) { // var_dump($e->getMessage()); $this->pass(); } } }
private function loadLastVisitorDetailsFromDatabase($idSite, $period = false, $date = false, $segment = false, $filter_limit = false, $maxIdVisit = false, $visitorId = false, $minTimestamp = false) { // var_dump($period); var_dump($date); var_dump($filter_limit); var_dump($maxIdVisit); var_dump($visitorId); //var_dump($minTimestamp); if (empty($filter_limit)) { $filter_limit = 100; } $where = $whereBind = array(); $where[] = "log_visit.idsite = ? "; $whereBind[] = $idSite; $orderBy = "idsite, visit_last_action_time DESC"; $orderByParent = "sub.visit_last_action_time DESC"; if (!empty($visitorId)) { $where[] = "log_visit.idvisitor = ? "; $whereBind[] = @Piwik_Common::hex2bin($visitorId); } if (!empty($maxIdVisit)) { $where[] = "log_visit.idvisit < ? "; $whereBind[] = $maxIdVisit; $orderBy = "idvisit DESC"; $orderByParent = "sub.idvisit DESC"; } if (!empty($minTimestamp)) { $where[] = "log_visit.visit_last_action_time > ? "; $whereBind[] = date("Y-m-d H:i:s", $minTimestamp); } // If no other filter, only look at the last 24 hours of stats if (empty($visitorId) && empty($maxIdVisit) && empty($period) && empty($date)) { $period = 'day'; $date = 'yesterdaySameTime'; } // SQL Filter with provided period if (!empty($period) && !empty($date)) { $currentSite = new Piwik_Site($idSite); $currentTimezone = $currentSite->getTimezone(); $dateString = $date; if ($period == 'range') { $processedPeriod = new Piwik_Period_Range('range', $date); if ($parsedDate = Piwik_Period_Range::parseDateRange($date)) { $dateString = $parsedDate[2]; } } else { $processedDate = Piwik_Date::factory($date); if ($date == 'today' || $date == 'now' || $processedDate->toString() == Piwik_Date::factory('now', $currentTimezone)->toString()) { $processedDate = $processedDate->subDay(1); } $processedPeriod = Piwik_Period::factory($period, $processedDate); } $dateStart = $processedPeriod->getDateStart()->setTimezone($currentTimezone); $where[] = "log_visit.visit_last_action_time >= ?"; $whereBind[] = $dateStart->toString('Y-m-d H:i:s'); if (!in_array($date, array('now', 'today', 'yesterdaySameTime')) && strpos($date, 'last') === false && strpos($date, 'previous') === false && Piwik_Date::factory($dateString)->toString('Y-m-d') != Piwik_Date::factory('now', $currentTimezone)->toString()) { $dateEnd = $processedPeriod->getDateEnd()->setTimezone($currentTimezone); $where[] = " log_visit.visit_last_action_time <= ?"; $dateEndString = $dateEnd->addDay(1)->toString('Y-m-d H:i:s'); $whereBind[] = $dateEndString; } } if (count($where) > 0) { $where = join(" \n\t\t\t\tAND ", $where); } else { $where = false; } $segment = new Piwik_Segment($segment, $idSite); // Subquery to use the indexes for ORDER BY $select = "log_visit.*"; $from = "log_visit"; $subQuery = $segment->getSelectQuery($select, $from, $where, $whereBind, $orderBy); $sqlLimit = $filter_limit >= 1 ? " LIMIT " . (int) $filter_limit : ""; // Group by idvisit so that a visitor converting 2 goals only appears once $sql = "\n\t\t\tSELECT sub.* \n\t\t\tFROM ( \n\t\t\t\t" . $subQuery['sql'] . "\n\t\t\t\t{$sqlLimit}\n\t\t\t) AS sub\n\t\t\tGROUP BY sub.idvisit\n\t\t\tORDER BY {$orderByParent}\n\t\t"; try { $data = Piwik_FetchAll($sql, $subQuery['bind']); } catch (Exception $e) { echo $e->getMessage(); exit; } //var_dump($whereBind); echo($sql); //var_dump($data); return $data; }
/** * join conversion on visit, then actions * make sure actions are joined before conversions * * @group Core * @group Segment */ public function testGetSelectQueryJoinConversionAndActionOnVisit() { $select = 'log_visit.*'; $from = 'log_visit'; $where = false; $bind = array(); $segment = 'visitConvertedGoalId==1;visitServerHour==12;customVariablePageName1==Test'; $segment = new Piwik_Segment($segment, $idSites = array()); $query = $segment->getSelectQuery($select, $from, $where, $bind); $expected = array("sql" => "\n SELECT\n log_inner.*\n FROM\n (\n SELECT\n log_visit.*\n FROM\n " . Piwik_Common::prefixTable('log_visit') . " AS log_visit\n LEFT JOIN " . Piwik_Common::prefixTable('log_link_visit_action') . " AS log_link_visit_action ON log_link_visit_action.idvisit = log_visit.idvisit\n LEFT JOIN " . Piwik_Common::prefixTable('log_conversion') . " AS log_conversion ON log_conversion.idlink_va = log_link_visit_action.idlink_va AND log_conversion.idsite = log_link_visit_action.idsite\n WHERE\n log_conversion.idgoal = ? AND HOUR(log_visit.visit_last_action_time) = ? AND log_link_visit_action.custom_var_k1 = ?\n GROUP BY log_visit.idvisit\n ) AS log_inner", "bind" => array(1, 12, 'Test')); $this->assertEquals($this->_filterWhitsSpaces($expected), $this->_filterWhitsSpaces($query)); }