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

-- ====================================================
-- Author:		Magic Software
-- Create date: 20-May-2009
-- Description:	To create new reseller license account
-- ====================================================

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

CREATE PROCEDURE [dbo].[InsertResellerLicenseAccount] 
	-- Add the parameters for the stored procedure here
	@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), @sEditionList varchar(256), 
	@iTotalPrice numeric(14,2), @iCreatorId	int, @sProductKey varchar(50),@iNoofImages int
AS
BEGIN
	IF 1=0 BEGIN
		SET FMTONLY OFF
	END
	-- 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 @sAccountNumber VARCHAR(50)
			DECLARE @iLicenseEditionId INT
			DECLARE @iIsDistrictSiteAccount TINYINT
			DECLARE @iActive 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 @iCount INT
			DECLARE @iLicenseLogins INT
			DECLARE @iPrice NUMERIC(14,2)
			DECLARE @sPaymentMode VARCHAR(10)
			DECLARE @iUserTypeId TINYINT
			DECLARE @sLoginId VARCHAR(50)
			DECLARE @iAIAUserId INT
			DECLARE @iLastAIAUserId INT
			DECLARE @iUserExists TINYINT
			DECLARE @sCountryCode VARCHAR(10)
			
			-- create temporary table to store newly created account number, loginid & password
			CREATE TABLE #LicenseDetail
			(
				AccountNumber VARCHAR(50),
				LoginId VARCHAR(50),
				Password VARCHAR(50)
			)
			
			-- set the parameters to default values
			SET @iCount = 1
			SET @iLicenseLogins = 1;
			SET @iActive = 1
			SET @iIsDistrictSiteAccount = 0
			SET @iModesty = 0
			SET @sPaymentMode = 'CASH'
			SET @sRecordDelimiter = '|'
			SET @sEditionLoginDelimiter = '-'
			SET @dtCurrentDate = getdate()
			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 @dtEndDate = DATEADD(ms,-3,DATEADD(DAY,1,CONVERT(DATETIME,@sEndDate)))
			
			-- fetch the id for the Reseller user type
			SELECT @iUserTypeId = Id FROM UserType WHERE Title = 'Reseller'
			
			-- calculate the price of an individual reseller license
			SET @iPrice = @iTotalPrice / @iTotalLogins
			
			-- get the edition id and total number of logins 
			SET @cEditionLogins = CURSOR FAST_FORWARD FOR SELECT item FROM dbo.fnSplit(@sEditionList,@sRecordDelimiter)
			OPEN @cEditionLogins
			FETCH NEXT FROM @cEditionLogins INTO @sitem
						
			WHILE(@iCount <= @iTotalLogins)
			BEGIN
				SET @iUserExists = 0
				-- create a new reseller license
				INSERT INTO License(LicenseeFirstName, LicenseeLastName, LicenseTypeId, AccountTypeId, 
				InstitutionName, EmailId, Address1, Address2, City, Zip, StateId, CountryId, Phone, TotalLogins, IsActive, 
				IsDistrictSiteLicense, CreationDate, ProductId) VALUES (@sLicenseeFname, @sLicenseeLname, @iLicenseTypeId, 
				@iAccountTypeId, @sInstitutionName, @sEmailId, @sAddress1, @sAddress2, @sCity, @sZip, @iStateId, @iCountryId,
				@sPhone, @iLicenseLogins, @iActive, @iIsDistrictSiteAccount, @dtCurrentDate, @sProductKey)
				-- to get the last inserted license id identity value in the current session
				SET @iLicenseId = SCOPE_IDENTITY()
				
				-- get a unique account number
				SET @sAccountNumber = 'AIARS'+RIGHT('000000'+CONVERT(varchar(10),@iLicenseId),6)
				
				UPDATE License SET AccountNumber = @sAccountNumber WHERE Id = @iLicenseId
				
				-- insert the subscription detail of license
				INSERT INTO LicenseSubscriptionDetail(LicenseId, SubscriptionValidFrom, SubscriptionValidThrough,
				PaymentMode, TotalAmount, AmountPaid,NoofImages) VALUES(@iLicenseId, @dtStartDate, @dtEndDate, @sPaymentMode, @iPrice, @iPrice,@iNoofImages)
				
				IF SUBSTRING(@sitem,1,CHARINDEX(@sEditionLoginDelimiter,@sitem)-1) <= 4
				BEGIN
					-- insert All resource module of license for Instructor Edition
					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;
				END
				ELSE IF SUBSTRING(@sitem,1,CHARINDEX(@sEditionLoginDelimiter,@sitem)-1) = 8
				BEGIN
					-- insert All resource module of license for Library Edition
					INSERT INTO ModuleToLicense SELECT @iLicenseId as LicenseId,ResourceModule.Id as ModuleId, CASE when ResourceModule.id < 11 and ResourceModule.id <> 6 or ResourceModule.id = 13 then 0 else 1 end as Status FROM ResourceModule;
				END
				ELSE IF SUBSTRING(@sitem,1,CHARINDEX(@sEditionLoginDelimiter,@sitem)-1) = 9
				BEGIN
					-- insert All resource module of license for Library Edition
					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;
				END
				
				-- insert the mapping of license with edition
				INSERT INTO LicenseToEdition(LicenseId, EditionId, TotalLogins, IsModesty) 
				SELECT @iLicenseId, SUBSTRING(@sitem,1,CHARINDEX(@sEditionLoginDelimiter,@sitem)-1), 
					@iLicenseLogins, @iModesty
				
				-- to get the last inserted licenseedition id identity value in the current session
				SET @iLicenseEditionId = SCOPE_IDENTITY()
				
				-- fetch the last auto incremented id of aiauser table
				SET @iLastAIAUserId = (SELECT MAX(Id) FROM AIAUser)
				
				-- create a loginid for user
				SET @sLoginId = 'RS'+CAST(convert(varchar,@dtCurrentDate,12)as varchar)+char(@iLastAIAUserId/260000%26+65)+
					char(@iLastAIAUserId/10000%26+65)+ char(@iLastAIAUserId/1000%10+48)+char(@iLastAIAUserId/100%10+48)+
					char(@iLastAIAUserId/10%10+48)+char(@iLastAIAUserId%10+48)
				
				-- check if the created loginid already exists if yes then create a new one
				SET @iUserExists = (SELECT 1 FROM AIAUser WHERE LoginId = @sLoginId)
				WHILE @iUserExists > 0
				BEGIN
					SET @iLastAIAUserId = @iLastAIAUserId + 1
					SET @sLoginId = 'RS'+CAST(convert(varchar,@dtCurrentDate,12)as varchar)+char(@iLastAIAUserId/260000%26+65)+
						char(@iLastAIAUserId/10000%26+65)+ char(@iLastAIAUserId/1000%10+48)+char(@iLastAIAUserId/100%10+48)+
						char(@iLastAIAUserId/10%10+48)+char(@iLastAIAUserId%10+48)
					SET @iUserExists = (SELECT 1 FROM AIAUser WHERE LoginId = @sLoginId)
				END
				
				-- create a user for reseller account
				INSERT INTO AIAUser (LoginId, Password, UserTypeId, IsActive, CreatorId, CreationDate, ModifierId, ModifiedDate)
					VALUES(@sLoginId, @sLoginId, @iUserTypeId, @iActive, @iCreatorId, @dtCurrentDate, @iCreatorId, @dtCurrentDate)
				-- to get the last inserted aiauser id identity value in the current session
				SET @iAIAUserId = SCOPE_IDENTITY()
				-- insert the mapping of user with license edition
				INSERT INTO AIAUserToLicenseEdition (UserId, LicenseEditionId) VALUES (@iAIAUserId, @iLicenseEditionId)
				
				-- insert newly created account number, loginid & password into temporary table
				INSERT INTO #LicenseDetail (AccountNumber,LoginId,Password) VALUES(@sAccountNumber, @sLoginId, @sLoginId)
				
				SET @iCount = @iCount+1
			END
		COMMIT TRANSACTION
		SELECT @sErrorStatus as SPStatus,AccountNumber,LoginId,Password FROM #LicenseDetail
		-- drop the temporary table
		DROP TABLE #LicenseDetail
	END TRY
	BEGIN CATCH
		IF @@TRANCOUNT > 0
			ROLLBACK TRANSACTION
		SELECT Error_Message() as SPStatus,'' as AccountNumber,'' as LoginId,'' as Password 
	END CATCH
    
END


GO