PHP Piwik_Query函数代码示例

 public function deleteGoal($idSite, $idGoal)
 {
     Piwik::checkUserHasAdminAccess($idSite);
     Piwik_Query("UPDATE " . Piwik::prefixTable('goal') . "\n\t\t\t\t\t\t\t\t\t\tSET deleted = 1\n\t\t\t\t\t\t\t\t\t\tWHERE idsite = ? \n\t\t\t\t\t\t\t\t\t\t\tAND idgoal = ?", array($idSite, $idGoal));
     Zend_Registry::get('db')->query("DELETE FROM " . Piwik::prefixTable("log_conversion") . " WHERE idgoal = ?", $idGoal);
     Piwik_Common::regenerateCacheWebsiteAttributes($idSite);
 }

    /**
     * Records the layout in the DB for the given user.
     *
     * @param string $login
     * @param int $idDashboard
     * @param string $layout
     */
    protected function saveLayoutForUser($login, $idDashboard, $layout)
    {
        $paramsBind = array($login, $idDashboard, $layout, $layout);
        Piwik_Query('INSERT INTO ' . Piwik::prefixTable('user_dashboard') . ' (login, iddashboard, layout)
						VALUES (?,?,?)
					ON DUPLICATE KEY UPDATE layout=?', $paramsBind);
    }

    /**
     * Function called to save the Feedburner ID entered in the form
     *
     */
    function saveFeedburnerName()
    {
        // we save the value in the DB for an authenticated user
        if (Piwik::getCurrentUserLogin() != 'anonymous') {
            Piwik_Query('UPDATE ' . Piwik::prefixTable('site') . ' 
						 SET feedburnerName = ? WHERE idsite = ?', array(Piwik_Common::getRequestVar('name', '', 'string'), Piwik_Common::getRequestVar('idSite', 1, 'int')));
        }
    }

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

	/**
	 * Sets the option value in the database
	 *
	 * @param string $name
	 * @param string $value
	 * @param int $autoload if set to 1, this option value will be automatically loaded; should be set to 1 for options that will always be used in the Piwik request.
	 */
	public function set($name, $value, $autoload = 0)
	{
		$autoload = (int)$autoload;
		Piwik_Query('INSERT INTO `'. Piwik::prefixTable('option') . '` (option_name, option_value, autoload) '.
					' VALUES (?, ?, ?) '.
					' ON DUPLICATE KEY UPDATE option_value = ?',
					array($name, $value, $autoload, $value));
		$this->all[$name] = $value;
	}

 function optimizeArchiveTable()
 {
     $tablesPiwik = Piwik::getTablesInstalled();
     $archiveTables = array_filter($tablesPiwik, array("Piwik_CoreAdminHome", "isArchiveTable"));
     if (empty($archiveTables)) {
         return;
     }
     $query = "OPTIMIZE TABLE " . implode(",", $archiveTables);
     Piwik_Query($query);
 }

 public function test_getOption()
 {
     // empty table, expect false (i.e., not found)
     $this->assertTrue(Piwik_GetOption('anonymous_defaultReport') === false);
     // populate table, expect '1' (i.e., found)
     Piwik_Query("INSERT INTO " . Piwik_Common::prefixTable('option') . " VALUES ('anonymous_defaultReport', '1',true)");
     $this->assertTrue(Piwik_GetOption('anonymous_defaultReport') === '1');
     // delete row (bypassing API), expect '1' (i.e., from cache)
     Piwik_Query("DELETE FROM " . Piwik_Common::prefixTable('option') . " WHERE option_name = ?", array('anonymous_defaultReport'));
     $this->assertTrue(Piwik_GetOption('anonymous_defaultReport') === '1');
     // force cache reload, expect false (i.e., not found)
     Piwik_Option::getInstance()->clearCache();
     $this->assertTrue(Piwik_GetOption('anonymous_defaultReport') === false);
 }

    /** Log a logout */
    public function logLogout($idvisit)
    {
        $bind = array(':idvisit' => intval($idvisit), ':now' => self::now());
        $sql = '
			UPDATE
				' . self::loginTable() . ' AS login
			SET
				datetime_logout = :now,
				duration = TIMESTAMPDIFF(MINUTE, datetime_login, :now)
			WHERE
				idvisit = :idvisit AND
				datetime_logout = "0000-00-00 00:00:00"
		';
        return Piwik_Query($sql, $bind);
    }

 static function update()
 {
     try {
         $dashboards = Piwik_FetchAll('SELECT * FROM `' . Piwik_Common::prefixTable('user_dashboard') . '`');
         foreach ($dashboards as $dashboard) {
             $idDashboard = $dashboard['iddashboard'];
             $login = $dashboard['login'];
             $layout = $dashboard['layout'];
             $layout = html_entity_decode($layout);
             $layout = str_replace("\\\"", "\"", $layout);
             Piwik_Query('UPDATE `' . Piwik_Common::prefixTable('user_dashboard') . '` SET layout = ? WHERE iddashboard = ? AND login = ?', array($layout, $idDashboard, $login));
         }
         Piwik_Updater::updateDatabase(__FILE__, self::getSql());
     } catch (Exception $e) {
     }
 }

    static function update()
    {
        Piwik_Updater::updateDatabase(__FILE__, self::getSql());
        if (!Piwik_PluginsManager::getInstance()->isPluginLoaded('PDFReports')) {
            return;
        }
        try {
            // Piwik_Common::prefixTable('pdf') has been heavily refactored to be more generic
            // The following actions are taken in this update script :
            // - create the new generic report table Piwik_Common::prefixTable('report')
            // - migrate previous reports, if any, from Piwik_Common::prefixTable('pdf') to Piwik_Common::prefixTable('report')
            // - delete Piwik_Common::prefixTable('pdf')
            $reports = Piwik_FetchAll('SELECT * FROM `' . Piwik_Common::prefixTable('pdf') . '`');
            foreach ($reports as $report) {
                $idreport = $report['idreport'];
                $idsite = $report['idsite'];
                $login = $report['login'];
                $description = $report['description'];
                $period = $report['period'];
                $format = $report['format'];
                $display_format = $report['display_format'];
                $email_me = $report['email_me'];
                $additional_emails = $report['additional_emails'];
                $reports = $report['reports'];
                $ts_created = $report['ts_created'];
                $ts_last_sent = $report['ts_last_sent'];
                $deleted = $report['deleted'];
                $parameters = array();
                if (!is_null($additional_emails)) {
                    $parameters[Piwik_PDFReports::ADDITIONAL_EMAILS_PARAMETER] = preg_split('/,/', $additional_emails);
                }
                $parameters[Piwik_PDFReports::EMAIL_ME_PARAMETER] = is_null($email_me) ? Piwik_PDFReports::EMAIL_ME_PARAMETER_DEFAULT_VALUE : (bool) $email_me;
                $parameters[Piwik_PDFReports::DISPLAY_FORMAT_PARAMETER] = $display_format;
                Piwik_Query('INSERT INTO `' . Piwik_Common::prefixTable('report') . '` SET
					idreport = ?, idsite = ?, login = ?, description = ?, period = ?,
					type = ?, format = ?, reports = ?, parameters = ?, ts_created = ?,
					ts_last_sent = ?, deleted = ?', array($idreport, $idsite, $login, $description, is_null($period) ? Piwik_PDFReports::DEFAULT_PERIOD : $period, Piwik_PDFReports::EMAIL_TYPE, is_null($format) ? Piwik_PDFReports::DEFAULT_REPORT_FORMAT : $format, Piwik_Common::json_encode(preg_split('/,/', $reports)), Piwik_Common::json_encode($parameters), $ts_created, $ts_last_sent, $deleted));
            }
            Piwik_Query('DROP TABLE `' . Piwik_Common::prefixTable('pdf') . '`');
        } catch (Exception $e) {
        }
    }

 public function test_RunAllTests()
 {
     parent::test_RunAllTests();
     if (Test_Integration::$apiTestingLevel != Test_Integration::NO_API_TESTING) {
         $this->_checkExpectedRowsInArchiveTable();
         // Force Purge to happen again this table (since it was called at end of archiving)
         Piwik_SetOption(Piwik_ArchiveProcessing_Period::FLAG_TABLE_PURGED . Piwik_Common::prefixTable('archive_blob_2010_12'), '2010-01-01');
         Piwik_SetOption(Piwik_ArchiveProcessing_Period::FLAG_TABLE_PURGED . Piwik_Common::prefixTable('archive_blob_2011_01'), '2010-01-01');
         foreach (array('archive_numeric_2010_12', 'archive_blob_2010_12', 'archive_numeric_2011_01', 'archive_blob_2011_01') as $table) {
             // INSERT some ERROR records.
             // We use period=range since the test below is restricted to these
             Piwik_Query(" INSERT INTO `" . Piwik_Common::prefixTable($table) . "` (`idarchive` ,`name` ,`idsite` ,`date1` ,`date2` ,`period` ,`ts_archived` ,`value`)\n\t\t\t\t\tVALUES  (10000, 'done', '1', '2010-12-06', '2010-12-06', '" . Piwik::$idPeriods['range'] . "', '2010-12-06' , '" . Piwik_ArchiveProcessing::DONE_ERROR . "'), \n\t\t\t\t\t\t\t(20000, 'doneX', '2', '2012-07-06', '2012-07-06', '" . Piwik::$idPeriods['range'] . "', '' , '" . Piwik_ArchiveProcessing::DONE_ERROR . "')");
         }
         // We've added error rows which should fail the following test
         $this->_checkExpectedRowsInArchiveTable($expectPass = false);
         // Test Scheduled tasks Table Optimize, and Delete old reports
         Piwik_CoreAdminHome::purgeOutdatedArchives();
         Piwik_CoreAdminHome::optimizeArchiveTable();
         // Check that only the good reports were not deleted:
         $this->_checkExpectedRowsInArchiveTable();
     }
 }

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

 public static function setUpBeforeClass()
 {
     $dbName = false;
     if (!empty($GLOBALS['PIWIK_BENCHMARK_DATABASE'])) {
         $dbName = $GLOBALS['PIWIK_BENCHMARK_DATABASE'];
     }
     // connect to database
     self::createTestConfig();
     self::connectWithoutDatabase();
     // create specified fixture (global var not set, use default no-data fixture (see end of this file))
     if (empty($GLOBALS['PIWIK_BENCHMARK_FIXTURE'])) {
         $fixtureName = 'Piwik_Test_Fixture_EmptyOneSite';
     } else {
         $fixtureName = 'Piwik_Test_Fixture_' . $GLOBALS['PIWIK_BENCHMARK_FIXTURE'];
     }
     self::$fixture = new $fixtureName();
     // figure out if the desired fixture has already been setup, and if not empty the database
     $installedFixture = false;
     try {
         if (isset(self::$fixture->tablesPrefix)) {
             Piwik_Config::getInstance()->database['tables_prefix'] = self::$fixture->tablesPrefix;
             Piwik_Common::$cachedTablePrefix = null;
         }
         Piwik_Query("USE " . $dbName);
         $installedFixture = Piwik_GetOption('benchmark_fixture_name');
     } catch (Exception $ex) {
         // ignore
     }
     $createEmptyDatabase = $fixtureName != $installedFixture;
     parent::setUpBeforeClass($dbName, $createEmptyDatabase, $createConfig = false);
     // if we created an empty database, setup the fixture
     if ($createEmptyDatabase) {
         self::$fixture->setUp();
         Piwik_SetOption('benchmark_fixture_name', $fixtureName);
     }
 }

 /**
  * Event hook that adds a row into the DB that references unused idaction AFTER LogDataPurger
  * does the insert into the temporary table. When log_actions are deleted, this idaction should still
  * be kept. w/ the wrong strategy, it won't be and there will be a dangling reference
  * in the log_link_visit_action table.
  *
  * @param Piwik_Event_Notification $notification  notification object
  */
 public function addReferenceToUnusedAction($notification)
 {
     $unusedIdAction = $this->unusedIdAction;
     if (empty($unusedIdAction)) {
         return;
     }
     $tempTableName = Piwik_Common::prefixTable(Piwik_PrivacyManager_LogDataPurger::TEMP_TABLE_NAME);
     $logLinkVisitActionTable = Piwik_Common::prefixTable("log_link_visit_action");
     $sql = "INSERT INTO {$logLinkVisitActionTable}\n\t\t\t\t\t\t\t(idsite, idvisitor, server_time, idvisit, idaction_url, idaction_url_ref,\n\t\t\t\t\t\t\tidaction_name, idaction_name_ref, time_spent_ref_action)\n\t\t\t\t\t VALUES (1, 'abc', NOW(), 15, {$unusedIdAction}, {$unusedIdAction},\n\t\t\t\t\t\t\t {$unusedIdAction}, {$unusedIdAction}, 1000)";
     Piwik_Query($sql);
 }

 /**
  * Inserts a record in the right table (either NUMERIC or BLOB)
  *
  * @param Piwik_ArchiveProcessing_Record $record
  */
 protected function insertRecord($record)
 {
     // table to use to save the data
     if (is_numeric($record->value)) {
         $table = $this->tableArchiveNumeric;
     } else {
         $table = $this->tableArchiveBlob;
     }
     // ignore duplicate idarchive
     // @see http://dev.piwik.org/trac/ticket/987
     $query = "INSERT IGNORE INTO " . $table->getTableName() . " (idarchive, idsite, date1, date2, period, ts_archived, name, value)\n\t\t\t\t\tVALUES (?,?,?,?,?,?,?,?)";
     Piwik_Query($query, array($this->idArchive, $this->idsite, $this->strDateStart, $this->strDateEnd, $this->periodId, date("Y-m-d H:i:s"), $record->name, $record->value));
 }