USE [AIADatabaseV5]
GO
/****** Object:  StoredProcedure [dbo].[InsertDemoLicenseAccount]    Script Date: 2/1/2018 12:15:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
    
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[InsertDemoLicenseAccount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[InsertDemoLicenseAccount]
GO	
	   
CREATE PROCEDURE [dbo].[InsertDemoLicenseAccount]           
AS          
BEGIN          
           
 -- SET NOCOUNT ON added to prevent extra result sets from          
 -- interfering with SELECT statements.          
 SET NOCOUNT ON;          
           
 BEGIN TRY          
  BEGIN TRANSACTION          
   DECLARE @sAccountNumber varchar(50)           
   DECLARE @sLoginId varchar(50)          
   DECLARE @iLicenseId int          
   DECLARE @iLicenseEditionId int          
   DECLARE @iAIAUserId int          
   DECLARE @dtStartDate datetime          
   DECLARE @dtEndDate datetime          
   DECLARE @iLastAIAUserId int          
   DECLARE @sMailBody  varchar(1000)          
             
   DECLARE @sLicenseeFname varchar(50) = 'Guest'          
   DECLARE @sLicenseeLname varchar(50) = 'Account'          
   DECLARE @sEmailId varchar(50) = 'edsales@adamcorp.com'          
   -- license type of test account          
   DECLARE @iLicenseTypeId tinyint = 5          
   -- user type of test account          
   DECLARE @iUserTypeId tinyint = 8          
   -- set account type to High School          
   DECLARE @iAccountTypeId tinyint = 1          
   DECLARE @iAmount tinyint = 0          
   DECLARE @iTotalLogins tinyint = 100          
   DECLARE @iActive tinyint = 1          
   DECLARE @iModesty tinyint = 0          
   DECLARE @dtCurrentDate datetime = getdate()          
   DECLARE @iCountryId int = 233          
   DECLARE @iStateId int = 51          
   -- set edition to Instructor's Edition          
   DECLARE @iEditionId tinyint = 1          
   DECLARE @iCreatorId int = 1          
   DECLARE @iLicenseExists tinyint = 0          
   DECLARE @iLicenseCounter int          
   DECLARE @sErrorStatus char(2) = 'ok'          
          
   -- set the startdate to current date & enddate to seven days ahead of current date          
   SELECT @dtStartDate = @dtCurrentDate          
   SELECT @dtEndDate = DATEADD(ms,-3,DATEADD(DAY,7,CONVERT(DATETIME,(CONVERT(VARCHAR,@dtCurrentDate,101)))))          
             
   -- create a new guest license account          
   INSERT INTO License(LicenseeFirstName, LicenseeLastName, LicenseTypeId, AccountTypeId,           
   EmailId, Zip, StateId, CountryId, TotalLogins, IsActive, IsDistrictSiteLicense,           
   CreationDate) VALUES (@sLicenseeFname, @sLicenseeLname, @iLicenseTypeId, @iAccountTypeId,          
   @sEmailId, '', @iStateId, @iCountryId, @iTotalLogins, @iActive, 0, @dtCurrentDate )          
   -- to get the last inserted identity value in the current session          
   SET @iLicenseId = SCOPE_IDENTITY()          
             
   SET @sAccountNumber = 'AIAGA'+RIGHT('000000'+CONVERT(varchar(10),@iLicenseId),6)          
             
   -- check if the created account number already exists if yes then create a new one          
   SET @iLicenseExists = (SELECT 1 FROM License WHERE AccountNumber = @sAccountNumber)          
   SET @iLicenseCounter = @iLicenseId          
   WHILE @iLicenseExists > 0          
   BEGIN          
    SET @iLicenseCounter = @iLicenseCounter + 1          
    SET @sAccountNumber = 'AIAGA'+RIGHT('000000'+CONVERT(varchar(10),@iLicenseCounter),6)          
    SET @iLicenseExists = (SELECT 1 FROM License WHERE AccountNumber = @sAccountNumber)          
   END          
   UPDATE License SET AccountNumber = @sAccountNumber WHERE Id = @iLicenseId          
             
   INSERT INTO LicenseSubscriptionDetail(LicenseId, SubscriptionValidFrom, SubscriptionValidThrough,          
   TotalAmount, AmountPaid, AmountPending) VALUES(@iLicenseId, @dtStartDate, @dtEndDate, @iAmount, @iAmount, @iAmount)          
             
   -- insert resource module of license          
   INSERT INTO ModuleToLicense SELECT @iLicenseId as LicenseId,ResourceModule.Id as ModuleId,1 as Status           
   FROM ResourceModule          
             
   INSERT INTO LicenseToEdition(LicenseId, EditionId, TotalLogins, IsModesty)           
   VALUES(@iLicenseId, @iEditionId, @iTotalLogins, @iModesty)          
   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 = 'GA'+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)          
             
   INSERT INTO AIAUser(LoginId, Password, Firstname, Lastname, UserTypeId, EmailId, IsActive,           
   CreatorId, CreationDate, ModifierId, ModifiedDate) VALUES(@sLoginId, @sLoginId, @sLicenseeFname, @sLicenseeLname,           
   @iUserTypeId, @sEmailId, @iActive, @iCreatorId, @dtCurrentDate, @iCreatorId, @dtCurrentDate)          
   SET @iAIAUserId = SCOPE_IDENTITY()          
             
   INSERT INTO AIAUserToLicenseEdition(UserId, LicenseEditionId) VALUES(@iAIAUserId, @iLicenseEditionId)          
             
   SET @sMailBody = 'Daily demo Account has been created:<br><br>          
       <b>User Id</b>: '+@sLoginId+            
       '<br><b>Password</b>: '+@sLoginId+          
       '<br><br> This demo account will be active for 7 days effective '+CONVERT(VARCHAR,@dtStartDate,101)+' to '+CONVERT(VARCHAR,@dtEndDate,101);          
             
  COMMIT TRANSACTION          
            
  EXEC msdb..sp_send_dbmail          
   @profile_name='MailProfile',          
            @recipients='info@tfei.org.uk;rahulr@Ebix.com;kpaul@ebix.com;digitalproducts@flr.follett.com;jennifer.hickey@ebix.com;debora.durazzo@ebix.com;renata.nascimento@ebix.com;thaisa.braguim@ebix.com;DigitalContentTrials@FollettSoftware.com; timothy.spaid@ebix.com; lori.byro@ebix.com; luis.maida@rackspace.ebix.com; Daniela.Laiati@rackspace.ebix.com',              
            @body = @sMailBody,           
            @body_format = 'HTML',          
            @subject = 'Demo Account Created.'           
            
  SELECT @sErrorStatus as SPStatus          
 END TRY          
 BEGIN CATCH          
  IF @@TRANCOUNT > 0          
   ROLLBACK TRANSACTION          
  SELECT Error_Message() as SPStatus          
 END CATCH          
END 
GO