CAllSocNetLogEvents::GetSQL

static function GetSQL($user_id, $arMyEntities, $transport, $visible, $table_alias = "L")
{
	global $DB;

	if (intval($user_id) <= 0)
		return false;

	if ((!defined("DisableSonetLogVisibleSubscr") || DisableSonetLogVisibleSubscr !== true) && $visible && $visible <> '')
	{
		$key_res = CSocNetGroup::GetFilterOperation($visible);
		$strField = $key_res["FIELD"];
		$strNegative = $key_res["NEGATIVE"];
		$strOperation = $key_res["OPERATION"];
		$visibleFilter = "AND (".($strNegative == "Y" ? " SLE.VISIBLE IS NULL OR NOT " : "")."(SLE.VISIBLE ".$strOperation." '".$DB->ForSql($strField)."'))";
		
		$transportFilter = "";				
	}
	else
	{
		$visibleFilter = "";			

		if ($transport && $transport <> '')
		{
			$key_res = CSocNetGroup::GetFilterOperation($transport);
			$strField = $key_res["FIELD"];
			$strNegative = $key_res["NEGATIVE"];
			$strOperation = $key_res["OPERATION"];
			$transportFilter = "AND (".($strNegative == "Y" ? " SLE.TRANSPORT IS NULL OR NOT " : "")."(SLE.TRANSPORT ".$strOperation." '".$DB->ForSql($strField)."'))";
		}
		else
			$transportFilter = "";
	}

	$strMyEntities = array();
	foreach($arMyEntities as $entity_type_tmp => $arMyEntity)
	{
		if (is_array($arMyEntity) && count($arMyEntity) > 0)
		{
			$strMyEntities[$entity_type_tmp] = $table_alias.".ENTITY_ID IN (".implode(",", $arMyEntity).")";
			$strNotMyEntities[$entity_type_tmp] = "(".$table_alias.".ENTITY_ID NOT IN (".implode(",", $arMyEntity).") AND ".$table_alias.".ENTITY_TYPE = '".$entity_type_tmp."')";
		}
	}

	$arCBFilterEntityType = array();
	$arSocNetAllowedSubscribeEntityTypesDesc = CSocNetAllowed::GetAllowedEntityTypesDesc();

	foreach($arSocNetAllowedSubscribeEntityTypesDesc as $entity_type_tmp => $arEntityTypeTmp)
	{
		if (
			array_key_exists("USE_CB_FILTER", $arEntityTypeTmp)
			&& $arEntityTypeTmp["USE_CB_FILTER"] == "Y"
		)
		{
			$arCBFilterEntityType[] = "'".$entity_type_tmp."'";
		}
	}

	if (is_array($arCBFilterEntityType) && count($arCBFilterEntityType) > 0)
	{
		$strCBFilterEntityType = $table_alias.".ENTITY_TYPE IN (".implode(",", $arCBFilterEntityType).") AND ";
		$strNotCBFilterEntityType = $table_alias.".ENTITY_TYPE NOT IN (".implode(",", $arCBFilterEntityType).") OR ";
	}
	else
	{
		$strCBFilterEntityType = "";
		$strNotCBFilterEntityType = "";
	}

	$strSQL = "
	EXISTS(
		SELECT ID 
		FROM b_sonet_log_events SLE 
		WHERE 
			SLE.USER_ID = ".$user_id." 
			AND SLE.ENTITY_TYPE = ".$table_alias.".ENTITY_TYPE
			AND SLE.ENTITY_CB = 'N'
			AND SLE.ENTITY_ID = ".$table_alias.".ENTITY_ID
			AND SLE.EVENT_ID = ".$table_alias.".EVENT_ID
			".$transportFilter."
			".$visibleFilter."
	)
	OR 
	(
		".$strCBFilterEntityType."
		EXISTS(
			SELECT ID 
			FROM b_sonet_log_events SLE 
			WHERE 
				SLE.USER_ID = ".$user_id." 
				AND SLE.ENTITY_CB = 'Y'
				AND SLE.ENTITY_ID = ".$table_alias.".USER_ID
				AND SLE.EVENT_ID = ".$table_alias.".EVENT_ID
				".$transportFilter."
				".$visibleFilter."
		)		
	)
	OR 
	(
		(
			NOT EXISTS(
				SELECT ID 
				FROM b_sonet_log_events SLE 
				WHERE 
					SLE.USER_ID = ".$user_id." 
					AND SLE.ENTITY_TYPE = ".$table_alias.".ENTITY_TYPE
					AND SLE.ENTITY_CB = 'N'
					AND SLE.ENTITY_ID = ".$table_alias.".ENTITY_ID
					AND SLE.EVENT_ID = ".$table_alias.".EVENT_ID
			)
			OR
			EXISTS(
				SELECT ID 
				FROM b_sonet_log_events SLE 
				WHERE 
					SLE.USER_ID = ".$user_id." 
					AND SLE.ENTITY_TYPE = ".$table_alias.".ENTITY_TYPE
					AND SLE.ENTITY_CB = 'N'
					AND SLE.ENTITY_ID = ".$table_alias.".ENTITY_ID
					AND SLE.EVENT_ID = ".$table_alias.".EVENT_ID
					AND ".($visibleFilter ? "SLE.VISIBLE = 'I'" : "SLE.TRANSPORT = 'I'")."
			)
		)
		AND 
		(
			".$strNotCBFilterEntityType."			
			NOT EXISTS(
				SELECT ID 
				FROM b_sonet_log_events SLE 
				WHERE 
					SLE.USER_ID = ".$user_id." 
					AND SLE.ENTITY_CB = 'Y'
					AND SLE.ENTITY_ID = ".$table_alias.".USER_ID
					AND SLE.EVENT_ID = ".$table_alias.".EVENT_ID
			)
			OR 
			EXISTS(
				SELECT ID 
				FROM b_sonet_log_events SLE 
				WHERE 
					SLE.USER_ID = ".$user_id." 
					AND SLE.ENTITY_CB = 'Y'
					AND SLE.ENTITY_ID = ".$table_alias.".USER_ID
					AND SLE.EVENT_ID = ".$table_alias.".EVENT_ID
					AND ".($visibleFilter ? "SLE.VISIBLE = 'I'" : "SLE.TRANSPORT = 'I'")."
			)
			
		)
		AND
		(
			EXISTS(
				SELECT ID 
				FROM b_sonet_log_events SLE 
				WHERE 
					SLE.USER_ID = ".$user_id." 
					AND SLE.ENTITY_TYPE = ".$table_alias.".ENTITY_TYPE
					AND SLE.ENTITY_CB = 'N'
					AND SLE.ENTITY_ID = ".$table_alias.".ENTITY_ID
					AND SLE.EVENT_ID = 'all'
					".$transportFilter."
					".$visibleFilter."
			)
			OR
			(
				".$strCBFilterEntityType."				
				EXISTS(
					SELECT ID 
					FROM b_sonet_log_events SLE 
					WHERE 
						SLE.USER_ID = ".$user_id." 
						AND SLE.ENTITY_CB = 'Y'
						AND SLE.ENTITY_ID = ".$table_alias.".USER_ID
						AND SLE.EVENT_ID = 'all'
						".$transportFilter."
						".$visibleFilter."
				)
			)
			OR
			(
				(
					NOT EXISTS(
						SELECT ID 
						FROM b_sonet_log_events SLE 
						WHERE 
							SLE.USER_ID = ".$user_id." 
							AND SLE.ENTITY_TYPE = ".$table_alias.".ENTITY_TYPE
							AND SLE.ENTITY_CB = 'N'
							AND SLE.ENTITY_ID = ".$table_alias.".ENTITY_ID
							AND SLE.EVENT_ID = 'all'
					)
					OR 
					EXISTS(
						SELECT ID 
						FROM b_sonet_log_events SLE 
						WHERE 
							SLE.USER_ID = ".$user_id." 
							AND SLE.ENTITY_TYPE = ".$table_alias.".ENTITY_TYPE
							AND SLE.ENTITY_CB = 'N'
							AND SLE.ENTITY_ID = ".$table_alias.".ENTITY_ID
							AND SLE.EVENT_ID = 'all'
							AND ".($visibleFilter ? "SLE.VISIBLE = 'I'" : "SLE.TRANSPORT = 'I'")."
					)
				)
				AND 
				(
					".$strNotCBFilterEntityType."					
					NOT EXISTS(
						SELECT ID 
						FROM b_sonet_log_events SLE 
						WHERE 
							SLE.USER_ID = ".$user_id." 
							AND SLE.ENTITY_CB = 'Y'
							AND SLE.ENTITY_ID = ".$table_alias.".USER_ID
							AND SLE.EVENT_ID = 'all'
					)
					OR
					EXISTS(
						SELECT ID 
						FROM b_sonet_log_events SLE 
						WHERE 
							SLE.USER_ID = ".$user_id." 
							AND SLE.ENTITY_CB = 'Y'
							AND SLE.ENTITY_ID = ".$table_alias.".USER_ID
							AND SLE.EVENT_ID = 'all'
							AND ".($visibleFilter ? "SLE.VISIBLE = 'I'" : "SLE.TRANSPORT = 'I'")."
					)
				)					
				AND
				(
				";

	if (count($strMyEntities) > 0)
	{
		foreach ($strMyEntities as $entity_type_tmp	=> $strMyEntity)
		{
			$strSQL .= ($strMyEntity <> '' ?	"
					(
						".$strMyEntity."
						AND
						(
							EXISTS(
								SELECT ID 
								FROM b_sonet_log_events SLE 
								WHERE 
									SLE.USER_ID = ".$user_id." 
									AND SLE.ENTITY_TYPE = '".$entity_type_tmp."'
									AND SLE.ENTITY_TYPE = ".$table_alias.".ENTITY_TYPE 
									AND SLE.ENTITY_ID = 0 
									AND SLE.ENTITY_MY = 'Y'
									AND SLE.EVENT_ID = ".$table_alias.".EVENT_ID
									".$transportFilter."
									".$visibleFilter."
							)
							OR
							(
								(
									EXISTS(
										SELECT ID 
										FROM b_sonet_log_events SLE 
										WHERE 
											SLE.USER_ID = ".$user_id." 
											AND SLE.ENTITY_TYPE = '".$entity_type_tmp."'
											AND SLE.ENTITY_TYPE = ".$table_alias.".ENTITY_TYPE 
											AND SLE.ENTITY_ID = 0 
											AND SLE.ENTITY_MY = 'Y'
											AND SLE.EVENT_ID = ".$table_alias.".EVENT_ID
											AND ".($visibleFilter ? "SLE.VISIBLE = 'I'" : "SLE.TRANSPORT = 'I'")."
									)
									OR
									NOT EXISTS(
										SELECT ID 
										FROM b_sonet_log_events SLE 
										WHERE 
											SLE.USER_ID = ".$user_id." 
											AND SLE.ENTITY_TYPE = '".$entity_type_tmp."'
											AND SLE.ENTITY_TYPE = ".$table_alias.".ENTITY_TYPE 
											AND SLE.ENTITY_ID = 0 
											AND SLE.ENTITY_MY = 'Y'
											AND SLE.EVENT_ID = ".$table_alias.".EVENT_ID
									)
								)
								AND
								(
									EXISTS(
										SELECT ID 
										FROM b_sonet_log_events SLE 
										WHERE 
											SLE.USER_ID = ".$user_id." 
											AND SLE.ENTITY_TYPE = '".$entity_type_tmp."'
											AND SLE.ENTITY_TYPE = ".$table_alias.".ENTITY_TYPE 
											AND SLE.ENTITY_ID = 0 
											AND SLE.ENTITY_MY = 'Y'
											AND SLE.EVENT_ID = 'all'
											".$transportFilter."
											".$visibleFilter."
									)
								)
							)
						)
					)
					OR
			" : "");
		}
	}

	$strSQL .=	"
					(
						EXISTS(
							SELECT ID 
							FROM b_sonet_log_events SLE 
							WHERE 
								SLE.USER_ID = ".$user_id."
								AND SLE.ENTITY_TYPE = ".$table_alias.".ENTITY_TYPE 
								AND SLE.ENTITY_ID = 0 
								AND SLE.ENTITY_MY = 'N'
								AND SLE.EVENT_ID = ".$table_alias.".EVENT_ID
								".$transportFilter."
								".$visibleFilter."
						)
						OR
						(
							(
								EXISTS(
									SELECT ID 
									FROM b_sonet_log_events SLE 
									WHERE 
										SLE.USER_ID = ".$user_id."
										AND SLE.ENTITY_TYPE = ".$table_alias.".ENTITY_TYPE 
										AND SLE.ENTITY_ID = 0 
										AND SLE.ENTITY_MY = 'N'
										AND SLE.EVENT_ID = ".$table_alias.".EVENT_ID
										AND ".($visibleFilter ? "SLE.VISIBLE = 'I'" : "SLE.TRANSPORT = 'I'")."
									)
								OR
								NOT EXISTS(
									SELECT ID 
									FROM b_sonet_log_events SLE 
									WHERE 
										SLE.USER_ID = ".$user_id."
										AND SLE.ENTITY_TYPE = ".$table_alias.".ENTITY_TYPE 
										AND SLE.ENTITY_ID = 0 
										AND SLE.ENTITY_MY = 'N'
										AND SLE.EVENT_ID = ".$table_alias.".EVENT_ID
								)
							)
							AND 
							(
								EXISTS(
									SELECT ID 
									FROM b_sonet_log_events SLE 
									WHERE 
										SLE.USER_ID = ".$user_id."
										AND SLE.ENTITY_TYPE = ".$table_alias.".ENTITY_TYPE 
										AND SLE.ENTITY_ID = 0 
										AND SLE.ENTITY_MY = 'N'
										AND SLE.EVENT_ID = 'all'
								".$transportFilter."
								".$visibleFilter."
								)
								OR
								EXISTS(
									SELECT ID 
									FROM b_sonet_log_events SLE 
									WHERE 
										SLE.USER_ID = ".$user_id."
										AND SLE.ENTITY_TYPE = ".$table_alias.".ENTITY_TYPE 
										AND SLE.ENTITY_ID = 0 
										AND SLE.ENTITY_MY = 'N'
										AND SLE.EVENT_ID = 'all'
										AND ".($visibleFilter ? "SLE.VISIBLE = 'I'" : "SLE.TRANSPORT = 'I'")."
								)
								OR
								NOT EXISTS(
									SELECT ID 
									FROM b_sonet_log_events SLE 
									WHERE 
										SLE.USER_ID = ".$user_id."
										AND SLE.ENTITY_TYPE = ".$table_alias.".ENTITY_TYPE 
										AND SLE.ENTITY_ID = 0 
										AND SLE.ENTITY_MY = 'N'
										AND SLE.EVENT_ID = 'all'
								)
							)
						)
					)
				)	
			)
		)
	)";

	return $strSQL;

}