USE [AIADatabaseV5]
GO
/****** Object:  StoredProcedure [dbo].[DeleteLicense]    Script Date: 02/06/2018 10:49:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Magic Software
-- Create date: 24-Sep-2009
-- Description:	to delete the license
-- =============================================
CREATE PROCEDURE [dbo].[DeleteLicense] 
	@iLicenseId int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	BEGIN TRY
		BEGIN TRANSACTION
			DECLARE @iLicenseTypeId TINYINT
			DECLARE @cGetDetail CURSOR
			DECLARE @iSiteId INT
			DECLARE @iUserId INT
			DECLARE @sErrorStatus CHAR(2)
			
			SET @sErrorStatus = 'ok'
			SET @iLicenseTypeId = (SELECT LicenseTypeId FROM License WHERE Id = @iLicenseId)
			
			-- check if license is site license
			IF @iLicenseTypeId = 3 
			BEGIN
				-- delete records from tables which store information about building level account
				SET @cGetDetail = CURSOR FAST_FORWARD FOR SELECT SiteId FROM SiteToLicenseEdition WHERE LicenseEditionId IN (SELECT Id FROM LicenseToEdition WHERE LicenseId = @iLicenseId)
				OPEN @cGetDetail
				FETCH NEXT FROM @cGetDetail INTO @iSiteId
				WHILE @@FETCH_STATUS = 0
				BEGIN
					DELETE FROM AIAUserToSite WHERE SiteId = @iSiteId
					DELETE FROM SiteToLicenseEdition WHERE SiteId = @iSiteId
					DELETE FROM Site WHERE Id = @iSiteId
					
					FETCH NEXT FROM @cGetDetail INTO @iSiteId
				END
				CLOSE @cGetDetail
			END
			-- delete records from tables which store information about user
			SET @cGetDetail = CURSOR FAST_FORWARD FOR SELECT Userid FROM AIAUserToLicenseEdition WHERE LicenseEditionId IN (SELECT Id FROM LicenseToEdition WHERE LicenseId = @iLicenseId)
			OPEN @cGetDetail
			FETCH NEXT FROM @cGetDetail INTO @iUserId
			WHILE @@FETCH_STATUS = 0
			BEGIN
				DELETE FROM AIAUserToLicenseEdition WHERE Userid = @iUserId
				DELETE FROM LoginDetail WHERE UserId = @iUserId
				DELETE FROM SessionManager WHERE UserId = @iUserId
				DELETE FROM IncorrectLoginAttempts WHERE UserId = @iUserId
				DELETE FROM UserGroupToAIAUser WHERE UserId = @iUserId
				DELETE FROM AIAUser WHERE Id = @iUserId
								
				FETCH NEXT FROM @cGetDetail INTO @iUserId
			END
			CLOSE @cGetDetail
			-- delete records from tables which store information about the license
			DELETE FROM UserGroup WHERE LicenseId = @iLicenseId
			DELETE FROM LicenseToEdition WHERE LicenseId = @iLicenseId
			DELETE FROM SingleUserDetail WHERE LicenseId = @iLicenseId
			DELETE FROM LicenseSubscriptionDetail WHERE LicenseId = @iLicenseId
			DELETE FROM License WHERE Id = @iLicenseId
			
		COMMIT TRANSACTION
		SELECT @sErrorStatus as SPStatus
	END TRY
	BEGIN CATCH
		IF @@TRANCOUNT > 0
			ROLLBACK TRANSACTION
		SELECT Error_Message() as SPStatus
	END CATCH
	
END
GO