PHP Piwik_Common::getSqlStringFieldsArray方法代码示例

 /**
  * Given a list of fields defining numeric values, it will return a Piwik_DataTable_Array
  * which is an array of Piwik_DataTable_Simple, ordered by chronological order
  *
  * @param array|string $fields array( fieldName1, fieldName2, ...)  Names of the mysql table fields to load
  * @return Piwik_DataTable_Array
  */
 public function getDataTableFromNumeric($fields)
 {
     $inNames = Piwik_Common::getSqlStringFieldsArray($fields);
     // we select in different shots
     // one per distinct table (case we select last 300 days, maybe we will  select from 10 different tables)
     $queries = array();
     foreach ($this->archives as $archive) {
         $archive->setRequestedReport(is_string($fields) ? $fields : current($fields));
         $archive->prepareArchive();
         if (!$archive->isThereSomeVisits) {
             continue;
         }
         $table = $archive->archiveProcessing->getTableArchiveNumericName();
         // for every query store IDs
         $queries[$table][] = $archive->getIdArchive();
     }
     // we select the requested value
     $db = Zend_Registry::get('db');
     // date => array( 'field1' =>X, 'field2'=>Y)
     // date2 => array( 'field1' =>X2, 'field2'=>Y2)
     $arrayValues = array();
     foreach ($queries as $table => $aIds) {
         $inIds = implode(', ', array_filter($aIds));
         if (empty($inIds)) {
             // Probable timezone configuration error, i.e., mismatch between PHP and MySQL server.
             continue;
         }
         $sql = "SELECT value, name, date1 as startDate\n\t\t\t\t\tFROM {$table}\n\t\t\t\t\tWHERE idarchive IN ( {$inIds} )\n\t\t\t\t\tAND name IN ( {$inNames} )\n\t\t\t\t\tORDER BY date1, name";
         $values = $db->fetchAll($sql, $fields);
         foreach ($values as $value) {
             $timestamp = Piwik_Date::factory($value['startDate'])->getTimestamp();
             $arrayValues[$timestamp][$value['name']] = $this->formatNumericValue($value['value']);
         }
     }
     $contentArray = array();
     // we add empty tables so that every requested date has an entry, even if there is nothing
     // example: <result date="2007-01-01" />
     $archiveByTimestamp = array();
     foreach ($this->archives as $archive) {
         $timestamp = $archive->getTimestampStartDate();
         $archiveByTimestamp[$timestamp] = $archive;
         $contentArray[$timestamp]['table'] = new Piwik_DataTable_Simple();
         $contentArray[$timestamp]['prettyDate'] = $archive->getPrettyDate();
     }
     foreach ($arrayValues as $timestamp => $aNameValues) {
         // undefined in some edge/unknown cases see http://dev.piwik.org/trac/ticket/2578
         if (isset($contentArray[$timestamp]['table'])) {
             $contentArray[$timestamp]['table']->addRowsFromArray($aNameValues);
         }
     }
     $contentArray;
     $tableArray = $this->getNewDataTableArray();
     foreach ($contentArray as $timestamp => $aData) {
         $tableArray->addTable($aData['table'], $aData['prettyDate']);
         $this->loadMetadata($tableArray, $archiveByTimestamp[$timestamp]);
     }
     return $tableArray;
 }

 /**
  * Helper function used by other record* methods which will INSERT or UPDATE the conversion in the DB
  * 
  * @param array $newGoal
  * @param bool $mustUpdateNotInsert If set to true, the previous conversion will be UPDATEd. This is used for the Cart Update conversion (only one cart per visit)
  * @param array $updateWhere
  * @return bool
  */
 protected function recordGoal($newGoal, $mustUpdateNotInsert = false, $updateWhere = array())
 {
     $newGoalDebug = $newGoal;
     $newGoalDebug['idvisitor'] = bin2hex($newGoalDebug['idvisitor']);
     printDebug($newGoalDebug);
     $fields = implode(", ", array_keys($newGoal));
     $bindFields = Piwik_Common::getSqlStringFieldsArray($newGoal);
     if ($mustUpdateNotInsert) {
         $updateParts = $sqlBind = $updateWhereParts = array();
         foreach ($newGoal as $name => $value) {
             $updateParts[] = $name . " = ?";
             $sqlBind[] = $value;
         }
         foreach ($updateWhere as $name => $value) {
             $updateWhereParts[] = $name . " = ?";
             $sqlBind[] = $value;
         }
         $sql = 'UPDATE  ' . Piwik_Common::prefixTable('log_conversion') . "\t\n\t\t\t\t\tSET " . implode($updateParts, ', ') . "\n\t\t\t\t\t\tWHERE " . implode($updateWhereParts, ' AND ');
         Piwik_Tracker::getDatabase()->query($sql, $sqlBind);
         return true;
     } else {
         $sql = 'INSERT IGNORE INTO ' . Piwik_Common::prefixTable('log_conversion') . "\t\n\t\t\t\t\t({$fields}) VALUES ({$bindFields}) ";
         $bind = array_values($newGoal);
         $result = Piwik_Tracker::getDatabase()->query($sql, $bind);
         // If a record was inserted, we return true
         return Piwik_Tracker::getDatabase()->rowCount($result) > 0;
     }
 }

 /**
  * Returns the list of all the users
  * 
  * @param string $userLogins  Comma separated list of users to select. If not specified, will return all users
  * @return array the list of all the users
  */
 public function getUsers($userLogins = '')
 {
     Piwik::checkUserHasSomeAdminAccess();
     $where = '';
     $bind = array();
     if (!empty($userLogins)) {
         $userLogins = explode(',', $userLogins);
         $where = 'WHERE login IN (' . Piwik_Common::getSqlStringFieldsArray($userLogins) . ')';
         $bind = $userLogins;
     }
     $db = Zend_Registry::get('db');
     $users = $db->fetchAll("SELECT * \n\t\t\t\t\t\t\t\tFROM " . Piwik_Common::prefixTable("user") . "\n\t\t\t\t\t\t\t\t{$where} \n\t\t\t\t\t\t\t\tORDER BY login ASC", $bind);
     // Non Super user can only access login & alias
     if (!Piwik::isUserIsSuperUser()) {
         foreach ($users as &$user) {
             $user = array('login' => $user['login'], 'alias' => $user['alias']);
         }
     }
     return $users;
 }

	/**
	 * Records in the DB the association between the visit and this action.
	 * 
	 * @param int idVisit is the ID of the current visit in the DB table log_visit
	 * @param int idRefererActionUrl is the ID of the last action done by the current visit. 
	 * @param int timeSpentRefererAction is the number of seconds since the last action was done. 
	 * 				It is directly related to idRefererActionUrl.
	 */
	 public function record( $idVisit, $visitorIdCookie, $idRefererActionUrl, $idRefererActionName, $timeSpentRefererAction)
	 {
		$this->loadIdActionNameAndUrl();
		
		$idActionName = in_array($this->getActionType(), array(Piwik_Tracker_Action::TYPE_ACTION_NAME, Piwik_Tracker_Action::TYPE_ACTION_URL))
							? (int)$this->getIdActionName()
							: null;
		$insert = array(
			'idvisit' => $idVisit, 
			'idsite' => $this->idSite, 
			'idvisitor' => $visitorIdCookie, 
			'server_time' => Piwik_Tracker::getDatetimeFromTimestamp($this->timestamp), 
			'idaction_url' => (int)$this->getIdActionUrl(), 
			'idaction_name' => $idActionName, 
			'idaction_url_ref' => $idRefererActionUrl, 
			'idaction_name_ref' => $idRefererActionName, 
			'time_spent_ref_action' => $timeSpentRefererAction
		);
		$customVariables = Piwik_Tracker_Visit::getCustomVariables($scope = 'page', $this->request);
		$insert = array_merge($insert, $customVariables);

		// Mysqli apparently does not like NULL inserts?
		$insertWithoutNulls = array();
		foreach($insert as $column => $value)
		{
			if(!is_null($value))
			{
				$insertWithoutNulls[$column] = $value;
			}
		}
		
		$fields = implode(", ", array_keys($insertWithoutNulls));
		$bind = array_values($insertWithoutNulls);
		$values = Piwik_Common::getSqlStringFieldsArray($insertWithoutNulls);

		$sql = "INSERT INTO ".Piwik_Common::prefixTable('log_link_visit_action'). " ($fields) VALUES ($values)";
		Piwik_Tracker::getDatabase()->query( $sql, $bind ); 
		
		$this->idLinkVisitAction = Piwik_Tracker::getDatabase()->lastInsertId(); 
		
		$info = array( 
			'idSite' => $this->idSite, 
			'idLinkVisitAction' => $this->idLinkVisitAction, 
			'idVisit' => $idVisit, 
			'idRefererActionUrl' => $idRefererActionUrl, 
			'idRefererActionName' => $idRefererActionName, 
			'timeSpentRefererAction' => $timeSpentRefererAction, 
		); 
		printDebug($insertWithoutNulls);

		/* 
		* send the Action object ($this)  and the list of ids ($info) as arguments to the event 
		*/ 
		Piwik_PostEvent('Tracker.Action.record', $this, $info);
	 }

 /**
  * Performs a batch insert into a specific table by iterating through the data
  *
  * NOTE: you should use tableInsertBatch() which will fallback to this function if LOAD DATA INFILE not available
  *
  * @param string $tableName PREFIXED table name! you must call Piwik_Common::prefixTable() before passing the table name
  * @param array $fields array of unquoted field names
  * @param array $values array of data to be inserted
  * @param bool $ignoreWhenDuplicate Ignore new rows that contain unique key values that duplicate old rows
  */
 public static function tableInsertBatchIterate($tableName, $fields, $values, $ignoreWhenDuplicate = true)
 {
     $fieldList = '(' . join(',', $fields) . ')';
     $ignore = $ignoreWhenDuplicate ? 'IGNORE' : '';
     foreach ($values as $row) {
         $query = "INSERT {$ignore}\n\t\t\t\t\tINTO " . $tableName . "\n\t\t\t\t\t{$fieldList}\n\t\t\t\t\tVALUES (" . Piwik_Common::getSqlStringFieldsArray($row) . ")";
         Piwik_Query($query, $row);
     }
 }

 /**
  * @param $fields
  * @return array|array  (one row in the array per row fetched in the DB)
  */
 private function loadValuesFromDB($fields)
 {
     $requestedMetrics = is_string($fields) ? array($fields) : $fields;
     $inNames = Piwik_Common::getSqlStringFieldsArray($fields);
     // get the archive ids
     if (!$this->getFirstArchive()->isArchivingDisabled()) {
         $archiveIds = $this->getArchiveIdsAfterLaunching($requestedMetrics);
     } else {
         $archiveIds = $this->getArchiveIdsWithoutLaunching($requestedMetrics);
     }
     $archiveIds = implode(', ', array_filter($archiveIds));
     // if no archive ids are found, avoid executing any SQL queries
     if (empty($archiveIds)) {
         return array();
     }
     // select archive data
     $sql = "SELECT value, name, idarchive, idsite\n\t\t\t\t\t\t\t\tFROM {$this->getNumericTableName()}\n\t\t\t\t\t\t\t\tWHERE idarchive IN ( {$archiveIds} )\n\t\t\t\t\t\t\t\t\tAND name IN ( {$inNames} )";
     return Piwik_FetchAll($sql, $fields);
 }

 /**
  * Save new visitor information to log_visit table.
  * Provides pre- and post- event hooks (Tracker.saveVisitorInformation and Tracker.saveVisitorInformation.end) for plugins
  */
 protected function saveVisitorInformation()
 {
     Piwik_PostEvent('Tracker.saveVisitorInformation', $this->visitorInfo);
     if (empty($this->visitorInfo['location_country'])) {
         $this->visitorInfo['location_country'] = 'xx';
     }
     $this->visitorInfo['location_continent'] = Piwik_Common::getContinent($this->visitorInfo['location_country']);
     $this->visitorInfo['location_browser_lang'] = substr($this->visitorInfo['location_browser_lang'], 0, 20);
     $this->visitorInfo['referer_name'] = substr($this->visitorInfo['referer_name'], 0, 70);
     $this->visitorInfo['referer_keyword'] = substr($this->visitorInfo['referer_keyword'], 0, 255);
     $this->visitorInfo['config_resolution'] = substr($this->visitorInfo['config_resolution'], 0, 9);
     $fields = implode(", ", array_keys($this->visitorInfo));
     $values = Piwik_Common::getSqlStringFieldsArray($this->visitorInfo);
     $sql = "INSERT INTO " . Piwik_Common::prefixTable('log_visit') . " ({$fields}) VALUES ({$values})";
     $bind = array_values($this->visitorInfo);
     Piwik_Tracker::getDatabase()->query($sql, $bind);
     $idVisit = Piwik_Tracker::getDatabase()->lastInsertId();
     $this->visitorInfo['idvisit'] = $idVisit;
     $this->visitorInfo['visit_first_action_time'] = $this->getCurrentTimestamp();
     $this->visitorInfo['visit_last_action_time'] = $this->getCurrentTimestamp();
     Piwik_PostEvent('Tracker.saveVisitorInformation.end', $this->visitorInfo);
 }

    /**
     * Returns all websites with a timezone matching one the specified timezones
     *  
     * @param array $timezones
     * @ignore
     */
    public function getSitesIdFromTimezones($timezones)
    {
        Piwik::checkUserIsSuperUser();
        $timezones = Piwik::getArrayFromApiParameter($timezones);
        $timezones = array_unique($timezones);
        $ids = Zend_Registry::get('db')->fetchAll('SELECT idsite 
					FROM ' . Piwik_Common::prefixTable('site') . ' 
					WHERE timezone IN (' . Piwik_Common::getSqlStringFieldsArray($timezones) . ')
					ORDER BY idsite ASC', $timezones);
        $return = array();
        foreach ($ids as $id) {
            $return[] = $id['idsite'];
        }
        return $return;
    }

	private function loadValuesFromDB($fields)
	{
		$inNames = Piwik_Common::getSqlStringFieldsArray($fields);
		$archiveIds = $this->getArchiveIds();
		if(empty($archiveIds))
		{
			return array();
		}
 		$sql = "SELECT value, name, idarchive, idsite
								FROM {$this->getNumericTableName()}
								WHERE idarchive IN ( $archiveIds )
									AND name IN ( $inNames )";
		return Piwik_FetchAll($sql, $fields);
	}