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));
}
