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