USE [AIADatabaseV5]
GO
/****** Object:  StoredProcedure [dbo].[EC_CreateUser]    Script Date: 02/06/2018 10:49:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[EC_CreateUser] 
(
	-- Add the parameters for Licence table 
	@sAccountNumber varchar(50),
	@sLicenseeFirstName varchar(50),
	@sLicenseeLastName varchar(50),
	@sInstitutionName varchar(100),
	@sAddress1 varchar(100),
	@sAddress2 varchar(100),
	@iCountryId	int,
	@iStateId int,
	@sCity varchar(50),
	@sZip varchar(20),
	@sPhone varchar(30)=NULL,
	@sEmailId varchar(50),
	@tiAccountTypeId tinyint,
	@bIsActive bit,
	@dtCreationDate datetime,
	@ioutId int output,
	-- Add the parameters for LicenceSubscriptiontable
	@siSubscriptionPlanId smallint,
	@dtSubscriptionValidFrom datetime,
	@dtSubscriptionValidThrough datetime,
	@sPaymentMode varchar(20),
	@mTotalAmount money,
	@mAmountPaid money,	
	@mAmountPending money,
	-- Add the parameters for SingleUsertable
	@siClassSize smallint,
	@iInstitutionType int,
	@sDepartment varchar(50),
	@iAIARequired int,
	@sCourses varchar(500),
	@iCoursesConduct int,
	@iReferedBy int,
	@iInternetProduct int,
	@iMultimediaProduct int,
	@sOtherAdamProduct varchar(50),
	@bIsOtherAdamProduct bit,
	-- Add the parameters for AIAUser
	@sLoginId varchar(50),				
	@sPassword varchar(50),	
	@sFirstName varchar(100), 
	@sLastName varchar(100),
    @tiSecurityQuestionId tinyint,
	@sSecurityAnswer varchar(50),	
	@dtDeactivationDate datetime=NULL,
	@ioutUserId int out,
	-- Add the parameters for LicenseToEdition
	@tiEditionId tinyint,
	-- Add the parameters for LicenseToEdition
	@sStudentID varchar(50)=NULL,
	@ioutLicenseEditionId int out,
	@sVersion varchar(50),
	@iCardNumber int,
	@iDiscountCodeId Int =0,
    @desDiscountPercentage Decimal(5,2) =0,
	-- Add the Aod and Lite Parameters
	@iAod bit,
	@iLite bit
)
AS

--insert data into License table
INSERT INTO License (LicenseeFirstName,LicenseeLastName,LicenseTypeId,InstitutionName,Address1,Address2,CountryId,StateId,City,Zip,Phone,EmailId,TotalLogins,AccountTypeId,IsActive,IsDistrictSiteLicense,CreationDate,ModifiedDate,CancellationDate,NoOfRenewals,IsTermsAccepted,CardNumber)
			VALUES	(@sLicenseeFirstName,@sLicenseeLastName,2,@sInstitutionName,@sAddress1,@sAddress2,@iCountryId,@iStateId,@sCity,@sZip,@sPhone,@sEmailId,1,@tiAccountTypeId,@bIsActive,'False',@dtCreationDate,null,null,0,1,@iCardNumber) 
SELECT @ioutId = SCOPE_IDENTITY()

-- get a unique account number
IF @tiEditionId = 1
BEGIN
	SET @sAccountNumber = 'AIAI'+RIGHT('0000000'+CONVERT(varchar(10),@ioutId),6)
END
ELSE
BEGIN
	SET @sAccountNumber = 'AIAS'+RIGHT('0000000'+CONVERT(varchar(10),@ioutId),6)
END
UPDATE License SET AccountNumber = @sAccountNumber WHERE Id = @ioutId

--insert data into License subscription table
INSERT INTO LicenseSubscriptionDetail 
									(
										LicenseId,
										SubscriptionPlanId,
										SubscriptionValidFrom,
										SubscriptionValidThrough,
										PaymentMode,
										TotalAmount,
										AmountPaid,
										AmountPending,
										NoofImages
									)
							VALUES	(
										@ioutId,
										@siSubscriptionPlanId,
										@dtSubscriptionValidFrom,
										@dtSubscriptionValidThrough,
										@sPaymentMode,
										@mTotalAmount,
										@mAmountPaid,
										@mAmountPending,
										CASE @tiEditionId
											WHEN '1' THEN '100'
											WHEN '2' THEN '100'
											WHEN '8' THEN '100'
											WHEN '9' THEN '100'
											ELSE '0'
										END
									)
--insert data into SingleUserDetail
INSERT INTO SingleUserDetail (LicenseId,StudentID,ClassSize,InstitutionTypeId,Department,AIARequiredId,Courses,CourseConductId,ReferId,InternetProductId,MultimediaProductId,OtherAdamProduct,IsOtherAdamProduct)
			VALUES	(@ioutId,@sStudentID,@siClassSize,@iInstitutionType,@sDepartment,@iAIARequired,@sCourses,@iCoursesConduct,@iReferedBy,@iInternetProduct,@iMultimediaProduct,@sOtherAdamProduct,@bIsOtherAdamProduct)
			

IF @tiEditionId <= 4
BEGIN
	IF @iLite = 0
	BEGIN
		INSERT INTO ModuleToLicense SELECT @ioutId as LicenseId,ResourceModule.Id as ModuleId,1 as Status FROM ResourceModule where ResourceModule.Id <8
		INSERT INTO ModuleToLicense SELECT @ioutId as LicenseId,ResourceModule.Id as ModuleId,0 as Status FROM ResourceModule where ResourceModule.Id >7 and ResourceModule.Id < 1017
		IF (@iAod = 0)
		BEGIN
			INSERT INTO ModuleToLicense SELECT @ioutId as LicenseId,ResourceModule.Id as ModuleId,0 as Status FROM ResourceModule where ResourceModule.Id = 1017
		END
		IF (@iAod = 1)
		BEGIN
			INSERT INTO ModuleToLicense SELECT @ioutId as LicenseId,ResourceModule.Id as ModuleId,1 as Status FROM ResourceModule where ResourceModule.Id = 1017
		END
	END
	IF @iLite = 1
	BEGIN
		INSERT INTO ModuleToLicense SELECT @ioutId as LicenseId,ResourceModule.Id as ModuleId,1 as Status FROM ResourceModule where ResourceModule.Id in (1, 2, 4, 5, 7, 8, 10)
		INSERT INTO ModuleToLicense SELECT @ioutId as LicenseId,ResourceModule.Id as ModuleId,0 as Status FROM ResourceModule where ResourceModule.Id in (3, 6, 9)
		INSERT INTO ModuleToLicense SELECT @ioutId as LicenseId,ResourceModule.Id as ModuleId,0 as Status FROM ResourceModule where ResourceModule.Id >10 and ResourceModule.Id < 1017
		IF @iAod = 0
		BEGIN
			INSERT INTO ModuleToLicense SELECT @ioutId as LicenseId,ResourceModule.Id as ModuleId,0 as Status FROM ResourceModule where ResourceModule.Id in (1017)
		END
		IF @iAod = 1
		BEGIN
			INSERT INTO ModuleToLicense SELECT @ioutId as LicenseId,ResourceModule.Id as ModuleId,1 as Status FROM ResourceModule where ResourceModule.Id in (1017)
		END
	END
END
ELSE IF @tiEditionId = 8
BEGIN
	-- insert All resource module of license
	INSERT INTO ModuleToLicense SELECT @ioutId as LicenseId,ResourceModule.Id as ModuleId,0 as Status FROM ResourceModule where ResourceModule.Id < 11 and ResourceModule.Id <> 6;
	-- insert AP resource module of license
	INSERT INTO ModuleToLicense SELECT @ioutId as LicenseId,ResourceModule.Id as ModuleId,1 as Status FROM ResourceModule where ResourceModule.Id in (6,11,12);
END
ELSE 
BEGIN
	-- insert All resource module of license
	INSERT INTO ModuleToLicense SELECT @ioutId as LicenseId,ResourceModule.Id as ModuleId,0 as Status FROM ResourceModule where ResourceModule.Id < 11 and ResourceModule.Id <> 6;
	-- insert AP resource module of license
	INSERT INTO ModuleToLicense SELECT @ioutId as LicenseId,ResourceModule.Id as ModuleId,1 as Status FROM ResourceModule where ResourceModule.Id in (6,11,12,13);
END

-- insert the mapping of discount with license
IF @iDiscountCodeId != 0
BEGIN
	INSERT INTO DiscountToLicense (DiscountId, LicenseId, DiscountPercentage) 
		VALUES(@iDiscountCodeId, @ioutId, @desDiscountPercentage)
END
--insert data into AIAUser
INSERT INTO AIAUser (LoginId,Password,FirstName,LastName,UserTypeId,EmailId,IsActive,SecurityQuestionId,SecurityAnswer,CreatorId,CreationDate,ModifierId,ModifiedDate,DeactivationDate)
			VALUES	(@sLoginId,@sPassword,@sFirstName,@sLastName,5,@sEmailId,@bIsActive,@tiSecurityQuestionId,@sSecurityAnswer,null,@dtCreationDate,null,@dtCreationDate,@dtDeactivationDate)			
			
SELECT @ioutUserId = SCOPE_IDENTITY()
--update AIAUser and set createrid with user id 	
UPDATE	AIAUser SET CreatorId=@ioutUserId, ModifierId=@ioutUserId where Id=@ioutUserId		
--insert data into LicenseToEdition
INSERT INTO LicenseToEdition (LicenseId,EditionId,TotalLogins,IsModesty)
			VALUES	(@ioutId,@tiEditionId,1,'True')

SELECT @ioutLicenseEditionId = SCOPE_IDENTITY()
--insert data into AIAUserToLicenseEdition table
INSERT INTO AIAUserToLicenseEdition (UserId,LicenseEditionId)
			VALUES (@ioutUserId,@ioutLicenseEditionId)

--update SingleUserDetail with StudentID ,if user is student then StudentId is updated with user id with prefix 'STU' else StudentId is null
/*IF  @sVersion='Student'
BEGIN
		UPDATE SingleUserDetail set StudentID='STU' + cast(@ioutUserId as varchar) where LicenseId=@ioutId
END
ELSE

BEGIN
		UPDATE SingleUserDetail set StudentID=null where LicenseId=@ioutId
END*/
GO