USE [AIADatabaseV5]
GO
/****** Object:  StoredProcedure [dbo].[InsertNewLicenseAccount]    Script Date: 2/1/2018 12:15:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Magic Software
-- Create date: 22-Apr-2009
-- Description:	To create a new license account
-- =============================================

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

CREATE PROCEDURE [dbo].[InsertNewLicenseAccount] 
	-- Add the parameters for the stored procedure here
	@sAccountNumber varchar(50), @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), @iTotalLogins int, @sStartDate varchar(20), 
	@sEndDate 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 @iLicenseId INT
			DECLARE @iSiteId INT
			DECLARE @iLicenseEditionId INT
			DECLARE @iIsDistrictSiteAccount TINYINT
			DECLARE @iActive TINYINT
			DECLARE @iIsMasterIP TINYINT
			DECLARE @iModesty TINYINT
			DECLARE @dtStartDate DATETIME
			DECLARE @dtEndDate DATETIME
			DECLARE @sErrorStatus CHAR(2)
			DECLARE @dtCurrentDate DATETIME
			DECLARE @sitem VARCHAR(100)
			DECLARE @sRecordDelimiter CHAR(1)
			DECLARE @sEditionLoginDelimiter CHAR(1)
			DECLARE @sCountryCode VARCHAR(10)
			DECLARE @iIsInsEditionSelected TINYINT
			DECLARE @iIsLibEditionSelected TINYINT
			DECLARE @iIsAcademicLibEditionSelected TINYINT

			-- set the parameters to default values
			SET @iActive = 1
			SET @iIsMasterIP = 1
			SET @iIsDistrictSiteAccount = 0
			SET @iModesty = 0
			SET @sRecordDelimiter = '|'
			SET @sEditionLoginDelimiter = '-'
			SET @dtCurrentDate = getdate()
			SET @sErrorStatus = 'ok'
			SET @iIsInsEditionSelected = 0;
			SET @iIsLibEditionSelected = 0;
			SET @iIsAcademicLibEditionSelected = 0;
			
			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 @dtEndDate = DATEADD(ms,-3,DATEADD(DAY,1,CONVERT(DATETIME,@sEndDate)))
			
			INSERT INTO License(AccountNumber, LicenseeFirstName, LicenseeLastName, LicenseTypeId, AccountTypeId, 
			InstitutionName, EmailId, Address1, Address2, City, Zip, StateId, CountryId, Phone, TotalLogins, IsActive, 
			IsDistrictSiteLicense, CreationDate,ProductId) VALUES (@sAccountNumber, @sLicenseeFname, @sLicenseeLname, @iLicenseTypeId, 
			@iAccountTypeId, @sInstitutionName, @sEmailId, @sAddress1, @sAddress2, @sCity, @sZip, @iStateId, @iCountryId,
			@sPhone, @iTotalLogins, @iActive, @iIsDistrictSiteAccount, @dtCurrentDate,@sProductKey)
			-- to get the last inserted license id identity value in the current session
			SET @iLicenseId = SCOPE_IDENTITY()
			
			INSERT INTO LicenseSubscriptionDetail(LicenseId, SubscriptionValidFrom, SubscriptionValidThrough,
			TotalAmount, AmountPaid,NoofImages) VALUES(@iLicenseId, @dtStartDate, @dtEndDate, @iPrice, @iPrice,@iNoofImages)
			
			-- check if license is site license
			IF @iLicenseTypeId = 3
			BEGIN
				INSERT INTO Site (SiteIP, Title, InstituteName, Address1, Address2, City, Zip, Phone,
				StateId, CountryId, IsMaster, IsActive, CreationDate, SiteIPTo, SiteMasterIpTo)
				VALUES(@sMasterIP, @sMasterIP, @sInstitutionName, @sAddress1, @sAddress2, @sCity, @sZip, @sPhone,
				@iStateId, @iCountryId, @iIsMasterIP, @iActive, @dtCurrentDate,@sSiteIPTo, @sSiteMasterIPTo)
				-- to get the last inserted site id identity value in the current session
				SET @iSiteId = SCOPE_IDENTITY()
			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
				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
				
				-- chekc if selected edition is instructor or library edition
				IF SUBSTRING(@sitem,1,CHARINDEX(@sEditionLoginDelimiter,@sitem)-1) <= 4
				BEGIN
					SET @iIsInsEditionSelected = 1;
				END
				IF SUBSTRING(@sitem,1,CHARINDEX(@sEditionLoginDelimiter,@sitem)-1) >  4
				BEGIN
					SET @iIsLibEditionSelected = 1;
				END
				IF SUBSTRING(@sitem,1,CHARINDEX(@sEditionLoginDelimiter,@sitem)-1) =  9
				BEGIN
					SET @iIsAcademicLibEditionSelected = 1;
				END

				-- 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
				FETCH NEXT FROM @cEditionLogins INTO @sitem
			END
			
			IF @iIsInsEditionSelected = 1 AND @iIsLibEditionSelected = 1
			BEGIN
				-- insert All resource module of license
				INSERT INTO ModuleToLicense SELECT @iLicenseId as LicenseId,ResourceModule.Id as ModuleId, CASE when ResourceModule.id in (8,9,10) then 0 else 1 end as Status FROM ResourceModule WHERE ResourceModule.Id <> 13;
			END
			ELSE IF @iIsInsEditionSelected = 1 AND @iIsLibEditionSelected = 0
			BEGIN
				-- insert All resource module of license
				INSERT INTO ModuleToLicense SELECT @iLicenseId as LicenseId,ResourceModule.Id as ModuleId, CASE when ResourceModule.id > 7 then 0 else 1 end as Status FROM ResourceModule WHERE ResourceModule.Id <> 13;
			END
			ELSE IF @iIsInsEditionSelected = 0 AND @iIsLibEditionSelected = 1
			BEGIN
				-- insert All resource module of license
				INSERT INTO ModuleToLicense SELECT @iLicenseId as LicenseId,ResourceModule.Id as ModuleId, CASE when ResourceModule.id < 11 and ResourceModule.id <> 6  then 0 else 1 end as Status FROM ResourceModule WHERE ResourceModule.Id <> 13;
			END

			IF @iIsAcademicLibEditionSelected = 1
			BEGIN
				-- insert ADAM Image Resouce to license
				INSERT INTO ModuleToLicense SELECT @iLicenseId as LicenseId,ResourceModule.Id as ModuleId,  1 as Status FROM ResourceModule WHERE ResourceModule.Id = 13;
			END
			ELSE
			BEGIN
				-- insert ADAM Image Resouce to license
				INSERT INTO ModuleToLicense SELECT @iLicenseId as LicenseId,ResourceModule.Id as ModuleId,  0 as Status FROM ResourceModule WHERE ResourceModule.Id = 13;
			END
			
			
		COMMIT TRANSACTION
		SELECT @sErrorStatus as SPStatus
	END TRY
	BEGIN CATCH
		IF @@TRANCOUNT > 0
			ROLLBACK TRANSACTION
		SELECT Error_Message() as SPStatus
	END CATCH
    
END


GO