/**
* @param Piwik_Site $oSite
* @param string $strPeriod eg. 'day' 'week' etc.
* @param string $strDate A date range, eg. 'last10', 'previous5' or 'YYYY-MM-DD,YYYY-MM-DD'
*/
function __construct($sites, $strPeriod, $strDate, Piwik_Segment $segment)
{
foreach ($sites as $idSite) {
$archive = Piwik_Archive::build($idSite, $strPeriod, $strDate, $segment->getString());
$archive->setSite(new Piwik_Site($idSite));
$archive->setSegment($segment);
$this->archives[$idSite] = $archive;
}
ksort($this->archives);
}
/**
* Builds an array of Piwik_Archive of a given date range
*
* @param Piwik_Site $oSite
* @param string $strPeriod eg. 'day' 'week' etc.
* @param string $strDate A date range, eg. 'last10', 'previous5' or 'YYYY-MM-DD,YYYY-MM-DD'
*/
function __construct(Piwik_Site $oSite, $strPeriod, $strDate, Piwik_Segment $segment)
{
$rangePeriod = new Piwik_Period_Range($strPeriod, $strDate, $oSite->getTimezone());
foreach ($rangePeriod->getSubperiods() as $subPeriod) {
$startDate = $subPeriod->getDateStart();
$archive = Piwik_Archive::build($oSite->getId(), $strPeriod, $startDate, $segment->getString());
$archive->setSegment($segment);
$this->archives[] = $archive;
}
}
/**
* Generate advisory lock name
*
* @param int $idsite
* @param Piwik_Period $period
* @return string
*/
public static function getArchiveProcessingLockName($idsite, $period, Piwik_Segment $segment)
{
$config = Zend_Registry::get('config');
$lockName = 'piwik.' . $config->database->dbname . '.' . $config->database->tables_prefix . '/' . $idsite . '/' . (!$segment->isEmpty() ? $segment->getHash() . '/' : '') . $period->getId() . '/' . $period->getDateStart()->toString('Y-m-d') . ',' . $period->getDateEnd()->toString('Y-m-d');
return $lockName . '/' . md5($lockName . $config->superuser->salt);
}
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;
}
}
$sqlWhere = "";
if(count($where) > 0)
{
$sqlWhere = "
WHERE " . join("
AND ", $where);
}
$segment = new Piwik_Segment($segment, $idSite);
$segmentSql = $segment->getSql();
$sqlSegment = $segmentSql['sql'];
if(!empty($sqlSegment)) $sqlSegment = ' AND '.$sqlSegment;
$whereBind = array_merge ( $whereBind, $segmentSql['bind'] );
// Subquery to use the indexes for ORDER BY
// Group by idvisit so that a visitor converting 2 goals only appears twice
$sql = "
SELECT sub.*
FROM (
SELECT *
FROM " . Piwik_Common::prefixTable('log_visit') . " AS log_visit
$sqlWhere
$sqlSegment
ORDER BY $orderBy
LIMIT ".(int)$filter_limit."
) AS sub
GROUP BY sub.idvisit
ORDER BY $orderByParent
";
try {
$data = Piwik_FetchAll($sql, $whereBind);
} catch(Exception $e) {
echo $e->getMessage();exit;
}
//var_dump($whereBind); echo($sql);
//var_dump($data);
return $data;
}
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();
}
}
}
/**
* @param Piwik_Segment $segment
* @param Piwik_Period $period
* @return bool
*/
protected static function shouldProcessReportsAllPluginsFor($segment, $period)
{
if ($segment->isEmpty() && $period->getLabel() != 'range') {
return true;
}
$segmentsToProcess = Piwik::getKnownSegmentsToArchive();
if (!empty($segmentsToProcess)) {
// If the requested segment is one of the segments to pre-process
// we ensure that any call to the API will trigger archiving of all reports for this segment
$segment = $segment->getString();
if (in_array($segment, $segmentsToProcess)) {
return true;
}
}
return false;
}
/**
* Generate advisory lock name
*
* @param int $idsite
* @param Piwik_Period $period
* @param Piwik_Segment $segment
* @return string
*/
protected function getProcessingLockName($idsite, $period, $segment)
{
$config = Config::getInstance();
$lockName = 'piwik.' . $config->database['dbname'] . '.' . $config->database['tables_prefix'] . '/' . $idsite . '/' . (!$segment->isEmpty() ? $segment->getHash() . '/' : '') . $period->getId() . '/' . $period->getDateStart()->toString('Y-m-d') . ',' . $period->getDateEnd()->toString('Y-m-d');
$return = $lockName . '/' . md5($lockName . SettingsPiwik::getSalt());
return $return;
}
/**
* Generate advisory lock name
*
* @param int $idsite
* @param Piwik_Period $period
* @return string
*/
public static function getArchiveProcessingLockName($idsite, $period, Piwik_Segment $segment)
{
$config = Piwik_Config::getInstance();
$lockName = 'piwik.' . $config->database['dbname'] . '.' . $config->database['tables_prefix'] . '/' . $idsite . '/' . (!$segment->isEmpty() ? $segment->getHash() . '/' : '') . $period->getId() . '/' . $period->getDateStart()->toString('Y-m-d') . ',' . $period->getDateEnd()->toString('Y-m-d');
return $lockName . '/' . md5($lockName . $config->superuser['salt']);
}
/**
* 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));
}
