PHP Db::fetchRow方法代码示例

 public function getActiveGoal($idSite, $idGoal)
 {
     $idSite = (int) $idSite;
     $idGoal = (int) $idGoal;
     $goals = Db::fetchRow("SELECT * FROM " . $this->table . "\n                                WHERE idsite = {$idSite} AND idgoal = {$idGoal}\n                                      AND deleted = 0 LIMIT 1");
     return $goals;
 }

 public static function get($idvisitor)
 {
     $row = Db::fetchRow("SELECT * FROM " . Common::prefixTable('chat_personnal_informations') . " WHERE idvisitor = ?", array(@Common::hex2bin($idvisitor)));
     if (!$row) {
         $row = array('name' => NULL, 'email' => NULL, 'phone' => NULL, 'comments' => NULL);
     } else {
         $row = ChatCommon::formatRow($row);
     }
     return $row;
 }

 /**
  * Gets the MySQL table status of the requested Piwik table.
  *
  * @param string $table The name of the table. Should not be prefixed (ie, 'log_visit' is
  *                      correct, 'piwik_log_visit' is not).
  * @return array See http://dev.mysql.com/doc/refman/5.1/en/show-table-status.html .
  */
 public function getTableStatus($table)
 {
     $prefixed = Common::prefixTable($table);
     // if we've already gotten every table status, don't issue an uneeded query
     if (!is_null($this->tableStatuses) && isset($this->tableStatuses[$prefixed])) {
         return $this->tableStatuses[$prefixed];
     } else {
         return Db::fetchRow("SHOW TABLE STATUS LIKE ?", array($prefixed));
     }
 }

 /**
  * Create access table record for the user
  * THis will create a record for the user to access at least one site when he logs in first time
  * 
  * @param String    $userLogin  The user login string
  * @param Integer   $idSite     The ID of the site user is given access to
  * @return type Description
  */
 public function createAccess($userLogin, $idSite)
 {
     //TODO: get the list of user access to the sites and update the records accordingly
     //check if the record already exists
     $sql = "SELECT *\r\n                FROM " . Common::prefixTable($this->__PIWIK_ACCESS_TABLE) . " pa\r\n                WHERE pa.login = '" . $userLogin . "' AND pa.idsite = " . $idSite;
     if (!($access = Db::fetchRow($sql))) {
         $sql = "INSERT INTO " . Common::prefixTable($this->__PIWIK_ACCESS_TABLE) . " (login, idsite, access) \r\n                    VALUES('" . $userLogin . "', " . (int) $idSite . ", 'view')";
         Db::exec($sql);
     }
 }

 public function test_PiwikUserIsNotCreated_IfPiwikUserAlreadyExists()
 {
     Access::getInstance()->setSuperUserAccess(true);
     UsersManagerAPI::getInstance()->addUser(self::TEST_LOGIN, self::TEST_PASS, 'billionairephilanthropistplayboy@starkindustries.com', $alias = false);
     Access::getInstance()->setSuperUserAccess(false);
     $this->authenticateViaLdap();
     $user = Db::fetchRow("SELECT login, password, alias, email, token_auth FROM " . Common::prefixTable('user') . " WHERE login = ?", array(self::TEST_LOGIN));
     $this->assertNotEmpty($user);
     $this->assertEquals(array('login' => self::TEST_LOGIN, 'password' => md5(self::TEST_PASS), 'alias' => self::TEST_LOGIN, 'email' => 'billionairephilanthropistplayboy@starkindustries.com', 'token_auth' => UsersManagerAPI::getInstance()->getTokenAuth(self::TEST_LOGIN, md5(self::TEST_PASS))), $user);
     $this->assertNoAccessInDb();
 }

 /**
  * Computes the total number of unique visitors who visited at least one site in,
  * a set of sites and the number of unique visitors that visited all of the sites
  * in the set.
  *
  * Comparison is done in dates for the UTC time, not for the site specific time.
  *
  * Performance: The SQL query this method executes was tested on a Piwik instance
  *              with 13 million visits total. Computing data for 4 sites with no
  *              date limit took 13s to complete.
  *
  * @param int[] $idSites The IDs of the sites for whom unique visitor counts should be
  *                       computed.
  * @param Date $startDate The lower bound of the date range of the visits to check.
  * @param Date $endDate The upper bound of the date range of the visits to check.
  * @param Segment $segment An optional segment to apply to the visits set before aggregation.
  *                         To supply no segment, use `new Segment()`.
  * @return int[] Returns two metrics: **nb_total_visitors** and **nb_shared_visitors**.
  *
  *               **nb_total_visitors** is the total number of unique visitors who visited
  *               at least one site in the list.
  *
  *               **nb_shared_visitors** is the total number of unique visitors who visited
  *               every site in the list.
  * @throws Exception if less than 2 site IDs are supplied,
  */
 public function getCommonVisitorCount($idSites, Date $startDate, Date $endDate, Segment $segment)
 {
     Log::debug("%s::%s('%s', '%s', '%s', '%s') called", "Model\\DistinctMetricsAggregator", __FUNCTION__, $idSites, $startDate, $endDate, $segment);
     if (count($idSites) == 1) {
         throw new Exception(Piwik::translate('InterSites_PleasSupplyAtLeastTwoDifferentSites'));
     }
     $select = "config_id, COUNT(DISTINCT idsite) AS sitecount";
     $from = array('log_visit');
     $where = 'visit_last_action_time >= ? AND visit_last_action_time <= ? AND idsite IN (' . Common::getSqlStringFieldsArray($idSites) . ')';
     $orderBy = false;
     $groupBy = 'config_id';
     $startDateTime = new \DateTime($startDate->toString());
     $endDateTime = new \DateTime($endDate->toString());
     $bind = array_merge(array($startDateTime->format("Y-m-d 00:00:00"), $endDateTime->format("Y-m-d 23:59:59")), $idSites);
     $innerQuery = $segment->getSelectQuery($select, $from, $where, $bind, $orderBy, $groupBy);
     $wholeQuery = "SELECT COUNT(sitecount_by_config.config_id) AS nb_total_visitors,\n                              SUM(IF(sitecount_by_config.sitecount >= " . count($idSites) . ", 1, 0)) AS nb_shared_visitors\n                         FROM ( {$innerQuery['sql']} ) AS sitecount_by_config";
     $result = Db::fetchRow($wholeQuery, $innerQuery['bind']);
     // nb_shared_visitors can be NULL if there are no visits
     if ($result['nb_shared_visitors'] === null) {
         $result['nb_shared_visitors'] = 0;
     }
     Log::debug("%s::%s() returned '%s'", "Model\\DistinctMetricsAggregator", __FUNCTION__, $result);
     return $result;
 }

 public function getVisitorLastMessage()
 {
     $arguments = array($this->idsite, @Common::hex2bin($this->idvisitor));
     $row = Db::fetchRow("SELECT idvisitor, answerfrom, content, microtime,\n\t\t(SELECT name FROM " . Common::prefixTable('chat_personnal_informations') . " WHERE idvisitor = chat.idvisitor) AS name\n\t\tFROM " . Common::prefixTable('chat') . " AS chat WHERE idsite = ? AND idvisitor = ? ORDER BY microtime DESC LIMIT 1", $arguments);
     $row = ChatCommon::formatRow($row);
     return $row;
 }

 public static function get($idSegment)
 {
     return Db::fetchRow("SELECT * FROM " . Common::prefixTable('segment') . " WHERE idsegment = ?", array($idSegment));
 }

 /**
  * The actual task is defined in this method. Here you can access any option or argument that was defined on the
  * command line via $input and write anything to the console via $output argument.
  * In case anything went wrong during the execution you should throw an exception to make sure the user will get a
  * useful error message and to make sure the command does not exit with the status code 0.
  *
  * Ideally, the actual command is quite short as it acts like a controller. It should only receive the input values,
  * execute the task by calling a method of another class and output any useful information.
  *
  * Execute the command like: ./console snoopy:recalculate-score --name="The Piwik Team"
  */
 protected function execute(InputInterface $input, OutputInterface $output)
 {
     /**
      * Settings for scooring
      */
     $settings = new \Piwik\Plugins\SnoopyBehavioralScoring\Settings();
     /**
      * Which site we scoore
      */
     $matching_site = $settings->matching_site->getValue();
     /**
      * Which goals mark our visitor to stat with tracking
      */
     $matching_goals = $settings->matching_goals->getValue();
     /**
      * Enable full debug (Additional infor are displayed like cooling and adding)
      */
     $full_debug = $settings->full_console_debug->getValue();
     /**
      * Cooling factor that tells how fast the visitor will coole down when no action made
      */
     $cooling_factor = $settings->cooling_factor->getValue();
     /**
      * How much specific type of referer is worth
      */
     $campaign_entry = $settings->campaign_entry->getValue();
     /**
      * How much specific url is worth
      */
     $special_urls = array();
     $special_urls_raw = $settings->special_urls->getValue();
     $special_urls_raw = explode("\n", $special_urls_raw);
     foreach ($special_urls_raw as $url_info) {
         $special_url = explode(';', $url_info);
         if (!array_key_exists($special_url[0], $special_urls) && sizeof($special_url) == 2) {
             $special_urls[$special_url[0]] = (double) trim($special_url[1]);
         }
     }
     $output->writeln("<info>************************************************************************</info>");
     $output->writeln("<info>Starting visitor scoring...</info>");
     $output->writeln("<comment>Getting visitors to score...</comment>");
     $visitors_to_score = \Piwik\API\Request::processRequest('SnoopyBehavioralScoring.getVisitorIdsToScore', array());
     $previously_scored = Db::fetchAll(" SELECT DISTINCT idvisitor\n                                            FROM " . Common::prefixTable(SnoopyBehavioralScoring::getTableName()) . "\n                                            WHERE idvisitor IN('" . implode("','", $visitors_to_score) . "')\n                                            ORDER BY id DESC");
     /**
     		SCORE ALREADY SCORED VISITORS
     		 **/
     //First score already visitors
     $output->writeln(sprintf("<comment>Scoring already scored visitors (%s)...</comment>", count($previously_scored)));
     foreach ($previously_scored as $scored_visitor) {
         if (($key = array_search($scored_visitor['idvisitor'], $visitors_to_score)) !== false) {
             unset($visitors_to_score[$key]);
         }
         $idvisitor = $scored_visitor['idvisitor'];
         $scored_visitor = Db::fetchRow("SELECT *\n                                            FROM " . Common::prefixTable(SnoopyBehavioralScoring::getTableName()) . "\n                                            WHERE idvisitor = ?\n                                            ORDER BY id DESC\n                                            LIMIT 1", array($scored_visitor['idvisitor']));
         $output->writeln(sprintf("<info>Scoring visitor: %s</info>", $idvisitor));
         if ($full_debug) {
             $output->writeln(sprintf("<comment>Curent score: %s</comment>", $scored_visitor['score']));
         }
         $visits = Db::fetchAll("SELECT idvisit, visit_first_action_time, visit_last_action_time, referer_type\n                                    FROM " . Common::prefixTable("log_visit") . "\n                                    WHERE HEX(idvisitor) = ?\n                                    AND idsite = ?\n                                    AND visit_last_action_time > ?", array($idvisitor, $matching_site, $scored_visitor['created_at']));
         //$output->writeln(print_r($visits, true));
         $output->writeln(sprintf("<comment>Number of visits: %s</comment>", count($visits)));
         $visitor_score = $scored_visitor['score'];
         $last_date = null;
         $campaigns = array();
         $total_goals = 0;
         foreach ($visits as $visit) {
             $tmp_score = 0;
             $goals = Db::fetchRow("SELECT  COUNT(*) AS count\n\t\t\t\t\t\t\t\tFROM " . Common::prefixTable("log_conversion") . "\n\t\t\t\t\t\t\t\tWHERE idsite = ?\n\t\t\t\t\t\t\t\tAND idgoal IN(" . implode(",", $matching_goals) . ")\n\t\t\t\t\t\t\t\tAND HEX(idvisitor) = ?\n\t\t\t\t\t\t\t\tAND server_time >= ? AND server_time <= ?", array($matching_site, $idvisitor, $visit['visit_first_action_time'], $visit['visit_last_action_time']));
             $total_goals += $goals['count'];
             $tmp_score += $goals['count'] * 5;
             if ($full_debug) {
                 $output->writeln(sprintf("<comment>\tGoals: %s</comment>", $goals['count']));
                 $output->writeln(sprintf("<comment>\t\tFirst action: %s</comment>", $visit['visit_first_action_time']));
                 $output->writeln(sprintf("<comment>\t\tLast action: %s</comment>", $visit['visit_last_action_time']));
             }
             $visit_score = array();
             if ($full_debug) {
                 $output->writeln(sprintf("<comment>\tScoring visitid: %s</comment>", $visit['idvisit']));
             }
             /**
              * If visitor came from email campaign we add adittional campaign entry
              */
             if ($visit['referer_type'] == 6) {
                 if ($full_debug) {
                     $output->writeln("<comment>\t\tAdding campaign entry bonus</comment>");
                 }
                 if (!array_key_exists($visit['referer_name'], $campaigns)) {
                     $tmp_score += $campaign_entry;
                     $campaigns[$visit['referer_name']] = $visit['referer_name'];
//.........这里部分代码省略.........

 public function heatStatus($idvisitor)
 {
     $status = Db::fetchRow("SELECT status FROM " . Common::prefixTable("snoopy_visitors_statuses") . " WHERE idvisitor = ? ", array($idvisitor));
     if (!empty($status)) {
         return $status['status'];
     }
 }

 private function getIdVisit($idVisit)
 {
     return Db::fetchRow("SELECT * FROM " . Common::prefixTable('log_visit') . " WHERE idvisit = ?", array($idVisit));
 }

 public static function get($idAutoMsg)
 {
     return Db::fetchRow("SELECT * FROM " . Common::prefixTable('chat_automatic_message') . " WHERE id = ?", array($idAutoMsg));
 }

 public function getVisit($idVisit, $allColumns = false)
 {
     $columns = $allColumns ? "*" : "location_country, location_region, location_city, location_latitude, location_longitude";
     $visit = Db::fetchRow("SELECT {$columns} FROM " . Common::prefixTable('log_visit') . " WHERE idvisit = ?", array($idVisit));
     return $visit;
 }

 protected function getUser($login)
 {
     return Db::fetchRow("SELECT login, password, alias, email, token_auth FROM " . Common::prefixTable('user') . " WHERE login = ?", array($login));
 }

 public function getTableStatus($tableName)
 {
     return Db::fetchRow("SHOW TABLE STATUS LIKE ?", array($tableName));
 }