PHP util\PreparedStatementConditionBuilder类代码示例

 /**
  * @see	\wcf\data\AbstractDatabaseObjectAction::delete()
  */
 public function delete()
 {
     parent::delete();
     if (!empty($this->objects)) {
         // identify i18n labels
         $languageVariables = array();
         foreach ($this->objects as $object) {
             if (preg_match('~wcf.acp.label.label\\d+~', $object->label)) {
                 $languageVariables[] = $object->label;
             }
         }
         // remove language variables
         if (!empty($languageVariables)) {
             $conditions = new PreparedStatementConditionBuilder();
             $conditions->add("languageItem IN (?)", array($languageVariables));
             $sql = "SELECT\tlanguageItemID\n\t\t\t\t\tFROM\twcf" . WCF_N . "_language_item\n\t\t\t\t\t" . $conditions;
             $statement = WCF::getDB()->prepareStatement($sql);
             $statement->execute($conditions->getParameters());
             $languageItemIDs = array();
             while ($row = $statement->fetchArray()) {
                 $languageItemIDs[] = $row['languageItemID'];
             }
             $objectAction = new LanguageItemAction($languageItemIDs, 'delete');
             $objectAction->executeAction();
         }
     }
 }

 /**
  * @see wcf\system\setup\IFileHandler::logFiles()
  */
 public function logFiles(array $files)
 {
     if (empty($files)) {
         return;
     }
     // fetch already installed files
     $conditions = new PreparedStatementConditionBuilder();
     $conditions->add("packageID = ?", array($this->packageInstallation->getPackageID()));
     $conditions->add("filename IN (?)", array($files));
     $sql = "SELECT\tfilename\n\t\t\tFROM\twcf" . WCF_N . "_package_installation_file_log\n\t\t\t" . $conditions;
     $statement = WCF::getDB()->prepareStatement($sql);
     $statement->execute($conditions->getParameters());
     $installedFiles = array();
     while ($row = $statement->fetchArray()) {
         $installedFiles[] = $row['filename'];
     }
     // ignore files which have already been installed
     $installFiles = array();
     foreach ($files as $file) {
         if (in_array($file, $installedFiles)) {
             continue;
         }
         $installFiles[] = $file;
     }
     if (!empty($installFiles)) {
         $sql = "INSERT INTO\twcf" . WCF_N . "_package_installation_file_log\n\t\t\t\t\t\t(packageID, filename)\n\t\t\t\tVALUES\t\t(?, ?)";
         $statement = WCF::getDB()->prepareStatement($sql);
         foreach ($installFiles as $file) {
             $statement->execute(array($this->packageInstallation->getPackageID(), $file));
         }
     }
 }

 /**
  * @see	\wcf\system\cache\builder\AbstractCacheBuilder::rebuild()
  */
 protected function rebuild(array $parameters)
 {
     $data = array('boxes' => array(), 'pages' => array());
     // load boxes
     $boxList = new DashboardBoxList();
     $boxList->readObjects();
     foreach ($boxList as $box) {
         $data['boxes'][$box->boxID] = $box;
     }
     // load settings
     $objectTypes = ObjectTypeCache::getInstance()->getObjectTypes('com.woltlab.wcf.user.dashboardContainer');
     $objectTypeIDs = array();
     foreach ($objectTypes as $objectType) {
         $objectTypeIDs[] = $objectType->objectTypeID;
     }
     $conditions = new PreparedStatementConditionBuilder();
     $conditions->add("objectTypeID IN (?)", array($objectTypeIDs));
     $sql = "SELECT\t\t*\n\t\t\tFROM\t\twcf" . WCF_N . "_dashboard_option\n\t\t\t" . $conditions . "\n\t\t\tORDER BY\tshowOrder ASC";
     $statement = WCF::getDB()->prepareStatement($sql);
     $statement->execute($conditions->getParameters());
     while ($row = $statement->fetchArray()) {
         if (!isset($data['pages'][$row['objectTypeID']])) {
             $data['pages'][$row['objectTypeID']] = array();
         }
         $data['pages'][$row['objectTypeID']][] = $row['boxID'];
     }
     return $data;
 }

 /**
  * @see wcf\system\cache\ICacheBuilder::getData()
  */
 public function getData(array $cacheResource)
 {
     list($cache, $packageID) = explode('-', $cacheResource['cache']);
     $data = array();
     // get all menu items and filter menu items with low priority
     $sql = "SELECT\t\tmenuItem, menuItemID \n\t\t\tFROM\t\twcf" . WCF_N . "_page_menu_item menu_item\n\t\t\tLEFT JOIN\twcf" . WCF_N . "_package_dependency package_dependency\n\t\t\tON\t\t(package_dependency.dependency = menu_item.packageID)\n\t\t\tWHERE \t\tpackage_dependency.packageID = ?\n\t\t\tORDER BY\tpackage_dependency.priority ASC";
     $statement = WCF::getDB()->prepareStatement($sql);
     $statement->execute(array($packageID));
     $itemIDs = array();
     while ($row = $statement->fetchArray()) {
         $itemIDs[$row['menuItem']] = $row['menuItemID'];
     }
     if (count($itemIDs) > 0) {
         // get needed menu items and build item tree
         $conditions = new PreparedStatementConditionBuilder();
         $conditions->add("menu_item.menuItemID IN (?)", array($itemIDs));
         $conditions->add("menu_item.isDisabled = ?", array(0));
         $sql = "SELECT\t\tmenuItemID, menuItem, parentMenuItem, menuItemLink,\n\t\t\t\t\t\tpermissions, options, packageDir, menuPosition, className,\n\t\t\t\t\t\tCASE WHEN parentPackageID <> 0 THEN parentPackageID ELSE menu_item.packageID END AS packageID\n\t\t\t\tFROM\t\twcf" . WCF_N . "_page_menu_item menu_item\n\t\t\t\tLEFT JOIN\twcf" . WCF_N . "_package package\n\t\t\t\tON\t\t(package.packageID = menu_item.packageID)\n\t\t\t\t" . $conditions . "\n\t\t\t\tORDER BY\tshowOrder ASC";
         $statement = WCF::getDB()->prepareStatement($sql);
         $statement->execute($conditions->getParameters());
         while ($row = $statement->fetchArray()) {
             $data[$row['parentMenuItem'] ? $row['parentMenuItem'] : $row['menuPosition']][] = new PageMenuItem(null, $row);
         }
     }
     return $data;
 }

 /**
  * @see	\wcf\system\worker\IWorker::execute()
  */
 public function execute()
 {
     parent::execute();
     $users = $userIDs = array();
     foreach ($this->getObjectList() as $user) {
         $users[] = new UserEditor($user);
         $userIDs[] = $user->userID;
     }
     // update user ranks
     if (!empty($users)) {
         $action = new UserProfileAction($users, 'updateUserOnlineMarking');
         $action->executeAction();
     }
     if (!empty($userIDs)) {
         // update activity points
         UserActivityPointHandler::getInstance()->updateUsers($userIDs);
         // update like counter
         if (MODULE_LIKE) {
             $conditionBuilder = new PreparedStatementConditionBuilder();
             $conditionBuilder->add('user_table.userID IN (?)', array($userIDs));
             $sql = "UPDATE\twcf" . WCF_N . "_user user_table\n\t\t\t\t\tSET\tlikesReceived = (\n\t\t\t\t\t\t\tSELECT\tCOUNT(*)\n\t\t\t\t\t\t\tFROM\twcf" . WCF_N . "_like\n\t\t\t\t\t\t\tWHERE\tobjectUserID = user_table.userID\n\t\t\t\t\t\t\t\tAND likeValue = " . Like::LIKE . "\n\t\t\t\t\t\t)\n\t\t\t\t\t" . $conditionBuilder;
             $statement = WCF::getDB()->prepareStatement($sql);
             $statement->execute($conditionBuilder->getParameters());
         }
     }
 }

 /**
  * Updates the participants of this conversation.
  * 
  * @param	array<integer>	$participantIDs
  * @param	array<integer>	$invisibleParticipantIDs
  */
 public function updateParticipants(array $participantIDs, array $invisibleParticipantIDs = array())
 {
     $usernames = array();
     if (!empty($participantIDs) || !empty($invisibleParticipantIDs)) {
         $conditions = new PreparedStatementConditionBuilder();
         $conditions->add("userID IN (?)", array(array_merge($participantIDs, $invisibleParticipantIDs)));
         $sql = "SELECT\tuserID, username\n\t\t\t\tFROM\twcf" . WCF_N . "_user\n\t\t\t\t" . $conditions;
         $statement = WCF::getDB()->prepareStatement($sql);
         $statement->execute($conditions->getParameters());
         while ($row = $statement->fetchArray()) {
             $usernames[$row['userID']] = $row['username'];
         }
     }
     if (!empty($participantIDs)) {
         WCF::getDB()->beginTransaction();
         $sql = "INSERT INTO\t\twcf" . WCF_N . "_conversation_to_user\n\t\t\t\t\t\t\t(conversationID, participantID, username, isInvisible)\n\t\t\t\tVALUES\t\t\t(?, ?, ?, ?)\n\t\t\t\tON DUPLICATE KEY\n\t\t\t\tUPDATE\t\t\thideConversation = 0";
         $statement = WCF::getDB()->prepareStatement($sql);
         foreach ($participantIDs as $userID) {
             $statement->execute(array($this->conversationID, $userID, $usernames[$userID], 0));
         }
         WCF::getDB()->commitTransaction();
     }
     if (!empty($invisibleParticipantIDs)) {
         WCF::getDB()->beginTransaction();
         $sql = "INSERT INTO\t\twcf" . WCF_N . "_conversation_to_user\n\t\t\t\t\t\t\t(conversationID, participantID, username, isInvisible)\n\t\t\t\tVALUES\t\t\t(?, ?, ?, ?)";
         $statement = WCF::getDB()->prepareStatement($sql);
         foreach ($invisibleParticipantIDs as $userID) {
             $statement->execute(array($this->conversationID, $userID, $usernames[$userID], 1));
         }
         WCF::getDB()->commitTransaction();
     }
     $this->updateParticipantCount();
 }

	/**
	 * Compiles LESS stylesheets.
	 * 
	 * @param	wcf\data\style\Style	$style
	 */
	public function compile(Style $style) {
		// read stylesheets by dependency order
		$conditions = new PreparedStatementConditionBuilder();
		$conditions->add("file_log.filename REGEXP ?", array('style/([a-zA-Z0-9\_\-\.]+)\.less'));
		
		$sql = "SELECT		file_log.filename, package.packageDir
			FROM		wcf".WCF_N."_package_installation_file_log file_log
			LEFT JOIN	wcf".WCF_N."_package package
			ON		(file_log.packageID = package.packageID)
			".$conditions;
		$statement = WCF::getDB()->prepareStatement($sql);
		$statement->execute($conditions->getParameters());
		$files = array();
		while ($row = $statement->fetchArray()) {
			$files[] = WCF_DIR.$row['packageDir'].$row['filename'];
		}
		
		// get style variables
		$variables = $style->getVariables();
		$individualLess = '';
		if (isset($variables['individualLess'])) {
			$individualLess = $variables['individualLess'];
			unset($variables['individualLess']);
		}
		
		$this->compileStylesheet(
			WCF_DIR.'style/style-'.$style->styleID,
			$files,
			$variables,
			$individualLess,
			new Callback(function($content) use ($style) {
				return "/* stylesheet for '".$style->styleName."', generated on ".gmdate('r')." -- DO NOT EDIT */\n\n" . $content;
			})
		);
	}

 /**
  * Reads associated tags.
  */
 protected function getTags()
 {
     $this->tags = array();
     if (!empty($this->objectTypeIDs)) {
         // get tag ids
         $tagIDs = array();
         $conditionBuilder = new PreparedStatementConditionBuilder();
         $conditionBuilder->add('object.objectTypeID IN (?)', array($this->objectTypeIDs));
         $conditionBuilder->add('object.languageID IN (?)', array($this->languageIDs));
         $sql = "SELECT\t\tCOUNT(*) AS counter, object.tagID\n\t\t\t\tFROM\t\twcf" . WCF_N . "_tag_to_object object\n\t\t\t\t" . $conditionBuilder . "\n\t\t\t\tGROUP BY\tobject.tagID\n\t\t\t\tORDER BY\tcounter DESC";
         $statement = WCF::getDB()->prepareStatement($sql, 500);
         $statement->execute($conditionBuilder->getParameters());
         while ($row = $statement->fetchArray()) {
             $tagIDs[$row['tagID']] = $row['counter'];
         }
         // get tags
         if (!empty($tagIDs)) {
             $sql = "SELECT\t*\n\t\t\t\t\tFROM\twcf" . WCF_N . "_tag\n\t\t\t\t\tWHERE\ttagID IN (?" . (count($tagIDs) > 1 ? str_repeat(',?', count($tagIDs) - 1) : '') . ")";
             $statement = WCF::getDB()->prepareStatement($sql);
             $statement->execute(array_keys($tagIDs));
             while ($row = $statement->fetchArray()) {
                 $row['counter'] = $tagIDs[$row['tagID']];
                 $this->tags[$row['name']] = new TagCloudTag(new Tag(null, $row));
             }
             // sort by counter
             uasort($this->tags, array('self', 'compareTags'));
         }
     }
 }

	/**
	 * @see	wcf\system\option\ISearchableUserOption::getCondition()
	 */
	public function getCondition(PreparedStatementConditionBuilder &$conditions, Option $option, $value) {
		$value = StringUtil::trim($value);
		if (!$value) return false;
		
		$conditions->add("option_value.userOption".$option->optionID." = ?", array($value));
		return true;
	}

    protected function initUnreadArticle()
    {
        //Get application
        $classParts = explode('\\', get_called_class());
        if (WCF::getUser()->userID) {
            $conditionBuilder = new PreparedStatementConditionBuilder();
            $conditionBuilder->add(self::$articleType . '.lastChangeTime > ?', array(VisitTracker::getInstance()->getVisitTime(self::OBJECT_TYPE)));
            $conditionBuilder->add(self::$articleType . '.isDeleted = 0');
            $conditionBuilder->add(self::$articleType . '.isDisabled = 0');
            $conditionBuilder->add('tracked_visit.visitTime IS NULL');
            // apply language filter
            if (LanguageFactory::getInstance()->multilingualismEnabled() && count(WCF::getUser()->getLanguageIDs())) {
                $conditionBuilder->add('(' . self::$articleType . '.languageID IN (?) OR ' . self::$articleType . '.languageID IS NULL)', array(WCF::getUser()->getLanguageIDs()));
            }
            $sql = 'SELECT		COUNT(*) AS count, ' . self::$articleType . '_to_category.categoryID
				FROM		' . $classParts[0] . WCF_N . '_' . self::$articleType . ' ' . self::$articleType . '
				LEFT JOIN	wcf' . WCF_N . '_tracked_visit tracked_visit
				ON		(tracked_visit.objectTypeID = ' . VisitTracker::getInstance()->getObjectTypeID(self::OBJECT_TYPE) . ' AND tracked_visit.objectID = ' . self::$articleType . '.' . self::$articleType . 'ID AND tracked_visit.userID = ' . WCF::getUser()->userID . ')
				LEFT JOIN	' . $classParts[0] . WCF_N . '_' . self::$articleType . '_to_category ' . self::$articleType . '_to_category
				ON		(' . self::$articleType . '_to_category.' . self::$articleType . 'ID = ' . self::$articleType . '.' . self::$articleType . 'ID)
				' . $conditionBuilder . '
				GROUP BY	' . self::$articleType . '_to_category.categoryID';
            $statement = WCF::getDB()->prepareStatement($sql);
            $statement->execute($conditionBuilder->getParameters());
            while ($row = $statement->fetchArray()) {
                $this->unreadArticles[$row['categoryID']] = $row['count'];
            }
        }
    }

 /**
  * @see	\wcf\data\ISortableAction::validateUpdatePosition()
  */
 public function validateUpdatePosition()
 {
     if (!WCF::getSession()->getPermission('admin.project.canEditProject')) {
         throw new PermissionDeniedException();
     }
     if (!isset($this->parameters['data']['structure'])) {
         throw new UserInputException('structure');
     }
     $projectIDs = array();
     foreach ($this->parameters['data']['structure'][0] as $projectID) {
         if (!$projectID) {
             throw new UserInputException('structure');
         }
         $projectIDs[] = $projectID;
     }
     $projectIDs = array_unique($projectIDs);
     $conditions = new PreparedStatementConditionBuilder();
     $conditions->add("projectID IN (?)", array($projectIDs));
     $sql = "SELECT\tprojectID\n\t\t\t\tFROM\tict" . WCF_N . "_project\n\t\t\t\t" . $conditions;
     $statement = WCF::getDB()->prepareStatement($sql);
     $statement->execute($conditions->getParameters());
     while ($row = $statement->fetchArray()) {
         $key = array_search($row['projectID'], $projectIDs);
         if ($key !== false) {
             unset($projectIDs[$key]);
         }
     }
     if (!empty($projectIDs)) {
         throw new UserInputException('structure');
     }
 }

 /**
  * @see	\wcf\system\setup\IFileHandler::logFiles()
  */
 public function logFiles(array $files)
 {
     // remove file extension
     foreach ($files as &$file) {
         $file = substr($file, 0, -4);
     }
     unset($file);
     // fetch already installed acp templates
     $conditions = new PreparedStatementConditionBuilder();
     $conditions->add('packageID = ?', array($this->packageInstallation->getPackageID()));
     $conditions->add('templateName IN (?)', array($files));
     $conditions->add('application = ?', array($this->application));
     $sql = "SELECT\ttemplateName\n\t\t\tFROM\twcf" . WCF_N . "_" . $this->tableName . "\n\t\t\t" . $conditions;
     $statement = WCF::getDB()->prepareStatement($sql);
     $statement->execute($conditions->getParameters());
     while ($templateName = $statement->fetchColumn()) {
         $index = array_search($templateName, $files);
         if ($index !== false) {
             unset($files[$index]);
         }
     }
     if (!empty($files)) {
         $sql = "INSERT INTO\twcf" . WCF_N . "_" . $this->tableName . "\n\t\t\t\t\t\t(packageID, templateName, application)\n\t\t\t\tVALUES\t\t(?, ?, ?)";
         $statement = WCF::getDB()->prepareStatement($sql);
         foreach ($files as $file) {
             $statement->execute(array($this->packageInstallation->getPackageID(), $file, $this->application));
         }
     }
 }

 /**
  * Loads storage for a given set of users.
  * 
  * @param	array<integer>	$userIDs
  * @param	integer		$packageID
  */
 public function loadStorage(array $userIDs, $packageID = PACKAGE_ID)
 {
     $tmp = array();
     foreach ($userIDs as $userID) {
         if (!isset($this->cache[$userID])) {
             $tmp[] = $userID;
         }
     }
     // ignore users whose storage data is already loaded
     if (empty($tmp)) {
         return;
     }
     $conditions = new PreparedStatementConditionBuilder();
     $conditions->add("userID IN (?)", array($tmp));
     $conditions->add("packageID IN (?)", array(PackageDependencyHandler::getInstance()->getDependencies($packageID)));
     $sql = "SELECT\t*\n\t\t\tFROM\twcf" . WCF_N . "_user_storage\n\t\t\t" . $conditions;
     $statement = WCF::getDB()->prepareStatement($sql);
     $statement->execute($conditions->getParameters());
     while ($row = $statement->fetchArray()) {
         if (!isset($this->cache[$row['userID']])) {
             $this->cache[$row['userID']] = array();
         }
         $this->cache[$row['userID']][$row['field']] = $row['fieldValue'];
     }
 }

 /**
  * @see	\wcf\system\like\IViewableLikeProvider::prepare()
  */
 public function prepare(array $likes)
 {
     $responseIDs = array();
     foreach ($likes as $like) {
         $responseIDs[] = $like->objectID;
     }
     // get objects type ids
     $responses = array();
     $conditionBuilder = new PreparedStatementConditionBuilder();
     $conditionBuilder->add('comment_response.responseID IN (?)', array($responseIDs));
     $sql = "SELECT\t\tcomment.objectTypeID, comment_response.responseID\n\t\t\tFROM\t\twcf" . WCF_N . "_comment_response comment_response\n\t\t\tLEFT JOIN\twcf" . WCF_N . "_comment comment\n\t\t\tON\t\t(comment.commentID = comment_response.commentID)\n\t\t\t" . $conditionBuilder;
     $statement = WCF::getDB()->prepareStatement($sql);
     $statement->execute($conditionBuilder->getParameters());
     while ($row = $statement->fetchArray()) {
         $responses[$row['responseID']] = $row['objectTypeID'];
     }
     // group likes by object type id
     $likeData = array();
     foreach ($likes as $like) {
         if (isset($responses[$like->objectID])) {
             if (!isset($likeData[$responses[$like->objectID]])) {
                 $likeData[$responses[$like->objectID]] = array();
             }
             $likeData[$responses[$like->objectID]][] = $like;
         }
     }
     foreach ($likeData as $objectTypeID => $likes) {
         $objectType = CommentHandler::getInstance()->getObjectType($objectTypeID);
         if (CommentHandler::getInstance()->getCommentManager($objectType->objectType) instanceof IViewableLikeProvider) {
             CommentHandler::getInstance()->getCommentManager($objectType->objectType)->prepare($likes);
         }
     }
 }

 protected function getTopOptionCategories($packageID)
 {
     // get all option categories and filter categories with low priority
     $sql = "SELECT\t\tcategoryName, categoryID \n\t\t\tFROM\t\twcf" . WCF_N . "_option_category option_category\n\t\t\tLEFT JOIN\twcf" . WCF_N . "_package_dependency package_dependency\n\t\t\tON\t\t(package_dependency.dependency = option_category.packageID)\n\t\t\tWHERE \t\tpackage_dependency.packageID = ?\n\t\t\tORDER BY\tpackage_dependency.priority ASC";
     $statement = WCF::getDB()->prepareStatement($sql);
     $statement->execute(array($packageID));
     $optionCategories = array();
     while ($row = $statement->fetchArray()) {
         $optionCategories[$row['categoryName']] = $row['categoryID'];
     }
     $conditions = new PreparedStatementConditionBuilder();
     $conditions->add("categoryID IN (?)", array($optionCategories));
     $statementParameters = $conditions->getParameters();
     array_unshift($statementParameters, $packageID);
     $sql = "SELECT \t\tcategoryID, parentCategoryName, categoryName,\n\t\t\t\t\t(\n\t\t\t\t\t\tSELECT COUNT(*) FROM wcf" . WCF_N . "_option WHERE categoryName = category.categoryName AND packageID IN (\n\t\t\t\t\t\t\tSELECT dependency FROM wcf" . WCF_N . "_package_dependency WHERE packageID = ?\n\t\t\t\t\t\t)\n\t\t\t\t\t) AS count\n\t\t\tFROM\t\twcf" . WCF_N . "_option_category category\n\t\t\t" . $conditions;
     $statement = WCF::getDB()->prepareStatement($sql);
     $statement->execute($statementParameters);
     while ($row = $statement->fetchArray()) {
         if (!isset($this->optionCategoryStructure[$row['parentCategoryName']])) {
             $this->optionCategoryStructure[$row['parentCategoryName']] = array();
         }
         $this->optionCategoryStructure[$row['parentCategoryName']][] = $row;
     }
     $topOptionCategories = array();
     foreach ($this->optionCategoryStructure[''] as $optionCategory) {
         $count = $optionCategory['count'] + $this->countOptions($optionCategory['categoryName']);
         if ($count > 0) {
             $topOptionCategories[] = $optionCategory['categoryID'];
         }
     }
     return $topOptionCategories;
 }