SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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

-- ====================================================  
-- Author:  Magic Software  
-- Create date: 08-Feb-2018  
-- Description: To insert or update a site account for a license account and site  
-- ====================================================  
create PROCEDURE [dbo].[usp_InsertUpdateSiteAccount]
 -- Add the parameters for the stored procedure here  
	@iSiteId int, @sSiteIP varchar(2000), @sTitle varchar(100), @sInstituteName varchar(100), @sDepartment varchar(50),
    @sAddress1 varchar(100), @sAddress2 varchar(100), @sCity varchar(50), @Zip varchar(20), @Phone varchar(30),
    @StateId int, @CountryId int, @IsMaster bit, @CreationDate datetime, @ModifiedDate datetime,
    @IsActive bit, @UserId int, @sSiteIPTo varchar(100), @LicenseId int, @SiteEditionIds varchar(1000), 
	@Status bit out  
AS  
BEGIN  
 -- SET NOCOUNT ON added to prevent extra result sets from  
 -- interfering with SELECT statements.  
SET NOCOUNT ON;  

DECLARE @list varchar(1000)
DECLARE @pos INT, @tempEditionId int, @tempLicenseEditionId int;
DECLARE @len INT, @tempModesty bit;
DECLARE @value varchar(1000)

CREATE TABLE #LocalTempTable(
SiteId int,
LicenseEditionId int, 
IsModesty bit);

if(@SiteEditionIds != '') 
begin
	set @SiteEditionIds = @SiteEditionIds + ',';
end
SET @list = @SiteEditionIds

 set @Status = 0;  
 BEGIN TRY  
  BEGIN TRANSACTION  

			IF @iSiteId = 0 
			BEGIN
				INSERT INTO [dbo].[Site]([SiteIP],[Title],[InstituteName],[Department],[Address1],[Address2],
					[City],[Zip],[Phone],[StateId],[CountryId],[IsMaster],[CreationDate],[ModifiedDate],[IsActive],[SiteIPTo])
				VALUES(@sSiteIP, @sTitle, @sInstituteName, @sDepartment, @sAddress1, @sAddress2, @sCity, @Zip, @Phone,
					@StateId, @CountryId, @IsMaster, @CreationDate, @ModifiedDate, @IsActive, @sSiteIPTo);
				-- to get the last inserted identity value in the current session
				SET @iSiteId=SCOPE_IDENTITY();
				insert into AIAUserToSite values(@UserId, @iSiteId);
			END
			ELSE
			BEGIN
				UPDATE [dbo].[Site]	SET [SiteIP]=@sSiteIP, [Title]=@sTitle,[InstituteName]=@sInstituteName,
					[Department]=@sDepartment, [Address1]=@sAddress1, [Address2]=@sAddress2,[City]=@sCity, 
					[Zip]=@Zip, [Phone]=@Phone, [StateId]=@StateId, [CountryId]=@CountryId,
					[ModifiedDate]=@ModifiedDate, [IsActive]=@IsActive, [SiteIPTo]=@sSiteIPTo
				WHERE [Id]=@iSiteId
			END

			insert into #LocalTempTable 
			select SLE.* from SiteToLicenseEdition SLE inner join LicenseToEdition LE on SLE.LicenseEditionId = LE.Id where 
			SLE.SiteId = @iSiteId and LicenseId = @LicenseId;

			delete SLE from SiteToLicenseEdition SLE inner join LicenseToEdition LE on SLE.LicenseEditionId = LE.Id where 
			SLE.SiteId = @iSiteId and LicenseId = @LicenseId;

			set @pos = 0
			set @len = 0

			WHILE CHARINDEX(',', @list, @pos+1)>0
			BEGIN
				set @len = CHARINDEX(',', @list, @pos+1) - @pos;
				set @value = SUBSTRING(@list, @pos, @len);
				set @tempEditionId = convert(int, @value);
				select @tempLicenseEditionId = Id from LicenseToEdition where LicenseId = @LicenseId and EditionId = @tempEditionId;
				set @tempModesty = 0;
				if(exists(select * from #LocalTempTable where LicenseEditionId = @tempLicenseEditionId and SiteId = @iSiteId))
				begin
					select @tempModesty = IsModesty from #LocalTempTable where LicenseEditionId = @tempLicenseEditionId and SiteId = @iSiteId;
				end
				insert into SiteToLicenseEdition(SiteId, LicenseEditionId, IsModesty) values(@iSiteId, @tempLicenseEditionId, @tempModesty);
				set @pos = CHARINDEX(',', @list, @pos+@len) + 1;
			END

  COMMIT TRANSACTION  
  set @Status = 1;  
 END TRY  
 BEGIN CATCH  
  IF @@TRANCOUNT > 0  
   ROLLBACK TRANSACTION  
 END CATCH  
  
END  

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO