spAvailableContentTypes

CREATE procedure [dbo].[spAvailableContentTypes](
@ClientsID int,
@All bit
)
AS

DECLARE @Valid bit
set @valid = 1
--exec USP_INSTALLEDPRODUCTS_VALIDHASH @valid output
IF @Valid = 1
BEGIN
    EXEC dbo.USP_GET_KEY_ACCESS;
    IF @All = 1 --Used to populate the part gallery dropdown
    BEGIN

        SELECT
            ct.ID,
            ct.Name,
            ct.VersioningSupported,
            ct.[RealmTypeId],
            ct.[Description]
        FROM
            dbo.vwContentTypes ct
            JOIN dbo.ClientContentTypes cct
            ON ct.ID=cct.ContentTypesID            
        WHERE
            cct.ClientsID = @ClientsID
            AND ct.Static=0
            AND ct.Hidden=0
            AND (
                ct.Deprecated=0 --Valid part
                OR ( --Deprecated part, but the user already has 1 on the site, so we need to show it in the dropdown
                    ct.Deprecated=1
                    AND EXISTS(
                        SELECT TOP 1 (1)
                        FROM
                            dbo.SiteContent sc
                            JOIN dbo.ClientSites cs
                            ON sc.ClientSitesID = cs.ID
                        WHERE
                        cs.ClientsID = @ClientsID
                        AND sc.Deleted=0
                        AND sc.ContentTypesID=ct.ID
                    )
                )
            )
        ORDER BY
            ct.Name
    END
    ELSE
    --Don't show parts that can't be created
    --(ie. Can only be 1 User Networking Manager and there already is one)
    --Used to populate 'new part' form
    BEGIN
        SELECT
            ct.ID,
            ct.Name,
            ct.VersioningSupported,
            ct.[RealmTypeId],
            ct.[Description]
        FROM
            dbo.vwContentTypes ct
            JOIN dbo.ClientContentTypes cct
            ON ct.ID=cct.ContentTypesID            
        WHERE
            cct.ClientsID = @ClientsID 
            AND ct.Static=0
            AND ct.Hidden=0
            AND ct.Deprecated=0
            AND (
                ct.UniqueContent=0 --Parts that can have any number of instances
                OR ( --Parts that can only have 1 instance, but the user hasn't created it yet
                    ct.UniqueContent=1 
                    AND COALESCE (
                        (SELECT TOP 1
                            A.ID
                        FROM
                            dbo.SiteContent A
                            JOIN dbo.ClientSites
                            ON A.ClientSitesID=dbo.ClientSites.ID
                        WHERE
                            ClientSites.ClientsID=@ClientsID
                            AND A.Deleted=0
                            AND A.ContentTypesID=ct.ID
                        )
                        ,0
                    ) = 0
                )
            )
        ORDER BY
            ct.Name
    CLOSE symmetric key sym_BBInfinity
    END
END