USE [RedPen]
GO

/****** Object:  StoredProcedure [TF40].[sp_process_marks]    Script Date: 06/19/2023 13:45:08 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [TF40].[sp_process_marks]
(@ex2ppMapid int, @outflag int OUTPUT)
WITH 
EXECUTE AS CALLER
AS
begin
declare @error table (ckey varchar (1),error varchar(500));

--truncate values
truncate table process.tbl_tmp_mark_sch
	--update anser archive
	delete from data.ANSWER_KEY where ex2pp_mapid=@ex2ppMapid
insert into data.ANSWER_KEY
select c.EX2PP_MAPID ,QNUMBER,'A' as part,isnull(A,0) as answer from [TF40].T1_CLENSED c where c.EX2PP_MAPID=@ex2ppMapid and c.INDEX_NUMBER='**********'
union
select c.EX2PP_MAPID ,QNUMBER,'B' as part,isnull(B,0) as answer from [TF40].T1_CLENSED c where c.EX2PP_MAPID=@ex2ppMapid and c.INDEX_NUMBER='**********'
union
select c.EX2PP_MAPID ,QNUMBER,'C' as part,isnull(C,0)as answer from [TF40].T1_CLENSED c where c.EX2PP_MAPID=@ex2ppMapid and c.INDEX_NUMBER='**********'
union
select c.EX2PP_MAPID ,QNUMBER,'D' as part,isnull(D,0)as answer from [TF40].T1_CLENSED c where c.EX2PP_MAPID=@ex2ppMapid and c.INDEX_NUMBER='**********'
union
select c.EX2PP_MAPID ,QNUMBER,'E' as part,isnull(E,0)as answer from [TF40].T1_CLENSED c where c.EX2PP_MAPID=@ex2ppMapid and c.INDEX_NUMBER='**********'
-------------

	--insert values to memory table
	insert into process.tbl_tmp_mark_sch
	select m.Qnum,m.Correct_M,m.Incorrect_M,m.Both_M,m.Missing_M,m.Minimum_M
	from RedPen.T2002_MRKSCH m where m.Ex2mpid=@ex2ppMapid;
	
--KEY ANSWERES
DECLARE @KA varchar(1);
DECLARE @KB varchar(1);
DECLARE @KC varchar(1);
DECLARE @KD varchar(1);
DECLARE @KE varchar(1);

--marks
declare @CORRECT NUMERIC(4,2);
declare  @iCORRECT NUMERIC(4,2);
declare @MISSING NUMERIC(4,2);
declare @BOTH NUMERIC(4,2);
declare @MINIMUM NUMERIC(4,2);
--STUD ANSWERED
DECLARE @SA varchar(1);
DECLARE @SB varchar(1);
DECLARE @SC varchar(1);
DECLARE @SD varchar(1);
DECLARE @SE varchar(1);

DECLARE @SINDEX VARCHAR(50);
DECLARE @SMARK numeric(4,2);
declare @total_question int;
declare @cnt int;
declare @runner int;
--ASSIGN DEFAULTS
 SET @KA=NULL ;
 SET @KB =@KA;
 SET @KC =@KA;
 SET @KD =@KA;
 SET @KE =@KA;
 
--GET NOQ COUNT FROM PAPER
select @total_question= p.Total_Questions from RedPen.T1001_EXM2PPR p where p.Ex2mapid=@ex2ppMapid
--check all questions are answered
select @cnt= COUNT(*) from TF40.T1_CLENSED a where a.INDEX_NUMBER='**********'
if @cnt<>@total_question
begin
insert into @error values ('H','All questions are not answered in answer sheet');
end

--check null questions in answersheet
 SELECT @cnt= COUNT( QNUMBER) FROM TF40.T1_CLENSED  WHERE
 (A IS NULL OR B IS NULL OR C IS NULL OR D IS NULL OR E IS NULL ) 
 AND INDEX_NUMBER='**********'  and QNUMBER <=40
 
if @cnt>0
begin
insert into @error values ('H','Following questions are not answered correctly in answer sheet');
insert into @error
SELECT 'D', QNUMBER FROM TF40.T1_CLENSED  WHERE
 (A IS NULL OR B IS NULL OR C IS NULL OR D IS NULL OR E IS NULL ) 
 AND INDEX_NUMBER='**********' and QNUMBER <=40
 end
-- check either one answered chceck 

--check missing questions in answersheet
SELECT @cnt= COUNT( QNUMBER) FROM TF40.T1_CLENSED
 WHERE (A ='M' OR B ='M' OR C ='M' OR D ='M' OR E ='M' )
 AND INDEX_NUMBER='**********' 
if @cnt>0
begin
insert into @error values ('H','Following questions are not answered neither one in answer sheet');
insert into @error
SELECT 'D', QNUMBER FROM TF40.T1_CLENSED
 WHERE (A ='M' OR B ='M' OR C ='M' OR D ='M' OR E ='M' )
 AND INDEX_NUMBER='**********' 
end
 
 --more than one answered
 
 SELECT @cnt= COUNT( QNUMBER) FROM TF40.T1_CLENSED
 WHERE (A ='B' OR B ='B' OR C ='B' OR D ='B' OR E ='B' )
 AND INDEX_NUMBER='**********' 
if @cnt>0
begin
insert into @error values ('H','Following questions are Both answered in answer sheet');
insert into @error
SELECT 'D', QNUMBER FROM TF40.T1_CLENSED
 WHERE (A ='B' OR B ='B' OR C ='B' OR D ='B' OR E ='B' )
 AND INDEX_NUMBER='**********' 
end
 
  
 
--check is there any error
select @cnt= COUNT(*) from @error
if @cnt>0
begin
set @outflag=0;
select * from @error;
return;
end

-- process marks
delete  from data.RESULTS_STORE where EX2PP_MAPID=@ex2ppMapid
set @runner=1;
while @runner<=@total_question
begin
--get correct answer
select @KA=A,@KB=B,@KC=C,@KD=D,@KE=E FROM TF40.T1_CLENSED
 WHERE INDEX_NUMBER='**********' AND QNUMBER=@RUNNER;
--get correct marks
select @CORRECT= m.Correct_M,
@iCORRECT= m.Incorrect_M,
@BOTH= m.Both_M,
@MISSING= m.Missing_M,
@MINIMUM= m.Minimum_M from process.tbl_tmp_mark_sch m
where Qnum=@runner




DECLARE CUR_MARKs  CURSOR FOR
select c.INDEX_NUMBER,c.A,c.B,c.C,c.D,c.E  from TF40.T1_CLENSED c where
c.QNUMBER=@runner
OPEN CUR_MARKs
FETCH NEXT FROM CUR_MARKs INTO @SINDEX ,@SA,@SB,@SC,@SD,@SE
while @@FETCH_STATUS=0
begin

if @runner>=41
begin
set @SMARK=0;

if CONVERT(int,ISNULL(@SA,0))+CONVERT(int,ISNULL(@Sb,0))+CONVERT(int,ISNULL(@Sc,0))+CONVERT(int,ISNULL(@Sd,0))+CONVERT(int,ISNULL(@Se,0))<1
BEGIN
set @SMARK=@SMARK+@MISSING
INSERT INTO [RedPen].[data].[RESULTS_STORE] ([EX2PP_MAPID]
           ,[INDEX_NUMBER]
           ,[QNUMBER]
           ,[PART]
           ,[ANS_STATUS]
           ,[MARKS])
     VALUES
           (@ex2ppMapid
           ,@SINDEX
           ,@runner
           ,null
           ,'M'
           ,@MISSING)



GOTO nEXTq
END
if CONVERT(int,ISNULL(@SA,0))+CONVERT(int,ISNULL(@Sb,0))+CONVERT(int,ISNULL(@Sc,0))+CONVERT(int,ISNULL(@Sd,0))+CONVERT(int,ISNULL(@Se,0))>1
BEGIN
set @SMARK=@SMARK+@BOTH
INSERT INTO [RedPen].[data].[RESULTS_STORE] ([EX2PP_MAPID]
           ,[INDEX_NUMBER]
           ,[QNUMBER]
           ,[PART]
           ,[ANS_STATUS]
           ,[MARKS])
     VALUES
           (@ex2ppMapid
           ,@SINDEX
           ,@runner
           ,null
           ,'B'
           ,@BOTH)

GOTO nEXTq
END
IF @KA='1'
SET @KA='A'
IF @KB='1'
SET @KA='B'
IF @KC='1'
SET @KA='C'
IF @KD='1'
SET @KA='D'
IF @KE='1'
SET @KA='E'




IF @SA='1'
SET @SA='A'
IF @SB='1'
SET @SA='B'
IF @SC='1'
SET @SA='C'
IF @SD='1'
SET @SA='D'
IF @SE='1'
SET @SA='E'



IF @KA=@SA
begin
SET @SMARK=@SMARK+@CORRECT
INSERT INTO [RedPen].[data].[RESULTS_STORE] ([EX2PP_MAPID]
           ,[INDEX_NUMBER]
           ,[QNUMBER]
           ,[PART]
           ,[ANS_STATUS]
           ,[MARKS])
     VALUES
           (@ex2ppMapid
           ,@SINDEX
           ,@runner
           ,null
           ,'C'
           ,@CORRECT)
end
ELSE

begin
SET @SMARK=@SMARK+@iCORRECT
INSERT INTO [RedPen].[data].[RESULTS_STORE] ([EX2PP_MAPID]
           ,[INDEX_NUMBER]
           ,[QNUMBER]
           ,[PART]
           ,[ANS_STATUS]
           ,[MARKS])
     VALUES
           (@ex2ppMapid
           ,@SINDEX
           ,@runner
           ,null
           ,'I'
           ,@iCORRECT)
end



if @SMARK < @MINIMUM
set @SMARK=@MINIMUM;
--update marks
UPDATE TF40.T1_CLENSED
   SET  [MARKS] = @SMARK
      ,[FLAG] = 'M'
 WHERE [INDEX_NUMBER]=@SINDEX
 and [QNUMBER] = @runner
 and [EX2PP_MAPID] = @ex2ppMapid

end
else
begin
set @SMARK=0;
--A
IF @SA='M'
begin
SET @SMARK=@SMARK+@MISSING;
INSERT INTO [RedPen].[data].[RESULTS_STORE] ([EX2PP_MAPID],[INDEX_NUMBER],[QNUMBER],[PART],[ANS_STATUS],[MARKS])VALUES (@ex2ppMapid,@SINDEX,@runner,'A','M',@MISSING)
end
ELSE IF @SA='B'
begin
SET @SMARK=@SMARK+@BOTH;
INSERT INTO [RedPen].[data].[RESULTS_STORE] ([EX2PP_MAPID],[INDEX_NUMBER],[QNUMBER],[PART],[ANS_STATUS],[MARKS])VALUES (@ex2ppMapid,@SINDEX,@runner,'A','B',@BOTH)
end
ELSE IF @SA=@KA
begin
SET @SMARK=@SMARK+@CORRECT;
INSERT INTO [RedPen].[data].[RESULTS_STORE] ([EX2PP_MAPID],[INDEX_NUMBER],[QNUMBER],[PART],[ANS_STATUS],[MARKS])VALUES (@ex2ppMapid,@SINDEX,@runner,'A','C',@CORRECT)
end
ELSE IF @SA <> @KA
begin
SET @SMARK=@SMARK+@iCORRECT;
INSERT INTO [RedPen].[data].[RESULTS_STORE] ([EX2PP_MAPID],[INDEX_NUMBER],[QNUMBER],[PART],[ANS_STATUS],[MARKS])VALUES (@ex2ppMapid,@SINDEX,@runner,'A','I',@iCORRECT)
end
--B
IF @SB='M'
begin
SET @SMARK=@SMARK+@MISSING;
INSERT INTO [RedPen].[data].[RESULTS_STORE] ([EX2PP_MAPID],[INDEX_NUMBER],[QNUMBER],[PART],[ANS_STATUS],[MARKS])VALUES (@ex2ppMapid,@SINDEX,@runner,'B','M',@MISSING)
end
ELSE IF @SB='B'
begin
SET @SMARK=@SMARK+@BOTH;
INSERT INTO [RedPen].[data].[RESULTS_STORE] ([EX2PP_MAPID],[INDEX_NUMBER],[QNUMBER],[PART],[ANS_STATUS],[MARKS])VALUES (@ex2ppMapid,@SINDEX,@runner,'B','B',@BOTH)
end
ELSE IF @SB=@KB
begin
SET @SMARK=@SMARK+@CORRECT;
INSERT INTO [RedPen].[data].[RESULTS_STORE] ([EX2PP_MAPID],[INDEX_NUMBER],[QNUMBER],[PART],[ANS_STATUS],[MARKS])VALUES (@ex2ppMapid,@SINDEX,@runner,'B','C',@CORRECT)
end
ELSE IF @SB <> @KB
begin
SET @SMARK=@SMARK+@iCORRECT;
INSERT INTO [RedPen].[data].[RESULTS_STORE] ([EX2PP_MAPID],[INDEX_NUMBER],[QNUMBER],[PART],[ANS_STATUS],[MARKS])VALUES (@ex2ppMapid,@SINDEX,@runner,'B','I',@iCORRECT)
end
--C
IF @SC='M'
begin
SET @SMARK=@SMARK+@MISSING;
INSERT INTO [RedPen].[data].[RESULTS_STORE] ([EX2PP_MAPID],[INDEX_NUMBER],[QNUMBER],[PART],[ANS_STATUS],[MARKS])VALUES (@ex2ppMapid,@SINDEX,@runner,'C','M',@MISSING)
end
ELSE IF @SC='B'
begin
SET @SMARK=@SMARK+@BOTH;
INSERT INTO [RedPen].[data].[RESULTS_STORE] ([EX2PP_MAPID],[INDEX_NUMBER],[QNUMBER],[PART],[ANS_STATUS],[MARKS])VALUES (@ex2ppMapid,@SINDEX,@runner,'C','B',@BOTH)
end
ELSE IF @SC=@KC
begin
SET @SMARK=@SMARK+@CORRECT;
INSERT INTO [RedPen].[data].[RESULTS_STORE] ([EX2PP_MAPID],[INDEX_NUMBER],[QNUMBER],[PART],[ANS_STATUS],[MARKS])VALUES (@ex2ppMapid,@SINDEX,@runner,'C','C',@CORRECT)
end
ELSE IF @SC <> @KC
begin
SET @SMARK=@SMARK+@iCORRECT;
INSERT INTO [RedPen].[data].[RESULTS_STORE] ([EX2PP_MAPID],[INDEX_NUMBER],[QNUMBER],[PART],[ANS_STATUS],[MARKS])VALUES (@ex2ppMapid,@SINDEX,@runner,'C','I',@iCORRECT)
end
--D
IF @SD='M'
begin
SET @SMARK=@SMARK+@MISSING;
INSERT INTO [RedPen].[data].[RESULTS_STORE] ([EX2PP_MAPID],[INDEX_NUMBER],[QNUMBER],[PART],[ANS_STATUS],[MARKS])VALUES (@ex2ppMapid,@SINDEX,@runner,'D','M',@MISSING)
end
ELSE IF @SD='B'
begin
SET @SMARK=@SMARK+@BOTH;
INSERT INTO [RedPen].[data].[RESULTS_STORE] ([EX2PP_MAPID],[INDEX_NUMBER],[QNUMBER],[PART],[ANS_STATUS],[MARKS])VALUES (@ex2ppMapid,@SINDEX,@runner,'D','B',@BOTH)
end
ELSE IF @SD=@KD
begin
SET @SMARK=@SMARK+@CORRECT;
INSERT INTO [RedPen].[data].[RESULTS_STORE] ([EX2PP_MAPID],[INDEX_NUMBER],[QNUMBER],[PART],[ANS_STATUS],[MARKS])VALUES (@ex2ppMapid,@SINDEX,@runner,'D','C',@CORRECT)
end
ELSE IF @SD <> @KD
begin
SET @SMARK=@SMARK+@iCORRECT;
INSERT INTO [RedPen].[data].[RESULTS_STORE] ([EX2PP_MAPID],[INDEX_NUMBER],[QNUMBER],[PART],[ANS_STATUS],[MARKS])VALUES (@ex2ppMapid,@SINDEX,@runner,'D','I',@iCORRECT)
end
--E
IF @SE='M'
begin
SET @SMARK=@SMARK+@MISSING;
INSERT INTO [RedPen].[data].[RESULTS_STORE] ([EX2PP_MAPID],[INDEX_NUMBER],[QNUMBER],[PART],[ANS_STATUS],[MARKS])VALUES (@ex2ppMapid,@SINDEX,@runner,'E','M',@MISSING)
end
ELSE IF @SE='B'
begin
SET @SMARK=@SMARK+@BOTH;
INSERT INTO [RedPen].[data].[RESULTS_STORE] ([EX2PP_MAPID],[INDEX_NUMBER],[QNUMBER],[PART],[ANS_STATUS],[MARKS])VALUES (@ex2ppMapid,@SINDEX,@runner,'E','B',@BOTH)
end
ELSE IF @SE=@KE
begin
SET @SMARK=@SMARK+@CORRECT;
INSERT INTO [RedPen].[data].[RESULTS_STORE] ([EX2PP_MAPID],[INDEX_NUMBER],[QNUMBER],[PART],[ANS_STATUS],[MARKS])VALUES (@ex2ppMapid,@SINDEX,@runner,'E','C',@CORRECT)
end
ELSE IF @SE <> @KE
begin
SET @SMARK=@SMARK+@iCORRECT;
INSERT INTO [RedPen].[data].[RESULTS_STORE] ([EX2PP_MAPID],[INDEX_NUMBER],[QNUMBER],[PART],[ANS_STATUS],[MARKS])VALUES (@ex2ppMapid,@SINDEX,@runner,'E','I',@iCORRECT)
end

if @SMARK < @MINIMUM
set @SMARK=@MINIMUM;
--update marks
UPDATE TF40.T1_CLENSED
   SET  [MARKS] = @SMARK
      ,[FLAG] = 'M'
 WHERE [INDEX_NUMBER]=@SINDEX
 and [QNUMBER] = @runner
 and [EX2PP_MAPID] = @ex2ppMapid
end

nEXTq:
--insert into @error values ('H','record updated ' +CONVERT(varchar(10), @SMARK) );
FETCH NEXT FROM CUR_MARKs INTO @SINDEX ,@SA,@SB,@SC,@SD,@SE
end
close CUR_MARKs
deallocate CUR_MARKs
set @runner=@runner+1
end
set @outflag=1;
select * from @error
end





GO