GO
UPDATE sStudent.tAcademic Set ClassStatusId = null

UPDATE A SET ClassStatusId = 3
FROM sStudent.tAcademic A INNER JOIN sStudent.tStudentMaster  SM ON A.StudentId = SM.Id
INNER JOIN sStudent.tStudentAdmission SA ON A.StudentId = SA.StudentId AND A.ClassId = SA.LeftStandardId
WHERE SM.StatusId = 3

UPDATE sStudent.tAcademic SET ClassStatusId = 1 WHERE ClassStatusId is null
GO

GO
ALTER PROCEDURE [sStudent].[pStudentMasterAdd]
	 
	@rId bigint,
	@rCode nvarchar(20),
	@rRegistrationNumber nvarchar(20),
	@rNumber bigint,
	@rFirstName nvarchar(100),
	@rMiddleName nvarchar(100),
	@rLastName nvarchar(100),
	@rOrgId int,
	--@rDepartmentId int,
	@rAdmissionCategoryId int,
	@rStatusId smallint,
	@rRecordStatusId smallint,
	@rCreatedUserId bigint,
	@rUpdatedUserId bigint,
	@rOrgGroupBatchId int =0,
	@OutStudentId BIGINT = NULL OUTPUT,
	@rSUId nvarchar(20) = '',
	@rOrgGroupId int = 0,
	@rMobileNo nvarchar(50)='',
	@rStudentGroupId int=0,
	@rStudentOtherGroupId int=0,
	@rDateOfBirth Date=NULL,
    @rGenderId int=0,
    @rAcademicId bigint = 0
	
	
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
	--UPDATE sStudent.tAcademic SET ClassStatusId = 1 WHERE ClassStatusId is null WHERE Id = 
	
	--if(@rStatusId=3)
	--UPDATE A
	--	SET ClassStatusId = 3
	--	FROM sStudent.tAcademic A
	--	INNER JOIN sStudent.tStudentMaster SM ON A.StudentId = SM.Id
	--	--INNER JOIN sStudent.tStudentAdmission SA ON A.StudentId = SA.StudentId AND A.ClassId = SA.LeftStandardId
	--	WHERE SM.StatusId = 3 AND A.Id = @rAcademicId
	--else if(@rStatusId=1)
	--UPDATE A
	--SET ClassStatusId = 1
	--	FROM sStudent.tAcademic A
	--	INNER JOIN sStudent.tStudentMaster SM ON A.StudentId = SM.Id
	--	--INNER JOIN sStudent.tStudentAdmission SA ON A.StudentId = SA.StudentId AND A.ClassId = SA.LeftStandardId
	--	WHERE SM.StatusId = 3 AND A.Id = @rAcademicId
	
	
	
	
	Declare @lStudCap int,@lOrgStudentCap int
	--IF(@rNumber=0)
	--SET @rNumber=NULL
	IF(@rId=0)
	BEGIN		 
	 
		--IF((@rCode = Null) OR (@rCode = ''))	
		--BEGIN

		--	EXECUTE sCommon.pPrefixSetupNextNumberGet @rOrgId = @rOrgId, @rPrefixFunctionalityId = 2001,@rStoreLocationId = 0, @rNextNumber = @rCode OUTPUT

		--	SET @rCode = ISNULL(@rCode, '-300')

		--	IF @rCode = '-300'
		--	BEGIN
		--		 SELECT -300 AS Result, '' AS Code, 0 AS Id 
		--		 RETURN
		--	END
		--END
		--ELSE
		--BEGIN
			Select @lStudCap=ISNULL(NoOfStudent,0)+ISNULL(StudentExtraCap,0) from
			sSetup.tOrgGroupSetting Where OrgGroupId= @rOrgGroupId
			
			Select @lOrgStudentCap=[sSetup].[fStudentCapGet](@rOrgGroupBatchId)
			 
			IF(@lOrgStudentCap >= @lStudCap )
			BEGIN
				Select -111 AS RESULT, '' AS Code, @lStudCap AS Id 
				Return
			END
		
			IF EXISTS (SELECT SM.Id FROM sStudent.tStudentMaster SM INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = SM.OrgId
					   WHERE SM.SUId=@rSUId AND OM.OrgGroupId=@rOrgGroupId)
			BEGIN
				SELECT -101 AS RESULT, '' AS Code, 0 AS Id 
				RETURN
			END 
			ELSE IF EXISTS (SELECT Id from sStudent.tStudentMaster WHERE Code=@rCode AND OrgId=@rOrgId)
			BEGIN
				SELECT -100 AS RESULT, '' AS Code, 0 AS Id 
				RETURN
			END 
			ELSE IF EXISTS (SELECT Id from sStudent.tStudentMaster WHERE ISNULL(@rRegistrationNumber,'')!='' AND RegistrationNumber=@rRegistrationNumber AND OrgId=@rOrgId)
			BEGIN
				SELECT -200 AS RESULT, '' AS Code, 0 AS Id 
				RETURN
			END 
		--END
	END
	ELSE
	BEGIN
	
		Select @lStudCap=ISNULL(NoOfStudent,0)+ISNULL(StudentExtraCap,0) from
		sSetup.tOrgGroupSetting Where OrgGroupId= @rOrgGroupId
		
		Select @lOrgStudentCap=[sSetup].[fStudentCapGet](@rOrgGroupBatchId)
		 
		IF(@lOrgStudentCap >= @lStudCap AND (@rStatusId = 1 OR @rStatusId = 2))
		BEGIN
			Select -111 AS RESULT, '' AS Code, @lStudCap AS Id 
			Return
		END
	
		DECLARE @lReferenceSourceId bigint=0
		SELECT @lReferenceSourceId = ISNULL(ReferenceSourceId,0)  FROM sStudent.tStudentMaster WHERE Id = @rId
		
		IF(@lReferenceSourceId=0)
			SELECT @lReferenceSourceId = Id FROM sStudent.tStudentMaster WHERE ReferenceSourceId = @rId
		IF(@lReferenceSourceId = 0)
		BEGIN 
			IF EXISTS (SELECT SM.Id FROM sStudent.tStudentMaster SM INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = SM.OrgId
						   WHERE SM.SUId=@rSUId AND OM.OrgGroupId=@rOrgGroupId AND SM.Id!=@rId)
			BEGIN
				SELECT -101 AS RESULT, '' AS Code, 0 AS Id 
				RETURN
			END 
		END
		 
		IF EXISTS (SELECT Id from sStudent.tStudentMaster WHERE Code=@rCode AND OrgId=@rOrgId AND Id!=@rId)
		BEGIN
			SELECT -100 AS RESULT, '' AS Code, 0 AS Id 
			RETURN
		END 
		ELSE IF EXISTS (SELECT Id from sStudent.tStudentMaster WHERE RegistrationNumber=@rRegistrationNumber AND OrgId=@rOrgId AND Id!=@rId)
		BEGIN
			SELECT -200 AS RESULT, '' AS Code, 0 AS Id 
			RETURN
		END 
	END
	
	
	BEGIN TRY
	
	 BEGIN TRANSACTION	
	IF(@rId=0)
	BEGIN	
	DECLARE @lNextNumber BIGINT     
	EXECUTE sCommon.pTableRowIdGet @rOrgId, 'sStudent.tStudentMaster', 0, 1, 1,@outNextNumber = @lNextNumber output

	IF(@rNumber=0)
	BEGIN
	SET @rNumber=@lNextNumber
	END
	INSERT INTO sStudent.tStudentMaster
		(
			Id,
			Code,
			Number,
			RegistrationNumber,
			FirstName,
			MiddleName,
			LastName,
			OrgId,
			--DepartmentId,
			AdmissionCategoryId,
			StatusId,
			RecordStatusId,
			CreatedUserId,
			CreatedDateTime,
		    SUId,
		    StudentGroupId,
		    StudentOtherGroupId
		)
	VALUES
		(
			@lNextNumber,
			@rCode,
			@rNumber,
			@rRegistrationNumber,
			@rFirstName,
			@rMiddleName,
			@rLastName,
			@rOrgId,
			--@rDepartmentId,
			@rAdmissionCategoryId,
			@rStatusId,
			@rRecordStatusId,
			@rCreatedUserId,
			sCommon.fGetDate(),
			@rSUId,
			@rStudentGroupId ,
			@rStudentOtherGroupId 
		)
		
		INSERT INTO sStudent.tStudentOther
		(
			StudentId,
			DateOfBirth,
			GenderId,
			CreatedUserId,
			CreatedDateTime
		)
		VALUES
		(
			@lNextNumber,
			@rDateOfBirth,
			@rGenderId,
			@rCreatedUserId,
			sCommon.fGetDate()
		)
		IF(@rMobileNo!='')
		BEGIN
			INSERT INTO sStudent.tStudentCommunication
			(
				StudentId,
				ParentContactNo,
				CreatedUserId,
				CreatedDateTime
			)
			VALUES
			(
				@lNextNumber,
				@rMobileNo,
				@rCreatedUserId,
				sCommon.fGetDate()
			)
		END
		      
	END	 
	 ELSE
	 BEGIN
	 SET @lNextNumber = @rId
			IF(@rNumber=0)
			BEGIN
			SET  @rNumber=(SELECT ISnull(Number,0) FROM sStudent.tStudentMaster WHERE  Id=@rId)
			IF(@rNumber=0)
			SET @rNumber=@lNextNumber
			END
	    UPDATE  sStudent.tStudentMaster
	    SET
			Code=@rCode,
			Number=@rNumber,
			RegistrationNumber=@rRegistrationNumber,
			FirstName=@rFirstName,
			MiddleName=@rMiddleName,
			LastName=@rLastName,
			--OrgId=@rOrgId,
			--DepartmentId=@rDepartmentId,
			AdmissionCategoryId=@rAdmissionCategoryId,
			StatusId=@rStatusId,
			RecordStatusId=@rRecordStatusId,
	        UpdatedUserId=@rUpdatedUserId,
	        UpdatedDateTime=sCommon.fGetDate(),
			SUId=@rSUId,
			StudentGroupId=@rStudentGroupId ,
		    StudentOtherGroupId=@rStudentOtherGroupId
	      
	      WHERE Id = @rId 
	      
	    UPDATE  sStudent.tStudentOther
		SET
			DateOfBirth=@rDateOfBirth,
			GenderId=@rGenderId,
	        UpdatedUserId=@rUpdatedUserId,
	        UpdatedDateTime=sCommon.fGetDate()
	      WHERE StudentId = @rId 
	     
	     
	     IF(@rStatusId=3)
		BEGIN
		    UPDATE sSecurity.tUser
			   SET IsObsolete=1 
			WHERE SourceTypeId=2003 AND SourceId=@rId
		END
	 END  
	 
		SET @OutStudentId = @lNextNumber
	  SELECT 1 AS Result, @rCode AS Code, @lNextNumber AS Id 
		--SELECT @lNextNumber AS Result
	
	
	
		COMMIT TRANSACTION
		
	END TRY
	
	BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH	
		
END

GO
ALTER PROCEDURE [sStudent].[pStudentAdmissionAdd]
	 
		   @rStudentId bigint,
		   @rPreviousSchool nvarchar(200),
           @rPreviousSchoolTypeId int,
           @rPreviousStudentId bigint,
           @rAdmissionFormId bigint,
           @rAdmissionFormNumber nvarchar(20),
           @rAdmissionStandardId int,
           @rAdmissionDate date,
           @rAdmissionYear nvarchar(10),
           @rLeftStandardId int,
           @rLeftDate date,
           @rLeftYear nvarchar(10),
           @rInactiveDate date,
           @rInActiveReason nvarchar(200),
           @rLeftReason nvarchar(200),
           @rLCProgress nvarchar(200),
           @rLCConduct nvarchar(200),
           @rLCBook nvarchar(20),
           @rLCNumber nvarchar(20),
           @rLCDate date,
           @rLCRemark nvarchar(200),
           @rLCCopy tinyint,
           @rLcId bigint,
           @rBatchId int,
           @rCreatedUserId bigint,
           @rUpdatedUserId bigint,
           @rIsGeneral bit,
           
           @rAdmissionNumber nvarchar(50)='',
           @rIsTransfer bit = 0,
           @rTransferFrom nvarchar(150)='',
           @rPassingYear nvarchar(20)='',
           @rIsTFWS bit = 0,
           @rIsCertificateHolder bit = 0,
           @rCertificateName nvarchar(100) = '',
           @rAdmissionQuotaId int =0,
           @rPreviousStandard nvarchar(50)='',
           @rAcademicId bigint = 0

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN

BEGIN TRY
BEGIN TRANSACTION	

 


IF(@rIsGeneral=1)	
BEGIN 
	IF NOT EXISTS(SELECT * FROM sStudent.tStudentAdmission WHERE StudentId = @rStudentId)
	BEGIN

	INSERT INTO sStudent.tStudentAdmission
		(
			   StudentId,
			   PreviousSchool,
			   AdmissionStandardId,
			   AdmissionDate,
			   LeftStandardId,
			   LeftDate,
			   InactiveDate,
			   BatchId,
			   CreatedUserId,
			   CreatedDateTime,
			   PreviousStandard
			   
		)
	VALUES
		(
			   @rStudentId,
			   @rPreviousSchool,
			   @rAdmissionStandardId,
			   @rAdmissionDate,
			   @rLeftStandardId,
			   @rLeftDate,
			   @rInactiveDate,
			   @rBatchId,
			   @rCreatedUserId,
			   sCommon.fGetDate(),
			   @rPreviousStandard
		)
		
		
		SELECT 1 AS Result             
	END	 
	 ELSE
	 BEGIN
		UPDATE sStudent.tStudentAdmission
	    SET
			  
			  AdmissionStandardId = @rAdmissionStandardId, 
			  AdmissionDate = @rAdmissionDate, 
			  LeftStandardId = @rLeftStandardId,
			  LeftDate = @rLeftDate, 
			  InactiveDate = @rInactiveDate, 
			  UpdatedUserId = @rUpdatedUserId, 
			  UpdatedDateTime =sCommon.fGetDate()
	
	      WHERE StudentId = @rStudentId 
	      
	  
	 END  
		SELECT 11 AS Result
	END
ELSE
BEGIN
		IF(@rIsGeneral=0)
	    IF NOT EXISTS(SELECT * FROM sStudent.tStudentAdmission WHERE StudentId = @rStudentId)
BEGIN

	INSERT INTO sStudent.tStudentAdmission
		(
			   StudentId,
			   PreviousSchool,
			   PreviousSchoolTypeId,
			   PreviousStudentId,
			   AdmissionFormId,
			   AdmissionFormNumber,
			   AdmissionStandardId,
			   AdmissionDate,
			   AdmissionYear,
			   LeftStandardId,
			   LeftDate,
			   LeftYear,
			   InactiveDate,
			   InActiveReason,
			   LeftReason,
			   LCProgress,
			   LCConduct,
			   LCBook,
			   LCNumber,
			   LCDate,
			   LCRemark,
			   LCCopy,
			   LcId,
			   BatchId,
			   CreatedUserId,
			   CreatedDateTime,
			   
			   AdmissionNumber,
			   IsTransfer,
			   TransferFrom,
			   PassingYear,
			   IsTFWS,
			   IsCertificateHolder,
			   CertificateName,
			   AdmissionQuotaId,
			   PreviousStandard
		)
	VALUES
		(
			   @rStudentId,
			   @rPreviousSchool,
			   @rPreviousSchoolTypeId,
			   @rPreviousStudentId,
			   @rAdmissionFormId,
			   @rAdmissionFormNumber,
			   @rAdmissionStandardId,
			   @rAdmissionDate,
			   @rAdmissionYear,
			   @rLeftStandardId,
			   @rLeftDate,
			   @rLeftYear,
			   @rInactiveDate,
			   @rInActiveReason,
			   @rLeftReason,
			   @rLCProgress,
			   @rLCConduct,
			   @rLCBook,
			   @rLCNumber,
			   @rLCDate,
			   @rLCRemark,
			   @rLCCopy,
			   @rLcId,
			   @rBatchId,
			   @rCreatedUserId,
			   sCommon.fGetDate(),
			   
			   @rAdmissionNumber,
			   @rIsTransfer,
			   @rTransferFrom,
			   @rPassingYear,
			   @rIsTFWS,
			   @rIsCertificateHolder,
			   @rCertificateName,
			   @rAdmissionQuotaId,
			   @rPreviousStandard
		)
		SELECT 1 AS Result             
	END	 
	ELSE
	BEGIN
		UPDATE sStudent.tStudentAdmission
	    SET
			  PreviousSchool = @rPreviousSchool,
			  PreviousSchoolTypeId = @rPreviousSchoolTypeId, 
			  PreviousStudentId = @rPreviousStudentId, 
			  AdmissionFormId = @rAdmissionFormId,
			  AdmissionFormNumber = @rAdmissionFormNumber, 
			  AdmissionStandardId = @rAdmissionStandardId, 
			  AdmissionDate = @rAdmissionDate, 
			  AdmissionYear = @rAdmissionYear,
			  LeftStandardId = @rLeftStandardId,
			  LeftDate = @rLeftDate, 
			  LeftYear = @rLeftYear,
			  InactiveDate = @rInactiveDate, 
			  InActiveReason = @rInActiveReason,
			  LeftReason = @rLeftReason, 
			  LCProgress = @rLCProgress,
			  LCConduct = @rLCConduct, 
			  LCBook = @rLCBook, 
			  LCNumber = @rLCNumber,
			  LCDate = @rLCDate, 
			  LCRemark = @rLCRemark, 
			  LCCopy = @rLCCopy, 
			  LcId = @rLcId, 
			  UpdatedUserId = @rUpdatedUserId, 
			  UpdatedDateTime =sCommon.fGetDate(),
			  
			  AdmissionNumber=@rAdmissionNumber,
			  IsTransfer=@rIsTransfer,
		  	  TransferFrom=@rTransferFrom,
			  PassingYear=@rPassingYear,
			  IsTFWS=@rIsTFWS,
			  IsCertificateHolder=@rIsCertificateHolder,
			  CertificateName=@rCertificateName,
			  AdmissionQuotaId=@rAdmissionQuotaId,
			  PreviousStandard=@rPreviousStandard
	      
	      WHERE StudentId = @rStudentId 	 
	      
	END  
	 
		SELECT 11 AS Result
		
	END

		UPDATE A
		SET ClassStatusId = 3
		FROM sStudent.tAcademic A
		INNER JOIN sStudent.tStudentMaster SM ON A.StudentId = SM.Id
		INNER JOIN sStudent.tStudentAdmission SA ON A.StudentId = SA.StudentId AND A.ClassId = SA.LeftStandardId
		WHERE SM.StatusId = 3 AND SM.Id = @rStudentId

	
	COMMIT TRANSACTION
	END TRY
	BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH	
		
END

GO
 
ALTER PROCEDURE [sSetup].[pPortalStudentAcademicMasterGet]
	 
	 @rOrgId int,
	 @rDivisionId int = 0,
	 @rBatchId int,	
	 @rStudentAcademicCreatedDate DateTime,
	 @rStudentAcademicUpdatedDate DateTime,	 
	 @rStudentMaxId bigint
		 
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
	 
BEGIN 	
	SELECT  
		Id,
		StudentId,
		ClassId,
		DivisionId,
		RollNo,
		OrgId,
		BatchId,
		CreatedUserId,
		ISNULL(CreatedDateTime,sCommon.fGetDate()) AS CreatedDateTime,	
		ISNULL(UpdatedUserId,0) AS UpdatedUserId,
		ISNULL(UpdatedDateTime,sCommon.fGetDate()) AS UpdatedDateTime,
		
		ISNULL(IsCurrent,0) AS IsCurrent,
		ISNULL(BatchStatusId,0) AS BatchStatusId,
		ISNULL(ExemptionTypeId,0) AS ExemptionTypeId,
		ISNULL(FeeStructureId,0) AS FeeStructureId,
		ISNULL(AdmissionCategoryId,0) AS AdmissionCategoryId,
		ISNULL(ClassStatusId,0) AS ClassStatusId
  FROM 
           sStudent.tAcademic
             
  WHERE   oRGiD = @rOrgId and
   DivisionId = @rDivisionId 
  And BatchId=@rBatchId 
  --AND StudentId > @rStudentMaxId
  --AND ( Convert(datetime,CreatedDatetime,120) > @rStudentAcademicCreatedDate OR  Convert(datetime,UpdatedDatetime,120) > @rStudentAcademicUpdatedDate)
     AND  (CONVERT(DATETIME,CONVERT(VARCHAR(20),CONVERT(DATETIME,  CreatedDateTime), 120)) > @rStudentAcademicCreatedDate OR 
       CONVERT(DATETIME,CONVERT(VARCHAR(20),CONVERT(DATETIME,  UpdatedDateTime), 120)) > @rStudentAcademicUpdatedDate )
  
  END

GO


GO
 


ALTER PROCEDURE [sSetup].[pPortalStudentAcademicMasterUpdate]
	 
	
	@rStrXml [nvarchar](max),
	@rDivisionId int
	
	
	
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
SET @rStrXml=REPLACE(@rStrXml,'&','AND')
DECLARE @loDoc INT
EXEC sp_xml_preparedocument @loDoc OUTPUT, @rStrXml
BEGIN
	--BEGIN TRY
	
	 BEGIN	
	 --DELETE FROM sStudent.tAcademic WHERE DivisionId = @rDivisionId
	
	 UPDATE sStudent.tAcademic
		SET
			StudentId = A.StudentId,
			ClassId = A.ClassId,
			DivisionId = A.DivisionId,
			RollNo = A.RollNo,
			OrgId = A.OrgId,
			BatchId = A.BatchId,
			UpdatedUserId = A.UpdatedUserId,
			UpdatedDateTime = A.UpdatedDateTime,
			CreatedDateTime = A.CreatedDateTime,
			IsCurrent=A.IsCurrent,
			BatchStatusId=A.BatchStatusId,
			ExemptionTypeId=A.ExemptionTypeId,
			FeeStructureId=A.FeeStructureId,
			AdmissionCategoryId=A.AdmissionCategoryId,
			ClassStatusId=A.ClassStatusId
		FROM
		(
		SELECT		
			Id,
			StudentId,
			ClassId,
			DivisionId,
			RollNo,
			OrgId,
			BatchId,
			UpdatedUserId,
			UpdatedDateTime,
			CreatedDateTime,
			IsCurrent,
			BatchStatusId,
			ExemptionTypeId,
			FeeStructureId,
			AdmissionCategoryId,
			ClassStatusId
						
		FROM OPENXML (@loDoc, '/Table/Academic', 1)
		WITH ( 
				Id bigint,
				StudentId bigint,
				ClassId int,					
				DivisionId int,
				RollNo int,
				OrgId int,
				BatchId int,
				UpdatedUserId bigint,
				UpdatedDateTime DateTime,
				CreatedDateTime DateTime,
				IsCurrent bit,
				BatchStatusId smallint,
				ExemptionTypeId int,
				FeeStructureId int,
				AdmissionCategoryId int,
				ClassStatusId smallint
			  )) A
			  Where sStudent.tAcademic.Id = A.Id
			  
			  
	   INSERT INTO sStudent.tAcademic
		(
			Id,
			StudentId,
			ClassId,
			DivisionId,
			RollNo,
			OrgId,
			BatchId,
			CreatedUserId,
			UpdatedUserId,
			UpdatedDateTime,
			CreatedDateTime,
			IsCurrent,
			BatchStatusId,
			ExemptionTypeId,
			FeeStructureId,
			AdmissionCategoryId,
			ClassStatusId
			)
		SELECT		
			A.Id,
			A.StudentId,
			A.ClassId,
			A.DivisionId,
			A.RollNo,
			A.OrgId,
			A.BatchId,
			A.CreatedUserId,
			A.UpdatedUserId,
			A.UpdatedDateTime,
			A.CreatedDateTime,
			A.IsCurrent,
			A.BatchStatusId,
			A.ExemptionTypeId,
			A.FeeStructureId,
			A.AdmissionCategoryId,
			1
						
		FROM OPENXML (@loDoc, '/Table/Academic', 1)
		WITH ( 
				Id bigint,
				StudentId bigint,
				ClassId int,					
				DivisionId int,
				RollNo int,
				OrgId int,
				BatchId int,
				CreatedUserId bigint,
				UpdatedUserId bigint,
				UpdatedDateTime DateTime,
				CreatedDateTime DateTime,
				IsCurrent bit,
				BatchStatusId smallint,
				ExemptionTypeId int,
				FeeStructureId int,
				AdmissionCategoryId int		
			  ) A LEFT JOIN sStudent.tAcademic AC ON A.Id = AC.Id
			  WHERE AC.Id IS NULL
			  
			  EXEC sp_xml_removedocument @loDoc
		
        SELECT 1 AS Result
	 END	
	
		
	--END TRY
	
	--BEGIN CATCH 
	
	--	SELECT -1 AS Result
	--END CATCH	
		
END







GO


GO
ALTER PROCEDURE [sStudent].[pAcademicAdd]
	 
	@rAcademicId bigint=0,
	@rStudentId bigint=0,
	@rClassId int=0,
	@rDivisionId int=0,
	@rRollNo int=0,
	@rOrgId int=0,
	@rBatchId int=0,
	@rCreatedUserId bigint=0,
	@rUpdatedUserId bigint=0,
	@rAdmissionCategoryId int = 0
	
	
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
	BEGIN TRY
	
	SET @rRollNo = ISNULL(@rRollNo,0)
	
	 BEGIN TRANSACTION	
 IF NOT EXISTS(Select Id from sStudent.tAcademic where StudentId=@rStudentId AND BatchId=@rBatchId)
BEGIN
	
	DECLARE @lNextNumber BIGINT     
	EXECUTE sCommon.pTableRowIdGet @rOrgId, 'sStudent.tAcademic', 0, 1, 1,@outNextNumber = @lNextNumber output



--IF ISNULL(@rRollNo,0) <> 0
--BEGIN
--IF EXISTS(Select Id from sStudent.tAcademic where RollNo = @rRollNo AND DivisionId = @rDivisionId )
--BEGIN
--SET @rRollNo = 0
--END
--END
--ELSE
--BEGIN
--  SELECT @rRollNo =  (ISNULL(max(RollNo),0) + 1) FROM  sStudent.tAcademic where DivisionId=@rDivisionId AND BatchId=@rBatchId
--END
 
	INSERT INTO sStudent.tAcademic
		(
			Id,
			StudentId,
			ClassId,
			DivisionId,
			RollNo,
			OrgId,
			BatchId,
			CreatedUserId,
			CreatedDateTime,
			IsCurrent,
			BatchStatusId,
			AdmissionCategoryId,
			ClassStatusId
		)
	VALUES
		(
			@lNextNumber,
			@rStudentId,
			@rClassId,
			@rDivisionId,
			@rRollNo,
			@rOrgId,
			@rBatchId,
			@rCreatedUserId,
			sCommon.fGetDate()
			,1
			,1
			,@rAdmissionCategoryId,
			1
		)	
		
	   INSERT INTO sStudent.tAcademicDetail
		(
		AcademicId,
		RepeatCount
		)
		VALUES
		(
		@lNextNumber,
		1
		)
			
        UPDATE sStudent.tAcademic SET IsCurrent = 0 WHERE StudentId = @rStudentId AND OrgId = @rOrgId AND BatchId <>  @rBatchId        
	END	 
	 ELSE
	 BEGIN
	 SET @lNextNumber = @rAcademicId
	 
	 
--if ISNULL(@rRollNo,0) <> 0
--BEGIN
--IF EXISTS(Select Id from sStudent.tAcademic where RollNo = @rRollNo AND DivisionId = @rDivisionId AND Id <> @rAcademicId )
--BEGIN
--SET @rRollNo = 0
--END
--END
	 
	    UPDATE sStudent.tAcademic
	    SET
			ClassId=@rClassId,
			DivisionId=@rDivisionId,
			RollNo=@rRollNo,
			OrgId=@rOrgId,
			BatchId=@rBatchId,
			UpdatedUserId=@rUpdatedUserId,
			UpdatedDateTime=sCommon.fGetDate() ,
			AdmissionCategoryId = @rAdmissionCategoryId
	      WHERE 
	       --Id = @rAcademicId  AND 
	       StudentId=@rStudentId  AND 
	       BatchId = @rBatchId
	      
	 END  			
		COMMIT TRANSACTION
		SELECT @lNextNumber AS Result
		
	--UPDATE sStudent.tAcademic SET BatchStatusId = 1 WHERE BatchStatusId is null
		
	END TRY
	
	BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH	
		
END

GO
