CREATE PROCEDURE [dbo].[applyMRSM]
@nosiri varchar(20),@tempatPeperiksaan varchar(4),
@nosiriExam varchar(20),

@NamaIbuBapa varchar(80), @KPIbuBapa varchar(12),
@WargaI varchar(1), @WargaB varchar(1), @Alamat1 varchar(50),
@Alamat2 varchar(50),
@Bandar varchar(50),@Poskod int,
@Negeri varchar(2),@NoTel varchar(20),
@Tanggung smallint,@BangsaI varchar(2),
@BangsaB varchar(2),
@KerjaI varchar(2),@KerjaB varchar(2),@Pendapatan decimal(18,0),

@trnxStat char(1) output
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
Begin Try

set transaction isolation level repeatable read
begin transaction

declare @workingExamKapasiti int, @nextprogram int, 
@progquota int,@allotedcnt int

declare @sesiExam varchar(10)
declare @lastNoSiri int



select top 1 @workingExamKapasiti=idx,
@progquota=Kapasiti, @allotedcnt=BilCalon
from tbl_WorkingExamKapasiti
where MRSM=@tempatPeperiksaan
order by MRSM, TingkatanId, ProgramId, SesiExamId

if @allotedcnt < @progquota
Begin
	--update permohonan dengan data yang terkini
	update tbl_MSTPermohonan 
	set NamaIbuBapa=@NamaIbuBapa,NoKpIbuBapa=@KPIbuBapa,
	WargaIbu =@WargaI,WargaBapa=@WargaB,Alamat1 =@Alamat1,Alamat2 =@Alamat2,
	Bandar = @Bandar,Poskod =@Poskod,Negeri =@Negeri,NoTelefon = @NoTel,
	Tanggungan =@Tanggung,BangsaIbu =@BangsaI,BangsaBapa=@BangsaB,
	KerjaIbu=@KerjaI,KerjaBapa =@KerjaB,PendapatanIbuBapa=@Pendapatan,
	TkhUpd=getdate() 
	where nosiri=@nosiri;
	
	--update permohonan with tempatPeperiksaan yang berjaya
	update tbl_MSTPermohonan set tbl_WorkingExamKapasiti=@workingExamKapasiti
	where NoSiri=@nosiri;
	
	--update statusRekod
	update dbo.tbl_MSTstudentlogin set StatusRekod=3, TkhUpdate=getdate()
	where NoSiri=@nosiri;
	
	--update BilCalon
	update dbo.tbl_WorkingExamKapasiti
	set BilCalon = BilCalon + 1 where idx = @workingExamKapasiti;
	
	--update NoSiriAkhir
	update dbo.tbl_WorkingExamKapasiti
	set @lastNoSiri=NoSiriAkhir = NoSiriAkhir + 1,
	TkhUpd=getdate()
	where idx = @workingExamKapasiti;
	
	--update NoSiriExam
	select @sesiExam=SesiExamId from dbo.tbl_WorkingExamKapasiti where idx=@workingExamKapasiti;
	update tbl_MSTPermohonan 
	set NoSiriExam=@nosiriExam + @sesiExam + right('000000' + convert(varchar(10),@lastNoSiri),6)
	where NoSiri=@nosiri;
	
	print 'transaction was successful'
	set @trnxStat=1
	
End
else
 begin
	--select another sesi
	select top 1 @nextprogram=  t.idx
	from dbo.tbl_WorkingExamKapasiti t
	cross apply (select count(*) as cnt
				   from  dbo.tbl_MSTPermohonan
				   where tbl_WorkingExamKapasiti = t.idx
				 )t1
	  where MRSM=@tempatPeperiksaan and cnt < t.Kapasiti
	  order by MRSM, TingkatanId, ProgramId, SesiExamId
	  
	  if @nextprogram is not null
		 --insert into @tResult (studentID, programID) values(@studentId,@nextprogram)
	  Begin
	  --update permohonan dengan data yang terkini
		update tbl_MSTPermohonan 
		set NamaIbuBapa=@NamaIbuBapa,NoKpIbuBapa=@KPIbuBapa,
		WargaIbu =@WargaI,WargaBapa=@WargaB,Alamat1 =@Alamat1,Alamat2 =@Alamat2,
		Bandar = @Bandar,Poskod =@Poskod,Negeri =@Negeri,NoTelefon = @NoTel,
		Tanggungan =@Tanggung,BangsaIbu =@BangsaI,BangsaBapa=@BangsaB,
		KerjaIbu=@KerjaI,KerjaBapa =@KerjaB,PendapatanIbuBapa=@Pendapatan,
		TkhUpd=getdate() 
		where nosiri=@nosiri;
	
		--update permohonan with tempatPeperiksaan yang berjaya
		update tbl_MSTPermohonan set tbl_WorkingExamKapasiti=@nextprogram
		where NoSiri=@nosiri;
		
		--update statusRekod
		update dbo.tbl_MSTstudentlogin set StatusRekod=3, TkhUpdate=getdate()
		where NoSiri=@nosiri;
		
		--update BilCalon
		update dbo.tbl_WorkingExamKapasiti
		set BilCalon = BilCalon + 1 
		where idx = @nextprogram;
		
		--update NoSiriAkhir
		update dbo.tbl_WorkingExamKapasiti
		set @lastNoSiri=NoSiriAkhir = NoSiriAkhir + 1,
		TkhUpd=getdate()
		where idx = @nextprogram;
		
		--update NoSiriExam
		select @sesiExam=SesiExamId from dbo.tbl_WorkingExamKapasiti where idx=@nextprogram;
		update tbl_MSTPermohonan 
		set NoSiriExam=@nosiriExam + @sesiExam + right('000000' + convert(varchar(10),@lastNoSiri),6)
		where NoSiri=@nosiri;
		
		print 'trasaction was successful'
		set @trnxStat=1
		
	  End
	  else
	  Begin		 
		 --raiserror ('no program available at this moment',16,1)
		 print 'transation was not successful'
		 set @trnxStat=0
	  End
 end

COMMIT

End Try

Begin Catch

if @@trancount > 0
ROLLBACK

declare @ErrMsg nvarchar(4000), @ErrSeverity int
select @ErrMsg=ERROR_MESSAGE(), @ErrSeverity=ERROR_SEVERITY()

RAISERROR(@ErrMsg,@ErrSeverity,1)

End Catch



END