--USE commserv
SET NOCOUNT ON

DECLARE @ErrorTag NVARCHAR(20) 
SET			@ErrorTag = '###ERROR###'

DECLARE @WarningTag NVARCHAR(20) 
SET			@WarningTag = '###WARNING###'

DECLARE @AdviceTag NVARCHAR(20) 
SET			@AdviceTag = '###ADVICE###'

DECLARE @HeaderTag NVARCHAR(20) 
SET			@HeaderTag = '###HEADER###'

DECLARE @DataTag NVARCHAR(20) 
SET			@DataTag = '###DATA###'

DECLARE @DescTag NVARCHAR(20) 
SET	@DescTag = '###DESCRIPTION###'

PRINT @DescTag + ' Check for Outlook Addin Client Group with no clients '

DECLARE @addinCGId INT
SET @addinCGId = ISNULL(( SELECT id FROM APP_ClientGroup WHERE flag & 0x0400 = 0x0400 ),0)

DECLARE @addinClientCount INT
SET @addinClientCount = 0

IF (@addinCGId <> 0)
BEGIN
	SET @addinClientCount = ISNULL((SELECT COUNT(*) FROM APP_ClientGroupAssoc WHERE clientGroupId = @addinCGId),0)
END


DECLARE @output nvarchar(max)
SET @output = ''
SET @output = @ErrorTag + ' There is an Outlook Addin Client group with no clients.' + char(13) 
			+ @AdviceTag + ' Before the upgrade, you must delete the following client group' + char(13)
			+ @HeaderTag + ' Client Group Name' + char(13) 
			+ '----------------------' + char(13)



SELECT @output = @output + @DataTag +  ' ' + ISNULL((select name from APP_ClientGroup where id = @addinCGId), '') + char(13)
	

IF @addinCGId = 0 AND @addinClientCount = 0 
BEGIN 
	PRINT 'There are no Outlook Addin Client Groups'
END 
ELSE IF @addinCGId > 0 AND @addinClientCount > 0
BEGIN
	PRINT 'There is a Outlook Addin Client Group with valid clients. You can proceed with the upgrade'
END
ELSE
BEGIN
	PRINT @output
END

CX_EXIT: 

IF  @addinCGId > 0 AND @addinClientCount = 0
	SELECT 2
ELSE
	SELECT 0

	
RETURN