public function updateFunnel($idSite, $idGoal, $idFunnel, $steps = array())
{
Piwik::checkUserHasAdminAccess($idSite);
$currentStepIds = array();
foreach ($steps as &$step) {
$idStep = $step['id'];
if (!is_numeric($idStep)) {
continue;
}
$currentStepIds[] = $idStep;
$name = $this->checkName($step['name']);
$url = $this->checkUrl($step['url']);
$exists = Piwik_FetchOne("SELECT idstep\n\t\t\t\t\t\t\t\t\tFROM " . Piwik_Common::prefixTable('funnel_step') . " \n\t\t\t\t\t\t\t\t\tWHERE idsite = ? \n\t\t\t\t\t\t\t\t\tAND idfunnel = ?\n\t\t\t\t\t\t\t\t\tAND idstep = ?", array($idSite, $idFunnel, $idStep));
if ($exists) {
Piwik_Query("UPDATE " . Piwik_Common::prefixTable('funnel_step') . "\n\t\t\t\t\t\t\t SET name = ?, url = ?, deleted = 0\n\t\t\t\t\t\t\t WHERE idsite = ? AND idstep = ? AND idfunnel = ?", array($name, $url, $idSite, $idStep, $idFunnel));
} else {
Piwik_Query("INSERT INTO " . Piwik_Common::prefixTable('funnel_step') . "\n\t\t\t\t\t\t\t (idsite, idfunnel, idstep, name, url) \n\t\t\t\t\t\t\t VALUES (?, ?, ?, ?, ?)", array($idSite, $idFunnel, $idStep, $name, $url));
}
}
// Any steps not currently defined should be set to deleted
$whereClause = " WHERE idsite = ? AND idfunnel = ? ";
$params = array($idSite, $idFunnel);
if (count($currentStepIds) > 0) {
$currentStepIds = join(', ', $currentStepIds);
$whereClause .= "AND idstep not in ({$currentStepIds})";
}
Piwik_Query("UPDATE " . Piwik_Common::prefixTable('funnel_step') . "\n\t\t\t\t\t SET deleted = 1\n\t\t\t\t\t {$whereClause}", $params);
Piwik_Common::regenerateCacheWebsiteAttributes($idSite);
}
/**
* Authenticates user
*
* @return Piwik_Auth_Result
*/
public function authenticate()
{
$rootLogin = Piwik_Config::getInstance()->superuser['login'];
$rootPassword = Piwik_Config::getInstance()->superuser['password'];
$rootToken = Piwik_UsersManager_API::getInstance()->getTokenAuth($rootLogin, $rootPassword);
if (is_null($this->login)) {
if ($this->token_auth === $rootToken) {
return new Piwik_Auth_Result(Piwik_Auth_Result::SUCCESS_SUPERUSER_AUTH_CODE, $rootLogin, $this->token_auth);
}
$login = Piwik_FetchOne('SELECT login
FROM ' . Piwik_Common::prefixTable('user') . '
WHERE token_auth = ?', array($this->token_auth));
if (!empty($login)) {
return new Piwik_Auth_Result(Piwik_Auth_Result::SUCCESS, $login, $this->token_auth);
}
} else {
if (!empty($this->login)) {
if ($this->login === $rootLogin && $this->getHashTokenAuth($rootLogin, $rootToken) === $this->token_auth || $rootToken === $this->token_auth) {
$this->setTokenAuth($rootToken);
return new Piwik_Auth_Result(Piwik_Auth_Result::SUCCESS_SUPERUSER_AUTH_CODE, $rootLogin, $this->token_auth);
}
$login = $this->login;
$userToken = Piwik_FetchOne('SELECT token_auth
FROM ' . Piwik_Common::prefixTable('user') . '
WHERE login = ?', array($login));
if (!empty($userToken) && ($this->getHashTokenAuth($login, $userToken) === $this->token_auth || $userToken === $this->token_auth)) {
$this->setTokenAuth($userToken);
return new Piwik_Auth_Result(Piwik_Auth_Result::SUCCESS, $login, $userToken);
}
}
}
return new Piwik_Auth_Result(Piwik_Auth_Result::FAILURE, $this->login, $this->token_auth);
}
public function authenticate()
{
$rootLogin = Zend_Registry::get('config')->superuser->login;
$rootPassword = Zend_Registry::get('config')->superuser->password;
$rootToken = Piwik_UsersManager_API::getTokenAuth($rootLogin, $rootPassword);
if($this->login == $rootLogin
&& $this->token_auth == $rootToken)
{
return new Piwik_Auth_Result(Piwik_Auth_Result::SUCCESS_SUPERUSER_AUTH_CODE, $this->login, $this->token_auth );
}
if($this->token_auth === $rootToken)
{
return new Piwik_Auth_Result(Piwik_Auth_Result::SUCCESS_SUPERUSER_AUTH_CODE, $rootLogin, $rootToken );
}
$login = Piwik_FetchOne(
'SELECT login FROM '.Piwik::prefixTable('user').' WHERE token_auth = ?',
array($this->token_auth)
);
if($login !== false)
{
if(is_null($this->login)
|| $this->login == $login)
{
return new Piwik_Auth_Result(Piwik_Auth_Result::SUCCESS, $login, $this->token_auth );
}
}
return new Piwik_Auth_Result( Piwik_Auth_Result::FAILURE, $this->login, $this->token_auth );
}
/**
* Convert segment expression to an action ID or an SQL expression.
*
* This method is used as a sqlFilter-callback for the segments of this plugin.
* Usually, these callbacks only return a value that should be compared to the
* column in the database. In this case, that doesn't work since multiple IDs
* can match an expression (e.g. "pageUrl=@foo").
*/
function getIdActionFromSegment($string, $sqlField, $matchType = '==')
{
// Field is visit_*_idaction_url or visit_*_idaction_name
$actionType = strpos($sqlField, '_name') === false ? Piwik_Tracker_Action::TYPE_ACTION_URL : Piwik_Tracker_Action::TYPE_ACTION_NAME;
// exact matches work by returning the id directly
if ($matchType == Piwik_SegmentExpression::MATCH_EQUAL || $matchType == Piwik_SegmentExpression::MATCH_NOT_EQUAL) {
$sql = Piwik_Tracker_Action::getSqlSelectActionId();
$bind = array($string, $string, $actionType);
$idAction = Piwik_FetchOne($sql, $bind);
// if the action is not found, we hack -100 to ensure it tries to match against an integer
// otherwise binding idaction_name to "false" returns some rows for some reasons (in case &segment=pageTitle==Větrnásssssss)
if (empty($idAction)) {
$idAction = -100;
}
return $idAction;
}
// now, we handle the cases =@ (contains) and !@ (does not contain)
// build the expression based on the match type
$sql = 'SELECT idaction FROM ' . Piwik_Common::prefixTable('log_action') . ' WHERE ';
switch ($matchType) {
case '=@':
// use concat to make sure, no %s occurs because some plugins use %s in their sql
$sql .= '( name LIKE CONCAT("%", ?, "%") AND type = ' . $actionType . ' )';
break;
case '!@':
$sql .= '( name NOT LIKE CONCAT("%", ?, "%") AND type = ' . $actionType . ' )';
break;
default:
throw new Exception("This match type is not available for action-segments.");
break;
}
return array('SQL' => $sql, 'bind' => $string);
}
function getCollectionUrlFromID($id, $date, $period)
{
$pidSite = Piwik_FetchOne("SELECT idSite FROM " . Piwik_Common::prefixTable("site_collections") . " WHERE cID = ?", $id);
Piwik_cdebug::clog('getCollectionUrlFromID: ' . 'http://piwik.slub-dresde.de/index.php?module=CoreHome&action=index&date=' . $date . '&period=' . $period . '&idSite=' . $pidSite);
if (is_numeric($pidSite)) {
return "http://piwik.slub-dresden.de/index.php?module=CoreHome&action=index&date=" . $date . "&period=" . $period . "&idSite=" . $pidSite;
}
}
/**
* This method displays a text containing an help about "How to build plugins for Piwik".
* This help is then used on http://piwik.org/docs/plugins/functions
*
*/
function index()
{
$out = '';
$out .= '<i>This page aims to list the different functions you can use when programming plugins for Piwik.</i><br />';
$out .= '<b>Be careful, the following APIs may change in the near future as Piwik is still in development.</b><br />';
$out .= '<h2>General</h2>';
$out .= '<h3>Accessible from your plugin controller</h3>';
$out .= '<code>$this->date</code> = current selected <b>Piwik_Date</b> object (<a href="http://dev.piwik.org/trac/browser/trunk/core/Date.php">class</a>)<br />';
$out .= '<code>$period = Piwik_Common::getRequestVar("period");</code> - Get the current selected period<br />';
$out .= '<code>$idSite = Piwik_Common::getRequestVar("idSite");</code> - Get the selected idSite<br />';
$out .= '<code>$site = new Piwik_Site($idSite);</code> - Build the Piwik_Site object (<a href="http://dev.piwik.org/trac/browser/trunk/core/Site.php">class</a>)<br />';
$out .= '<code>$this->str_date</code> = current selected date in YYYY-MM-DD format<br />';
$out .= '<h3>Misc</h3>';
$out .= '<code>Piwik_AddMenu( $mainMenuName, $subMenuName, $url );</code> - Adds an entry to the menu in the Piwik interface (See the example in the <a href="http://dev.piwik.org/trac/browser/tags/1.0/plugins/UserCountry/UserCountry.php#L76">UserCountry Plugin file</a>)<br />';
$out .= '<code>Piwik_AddWidget( $widgetCategory, $widgetName, $controllerName, $controllerAction, $customParameters = array());</code> - Adds a widget that users can add in the dashboard, or export using the Widgets link at the top of the screen. See the example in the <a href="http://dev.piwik.org/trac/browser/tags/1.0/plugins/UserCountry/UserCountry.php#L70">UserCountry Plugin file</a> or any other plugin)<br />';
$out .= '<code>Piwik_Common::prefixTable("site")</code> = <b>' . Piwik_Common::prefixTable("site") . '</b><br />';
$out .= '<h2>User access</h2>';
$out .= '<code>Piwik::getCurrentUserLogin()</code> = <b>' . Piwik::getCurrentUserLogin() . '</b><br />';
$out .= '<code>Piwik::isUserHasSomeAdminAccess()</code> = <b>' . self::boolToString(Piwik::isUserHasSomeAdminAccess()) . '</b><br />';
$out .= '<code>Piwik::isUserHasAdminAccess( array $idSites = array(1,2) )</code> = <b>' . self::boolToString(Piwik::isUserHasAdminAccess(array(1, 2))) . '</b><br />';
$out .= '<code>Piwik::isUserHasViewAccess( array $idSites = array(1) ) </code> = <b>' . self::boolToString(Piwik::isUserHasViewAccess(array(1))) . '</b><br />';
$out .= '<code>Piwik::isUserIsSuperUser()</code> = <b>' . self::boolToString(Piwik::isUserIsSuperUser()) . '</b><br />';
$out .= '<h2>Execute SQL queries</h2>';
$txtQuery = "SELECT token_auth FROM " . Piwik_Common::prefixTable('user') . " WHERE login = ?";
$result = Piwik_FetchOne($txtQuery, array('anonymous'));
$out .= '<code>Piwik_FetchOne("' . $txtQuery . '", array("anonymous"))</code> = <b>' . var_export($result, true) . '</b><br />';
$out .= '<br />';
$query = Piwik_Query($txtQuery, array('anonymous'));
$fetched = $query->fetch();
$token_auth = $fetched['token_auth'];
$out .= '<code>$query = Piwik_Query("' . $txtQuery . '", array("anonymous"))</code><br />';
$out .= '<code>$fetched = $query->fetch();</code><br />';
$out .= 'At this point, we have: <code>$fetched[\'token_auth\'] == <b>' . var_export($token_auth, true) . '</b></code><br />';
$out .= '<h2>Example Sites information API</h2>';
$out .= '<code>Piwik_SitesManager_API::getInstance()->getSitesWithViewAccess()</code> = <b><pre>' . var_export(Piwik_SitesManager_API::getInstance()->getSitesWithViewAccess(), true) . '</pre></b><br />';
$out .= '<code>Piwik_SitesManager_API::getInstance()->getSitesWithAdminAccess()</code> = <b><pre>' . var_export(Piwik_SitesManager_API::getInstance()->getSitesWithAdminAccess(), true) . '</pre></b><br />';
$out .= '<h2>Example API Users information</h2>';
$out .= 'View the list of API methods you can call on <a href="http://piwik.org/docs/analytics-api/reference">API reference</a><br />';
$out .= 'For example you can try <code>Piwik_UsersManager_API::getInstance()->getUsersSitesFromAccess("view");</code> or <code>Piwik_UsersManager_API::getInstance()->deleteUser("userToDelete");</code><br />';
$out .= '<h2>Javascript in Piwik</h2>';
$out .= '<h3>i18n internationalization</h3>';
$out .= 'In order to translate strings within Javascript code, you can use the javascript function _pk_translate( token );.
<ul><li>The "token" parameter is the string unique key found in the translation file. For this token string to be available in Javascript, you must
suffix your token by "_js" in the language file. For example, you can add <code>\'Goals_AddGoal_js\' => \'Add Goal\',</code> in the lang/en.php file</li>
<li>You then need to instruct Piwik to load your Javascript translations for your plugin; by default, all translation strings are not loaded in Javascript for performance reasons. This can be done by calling a custom-made Smarty modifier before the Javascript code requiring translations, eg.
<code>{loadJavascriptTranslations plugins=\'$YOUR_PLUGIN_NAME\'}</code>. In our previous example, the $YOUR_PLUGIN_NAME being Goals, we would write <code>{loadJavascriptTranslations plugins=\'Goals\'}</code>
</li><li>You can then print this string from your JS code by doing <code>_pk_translate(\'Goals_AddGoal_js\');</code>.
</li></ul>';
$out .= '<h3>Reload a widget in the dashboard</h3>';
$out .= 'It is sometimes useful to reload one widget in the dashboard (for example, every 20 seconds for a real time widget, or after a setting change).
You can easily force your widget to reload in the dashboard by calling the helper function <code>$(this).parents(\'[widgetId]\').dashboardWidget(\'reload\');</code>.';
$out .= '<h2>Smarty plugins</h2>';
$out .= 'There are some builtin plugins for Smarty especially developped for Piwik. <br />
You can find them on the <a href="http://dev.piwik.org/trac/browser/trunk/core/SmartyPlugins">SVN at /trunk/core/SmartyPlugins</a>. <br />
More documentation to come about smarty plugins.<br />';
echo $out;
}
/**
* Simple feedburner statistics output
*
*/
function feedburner()
{
$view = new Piwik_View('ExampleFeedburner/templates/feedburner.tpl');
$idSite = Piwik_Common::getRequestVar('idSite', 1, 'int');
$feedburnerFeedName = Piwik_FetchOne('SELECT feedburnerName FROM ' . Piwik::prefixTable('site') . ' WHERE idsite = ?', $idSite);
if (empty($feedburnerFeedName)) {
$feedburnerFeedName = 'Piwik';
}
$view->feedburnerFeedName = $feedburnerFeedName;
$view->idSite = $idSite;
echo $view->render();
}
/**
* Simple feedburner statistics output
*
*/
function feedburner()
{
$view = Piwik_View::factory('feedburner');
$idSite = Piwik_Common::getRequestVar('idSite', 1, 'int');
$feedburnerFeedName = Piwik_FetchOne('SELECT feedburnerName FROM ' . Piwik::prefixTable('site') . ' WHERE idsite = ?', $idSite);
if (empty($feedburnerFeedName)) {
$feedburnerFeedName = 'Piwik';
}
$view->feedburnerFeedName = $feedburnerFeedName;
$view->idSite = $idSite;
$view->fbStats = $this->getFeedData($feedburnerFeedName);
echo $view->render();
}
/**
* Returns the option value for the requested option $name, fetching from database, if not in cache.
*
* @param string $name Key
* @return string|false Value or false, if not found
*/
public function get($name)
{
$this->autoload();
if (isset($this->all[$name])) {
return $this->all[$name];
}
$value = Piwik_FetchOne('SELECT option_value ' . 'FROM `' . Piwik_Common::prefixTable('option') . '`' . 'WHERE option_name = ?', $name);
if ($value === false) {
return false;
}
$this->all[$name] = $value;
return $value;
}
function recordFunnelSteps($notification)
{
$info = $notification->getNotificationInfo();
$idSite = $info['idSite'];
printDebug('Looking for funnel steps');
$funnels = Piwik_Funnels_API::getInstance()->getFunnels($idSite);
if (count($funnels) > 0) {
$idVisit = $info['idVisit'];
$idLinkVisitAction = $info['idLinkVisitAction'];
$idRefererAction = $info['idRefererAction'];
$action = $notification->getNotificationObject();
$actionName = $action->getActionName();
$sanitizedUrl = $action->getActionUrl();
$actionUrl = htmlspecialchars_decode($sanitizedUrl);
$idActionUrl = $action->getIdActionUrl();
$url = Piwik_Common::getRequestVar('url', '', 'string', $action->getRequest());
printDebug("idActionUrl" . $idActionUrl . " idSite " . $idSite . " idVisit " . $idVisit . " idRefererAction " . $idRefererAction);
# Is this the next action for a recorded funnel step?
$previous_step_action = Piwik_Query("UPDATE " . Piwik_Common::prefixTable('log_funnel_step') . "\n\t\t\t\t\t\t\t\t\t\t\t\t\tSET idaction_url_next = ?\n\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE idsite = ? \n\t\t\t\t\t\t\t\t\t\t\t\t\tAND idvisit = ? \n\t\t\t\t\t\t\t\t\t\t\t\t\tAND idaction_url = ?\n\t\t\t\t\t\t\t\t\t\t\t\t\tAND idaction_url_next is null", array($idActionUrl, $idSite, $idVisit, $idRefererAction));
}
foreach ($funnels as &$funnel) {
$steps = $funnel['steps'];
foreach ($steps as &$step) {
if ($step['url'] == $actionUrl or $step['name'] == $actionName) {
printDebug("Matched Goal Funnel " . $funnel['idfunnel'] . " Step " . $step['idstep'] . "(name: " . $step['name'] . ", url: " . $step['url'] . "). ");
$serverTime = time();
$datetimeServer = Piwik_Tracker::getDatetimeFromTimestamp($serverTime);
// Look to see if this step has already been recorded for this visit
$exists = Piwik_FetchOne("SELECT idlink_va\n\t\t\t\t\t\t\t\t\t\t\t FROM " . Piwik_Common::prefixTable('log_funnel_step') . " \n\t\t\t\t\t\t\t\t\t\t\t WHERE idsite = ? \n\t\t\t\t\t\t\t\t\t\t\t AND idfunnel = ?\n\t\t\t\t\t\t\t\t\t\t\t AND idstep = ?\n\t\t\t\t\t\t\t\t\t\t\t AND idvisit = ?", array($idSite, $funnel['idfunnel'], $step['idstep'], $idVisit));
// Record it if not
if (!$exists) {
printDebug("Recording...");
Piwik_Query("INSERT INTO " . Piwik_Common::prefixTable('log_funnel_step') . "\n\t\t\t\t\t\t\t\t\t(idvisit, idsite, idaction_url, url, \n\t\t\t\t\t\t\t\t\t idgoal, idfunnel, idstep, idlink_va, \n\t\t\t\t\t\t\t\t\t idaction_url_ref, server_time)\n\t\t\t\t\t\t\t\t\tVALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", array($idVisit, $idSite, $idActionUrl, $url, $funnel['idgoal'], $step['idfunnel'], $step['idstep'], $idLinkVisitAction, $idRefererAction, $datetimeServer));
}
}
}
}
}
/**
* Returns the language for the user
*
* @param string $login
* @return string
*/
public function getLanguageForUser($login)
{
Piwik::checkUserIsSuperUserOrTheUser($login);
return Piwik_FetchOne('SELECT language FROM ' . Piwik_Common::prefixTable('user_language') . ' WHERE login = ? ', array($login));
}
/**
* This method displays a text containing an help about "How to build plugins for Piwik".
* This help is then used on http://dev.piwik.org/trac/wiki/Plugins/GlobalFunctions
*
*/
function index()
{
$out = '';
$out .= '<i>This page aims to list the different functions you can use when programming plugins for Piwik.</i><br>';
$out .= '<b>Be careful, the following APIs may change in the near future as Piwik is still in development.</b><br>';
$out .= '<h2>General</h2>';
$out .= '<h3>Accessible from your plugin controller</h3>';
$out .= '<code>$this->date</code> = current selected <b>Piwik_Date</b> object (<a href="http://dev.piwik.org/trac/browser/trunk/core/Date.php">class</a>)<br/>';
$out .= '<code>$period = Piwik_Common::getRequestVar("period");</code> - Get the current selected period<br/>';
$out .= '<code>$idSite = Piwik_Common::getRequestVar("idSite");</code> - Get the selected idSite<br/>';
$out .= '<code>$site = new Piwik_Site($idSite);</code> - Build the Piwik_Site object (<a href="http://dev.piwik.org/trac/browser/trunk/core/Site.php">class</a>)<br/>';
$out .= '<code>$this->str_date</code> = current selected date in YYYY-MM-DD format<br/>';
$out .= '<h3>Misc</h3>';
$out .= '<code>Piwik_AddMenu( $mainMenuName, $subMenuName, $url );</code> - Adds an entry to the menu in the Piwik interface (See the example in the <a href="http://dev.piwik.org/trac/browser/trunk/plugins/UserCountry/UserCountry.php#L146">UserCountry Plugin file</a>)<br/>';
$out .= '<code>Piwik_AddWidget( $widgetCategory, $widgetName, $controllerName, $controllerAction, $customParameters = array());</code> - Adds a widget that users can add in the dashboard, or export using the Widgets link at the top of the screen. See the example in the <a href="http://dev.piwik.org/trac/browser/trunk/plugins/UserCountry/UserCountry.php#L143">UserCountry Plugin file</a> or any other plugin)<br/>';
$out .= '<code>Piwik::prefixTable("site")</code> = <b>' . Piwik::prefixTable("site") . '</b><br/>';
$out .= '<h2>User access</h2>';
$out .= '<code>Piwik::getCurrentUserLogin()</code> = <b>' . Piwik::getCurrentUserLogin() . '</b><br/>';
$out .= '<code>Piwik::isUserHasSomeAdminAccess()</code> = <b>' . self::boolToString(Piwik::isUserHasSomeAdminAccess()) . '</b><br/>';
$out .= '<code>Piwik::isUserHasAdminAccess( array $idSites = array(1,2) )</code> = <b>' . self::boolToString(Piwik::isUserHasAdminAccess(array(1,2) )) . '</b><br/>';
$out .= '<code>Piwik::isUserHasViewAccess( array $idSites = array(1) ) </code> = <b>' . self::boolToString(Piwik::isUserHasViewAccess(array(1))) . '</b><br/>';
$out .= '<code>Piwik::isUserIsSuperUser()</code> = <b>' . self::boolToString(Piwik::isUserIsSuperUser()) . '</b><br/>';
$out .= '<h2>Execute SQL queries</h2>';
$txtQuery = "SELECT token_auth FROM ".Piwik::prefixTable('user')." WHERE login = ?";
$result = Piwik_FetchOne($txtQuery, array('anonymous'));
$out .= '<code>Piwik_FetchOne("'.$txtQuery.'", array("anonymous"))</code> = <b>' . var_export($result,true) . '</b><br/>';
$out .= '<br>';
$query = Piwik_Query($txtQuery, array('anonymous'));
$fetched = $query->fetch();
$token_auth = $fetched['token_auth'];
$out .= '<code>$query = Piwik_Query("'.$txtQuery.'", array("anonymous"))</code><br>';
$out .= '<code>$fetched = $query->fetch();</code><br>';
$out .= 'At this point, we have: <code>$fetched[\'token_auth\'] == <b>'.var_export($token_auth,true) . '</b></code><br/>';
$out .= '<h2>Example Sites information API</h2>';
$out .= '<code>Piwik_SitesManager_API::getSitesWithViewAccess()</code> = <b><pre>' .var_export(Piwik_SitesManager_API::getSitesWithViewAccess(),true) . '</pre></b><br/>';
$out .= '<code>Piwik_SitesManager_API::getSitesWithAdminAccess()</code> = <b><pre>' .var_export(Piwik_SitesManager_API::getSitesWithAdminAccess(),true) . '</pre></b><br/>';
$out .= '<h2>Example API Users information</h2>';
$out .= 'View the list of API methods you can call on <a href="http://dev.piwik.org/trac/wiki/API/Reference#Methods">API reference</a><br/>';
$out .= 'For example you can try <code>Piwik_UsersManager_API::getUsersSitesFromAccess("view");</code> or <code>Piwik_UsersManager_API::deleteUser("userToDelete");</code><br/>';
$out .= '<h2>Smarty plugins</h2>';
$out .= 'There are some builtin plugins for Smarty especially developped for Piwik. <br>
You can find them on the <a href="http://dev.piwik.org/trac/browser/trunk/core/SmartyPlugins">SVN at /trunk/core/SmartyPlugins</a>. <br>
More documentation to come about smarty plugins.<br/>';
echo $out;
}
public static function getSqlVersion()
{
return Piwik_FetchOne("show server_version");
}
public function createNewDashboard()
{
$this->checkTokenInUrl();
if (!Piwik::isUserIsAnonymous()) {
$login = Piwik::getCurrentUserLogin();
$nextId = Piwik_FetchOne('SELECT MAX(iddashboard)+1
FROM ' . Piwik_Common::prefixTable('user_dashboard') . ' WHERE login = ?', array($login));
if (empty($nextId)) {
$nextId = 1;
}
$name = urldecode(Piwik_Common::getRequestVar('name', '', 'string'));
$type = urldecode(Piwik_Common::getRequestVar('type', 'default', 'string'));
$layout = '{}';
if ($type == 'default') {
$layout = $this->getDefaultLayout();
}
Piwik_Query('INSERT INTO ' . Piwik_Common::prefixTable('user_dashboard') . ' (login, iddashboard, name, layout) VALUES (?, ?, ?, ?)', array($login, $nextId, $name, $layout));
echo Piwik_Common::json_encode($nextId);
} else {
echo '0';
}
}
/**
* Returns true if user with given email (userEmail) is known in the database, or the super user
*
* @return bool true if the user is known
*/
public function userEmailExists($userEmail)
{
Piwik::checkUserIsNotAnonymous();
$count = Piwik_FetchOne("SELECT count(*) \n\t\t\t\t\t\t\t\tFROM " . Piwik_Common::prefixTable("user") . " \n\t\t\t\t\t\t\t\tWHERE email = ?", $userEmail);
return $count != 0 || Piwik_Config::getInstance()->superuser['email'] == $userEmail;
}
private function tableExists($tableName)
{
$dbName = Piwik_Config::getInstance()->database['dbname'];
$sql = "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = ? AND table_name = ?";
return Piwik_FetchOne($sql, array($dbName, Piwik_Common::prefixTable($tableName))) == 1;
}
function getDeleteIdVisitOffset($deleteLogsOlderThan)
{
$date = Piwik_Date::factory("today");
$dateSubX = $date->subDay($deleteLogsOlderThan);
$sql = "SELECT `idvisit` FROM " . Piwik_Common::prefixTable("log_visit") . " WHERE '" . $dateSubX->toString('Y-m-d H:i:s') . "' " . "> `visit_last_action_time` AND `idvisit` > 0 ORDER BY `idvisit` DESC LIMIT 1";
$maxIdVisit = Piwik_FetchOne($sql);
return $maxIdVisit;
}
/** Check whether a user is already logged in */
private function getLoggedInUser($idvisit)
{
$bind = array(':idvisit' => intval($idvisit));
$sql = '
SELECT
iduser
FROM
' . self::loginTable() . '
WHERE
idvisit = :idvisit AND
datetime_logout = "0000-00-00 00:00:00"
';
return Piwik_FetchOne($sql, $bind);
}
private static function getMaxGoalId()
{
return Piwik_FetchOne("SELECT MAX(idgoal) FROM " . Piwik_Common::prefixTable('goal'));
}
private function getNextIdDashboard($login)
{
$nextIdQuery = sprintf('SELECT MAX(iddashboard)+1 FROM %s WHERE login = ?', Piwik_Common::prefixTable('user_dashboard'));
$nextId = Piwik_FetchOne($nextIdQuery, array($login));
if (empty($nextId)) {
$nextId = 1;
return $nextId;
}
return $nextId;
}
}
$info = $provider->getInfo();
Piwik::log("[note] Found working provider: {$info['id']}");
// when script run via browser, check for Super User
if (!Piwik_Common::isPhpCliMode()) {
try {
Piwik::checkUserIsSuperUser();
} catch (Exception $e) {
Piwik::log('[error] You must be logged in as Super User to run this script. Please login in to Piwik and refresh this page.');
exit;
}
}
// perform update
$logVisitFieldsToUpdate = array('location_country' => Piwik_UserCountry_LocationProvider::COUNTRY_CODE_KEY, 'location_region' => Piwik_UserCountry_LocationProvider::REGION_CODE_KEY, 'location_city' => Piwik_UserCountry_LocationProvider::CITY_NAME_KEY, 'location_latitude' => Piwik_UserCountry_LocationProvider::LATITUDE_KEY, 'location_longitude' => Piwik_UserCountry_LocationProvider::LONGITUDE_KEY);
$query = "SELECT count(*) FROM " . Piwik_Common::prefixTable('log_visit');
$count = Piwik_FetchOne($query);
$start = 0;
$limit = 1000;
Piwik::log("\n{$count} rows to process in " . Piwik_Common::prefixTable('log_visit') . " and " . Piwik_Common::prefixTable('log_conversion') . "...");
flush();
for (; $start < $count; $start += $limit) {
$rows = Piwik_FetchAll("SELECT idvisit, location_ip, " . implode(',', array_keys($logVisitFieldsToUpdate)) . "\n\t\t\t\t\t\tFROM " . Piwik_Common::prefixTable('log_visit') . " \n\t\t\t\t\t\tLIMIT {$start}, {$limit}");
if (!count($rows)) {
continue;
}
foreach ($rows as $i => $row) {
$fieldsToSet = array();
foreach ($logVisitFieldsToUpdate as $field => $ignore) {
if (empty($fieldsToSet[$field])) {
$fieldsToSet[] = $field;
}
/**
* Returns the min date out of all websites
* for which the current user has at least view access
*
* @return int timestamp
*/
public function getSitesMinDate()
{
if ($this->isSuperUser()) {
return Piwik_FetchOne('SELECT MIN(ts_created) FROM ' . Piwik::prefixTable('site'));
}
return Piwik_FetchOne($this->getSqlAccessSite("MIN(ts_created)"), $this->login);
}
function getGraph()
{
$idSite = Piwik_Common::getRequestVar('idSite');
$sDate = Piwik_Common::getRequestVar('date');
$bCalc = false;
switch (Piwik_Common::getRequestVar('period')) {
case 'range':
$bCalc = true;
$aDate = explode(',', $sDate);
$sStart = date('Y-m-d 00:00:00', strtotime($aDate[0]));
$sEnd = date('Y-m-d 23:59:59', strtotime($aDate[0]));
break;
case 'year':
$sSO = ' THIS YEAR';
$sEO = ' +1 YEAR -1 SECOND';
break;
case 'month':
$sSO = ' THIS MONTH';
$sEO = ' +1 MONTH -1 SECOND';
break;
case 'week':
$sSO = ' THIS WEEK';
$sEO = ' +1 WEEK -1 SECOND';
break;
case 'day':
default:
$sSO = '';
$sEO = ' +1 DAY -1 SECOND';
}
if (!$bCalc) {
$sStart = date('Y-m-d H:i:s', strtotime($sDate . $sSO));
$sEnd = date('Y-m-d H:i:s', strtotime($sStart . $sEO));
}
$sSite = Piwik_FetchOne("SELECT main_url FROM " . Piwik_Common::prefixTable('site'));
$aHosts = array(parse_url($sSite, PHP_URL_HOST));
$aSiteHosts = Piwik_FetchAll("SELECT url FROM " . Piwik_Common::prefixTable('site_url') . " WHERE idsite = ?", array($idSite));
foreach ($aSiteHosts as $aHost) {
$aHosts[] = parse_url($aHost['url'], PHP_URL_HOST);
}
$aVisitActions = Piwik_FetchAll("SELECT N.name AS node, R.name AS node_ref, COUNT(1) AS num \n\t\t\tFROM " . Piwik_Common::prefixTable('log_link_visit_action') . " L\n\t\t\tJOIN " . Piwik_Common::prefixTable('log_action') . " N ON N.idaction = L.idaction_url\n\t\t\tJOIN " . Piwik_Common::prefixTable('log_action') . " R ON R.idaction = L.idaction_url_ref\n\t\t\tWHERE L.idsite = ? AND L.server_time BETWEEN ? AND ? \n\t\t\tGROUP BY node, node_ref\n\t\t\tORDER BY num DESC LIMIT 0, 20", array($idSite, $sStart, $sEnd));
$aTrails = array('nodes' => array(), 'edges' => array());
$aReturn = array('data' => $aTrails);
foreach (array_keys($aVisitActions) as $id) {
$aVisitAction =& $aVisitActions[$id];
foreach (array('node', 'node_ref') as $k) {
$aVisitAction[$k] = preg_replace('#^(https?://)?(www\\.)?#', '', str_replace($aHosts, '', $aVisitAction[$k]));
}
$sNode = $aVisitAction['node'];
if (!isset($aTrails['nodes'][$sNode])) {
$aTrails['nodes'][$sNode] = array('visits' => $aVisitAction['num'], 'label' => $sNode, 'out' => 0);
} else {
$aTrails['nodes'][$sNode]['visits'] += $aVisitAction['num'];
}
}
while ($aVisitActions) {
$aVisitAction = array_shift($aVisitActions);
$sEdge = $aVisitAction['node_ref'] . '_to_' . $aVisitAction['node'];
$sNode = $aVisitAction['node_ref'];
if (!isset($aTrails['nodes'][$sNode])) {
$aTrails['nodes'][$sNode] = array('visits' => $aVisitAction['num'], 'label' => $sNode, 'out' => $aVisitAction['num']);
} else {
$aTrails['nodes'][$sNode]['out'] += $aVisitAction['num'];
}
if (empty($aTrails['edges'][$sEdge])) {
$aTrails['edges'][$sEdge] = array('target' => $aVisitAction['node'], 'source' => $aVisitAction['node_ref'], 'weight' => $aVisitAction['num']);
} else {
$aTrails['edges'][$sEdge]['weight'] += $aVisitAction['num'];
}
}
foreach (array_keys($aTrails['nodes']) as $sNode) {
$aNode =& $aTrails['nodes'][$sNode];
$aReturn['data']['nodes'][] = array('id' => strval($sNode), 'label' => strval($aNode['label']), 'visits' => $aNode['visits'], 'bounce' => $aNode['out'] / $aNode['visits']);
unset($aTrails['nodes'][$sNode]);
}
foreach (array_keys($aTrails['edges']) as $sEdge) {
$aEdge =& $aTrails['edges'][$sEdge];
$aReturn['data']['edges'][] = array('id' => strval($sEdge), 'target' => strval($aEdge['target']), 'source' => strval($aEdge['source']), 'weight' => $aEdge['weight']);
unset($aTrails['edges'][$sEdge]);
}
return json_encode($aReturn);
}
public static function getMysqlVersion()
{
return Piwik_FetchOne("SELECT VERSION()");
}
/**
* Returns true if user with given email (userEmail) is known in the database
*
* @return bool true if the user is known
*/
public function userEmailExists($userEmail)
{
Piwik::checkUserHasSomeAdminAccess();
$count = Piwik_FetchOne("SELECT count(*) \n\t\t\t\t\t\t\t\t\t\t\t\t\tFROM " . Piwik::prefixTable("user") . " \n\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE email = ?", $userEmail);
return $count != 0;
}
function doStepMatchAndSave($idSite, $idVisit, $idRefererAction, $actionName = "", $actionUrl = "", $idActionUrl = Piwik_Funnels::INDEX_MANUAL_CONVERSION)
{
printDebug('Looking for funnel steps');
$websiteAttributes = Piwik_Tracker_Cache::getCacheWebsiteAttributes($idSite);
if (isset($websiteAttributes['funnels'])) {
$funnels = $websiteAttributes['funnels'];
printDebug('got funnel steps');
} else {
$funnels = array();
}
if (count($funnels) <= 0) {
return;
}
printDebug("idActionUrl " . $idActionUrl . " idSite " . $idSite . " idVisit " . $idVisit . " idRefererAction " . $idRefererAction);
// Is this the next action for a recorded funnel step?
$previous_step_action = Piwik_Query("UPDATE " . Piwik_Common::prefixTable('log_funnel_step') . "\n SET idaction_url_next = ?\n WHERE idsite = ? \n AND idvisit = ? \n AND idaction_url = ?\n AND idaction_url_next is null", array($idActionUrl, $idSite, $idVisit, $idRefererAction));
// early out for special case of manual conversion
// Since this is a manual conversion for a goal, there is no URL to
// match with, so the following loop is simply a waste of resources
if ($idActionUrl == Piwik_Funnels::INDEX_MANUAL_CONVERSION) {
return;
}
foreach ($funnels as &$funnel) {
$steps = $funnel['steps'];
foreach ($steps as &$step) {
$url = $actionUrl;
// Matching on Page Title
if ($step['match_attribute'] == 'title') {
$url = $actionName;
}
if (self::isMatch($url, $step['pattern_type'], $step['url'], $step['case_sensitive'])) {
printDebug("Matched Goal Funnel " . $funnel['idfunnel'] . " Step " . $step['idstep'] . "(name: " . $step['name'] . ", url: " . $step['url'] . "). ");
$serverTime = time();
$datetimeServer = Piwik_Tracker::getDatetimeFromTimestamp($serverTime);
// Look to see if this step has already been recorded for this visit
$exists = Piwik_FetchOne("SELECT *\n FROM " . Piwik_Common::prefixTable('log_funnel_step') . " \n WHERE idsite = ? \n AND idfunnel = ?\n AND idstep = ?\n AND idvisit = ?", array($idSite, $funnel['idfunnel'], $step['idstep'], $idVisit));
// Record it if not
if (!$exists) {
printDebug("Recording...");
Piwik_Query("INSERT INTO " . Piwik_Common::prefixTable('log_funnel_step') . "\n (idvisit, idsite, idaction_url, url, \n idgoal, idfunnel, idstep, \n idaction_url_ref, server_time)\n VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)", array($idVisit, $idSite, $idActionUrl, $url, $funnel['idgoal'], $step['idfunnel'], $step['idstep'], $idRefererAction, $datetimeServer));
}
}
}
}
}
/**
* Returns true if user with given email (userEmail) is known in the database
*
* @return bool true if the user is known
*/
public function userEmailExists($userEmail)
{
Piwik::checkUserIsNotAnonymous();
$count = Piwik_FetchOne("SELECT count(*) \n\t\t\t\t\t\t\t\tFROM " . Piwik_Common::prefixTable("user") . " \n\t\t\t\t\t\t\t\tWHERE email = ?", $userEmail);
return $count != 0;
}
private function insertActionsToKeep($maxIds, $olderThan = true)
{
$tempTableName = Piwik_Common::prefixTable(self::TEMP_TABLE_NAME);
$idColumns = $this->getTableIdColumns();
foreach ($this->getIdActionColumns() as $table => $columns) {
$idCol = $idColumns[$table];
foreach ($columns as $col) {
$select = "SELECT {$col} FROM " . Piwik_Common::prefixTable($table) . " WHERE {$idCol} >= ? AND {$idCol} < ?";
$sql = "INSERT IGNORE INTO {$tempTableName} {$select}";
if ($olderThan) {
$start = 0;
$finish = $maxIds[$table];
} else {
$start = $maxIds[$table];
$finish = Piwik_FetchOne("SELECT MAX({$idCol}) FROM " . Piwik_Common::prefixTable($table));
}
Piwik_SegmentedQuery($sql, $start, $finish, self::$selectSegmentSize);
}
}
// allow code to be executed after data is inserted. for concurrency testing purposes.
if ($olderThan) {
Piwik_PostEvent("LogDataPurger.actionsToKeepInserted.olderThan");
} else {
Piwik_PostEvent("LogDataPurger.actionsToKeepInserted.newerThan");
}
}
/**
* If we're going to keep segmented reports, we need to know which archives are
* for segments. This info is only in the numeric tables, so we must query them.
*/
private function findSegmentArchives($numericTables)
{
if (!is_null($this->segmentArchiveIds)) {
return;
}
foreach ($numericTables as $table) {
$tableDate = $this->getArchiveTableDate($table);
$maxIdArchive = Piwik_FetchOne("SELECT MAX(idarchive) FROM {$table}");
$sql = "SELECT idarchive\n\t\t\t\t\t FROM {$table}\n\t\t\t\t\t WHERE name != 'done'\n\t\t\t\t\t AND name LIKE 'done_%.%'\n\t\t\t\t\t AND idarchive >= ?\n\t\t\t\t\t AND idarchive < ?";
$this->segmentArchiveIds[$tableDate] = array();
foreach (Piwik_SegmentedFetchAll($sql, 0, $maxIdArchive, self::$selectSegmentSize) as $row) {
$this->segmentArchiveIds[$tableDate][] = $row['idarchive'];
}
}
}
private function insertOrUpdatePage($idSite, $idExperiment, $pageInfo, $experimentName, $isOriginal)
{
$idPage = $pageInfo['id'];
if (!is_numeric($idPage)) {
throw new Exception(Piwik_TranslateException('ABTests_ExceptionNoPageId', $experimentName));
}
$name = $this->checkName($pageInfo['name']);
$url = $this->checkUrl($pageInfo['url']);
$exists = Piwik_FetchOne("SELECT idexperiment\n\t\t\t\t\t\t\t\tFROM " . Piwik_Common::prefixTable('experiment_page') . " \n\t\t\t\t\t\t\t\tWHERE idsite = ? \n\t\t\t\t\t\t\t\tAND idexperiment = ?\n\t\t\t\t\t\t\t\tAND idpage = ?", array($idSite, $idExperiment, $idPage));
if ($exists) {
Piwik_Query("UPDATE " . Piwik_Common::prefixTable('experiment_page') . "\n\t\t\t\t\t\t SET name = ?, url = ?, deleted = 0, original = ?\n\t\t\t\t\t\t WHERE idsite = ? AND idpage = ? AND idexperiment = ?", array($name, $url, $isOriginal, $idSite, $idPage, $idExperiment));
} else {
Piwik_Query("INSERT INTO " . Piwik_Common::prefixTable('experiment_page') . "\n\t\t\t\t\t\t (idsite, idexperiment, idpage, name, url, original) \n\t\t\t\t\t\t VALUES (?, ?, ?, ?, ?, ?)", array($idSite, $idExperiment, $idPage, $name, $url, $isOriginal));
}
return $idPage;
}
