USE [AIADatabaseV5]
GO
/****** Object:  StoredProcedure [dbo].[usp_SaveLabExerciseAttempts]    Script Date: 03/05/2018 12:10:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- ====================================================  
-- Author: Nikita Kulshreshtha 
-- Create date: 21-Feb-2018  
-- Description: To insert a LabExerciseData 
-- ====================================================  

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

CREATE PROCEDURE [dbo].[usp_SaveLabExerciseAttempts]
 -- Add the parameters for the stored procedure here  
 
	@UserId int,
	@LabExerciseIdentifier nchar(10),
	@LastQuestion int,
	@TotalQuestions int,
	@LabExerciseTempTbl LabExerciseTempTableType READONLY

	
AS  
BEGIN  
 -- SET NOCOUNT ON added to prevent extra result sets from  
 -- interfering with SELECT statements.  
 SET NOCOUNT ON;  
  
  BEGIN TRY  
  BEGIN TRANSACTION  
  DECLARE @labQuizId int;
  DECLARE @count int;
  DECLARE @Score int;
  DECLARE @MaxScore int;
  DECLARE @UserAnswers nvarchar(2000);
  DECLARE	@QuestionNo int;
  DECLARE	@CorrectAnswers nvarchar(2000);  
  DECLARE	@DragItems nvarchar(max)
  DECLARE @c int

  INSERT INTO  LabExercise(UserId, LabExerciseIdentifier,IsActive, LastQuestion,TotalQuestions,SavedDate, ModifiedDate,CallFrom) 
  values(@UserId, @LabExerciseIdentifier, 1, @LastQuestion, @TotalQuestions,GETDATE(),GETDATE(),'AIAHTML5')
 print 'inserted in 1st table'
  SELECT @labQuizId =  max(Id) from LabExercise where LabExerciseIdentifier = @LabExerciseIdentifier and UserId= @UserId 
 print @labQuizId
-- --check count before update
SELECT @count =  count(*) FROM LabExercise where LabExerciseIdentifier = @LabExerciseIdentifier and UserId= @UserId and Id!=@labQuizId
print  @count
 if(@count>0)
 BEGIN
 print N'inside if'
   UPDATE LabExercise set IsActive = 0 where LabExerciseIdentifier = @LabExerciseIdentifier and UserId= @UserId and Id!=@labQuizId

----create temp table
create table #Temp
(
    MaxScore int, 
    UserAnswers NVarchar(2000), 
    QuestionNo int,
    CorrectAnswers NVarchar(2000),
    DragItems NVarchar(2000), 
    Score int
)
select * from @LabExerciseTempTbl
insert into #Temp(MaxScore,UserAnswers,QuestionNo,CorrectAnswers,DragItems,Score) 
select MaxScore,UserAnswers,QuestionNo,CorrectAnswers,DragItems,Score from @LabExerciseTempTbl
select @c = count(*) from #Temp
print @c
print 'inserted in temp table'
 DECLARE LABEX_CURSOR CURSOR 
 LOCAL FORWARD_ONLY FOR
  SELECT  MaxScore, UserAnswers,QuestionNo, CorrectAnswers, DragItems,Score FROM #Temp
    print 'cursor declared'
  OPEN LABEX_CURSOR
  print 'cursor open'
  FETCH NEXT FROM LABEX_CURSOR INTO @MaxScore, @UserAnswers, @QuestionNo, @CorrectAnswers, @DragItems,@Score
  WHILE @@FETCH_STATUS = 0
  BEGIN
  print 'inside cursor'
   INSERT INTO LabExerciseDetails(LabQuizId,StateObject,Score,MaxScore,UserAnswers,QuestionNo,CorrectAnswers,DragItems) values(@labQuizId,null,@Score,@MaxScore,@UserAnswers,@QuestionNo,@CorrectAnswers,@DragItems) 
  FETCH NEXT FROM LABEX_CURSOR INTO @MaxScore, @UserAnswers, @QuestionNo, @CorrectAnswers, @DragItems,@Score
  print 'end cursor'
  END
  CLOSE LABEX_CURSOR
  DEALLOCATE LABEX_CURSOR
  END
  COMMIT TRANSACTION  
 
 END TRY  
 BEGIN CATCH  
  IF @@TRANCOUNT > 0  
   ROLLBACK TRANSACTION  
 END CATCH  
  
END