USE [UMBPRM]

DECLARE @Num INT
DECLARE @CountSQL NVARCHAR(150)
DECLARE @CountOUTSQL NVARCHAR(150)
DECLARE @Where NVARCHAR(250)
DECLARE @Object NVARCHAR(150)
DECLARE @DelObject NVARCHAR(150)
DECLARE @SQL NVARCHAR(800)
DECLARE db_cursor CURSOR FOR
SELECT Name
FROM SALESFORCE...sys_sfobjects
WHERE LTRIM(RTRIM(Deletable)) = 'true' AND LTRIM(RTRIM(Queryable)) = 'true' ORDER BY Name DESC

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @Object

WHILE @@FETCH_STATUS = 0
BEGIN
		PRINT 'Processing ' + @Object

		SET @DelObject = @Object + '_Delete'

		-----------------------------------------------------
		-- DROP OLD TABLES
		-----------------------------------------------------
		SET @SQL = 'IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ''dbo'' AND  TABLE_NAME = ''' + @DelObject + '''))
					BEGIN
						DROP TABLE ' + @DelObject + '
					END'

		PRINT 'Dropping old table'
		EXEC (@SQL)

		-----------------------------------------------------
		-- GENERATE LOAD TABLE FOR BULK INSERT & DELETE
		-----------------------------------------------------
		SET @SQL = 'EXEC sf_generate ''Delete'', ''SALESFORCE'', ''' + @DelObject + ''''

		PRINT 'Generating load table'
		EXEC (@SQL)

		-- OBJECTS IGNORED AND WHY
		-- ContentDocumentLink: Implementation restriction: ContentDocumentLink requires a filter by a single Id, ContentDocumentId or LinkedEntityId using the equals operator
		-- Vote: Implementation restriction: When querying the Vote object, you must filter using the following syntax: ParentId = [single ID], Parent.Type = [single Type], Id = [single ID], or Id IN [list of ID's].

		-- CHECK IF ID COLUMN EXISTS
		IF (COL_LENGTH(@DelObject,'Id') IS NOT NULL) AND (@Object NOT IN ('ContentDocumentLink', 'Vote'))
		BEGIN

			-----------------------------------------------------
			-- INSERT ALL DATA FROM SF INTO LOCAL DEL TABLE
			-----------------------------------------------------
			IF (@Object = 'Case')
			BEGIN
				SET @Object = '[' + @Object + ']'
			END

			IF (@Object = 'Group')
			BEGIN
				SET @Object = '[' + @Object + ']'
			END

			SET @SQL = 'INSERT INTO ' + @DelObject + ' (Id)
							SELECT Id FROM SALESFORCE...' + @Object

			PRINT 'Populating load table'
			EXEC (@SQL)

			IF (@Object = '[Case]')
			BEGIN
				SET @Object = REPLACE(REPLACE(@Object, '[', ''), ']', '')
			END

			IF (@Object = '[Group]')
			BEGIN
				SET @Object = REPLACE(REPLACE(@Object, '[', ''), ']', '')
			END

			SET @CountSQL = N'SELECT @NumOUT = COUNT(*) FROM ' + @DelObject
			SET @CountOUTSQL = N'@NumOUT INT OUTPUT'

			EXEC sp_executesql
				@CountSQL,
				@CountOUTSQL,
				@NumOUT=@Num OUTPUT

			IF (@Num IS NOT NULL) AND (@Num > 0)
			BEGIN
				-----------------------------------------------------
				-- EXECUTE DELETION
				-----------------------------------------------------
				IF (@Object = 'Account')
				BEGIN
					SET @SQL = 'DELETE FROM SALESFORCE...' + @Object + ' WHERE Name <> ''GBHEM Constituent'''
				END
				ELSE
				BEGIN
					SET @SQL = 'EXEC SF_BulkOps ''HardDelete:bulkapi'', ''SALESFORCE'', ''' + @DelObject + ''''
				END

				PRINT 'Deleting records : ' + @SQL
				EXEC (@SQL)

				-----------------------------------------------------
				-- VERIFY
				-----------------------------------------------------
				SET @SQL = 'IF(NOT EXISTS (SELECT * FROM ' + @DelObject + ' WHERE LTRIM(RTRIM(Error)) <> ''Operation Successful.''))
							BEGIN
								PRINT ''ERROR: ' + @DelObject + '''
							END'

				PRINT 'Verifying deletion'
				EXEC (@SQL)
			END
			ELSE
			BEGIN
				PRINT 'No records to delete'
			END
		END

		FETCH NEXT FROM db_cursor INTO @Object
END

CLOSE db_cursor
DEALLOCATE db_cursor

INSERT INTO SALESFORCE...Global_App_Settings__c
	(Developers_Chatter_Group_Default_Member__c, Developers_Chatter_Group_Name__c) VALUES
	('rassad.sfadmin@gbhem.org.qa', 'Developers')