DictionaryManager::getInsertSelect

static function getInsertSelect($dictionaryId)
{
	$select = '';

	if ($dictionaryId == Dictionary::USER_DEPARTMENT)
	{
		$manager = Manager::getInstance();
		$connection = $manager->getDatabaseConnection();
		$structureIblockId = (int)$connection->queryScalar("
			select value
			from b_option
			where module_id = 'intranet' and name = 'iblock_structure'
		");

		if (
			$structureIblockId > 0
			&& $connection->isTableExists('b_utm_user')
			&& $connection->isTableExists('b_iblock_section')
		)
		{
			$select = '
				select
					' . Dictionary::USER_DEPARTMENT . " AS DICTIONARY_ID
					,U.ID AS VALUE_ID
					,D.DEPARTMENT_PATH AS VALUE_STR
				from
					b_user U
					inner join (
						select VALUE_ID as USER_ID, min(VALUE_INT) AS USER_DEPARTMENT_ID
						from b_utm_user
						where FIELD_ID = (select ID from b_user_field where ENTITY_ID='USER' and FIELD_NAME='UF_DEPARTMENT')
						group by VALUE_ID
					) UD on UD.USER_ID = U.ID
					inner join (
						select
							c.id DEPARTMENT_ID
							,case
								when p.id is not null
								then concat(
									group_concat(concat('[',p.id,'] ',p.name) order by p.left_margin separator ' / '),
									' / [', c.id, '] ',
									c.name)
								else concat('[', c.id, '] ', c.name)
							end DEPARTMENT_PATH
						from
							b_iblock_section c
							left join b_iblock_section p
								on p.iblock_id = c.iblock_id
								and p.left_margin < c.left_margin
								and p.right_margin > c.right_margin
						where
							c.iblock_id = " . $structureIblockId . "
						group by
							c.id
					) D on D.DEPARTMENT_ID = UD.USER_DEPARTMENT_ID
				where
					U.EXTERNAL_AUTH_ID NOT IN ('" . implode("', '", BitrixMainUserTable::getExternalUserTypes()) . "') OR U.EXTERNAL_AUTH_ID IS NULL
				";
		}
	}

	return $select;
}