-- Name:
--          sp_IHhelppublication

-- Description:
--          HREPL publication properties

-- Security:
--          Internal
-- Requires Certificate signature for catalog access

-- Returns:
--          Result set of publication properties

-- Owner:
--          

CREATE PROCEDURE sys.sp_IHhelppublication
(
    @publication	sysname = '%',
    @found			int = 23456 OUTPUT,		-- a flag indicate returning row
    @publisher		sysname,
    @publisher_type	sysname
)
AS
BEGIN
    SET NOCOUNT ON

    /*
    ** Declarations.
    */

    DECLARE @pubid				int
    DECLARE @has_subscription	bit
    DECLARE @retcode			int
    DECLARE @no_row				bit
    DECLARE @publish_bit		int
    DECLARE @pubname sysname
    DECLARE @username sysname
    DECLARE @OPT_ENABLED_FOR_HET_SUB int

   	DECLARE @publication_ids TABLE
	(
		pubid	int
	)
	
    SELECT @publish_bit = 1
    SELECT @username = suser_sname()
    SELECT @OPT_ENABLED_FOR_HET_SUB = 0x4

	IF @publisher IS NULL
	BEGIN
		RAISERROR (14043, 16, -1, '@publisher', 'sp_IHhelppublication')
		RETURN (1)
	END

    /*
    ** Initializations.
    */
    IF @found = 23456
    BEGIN
        SELECT @no_row=0
    END
    ELSE
    BEGIN
        SELECT @no_row=1
    END

    /*
    ** Parameter Check:  @publication.
    ** Check to make sure that there are some publications
    ** to display.
    */

    IF @publication IS NULL
	BEGIN
		RAISERROR (14043, 16, -1, '@publication', 'sp_IHhelppublication')
		RETURN (1)
	END

    IF @publication <> '%'
	BEGIN
		EXECUTE @retcode = dbo.sp_validname @publication

		IF @retcode <> 0
			RETURN (1)
	END

	-- Get list of matching publication id's for
	-- this publisher/type combo
	INSERT INTO @publication_ids
	SELECT pubid
	FROM   sys.fn_IHgetpubid(@publication, @publisher, @publisher_type)
	
	IF NOT EXISTS
	(
		SELECT	*
		FROM	@publication_ids
	)
	BEGIN
	    SELECT @found = 0
	    RETURN (0)
	END
	ELSE
	BEGIN
	    SELECT @found = 1

	    IF @no_row <>0
	    BEGIN
	        RETURN(0)
	    END
	END

    /*
    ** Create a temp table of pubids identifying publications that the current user has access to
    */
    CREATE TABLE #accessiblepubs (pubid int)

    DECLARE hC  CURSOR LOCAL FAST_FORWARD FOR
	SELECT	sp.pubid,
			sp.name
	FROM	syspublications sp,
			@publication_ids pi
	WHERE	((sp.name = @publication) or  (@publication = N'%'))
	  AND	sp.pubid = pi.pubid

    OPEN hC
    FETCH hC INTO @pubid, @pubname
    WHILE (@@fetch_status <> -1)
    BEGIN
		EXEC @retcode = sys.sp_MSreplcheck_pull	@publication		= @pubname,
												@raise_fatal_error	= 0,
												@given_login		= @username,
												@publisher			= @publisher

        IF (@retcode = 0 AND @@error = 0)
        BEGIN
            INSERT INTO #accessiblepubs values(@pubid)
		END

        FETCH hC INTO @pubid, @pubname
    END
    CLOSE hC
    DEALLOCATE hC

    /*
    ** Join the table of accessible pubids to the publication entries retrieved from syspublications
    */
	SELECT	'pubid'						= sp.pubid,
			'name'						= sp.name,
			'restricted'				= 0,
			'status'					= sp.status,
			-- using 'task' is for backward compatibilty
			'task'						= convert(int, 1),
			'replication frequency'		= sp.repl_freq,
			'synchronization method'	= sp.sync_method,
			'description'				= sp.description,
			'immediate_sync'			= sp.immediate_sync,
			'enabled_for_internet'		= sp.enabled_for_internet,
			'allow_push'				= sp.allow_push,
			'allow_pull'				= sp.allow_pull,
			'allow_anonymous'			= sp.allow_anonymous,
			'independent_agent'			= sp.independent_agent,
			'immediate_sync_ready'		= sp.immediate_sync_ready,
			-- SyncTran
			'allow_sync_tran'			= sp.allow_sync_tran,
			'autogen_sync_procs'		= sp.autogen_sync_procs,
			'snapshot_jobid'			= sp.snapshot_jobid,
			'retention'					= sp.retention,
			'has subscription'			= CASE WHEN EXISTS
											(
												SELECT	*
												FROM	IHsubscriptions
												WHERE	article_id IN
												(
													SELECT	article_id
													FROM	IHarticles
													WHERE	publication_id = sp.pubid
												)
											)
											THEN 1 ELSE 0 END,
			'allow_queued_tran'			= sp.allow_queued_tran,
			-- Portable snapshot
			'snapshot_in_defaultfolder'	= sp.snapshot_in_defaultfolder,
			'alt_snapshot_folder'		= sp.alt_snapshot_folder,
			-- Pre/post-snapshot commands
			'pre_snapshot_script'		= sp.pre_snapshot_script,
			'post_snapshot_script'		= sp.post_snapshot_script,
			-- Snapshot compression
			'compress_snapshot'			= sp.compress_snapshot,
			-- Post 7.0 ftp support
			'ftp_address'				= sp.ftp_address,
			'ftp_port'					= sp.ftp_port,
			'ftp_subdirectory'			= sp.ftp_subdirectory,
			'ftp_login'					= sp.ftp_login,
			'allow_dts'					= sp.allow_dts,
			'allow_subscription_copy'	= sp.allow_subscription_copy,
			-- 7.5 Queued updates
			'centralized_conflicts'		= NULL,
			'conflict_retention'		= 14,
			'conflict_policy'			= NULL,
			'queue_type'				= NULL,
			'backward_comp_level'		= sp.backward_comp_level,
			'publish_to_AD'				= CASE
											WHEN sp.ad_guidname IS NULL
											THEN 0 ELSE 1 END,
			'allow_initialize_from_backup' = sp.allow_initialize_from_backup,
			'replicate_ddl' = sp.replicate_ddl,
			-- PeerToPeer only (for now, not allowed in hetero)
			'enabled_for_p2p' = 0,
			'publish_local_changes_only' = 0,
			-- Enabled for heterogeneous subscribers only
			'enabled_for_het_sub' = case when (sp.options & @OPT_ENABLED_FOR_HET_SUB) = @OPT_ENABLED_FOR_HET_SUB then 1 else 0 end


	FROM	syspublications sp,
			MSpublications msp,
			master.dbo.sysservers ss,
			#accessiblepubs acc
	WHERE	sp.pubid = msp.publication_id
	  AND	msp.publisher_id = ss.srvid
	  AND	UPPER(ss.srvname collate database_default) = UPPER(@publisher) collate database_default
	  AND	((sp.name = @publication) or  (@publication = N'%'))
	  AND	sp.pubid = acc.pubid
	ORDER BY sp.name

    RETURN (0)
END