if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_UpdateLicenseAccount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_UpdateLicenseAccount]
GO

CREATE PROCEDURE [dbo].[usp_UpdateLicenseAccount] 
	-- Add the parameters for the stored procedure here
	@iLicenseId int, @sLicenseeFname varchar(50), @sLicenseeLname varchar(50),
	@iLicenseTypeId tinyint, @iAccountTypeId tinyint, @sInstitutionName varchar(100)='', @sAddress1 varchar(100)='', 
	@sAddress2 varchar(100)='', @sCity varchar(50)='', @sZip varchar(20)='', @iStateId int, @iCountryId int, 
	@sPhone varchar(30) = '', @sEmailId varchar(50), @iIsActive tinyint, @iTotalLogins int = 0, @iIsRennew tinyint,
	@sStartDate varchar(20), @sEndDate varchar(20), @sRenewDate varchar(20), @sMasterIP varchar(100) = '', 
	@sEditionList varchar(256), @iPrice numeric(14,2), @sProductKey	varchar(50), @sSiteIPTo varchar(100) = '', @sSiteMasterIPTo varchar(100) = '',
	@iNoofImages 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 @cEditionLogins CURSOR
			DECLARE @iSiteId INT
			DECLARE @iLicenseEditionId INT
			DECLARE @iModesty TINYINT
			DECLARE @dtStartDate DATETIME
			DECLARE @dtEndDate DATETIME
			DECLARE @dtRenewDate DATETIME
			DECLARE @sErrorStatus CHAR(2)
			DECLARE @dtCurrentDate DATETIME
			DECLARE @sitem VARCHAR(100)
			DECLARE @sRecordDelimiter CHAR(1)
			DECLARE @sEditionLoginDelimiter CHAR(1)
			DECLARE @sPaymentMode VARCHAR(10)
			DECLARE @iLicenseSubscriptionId INT
			DECLARE @iSubscriptionId SMALLINT
			DECLARE @dtCancellationDate DATETIME
			DECLARE @iEditionExists TINYINT
			DECLARE @sCountryCode VARCHAR(10)
			
			-- set the parameters to default values
			SET @iModesty = 0
			SET @sRecordDelimiter = '|'
			SET @sEditionLoginDelimiter = '-'
			SET @iSubscriptionId = NULL
			SET @dtCancellationDate = NULL
			SET @dtCurrentDate = getdate()
			SET @sPaymentMode = 'CASH'
			SET @sErrorStatus = 'ok'
			
			IF @iStateId = 0
			BEGIN
				SET @iStateId = (SELECT Id FROM State WHERE StateName='Other')
			END
			
			-- set the state to Other if the country is Non-US
			SET @sCountryCode = (SELECT CountryCode from Country WHERE Id = @iCountryId)
			IF @sCountryCode != 'US'
			BEGIN
				SET @iStateId = (SELECT Id FROM State WHERE StateName='Other')
			END
			
			-- convert the datatype of startdate & enddate parameter to datetime
			SELECT @dtStartDate = CONVERT(DATETIME,@sStartDate)
			SELECT @dtRenewDate = CONVERT(DATETIME,@sRenewDate)
			SELECT @dtEndDate = DATEADD(ms,-3,DATEADD(DAY,1,CONVERT(DATETIME,@sEndDate)))
			-- if user inactive the license then set the cancellation date to current date
			IF @iIsActive = 0 
			BEGIN
				SET @dtCancellationDate = 	@dtCurrentDate
			END
						
			UPDATE License SET LicenseeFirstName = @sLicenseeFname, LicenseeLastName = @sLicenseeLname,
			AccountTypeId = @iAccountTypeId, InstitutionName = @sInstitutionName, EmailId = @sEmailId,
			Address1 = @sAddress1, Address2 = @sAddress2, City = @sCity, Zip = @sZip, StateId = @iStateId,
			CountryId = @iCountryId, Phone = @sPhone, TotalLogins = @iTotalLogins, IsActive = @iIsActive,
			ModifiedDate = @dtCurrentDate, CancellationDate = @dtCancellationDate, ProductId = @sProductKey WHERE Id = @iLicenseId
			
			SET @iLicenseSubscriptionId = (SELECT MAX(Id) FROM LicenseSubscriptionDetail WHERE LicenseId = @iLicenseId)
			-- if the subscription of license is renew
			IF @iIsRennew = 1
			BEGIN
				-- check if license is single license
				IF @iLicenseTypeId = 2
				BEGIN
					SET @iSubscriptionId = (SELECT SubscriptionPlanId FROM LicenseSubscriptionDetail WHERE Id = @iLicenseSubscriptionId)
				END
				INSERT INTO LicenseSubscriptionDetail(LicenseId, SubscriptionPlanId, SubscriptionValidFrom, 
				SubscriptionValidThrough, RenewalDate, PaymentMode, TotalAmount, AmountPaid,NoofImages) 
				VALUES(@iLicenseId, @iSubscriptionId, @dtStartDate, @dtEndDate, @dtRenewDate, @sPaymentMode, @iPrice, @iPrice,@iNoofImages)
				UPDATE License SET NoOfRenewals = NoOfRenewals + 1 WHERE Id = @iLicenseId
			END
			ELSE
			BEGIN
				UPDATE LicenseSubscriptionDetail SET SubscriptionValidFrom = @dtStartDate, 
				SubscriptionValidThrough = @dtEndDate, TotalAmount = @iPrice, AmountPaid = @iPrice , NoofImages =@iNoofImages
				WHERE Id = @iLicenseSubscriptionId
			END
	
			-- check if license is site license
			IF @iLicenseTypeId = 3
			BEGIN
			
				SET @iSiteId = (SELECT DISTINCT Max(Site.Id) FROM LicenseToEdition 
				INNER JOIN SiteToLicenseEdition ON LicenseToEdition.Id = SiteToLicenseEdition.LicenseEditionId 
				INNER JOIN Site ON SiteToLicenseEdition.SiteId = Site.Id
				WHERE LicenseToEdition.LicenseId=@iLicenseId AND Site.IsMaster=1 AND Site.IsActive=1)
				
				UPDATE Site SET SiteIP = @sMasterIP, Title = @sMasterIP, ModifiedDate = @dtCurrentDate,
							SiteIPTo = @sSiteIPTo, SiteMasterIPTo = @sSiteMasterIPTo
				WHERE Id = @iSiteId
			END
			
			SET @cEditionLogins = CURSOR FAST_FORWARD FOR SELECT item FROM dbo.fnSplit(@sEditionList,@sRecordDelimiter)
			OPEN @cEditionLogins
			FETCH NEXT FROM @cEditionLogins INTO @sitem
			WHILE @@FETCH_STATUS = 0
			BEGIN
				SET @iEditionExists = (SELECT 1 FROM LicenseToEdition WHERE LicenseId = @iLicenseId AND EditionId = SUBSTRING(@sitem,1,CHARINDEX(@sEditionLoginDelimiter,@sitem)-1))
				
				IF @iEditionExists IS NULL OR @iEditionExists = 0
				BEGIN
					INSERT INTO LicenseToEdition(LicenseId, EditionId, TotalLogins, IsModesty) 
					SELECT @iLicenseId, SUBSTRING(@sitem,1,CHARINDEX(@sEditionLoginDelimiter,@sitem)-1), 
						SUBSTRING(@sitem,CHARINDEX(@sEditionLoginDelimiter,@sitem)+1,LEN(@sitem)), @iModesty
					-- check if license is site license
					IF @iLicenseTypeId = 3
					BEGIN
						-- to get the last inserted licenseedition id identity value in the current session
						SET @iLicenseEditionId = SCOPE_IDENTITY()
						INSERT INTO SiteToLicenseEdition (SiteId, LicenseEditionId, IsModesty) VALUES (@iSiteId, @iLicenseEditionId, @iModesty)		
					END
				END
				ELSE
				BEGIN
					UPDATE LicenseToEdition SET TotalLogins = SUBSTRING(@sitem,CHARINDEX(@sEditionLoginDelimiter,@sitem)+1,LEN(@sitem))
					WHERE LicenseId = @iLicenseId 
					AND EditionId = SUBSTRING(@sitem,1,CHARINDEX(@sEditionLoginDelimiter,@sitem)-1)
				END
				FETCH NEXT FROM @cEditionLogins INTO @sitem
			END
		
		COMMIT TRANSACTION
		SELECT @sErrorStatus as SPStatus
	END TRY
	BEGIN CATCH
		IF @@TRANCOUNT > 0
			ROLLBACK TRANSACTION
		SELECT Error_Message() as SPStatus
	END CATCH
    
END