ProjectProvider::getProjects

static function getProjects(array $options = []): EO_Workgroup_Collection
{
	$query = WorkgroupTable::query();
	$query->setSelect(
		[
			'ID',
			'NAME',
			'ACTIVE',
			'PROJECT',
			'CLOSED',
			'VISIBLE',
			'OPENED',
			'IMAGE_ID',
			'AVATAR_TYPE',
			'LANDING'
		]
	);

	if (isset($options['visible']) && is_bool(isset($options['visible'])))
	{
		$query->where('VISIBLE', $options['visible'] ? 'Y' : 'N');
	}

	if (isset($options['open']) && is_bool(isset($options['open'])))
	{
		$query->where('OPENED', $options['open'] ? 'Y' : 'N');
	}

	if (isset($options['closed']) && is_bool(isset($options['closed'])))
	{
		$query->where('CLOSED', $options['closed'] ? 'Y' : 'N');
	}

	if (isset($options['landing']) && is_bool(isset($options['landing'])))
	{
		$query->where('LANDING', $options['landing'] ? 'Y' : 'N');
	}

	if (isset($options['active']) && is_bool(isset($options['active'])))
	{
		$query->where('ACTIVE', $options['active'] ? 'Y' : 'N');
	}

	if (isset($options['project']) && is_bool(isset($options['project'])))
	{
		$query->where('PROJECT', $options['project'] ? 'Y' : 'N');
	}

	if (!empty($options['searchQuery']) && is_string($options['searchQuery']))
	{
		$query->whereMatch(
			'SEARCH_INDEX',
			FilterHelper::matchAgainstWildcard(
				Content::prepareStringToken($options['searchQuery']),
				'*',
				1
			)
		);
	}

	$currentUserId = (!empty($options['currentUserId']) && is_int($options['currentUserId'])
		? $options['currentUserId'] : $GLOBALS['USER']->getId());

	$query->registerRuntimeField(
		new Reference(
			'PROJECT_SITE',
			WorkgroupSiteTable::class,
			Join::on('this.ID', 'ref.GROUP_ID'),
			['join_type' => 'INNER']
		)
	);

	$siteId = !empty($options['siteId']) && is_string($options['siteId']) ? $options['siteId'] : SITE_ID;
	$query->where('PROJECT_SITE.SITE_ID', $siteId);

	if (
		(
			!isset($options['myProjectsOnly'])
			|| $options['myProjectsOnly'] === true
		)
		&& !CSocNetUser::isCurrentUserModuleAdmin()
	)
	{
		$query->registerRuntimeField(
			new Reference(
				'MY_PROJECT',
				UserToGroupTable::class,
				Join::on('this.ID', 'ref.GROUP_ID')
					->where('ref.USER_ID', $currentUserId)
					->where(
						'ref.ROLE',
						'<=',
						UserToGroupTable::ROLE_USER
					),
				['join_type' => 'INNER']
			)
		);
	}

	if (isset($options['viewed']) && is_bool(isset($options['viewed'])))
	{
		$query->registerRuntimeField(
			new Reference(
				'VIEWED_PROJECT',
				WorkgroupViewTable::class,
				Join::on('this.ID', 'ref.GROUP_ID')->where('ref.USER_ID', $currentUserId),
				['join_type' => 'INNER']
			)
		);
	}

	$extranetSiteId = Option::get('extranet', 'extranet_site');
	$extranetSiteId = (
		$extranetSiteId
		&& ModuleManager::isModuleInstalled('extranet') ? $extranetSiteId : false
	);
	if ($extranetSiteId)
	{
		$query->registerRuntimeField(
			new Reference(
				'EXTRANET_PROJECT',
				WorkgroupSiteTable::class,
				Join::on('this.ID', 'ref.GROUP_ID')->where('ref.SITE_ID', $extranetSiteId),
				['join_type' => 'LEFT']
			)
		);

		$query->registerRuntimeField(
			new ExpressionField(
				'IS_EXTRANET', 'IF(%s IS NOT NULL, 'Y', 'N')', ['EXTRANET_PROJECT.GROUP_ID']
			)
		);

		$query->addSelect('IS_EXTRANET');

		if (isset($options['extranet']) && is_bool($options['extranet']))
		{
			if ($options['extranet'])
			{
				$query->whereNotNull('EXTRANET_PROJECT.GROUP_ID');
			}
			else
			{
				$query->whereNull('EXTRANET_PROJECT.GROUP_ID');
			}
		}
	}

	$projectIds = [];
	$projectFilter = (
		isset($options['projectId'])
			? 'projectId'
			: (isset($options['!projectId']) ? '!projectId' : null)
	);

	if (isset($options[$projectFilter]))
	{
		if (is_array($options[$projectFilter]) && !empty($options[$projectFilter]))
		{
			foreach ($options[$projectFilter] as $id)
			{
				$projectIds[] = (int)$id;
			}

			$projectIds = array_unique($projectIds);

			if (!empty($projectIds))
			{
				if ($projectFilter === 'projectId')
				{
					$query->whereIn('ID', $projectIds);
				}
				else
				{
					$query->whereNotIn('ID', $projectIds);
				}
			}
		}
		else if (!is_array($options[$projectFilter]) && (int)$options[$projectFilter] > 0)
		{
			if ($projectFilter === 'projectId')
			{
				$query->where('ID', (int)$options[$projectFilter]);
			}
			else
			{
				$query->whereNot('ID', (int)$options[$projectFilter]);
			}
		}
	}

	if (
		$projectFilter === 'projectId'
		&& empty($options['order'])
		&& count($projectIds) > 1
	)
	{
		$query->registerRuntimeField(
			new ExpressionField(
				'ID_SEQUENCE', 'FIELD(%s, ' . implode(',', $projectIds) . ')', 'ID'
			)
		);

		$query->setOrder('ID_SEQUENCE');
	}
	elseif (!empty($options['order']) && is_array($options['order']))
	{
		$query->setOrder($options['order']);
	}
	else
	{
		$query->setOrder(['NAME' => 'asc']);
	}

	$isUserModuleAdmin = CSocNetUser::isUserModuleAdmin($currentUserId, $siteId);

	if (
		isset($options['features'])
		&& is_array($options['features'])
		&& !empty($options['features'])
	)
	{
		foreach (array_keys($options['features']) as $feature)
		{
			if (!self::isAllowedFeatures($feature))
			{
				return new EO_Workgroup_Collection();
			}

			$featureField = new Reference(
				"BF_{$feature}",
				FeatureTable::class,
				Join::on('this.ID', 'ref.ENTITY_ID')
					->where('ref.ENTITY_TYPE', FeatureTable::FEATURE_ENTITY_TYPE_GROUP)
					->where('ref.FEATURE', $feature)
					->where('ref.ACTIVE', 'N'),
				['join_type' => 'LEFT']
			);
			$query->registerRuntimeField($featureField);

			$query->whereNull("BF_{$feature}.ENTITY_ID");
		}

		if (!$isUserModuleAdmin)
		{
			$featuresPermissionsQuery = self::getFeaturesPermissionsQuery(
				$currentUserId,
				$options['features']
			);
			if ($featuresPermissionsQuery)
			{
				$query->whereIn('ID', $featuresPermissionsQuery);
			}
		}
	}

	if (isset($options['limit']) && is_int($options['limit']))
	{
		$query->setLimit($options['limit']);
	}
	elseif ($projectFilter !== 'projectId' || empty($projectIds))
	{
		$query->setLimit(100);
	}

	return $query->exec()->fetchCollection();
}