PHP Piwik_FetchAll函数代码示例

    public static function getAllDashboards($login)
    {
        $dashboards = Piwik_FetchAll('SELECT iddashboard, name
									  FROM ' . Piwik_Common::prefixTable('user_dashboard') . ' WHERE login = ? ORDER BY iddashboard', array($login));
        $pos = 0;
        $nameless = 1;
        foreach ($dashboards as &$dashboard) {
            if (!empty($dashboard['name'])) {
                $dashboard['name'] = $dashboard['name'];
            } else {
                $dashboard['name'] = Piwik_Translate('Dashboard_DashboardOf', $login);
                if ($nameless > 1) {
                    $dashboard['name'] .= " ({$nameless})";
                }
                if (empty($dashboard['layout'])) {
                    $layout = '[]';
                } else {
                    $layout = html_entity_decode($dashboard['layout']);
                    $layout = str_replace("\\\"", "\"", $layout);
                }
                $dashboard['layout'] = Piwik_Common::json_decode($layout);
                $nameless++;
            }
            $dashboard['name'] = Piwik_Common::unsanitizeInputValue($dashboard['name']);
            $pos++;
        }
        return $dashboards;
    }

 /**
  * Returns the layout in the DB for the given user, or false if the layout has not been set yet.
  * Parameters must be checked BEFORE this function call
  *
  * @param string $login
  * @param int $idDashboard
  * @param string|false $layout
  */
 protected function getLayoutForUser($login, $idDashboard)
 {
     $paramsBind = array($login, $idDashboard);
     $return = Piwik_FetchAll('SELECT layout FROM ' . Piwik::prefixTable('user_dashboard') . ' WHERE login = ? AND iddashboard = ?', $paramsBind);
     if (count($return) == 0) {
         return false;
     }
     return $return[0]['layout'];
 }

 public static function getGoals($idSite)
 {
     $goals = Piwik_FetchAll("SELECT * \n\t\t\t\t\t\t\t\t\t\t\tFROM " . Piwik_Common::prefixTable('goal') . " \n\t\t\t\t\t\t\t\t\t\t\tWHERE idsite = ?\n\t\t\t\t\t\t\t\t\t\t\t\tAND deleted = 0", $idSite);
     $cleanedGoals = array();
     foreach ($goals as &$goal) {
         unset($goal['idsite']);
         $cleanedGoals[$goal['idgoal']] = $goal;
     }
     return $cleanedGoals;
 }

    /** Add SiteSearch config to tracker cache */
    public function recordWebsiteDataInCache($notification)
    {
        $idsite = $notification->getNotificationInfo();
        $cache =& $notification->getNotificationObject();
        $sql = '
			SELECT sitesearch_url, sitesearch_parameter
			FROM ' . Piwik_Common::prefixTable('site') . ' AS site
			WHERE idsite = ' . intval($idsite) . '
		';
        $result = Piwik_FetchAll($sql);
        $site = $result[0];
        $cache['sitesearch_url'] = $site['sitesearch_url'];
        $cache['sitesearch_parameter'] = $site['sitesearch_parameter'];
    }

 public function getFunnels($idSite)
 {
     $funnel_table = Piwik_Common::prefixTable('funnel');
     $goal_table = Piwik_Common::prefixTable('goal');
     $funnel_step_table = Piwik_Common::prefixTable('funnel_step');
     $funnels = Piwik_FetchAll("SELECT " . $funnel_table . ".*, " . $goal_table . ".name as goal_name, " . $goal_table . ".idgoal\n\t\t\t\t\t\t\t\t   FROM   " . $funnel_table . ", " . $goal_table . " \n\t\t\t\t\t\t\t\t   WHERE  " . $funnel_table . ".idsite = ?\n\t\t\t\t\t\t\t\t   AND    " . $funnel_table . ".idgoal = " . $goal_table . ".idgoal\n\t\t\t\t\t\t\t\t   AND    " . $funnel_table . ".deleted = 0", array($idSite));
     $funnelsById = array();
     foreach ($funnels as &$funnel) {
         $funnel_steps = Piwik_FetchAll("SELECT *\n\t\t\t\t\t\t\t\t\t\t\tFROM   " . $funnel_step_table . "\n\t\t\t\t\t\t\t\t\t\t\tWHERE  idsite = ?\n\t\t\t\t\t\t\t\t\t\t\tAND idfunnel = ?\n\t\t\t\t\t\t\t\t\t\t\tAND deleted = 0", array($idSite, $funnel['idfunnel']));
         $funnel['steps'] = $funnel_steps;
         $funnelsById[$funnel['idfunnel']] = $funnel;
     }
     return $funnelsById;
 }

    private function autoload()
    {
        static $loaded = false;
        if ($loaded) {
            return;
        }
        $all = Piwik_FetchAll('SELECT option_value, option_name
								FROM `' . Piwik::prefixTable('option') . '` 
								WHERE autoload = 1');
        foreach ($all as $option) {
            $this->all[$option['option_name']] = $option['option_value'];
        }
        $loaded = true;
    }

 public static function getGoals($idSite)
 {
     $goals = Piwik_FetchAll("SELECT * \n\t\t\t\t\t\t\t\t\t\t\tFROM " . Piwik_Common::prefixTable('goal') . " \n\t\t\t\t\t\t\t\t\t\t\tWHERE idsite = ?\n\t\t\t\t\t\t\t\t\t\t\t\tAND deleted = 0", $idSite);
     $cleanedGoals = array();
     foreach ($goals as &$goal) {
         unset($goal['idsite']);
         if ($goal['match_attribute'] == 'manually') {
             unset($goal['pattern']);
             unset($goal['pattern_type']);
             unset($goal['case_sensitive']);
         }
         $cleanedGoals[$goal['idgoal']] = $goal;
     }
     return $cleanedGoals;
 }

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

 /**
  * Returns all Goals for a given website, or list of websites
  * 
  * @param string|array $idSite Array or Comma separated list of website IDs to request the goals for
  * @return array Array of Goal attributes
  */
 public function getGoals($idSite)
 {
     if (!is_array($idSite)) {
         $idSite = Piwik_Site::getIdSitesFromIdSitesString($idSite);
     }
     Piwik::checkUserHasViewAccess($idSite);
     $goals = Piwik_FetchAll("SELECT * \n\t\t\t\t\t\t\t\tFROM " . Piwik_Common::prefixTable('goal') . " \n\t\t\t\t\t\t\t\tWHERE idsite IN (" . implode(", ", $idSite) . ")\n\t\t\t\t\t\t\t\t\tAND deleted = 0");
     $cleanedGoals = array();
     foreach ($goals as &$goal) {
         if ($goal['match_attribute'] == 'manually') {
             unset($goal['pattern']);
             unset($goal['pattern_type']);
             unset($goal['case_sensitive']);
         }
         $cleanedGoals[$goal['idgoal']] = $goal;
     }
     return $cleanedGoals;
 }

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

 /**
  * Returns all Goals for a given website, or list of websites
  * 
  * @param string|array $idSite Array or Comma separated list of website IDs to request the goals for
  * @return array Array of Goal attributes
  */
 public function getGoals($idSite)
 {
     //TODO calls to this function could be cached as static
     // would help UI at least, since some UI requests would call this 2-3 times..
     $idSite = Piwik_Site::getIdSitesFromIdSitesString($idSite);
     if (empty($idSite)) {
         return array();
     }
     Piwik::checkUserHasViewAccess($idSite);
     $goals = Piwik_FetchAll("SELECT * \n\t\t\t\t\t\t\t\tFROM " . Piwik_Common::prefixTable('goal') . " \n\t\t\t\t\t\t\t\tWHERE idsite IN (" . implode(", ", $idSite) . ")\n\t\t\t\t\t\t\t\t\tAND deleted = 0");
     $cleanedGoals = array();
     foreach ($goals as &$goal) {
         if ($goal['match_attribute'] == 'manually') {
             unset($goal['pattern']);
             unset($goal['pattern_type']);
             unset($goal['case_sensitive']);
         }
         $cleanedGoals[$goal['idgoal']] = $goal;
     }
     return $cleanedGoals;
 }

 public function getExperiments($idSite)
 {
     Piwik::checkUserHasViewAccess($idSite);
     $experiment_table = Piwik_Common::prefixTable('experiment');
     $experiment_page_table = Piwik_Common::prefixTable('experiment_page');
     $goal_table = Piwik_Common::prefixTable('goal');
     $experiments = Piwik_FetchAll("SELECT " . $experiment_table . ".*, " . $goal_table . ".name as goal_name, " . $goal_table . ".idgoal\n\t\t\t\t\t\t\t\t   FROM   " . $experiment_table . ", " . $goal_table . " \n\t\t\t\t\t\t\t\t   WHERE  " . $experiment_table . ".idsite = ?\n\t\t\t\t\t\t\t\t   AND    " . $experiment_table . ".idgoal = " . $goal_table . ".idgoal\n\t\t\t\t\t\t\t\t   AND    " . $experiment_table . ".deleted = 0", array($idSite));
     $experimentsById = array();
     foreach ($experiments as &$experiment) {
         $experiment_pages = Piwik_FetchAll("SELECT *\n  \t\t\t\t\t\t\t\t                    \t FROM   " . $experiment_page_table . "\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 idexperiment = ?\n  \t\t\t\t\t\t\t\t\t\t\t                 AND deleted = 0", array($idSite, $experiment['idexperiment']));
         $experiment['variation_pages'] = array();
         foreach ($experiment_pages as $experiment_page) {
             if ($experiment_page['original'] == 1) {
                 $experiment['original_page'] = $experiment_page;
             } else {
                 $experiment['variation_pages'][] = $experiment_page;
             }
         }
         $experimentsById[$experiment['idexperiment']] = $experiment;
     }
     return $experimentsById;
 }

 /**
  * Returns the idArchive if the archive is available in the database.
  * Returns false if the archive needs to be computed.
  * 
  * An archive is available if
  * - for today, the archive was computed less than maxTimestampArchive seconds ago
  * - for any other day, if the archive was computed once this day was finished
  * - for other periods, if the archive was computed once the period was finished
  *
  * @return int|false
  */
 protected function isArchived()
 {
     $bindSQL = array($this->idsite, $this->strDateStart, $this->strDateEnd, $this->periodId);
     $timeStampWhere = " AND UNIX_TIMESTAMP(ts_archived) >= ? ";
     $bindSQL[] = $this->maxTimestampArchive;
     $sqlQuery = "\tSELECT idarchive, value, name, UNIX_TIMESTAMP(date1) as timestamp\n\t\t\t\t\t\tFROM " . $this->tableArchiveNumeric->getTableName() . "\n\t\t\t\t\t\tWHERE idsite = ?\n\t\t\t\t\t\t\tAND date1 = ?\n\t\t\t\t\t\t\tAND date2 = ?\n\t\t\t\t\t\t\tAND period = ?\n\t\t\t\t\t\t\tAND ( (name = 'done' AND value = " . Piwik_ArchiveProcessing::DONE_OK . ")\n\t\t\t\t\t\t\t\t\tOR name = 'nb_visits')\n\t\t\t\t\t\t\t{$timeStampWhere}\n\t\t\t\t\t\tORDER BY ts_archived DESC";
     $results = Piwik_FetchAll($sqlQuery, $bindSQL);
     if (empty($results)) {
         return false;
     }
     $idarchive = false;
     // we look for the more recent idarchive
     foreach ($results as $result) {
         if ($result['name'] == 'done') {
             $idarchive = $result['idarchive'];
             $this->timestampDateStart = $result['timestamp'];
             break;
         }
     }
     // case when we have a nb_visits entry in the archive, but the process is not finished yet or failed to finish
     // therefore we don't have the done=OK
     if ($idarchive === false) {
         return false;
     }
     // we look for the nb_visits result for this more recent archive
     foreach ($results as $result) {
         if ($result['name'] == 'nb_visits' && $result['idarchive'] == $idarchive) {
             $this->isThereSomeVisits = $result['value'] != 0;
             break;
         }
     }
     return $idarchive;
 }

 /**
  * Called at the end of the archiving process.
  * Does some cleaning job in the database.
  */
 protected function postCompute()
 {
     parent::postCompute();
     $blobTable = $this->tableArchiveBlob->getTableName();
     $numericTable = $this->tableArchiveNumeric->getTableName();
     $key = 'lastPurge_' . $blobTable;
     $timestamp = Piwik_GetOption($key);
     if (!$timestamp || $timestamp < time() - 86400) {
         Piwik_SetOption($key, time());
         // we delete out of date daily archives from table, maximum once per day
         // we only delete archives processed that are older than 1 day, to not delete archives we just processed
         $yesterday = Piwik_Date::factory('yesterday')->getDateTime();
         $result = Piwik_FetchAll("\n\t\t\t\t\t\t\tSELECT idarchive\n\t\t\t\t\t\t\tFROM {$numericTable}\n\t\t\t\t\t\t\tWHERE name LIKE 'done%'\n\t\t\t\t\t\t\t\tAND value = " . Piwik_ArchiveProcessing::DONE_OK_TEMPORARY . "\n\t\t\t\t\t\t\t\tAND ts_archived < ?", array($yesterday));
         $idArchivesToDelete = array();
         if (!empty($result)) {
             foreach ($result as $row) {
                 $idArchivesToDelete[] = $row['idarchive'];
             }
             $query = "DELETE \n    \t\t\t\t\t\tFROM %s\n    \t\t\t\t\t\tWHERE idarchive IN (" . implode(',', $idArchivesToDelete) . ")\n    \t\t\t\t\t\t";
             Piwik_Query(sprintf($query, $blobTable));
             Piwik_Query(sprintf($query, $numericTable));
         }
         Piwik::log("Purging temporary archives: done [ purged archives older than {$yesterday} from {$blobTable} and {$numericTable} ] [Deleted IDs: " . implode(',', $idArchivesToDelete) . "]");
         // Deleting "Custom Date Range" reports after 1 day, since they can be re-processed
         // and would take up unecessary space
         $query = "DELETE \n    \t\t\t\t\tFROM %s\n    \t\t\t\t\tWHERE period = ?\n    \t\t\t\t\t\tAND ts_archived < ?";
         $bind = array(Piwik::$idPeriods['range'], $yesterday);
         Piwik_Query(sprintf($query, $blobTable), $bind);
         Piwik_Query(sprintf($query, $numericTable), $bind);
     } else {
         Piwik::log("Purging temporary archives: skipped.");
     }
     if (!isset($this->archives)) {
         return;
     }
     foreach ($this->archives as $archive) {
         destroy($archive);
     }
     $this->archives = array();
 }

 /**
  * Given a monthly archive table, will delete all reports that are now outdated, 
  * or reports that ended with an error
  */
 public static function doPurgeOutdatedArchives($numericTable)
 {
     $blobTable = str_replace("numeric", "blob", $numericTable);
     $key = self::FLAG_TABLE_PURGED . $blobTable;
     $timestamp = Piwik_GetOption($key);
     // we shall purge temporary archives after their timeout is finished, plus an extra 6 hours
     // in case archiving is disabled or run once a day, we give it this extra time to run
     // and re-process more recent records...
     // TODO: Instead of hardcoding 6 we should put the actual number of hours between 2 archiving runs
     $temporaryArchivingTimeout = self::getTodayArchiveTimeToLive();
     $purgeEveryNSeconds = max($temporaryArchivingTimeout, 6 * 3600);
     // we only delete archives if we are able to process them, otherwise, the browser might process reports
     // when &segment= is specified (or custom date range) and would below, delete temporary archives that the
     // browser is not able to process until next cron run (which could be more than 1 hour away)
     if (self::isRequestAuthorizedToArchive() && (!$timestamp || $timestamp < time() - $purgeEveryNSeconds)) {
         Piwik_SetOption($key, time());
         // If Browser Archiving is enabled, it is likely there are many more temporary archives
         // We delete more often which is safe, since reports are re-processed on demand
         if (self::isBrowserTriggerArchivingEnabled()) {
             $purgeArchivesOlderThan = Piwik_Date::factory(time() - 2 * $temporaryArchivingTimeout)->getDateTime();
         } else {
             $purgeArchivesOlderThan = Piwik_Date::factory('today')->getDateTime();
         }
         $result = Piwik_FetchAll("\n\t\t\t\tSELECT idarchive\n\t\t\t\tFROM {$numericTable}\n\t\t\t\tWHERE name LIKE 'done%'\n\t\t\t\t\tAND ((  value = " . Piwik_ArchiveProcessing::DONE_OK_TEMPORARY . "\n\t\t\t\t\t\t    AND ts_archived < ?)\n\t\t\t\t\t\t OR value = " . Piwik_ArchiveProcessing::DONE_ERROR . ")", array($purgeArchivesOlderThan));
         $idArchivesToDelete = array();
         if (!empty($result)) {
             foreach ($result as $row) {
                 $idArchivesToDelete[] = $row['idarchive'];
             }
             $query = "DELETE \n    \t\t\t\t\t\tFROM %s\n    \t\t\t\t\t\tWHERE idarchive IN (" . implode(',', $idArchivesToDelete) . ")\n    \t\t\t\t\t\t";
             Piwik_Query(sprintf($query, $numericTable));
             // Individual blob tables could be missing
             try {
                 Piwik_Query(sprintf($query, $blobTable));
             } catch (Exception $e) {
             }
         }
         Piwik::log("Purging temporary archives: done [ purged archives older than {$purgeArchivesOlderThan} from {$blobTable} and {$numericTable} ] [Deleted IDs: " . implode(',', $idArchivesToDelete) . "]");
         // Deleting "Custom Date Range" reports after 1 day, since they can be re-processed
         // and would take up unecessary space
         $yesterday = Piwik_Date::factory('yesterday')->getDateTime();
         $query = "DELETE \n    \t\t\t\t\tFROM %s\n    \t\t\t\t\tWHERE period = ?\n    \t\t\t\t\t\tAND ts_archived < ?";
         $bind = array(Piwik::$idPeriods['range'], $yesterday);
         Piwik::log("Purging Custom Range archives: done [ purged archives older than {$yesterday} from {$blobTable} and {$numericTable} ]");
         Piwik_Query(sprintf($query, $numericTable), $bind);
         // Individual blob tables could be missing
         try {
             Piwik_Query(sprintf($query, $blobTable), $bind);
         } catch (Exception $e) {
         }
         // these tables will be OPTIMIZEd daily in a scheduled task, to claim lost space
     } else {
         Piwik::log("Purging temporary archives: skipped.");
     }
 }