 


ALTER PROCEDURE [sHrm].[pEmployeeBulkImagesAdd]
	 
	@rCode nvarchar(20),
	@rEmployeePhoto varbinary(max),
	@rOrgId smallint
	
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
	
	BEGIN TRY
	
	DECLARE @lEmployeeId bigint
	
	SELECT @lEmployeeId=Id FROM sHrm.tEmployeeMaster WHERE Code=@rCode AND OrgId=@rOrgId
	IF(ISNULL(@lEmployeeId,'') = '')
	BEGIN
		SELECT 1 AS Result
		RETURN
	END
	
	BEGIN TRANSACTION
	
	IF NOT EXISTS(SELECT EmployeeId FROM sHrm.tEmployeeImages WHERE EmployeeId=@lEmployeeId)
	BEGIN 
	INSERT INTO sHrm.tEmployeeImages
		(
			EmployeeId,
			EmployeePhoto,
			UpdatedDateTime
		)
	VALUES
		(
			@lEmployeeId,
			@rEmployeePhoto,
			Getdate()
		)
		             
	END	 
	 ELSE
	 BEGIN
	    UPDATE sHrm.tEmployeeImages
	    SET
			EmployeePhoto=@rEmployeePhoto,
			UpdatedDateTime=Getdate()
			
	      WHERE 
			EmployeeId=@lEmployeeId
	      
	 END
		SELECT 1 AS Result
	  
		COMMIT TRANSACTION
		
	END TRY
	
	BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH	
		
END


GO
ALTER PROCEDURE [sTool].[pTableRowIdAdd]
	
	@rOrgGroupId int
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

DECLARE @lOrgId smallint
DECLARE @lgetOrgId CURSOR
SET @lgetOrgId = CURSOR FOR
SELECT Id
FROM sSetup.tOrganizationMaster WHERE OrgGroupId = @rOrgGroupId
OPEN @lgetOrgId
FETCH NEXT
FROM @lgetOrgId INTO @lOrgId
WHILE @@FETCH_STATUS = 0
BEGIN

INSERT INTO [sCommon].[tTableRowId]
           ([OrgId]
           ,[TableTitle]
           ,[CurrentNumber]
           ,[Seed]
           ,[Increment])
SELECT
		   @lOrgId
		  ,[TableTitle]
		  
		  ,CASE 
      WHEN IdDataType = 'int' THEN sCommon.fGetIntIdForOrg(ISNULL(CurrentNumber,0),@lOrgId) 
      WHEN IdDataType = 'bigint' THEN sCommon.fGetbigIntIdForOrg(ISNULL(CurrentNumber,0),@lOrgId )
		END 
		
		  ,CASE 
      WHEN IdDataType = 'int' THEN sCommon.fGetIntIdForOrg(ISNULL(CurrentNumber,0),@lOrgId) 
      WHEN IdDataType = 'bigint' THEN sCommon.fGetbigIntIdForOrg(ISNULL(CurrentNumber,0),@lOrgId )
		END  
		   ,1
	  FROM 
		sCommon.tTableRowIdMaster
WHERE		
			(TableTitle NOT IN(SELECT TableTitle FROM sCommon.tTableRowId WHERE OrgId=@lOrgId))
		AND (TableType = 'Non Static' or TableType = '100Static' )
		AND (iddatatype <> '') 
		AND IdScope = 'Org'
FETCH NEXT
FROM @lgetOrgId INTO @lOrgId
END
CLOSE @lgetOrgId
DEALLOCATE @lgetOrgId


INSERT INTO [sCommon].[tTableRowId]
           ([OrgId]
           ,[TableTitle]
           ,[CurrentNumber]
           ,[Seed]
           ,[Increment])
SELECT
		   @rOrgGroupId
		  ,[TableTitle]
		  
		  ,CASE 
      WHEN IdDataType = 'int' THEN sCommon.fGetIntIdForOrg(ISNULL(CurrentNumber,0),@rOrgGroupId) 
      WHEN IdDataType = 'bigint' THEN sCommon.fGetbigIntIdForOrg(ISNULL(CurrentNumber,0),@rOrgGroupId )
		END 
		
		  ,CASE 
      WHEN IdDataType = 'int' THEN sCommon.fGetIntIdForOrg(ISNULL(CurrentNumber,0),@rOrgGroupId) 
      WHEN IdDataType = 'bigint' THEN sCommon.fGetbigIntIdForOrg(ISNULL(CurrentNumber,0),@rOrgGroupId )
		END  
		   ,1
	  FROM 
		sCommon.tTableRowIdMaster
WHERE		
		(TableTitle NOT IN(SELECT TableTitle FROM sCommon.tTableRowId WHERE OrgId=@rOrgGroupId))
		AND (TableType = 'Non Static' or TableType = '100Static' )
		AND (iddatatype <> '') 
		AND IdScope = 'OrgGroup'


GO


ALTER PROCEDURE [sAssessment].[pMyResultDetailGet]
	 
	@rExamSubGroupId int = 0,
	@rStudentId bigint =0,
	@rAcademicId bigint = 0,
	@rBatchId int = 0,
	@rResultId bigint=0,
	@rExamId int=0
	 
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
			 

BEGIN 
SELECT
		R.Id AS ResultId, 
		R.ExamId,
		ISNULL(E.ReportId,0) AS ReportId,
		ESGM.Title AS ExamName,
		StuM.FirstName+' '+StuM.LastName AS StudentName,
		StuM.Id AS StudentId,
		A.RollNo,
		DM.DisplayTitle AS Division,
		R.TotalMarks,
		R.TotalObtain,
		R.ResultStatus,
		R.ResultGrade,
		R.RankNumber,
		R.Percentage,
		R.Percentile,	
		ESGM.Id AS ExamSubGroupId,
		BM.Id AS BatchId,
		BM.Title AS BatchName,
		ED.Maximum,
		ED.Minimum,
		RD.Obtain,
		SM.Code AS SubjectCode, 
		SM.Title AS SubjectTitle, 
		ISNULL(RD.SubjectGrade,'') AS SubjectGrade
		
FROM 
			sAssessment.tResult R 
			INNER JOIN sAssessment.tResultDetail RD ON RD.ResultId=R.Id
			INNER JOIN sStudent.tAcademic A ON A.Id=R.AcademicId
			INNER JOIN sStudent.tStudentMaster StuM ON StuM.Id=A.StudentId
			INNER JOIN sSetup.tClassMaster CM ON CM.Id=A.ClassId
			INNER JOIN sSetup.tDivisionMaster DM ON DM.Id=A.DivisionId
			INNER JOIN sAssessment.tExamMaster E ON R.ExamId = E.Id
			INNER JOIN sAssessment.tExamDetail ED ON ED.ExamId=E.Id AND RD.ExamDetailId=ED.Id 
			INNER JOIN sSetup.tClassSubject CS ON ED.ClassSubjectId=CS.Id
			INNER JOIN sSetup.tSubjectMaster SM ON SM.Id=CS.SubjectId
			INNER JOIN sAssessment.tExamSubGroupMaster ESGM ON ESGM.Id = E.ExamSubGroupId
			INNER JOIN sSetup.tBatchMaster BM ON BM.Id = A.BatchId
		
			
	WHERE 
	
			(ESGM.Id=@rExamSubGroupId OR @rExamSubGroupId=0)
		--AND ((ESGM.Id = @rExamSubGroupId) OR (@rExamSubGroupId =0))
		AND  ((StuM.Id = @rStudentId) or(@rStudentId=0))
		AND ((A.Id = @rAcademicId) OR (@rAcademicId =0))
		AND ((BM.Id = @rBatchId) OR (@rBatchId =0))
		AND (E.IsPublished = 1)  AND ISNULL(E.ReportId,0)  > 0
		AND ((R.Id=@rResultId)OR(@rResultId=0))
		AND ((R.ExamId=@rExamId)OR(@rExamId=0))
	
		 order by ESGM.OrderIndex desc
	
END

GO

ALTER PROCEDURE [sAssessment].[pExamDetailAdd]
	 
	        @rId int
           ,@rExamId int
		   ,@rClassSubjectId int
		   ,@rMinimum decimal(5,2)
		   ,@rMaximum Decimal(5,2)
		   ,@rIsIncludeInTotal bit
		   ,@rIsDisplayInMarkSheet bit
		   ,@rIsShowSubjectGrade bit
		   ,@rDisplayOrder tinyint
		   ,@rGradeSetDetailId int
		   ,@rExamDate date
		   ,@rExamTime nvarchar(50)
		   ,@rExamTopic nvarchar(100)
		   ,@rCreatedUserId bigint
		   ,@rOrgId int
		   ,@rIsObsolete bit,
		   @rIsShowSubjectMark bit=1
		 
		   
		  	
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
	BEGIN TRY
	
	 BEGIN TRANSACTION	
	 DECLARE @lNextNumber BIGINT 

--IF(@rId = 0)	
IF NOT EXISTS (SELECT 1 FROM [sAssessment].[tExamDetail] WHERE ExamId = @rExamId
				AND ClassSubjectId = @rClassSubjectId )
	BEGIN
	EXECUTE sCommon.pTableRowIdGet @rOrgId, 'sAssessment.tExamDetail', 0, 1, 1,@outNextNumber = @lNextNumber output
	INSERT INTO [sAssessment].[tExamDetail]
			   (
			    [Id]
			   ,ExamId 
			   ,ClassSubjectId 
			   ,Minimum 
			   ,Maximum 
			   ,IsIncludeInTotal 
			   ,IsDisplayInMarkSheet 
			   ,IsShowSubjectGrade 
			   ,DisplayOrder 
			   ,GradeSetId 
			   ,ExamDate 
			   ,ExamTime 
			   ,ExamTopic 
			   ,UpdatedUserId
			   ,UpdatedDateTime,
			   IsShowSubjectMark
			   )
		 VALUES
			   (
				@lNextNumber
			   ,@rExamId 
			   ,@rClassSubjectId 
			   ,@rMinimum 
			   ,@rMaximum 
			   ,@rIsIncludeInTotal 
			   ,@rIsDisplayInMarkSheet 
			   ,@rIsShowSubjectGrade 
			   ,@rDisplayOrder 
			   ,@rGradeSetDetailId 
			   ,@rExamDate 
			   ,@rExamTime 
			   ,@rExamTopic 
			   ,0
			   ,sCommon.fGetDate(),
			   @rIsShowSubjectMark 
			   )
		    SELECT @lNextNumber AS Result
	
		--COMMIT TRANSACTION         
	END	 
ELSE
    BEGIN



	  IF @rIsObsolete = 1 
	  
	   BEGIN
	IF EXISTS (SELECT R.Id FROM sAssessment.tResultDetail R INNER JOIN  [sAssessment].[tExamDetail] ED ON R.ExamDetailId = ED.Id and R.ExamDetailId=@rId AND R.Obtain > @rMaximum) 
    BEGIN
		SELECT -104 AS Result
		RETURN
    END
			
			 SET @lNextNumber = @rId
				UPDATE  [sAssessment].[tExamDetail]
				SET
						ExamId =@rExamId 
					   ,ClassSubjectId =@rClassSubjectId 
					   ,Minimum=@rMinimum 
					   ,Maximum=@rMaximum 
					   ,IsIncludeInTotal =@rIsIncludeInTotal 
					   ,IsDisplayInMarkSheet =@rIsDisplayInMarkSheet 
					   ,IsShowSubjectGrade =@rIsShowSubjectGrade 
					   ,DisplayOrder =@rDisplayOrder 
					   ,GradeSetId =@rGradeSetDetailId 
					   ,ExamDate =@rExamDate 
					   ,ExamTime=@rExamTime 
					   ,ExamTopic =@rExamTopic 
					   ,UpdatedUserId=@rCreatedUserId
					   ,UpdatedDateTime=sCommon.fGetDate(),
					   IsShowSubjectMark=@rIsShowSubjectMark 
				  WHERE Id = @rId 	   
				  SELECT @lNextNumber AS Result
			
				--COMMIT TRANSACTION
			 END 
	ELSE
		BEGIN
		
			IF EXISTS (SELECT R.Id FROM sAssessment.tResultDetail R INNER JOIN  [sAssessment].[tExamDetail] ED ON R.ExamDetailId = ED.Id and R.ExamDetailId=@rId) 
			AND @rIsObsolete = 0
			BEGIN
				SELECT -105 AS Result
				RETURN
			END
		
			DELETE FROM [sAssessment].[tExamDetail] WHERE ID = @rId
			SELECT @rId AS Result
		END		  
	END 

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

GO

ALTER PROCEDURE [sAssessment].[pMarkEntryDetailAdd]
	 
	        @rId bigint
           ,@rResultId bigint
		   ,@rExamDetailId int
		   ,@rObtain Decimal(6,2)
		   ,@rIsPresent bit
		   ,@rExamId int
		   ,@rAcademicId bigint
		   ,@rOrgId int
		  	
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
	BEGIN TRY
	
	 BEGIN TRANSACTION	
	 DECLARE @lNextNumber BIGINT 
	 DECLARE @lMarkInDecimal bit
	 DECLARE @lClassId int

Select @lMarkInDecimal=IsMarksInDecimal,@lClassId = ClassId from sAssessment.tExamMaster Where Id = @rExamId
	 
--Select @lMarkInDecimal=IsMarksInDecimal from sAssessment.tExamMaster Where Id = @rExamId And ClassId IN (SELECT ClassId FROM sStudent.tAcademic WHERE ID=@rAcademicId)
--Select @lClassId = ClassId From sAssessment.tExamMaster Where Id=@rExamId

IF @lMarkInDecimal = 0
	BEGIN
		SET @rObtain = ROUND(@rObtain,0)
	END
		
If NOT EXISTS (SELECT 1 FROM sAssessment.tExamDetail Where Id= @rExamDetailId And   ClassSubjectId in 
				(Select cs.Id from sSetup.tClassSubject CS 
					Left join sAssessment.tOptionalSubject O ON o.ClassSubjectId=cs.Id 
					AND O.AcademicId=@rAcademicId
					WHERE (CS.ClassId =@lClassId AND IsOptional = 0) OR (IsOptional = 1 AND AcademicId IS NOt Null))
					)
BEGIN
	SELECT 1 as Result
END

ELSE IF NOT EXISTS(SELECT Id From sAssessment.tResultDetail Where ResultId IN (SELECT ID from sAssessment.tResult Where AcademicId=@rAcademicId AND ExamId=@rExamId) AND ExamDetailId=@rExamDetailId)	
BEGIN

	EXECUTE sCommon.pTableRowIdGet @rOrgId, 'sAssessment.tResultDetail', 0, 1, 1,@outNextNumber = @lNextNumber output	
	 
	INSERT INTO [sAssessment].[tResultDetail]
			   (
			    [Id]
			   ,[ResultId] 
			   ,[ExamDetailId]
			   ,[Obtain]
			   ,[IsPresent]
			   )
		 VALUES
			   (
				@lNextNumber
			   ,@rResultId
			   ,@rExamDetailId
			   ,@rObtain
			   ,@rIsPresent
			   )
			  
		    SELECT @lNextNumber AS Result
	
		--COMMIT TRANSACTION         
	END	 
ELSE
	 BEGIN
	 
	 Select @rId = Id From sAssessment.tResultDetail Where ResultId IN (SELECT ID from sAssessment.tResult Where AcademicId=@rAcademicId AND ExamId=@rExamId) AND ExamDetailId=@rExamDetailId
	
	    UPDATE  [sAssessment].[tResultDetail]
	    SET
	         -- ResultId=@rResultId
	         --,ExamDetailId=@rExamDetailId,
			 Obtain=@rObtain
			 ,IsPresent=@rIsPresent
			 ,SubjectGrade = ''
			 ,SubjectGradePoint=0
			 ,Percentile=0
	      WHERE Id = @rId 	   
	      SELECT @rId AS Result
	
		--COMMIT TRANSACTION
	 END  
		
	--END
--END	
COMMIT TRANSACTION		
	END TRY
	
	BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH	
		
END

GO

ALTER FUNCTION [sFee].[fReceiptNumberDateGet](@rAcademicId bigint,@rMonthId int,@rReceiptBookId int)
RETURNS nvarchar(2000) WITH EXECUTE AS CALLER
AS 
BEGIN

DECLARE @rReceiptDetail nvarchar(2000)
(SELECT 

			@rReceiptDetail= COALESCE(@rReceiptDetail+'<br>','') + Convert(nvarchar(20),R.ReceiptNumber) + '<br>' + Convert(nvarchar(20),R.ReceiptDate,103) + '<br>' + Convert(nvarchar(20),R.ReceiptAmount) 
			 FROM
			 (SELECT DISTINCT
			  R.ReceiptNumber,R.ReceiptDate,R.ReceiptAmount
			 FROM sFee.tReceipt R
			 INNER JOIN sFee.tReceiptDetail D ON R.Id = D.ReceiptId			 
			 INNER JOIN sFee.tFeeSetup S ON D.FeeSetupId = s.Id
			 INNER JOIN sFee.tFeeMaster F ON S.FeeId = F.Id
			 INNER JOIN sFee.tFeeHeadMaster FH ON FH.Id = F.FeeHeadId
			 
			 WHERE R.SourceId  = @rAcademicId AND Month(S.StartDate) = @rMonthId 
			  AND ((R.ReceiptBookId=@rReceiptBookId) OR (@rReceiptBookId=0))
			  AND FH.Number <> 100
			  	
			 ) R
			 
			 		)  ORDER BY R.ReceiptDate, R.ReceiptNumber
			
    RETURN ISNULL(@rReceiptDetail,'') 
END

GO
ALTER PROCEDURE [sStudent].[pStudentOtherAdd]
	 
		   @rStudentId bigint,
           @rUIDNumber nvarchar(50),
           @rDateOfBirth date,
           @rGenderId tinyint,
           @rReligionId int,     
           @rBloodGroupId tinyint,
           @rBirthPlace nvarchar(150),
           @rBirthTaluka nvarchar(150),
           @rBirthDistrict nvarchar(150),
           @rBirthdateInWord nvarchar(150),     
           @rFamilyCast nvarchar(100),
           @rFamilySubCast nvarchar(100),
           @rCastCategoryId smallint,
           @rTransportModeId int,
           --@rPickupBusRouteDestinationId int,
           --@rDropBusRouteDestinationId int,
           --@rBusRouteId int,
           --@rPickuppointId int,
           @rHouseId int,
           @rMotherTongueId int,
           @rNationalityId int,
           @rHobby nvarchar(50),           
           --@rFeeCategoryId int,      
           --@rResideModeId int,
           @rCreatedUserId bigint,
           @rUpdatedUserId bigint,
           @rReferenceRemark nvarchar(100) = '',
           @rAdhaarNumber nvarchar(20) = ''
 
	
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
	BEGIN TRY
	
	 BEGIN TRANSACTION	
IF NOT EXISTS(SELECT * FROM sStudent.tStudentOther WHERE StudentId = @rStudentId)
BEGIN

	--DECLARE @lNextNumber BIGINT     
	--EXECUTE sCommon.pTableRowIdGet 0, 'sStudent.tStudentCommunication', 0, 1, 1,@outNextNumber = @lNextNumber output


	INSERT INTO sStudent.tStudentOther
		(
		   StudentId,
           UIDNumber,
           DateOfBirth,
           GenderId,
           ReligionId,
           BloodGroupId,
           BirthPlace,
           BirthTaluka,
           BirthDistrict,
           BirthdateInWord,
      
           FamilyCast,
           FamilySubCast,
           CastCategoryId,
           TransportModeId,
           --PickupBusRouteDestinationId,
           --DropBusRouteDestinationId,
           --BusRouteId,
           --PickuppointId,
           HouseId,
           MotherTongueId,
           NationalityId,
           Hobby,
           ReferenceRemark,
           --FeeCategoryId,
         
           --ResideModeId,
           CreatedUserId,
           CreatedDateTime,
           AdhaarNumber 
		)
	VALUES
		(
		   @rStudentId,
           @rUIDNumber,
           @rDateOfBirth,
           @rGenderId,
           @rReligionId,
           @rBloodGroupId,
           @rBirthPlace,
           @rBirthTaluka,
           @rBirthDistrict,
           @rBirthdateInWord,
       
           @rFamilyCast,
           @rFamilySubCast,
           @rCastCategoryId,
           @rTransportModeId,
           --@rPickupBusRouteDestinationId,
           --@rDropBusRouteDestinationId,
           --@rBusRouteId,
           --@rPickuppointId,
           @rHouseId,
           @rMotherTongueId,
           @rNationalityId,
           @rHobby,
           @rReferenceRemark,
           --@rFeeCategoryId,
        
           --@rResideModeId,
           @rCreatedUserId,
           sCommon.fGetDate(),
           @rAdhaarNumber
          	
		)
		SELECT 1 AS Result             
	END	 
	 ELSE
	 BEGIN
	 --SET @lNextNumber = @rStudentId
	    UPDATE  sStudent.tStudentOther
	    SET
			  StudentId = @rStudentId, 
			  UIDNumber = @rUIDNumber, 
			  DateOfBirth = @rDateOfBirth, 
			  GenderId = @rGenderId,
			  ReligionId = @rReligionId, 
		 
			  BloodGroupId = @rBloodGroupId, 
			  BirthPlace = @rBirthPlace, 
			  BirthTaluka = @rBirthTaluka,
			  BirthDistrict = @rBirthDistrict,
			  BirthdateInWord = @rBirthdateInWord, 
		 
			  FamilyCast = @rFamilyCast, 
			  FamilySubCast = @rFamilySubCast, 
			  CastCategoryId = @rCastCategoryId, 
			  TransportModeId = @rTransportModeId,
			  --PickupBusRouteDestinationId=@rPickupBusRouteDestinationId,
			  --DropBusRouteDestinationId=@rDropBusRouteDestinationId,
			  --BusRouteId = @rBusRouteId,
			  --PickuppointId = @rPickuppointId, 
			  HouseId = @rHouseId, 
			  MotherTongueId=@rMotherTongueId,
			  NationalityId=@rNationalityId,
			  Hobby=@rHobby,
			  ReferenceRemark=@rReferenceRemark,
			  --FeeCategoryId = @rFeeCategoryId,
	 
			  --ResideModeId = @rResideModeId,
			  UpdatedUserId = @rUpdatedUserId, 
			  UpdatedDateTime =sCommon.fGetDate(),
			  AdhaarNumber=@rAdhaarNumber

	      WHERE StudentId = @rStudentId 
	       
	 END  
		SELECT 11 AS Result
	
		COMMIT TRANSACTION
		
	END TRY
	
	BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH	
		
END


GO
ALTER PROCEDURE [sStudent].[pImportStudent]
    
    @rOrgGroupId smallint,
    @rOrgId smallint,
	@rXmldoc [nvarchar](max),
	@rUserId bigint,
	@rBatchId int

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

BEGIN
BEGIN TRY

DECLARE @lCount INT
DECLARE @lInterrorCode INT,
			@intDoc INT,
			@lCode nvarchar(20),
			@lNumber bigint,
			@lRegistrationNumber nvarchar(20),
			@lFirstName nvarchar(100),
			@lMiddleName nvarchar(100),
			@lLastName nvarchar(100),
			@lClassId int,
			@lClass nvarchar(100),
			@lDivisionId int,
			@lDivision nvarchar(100),
			@lRollNo int,
			@lAdmissionStandardId int,
			@lAdmissionStandard nvarchar(100),
			@lAdmissionDate date,
			@lPreviousSchool nvarchar(200),
			@lDOB date,
			@lGenderId int = 0,
			@lGender nvarchar(10),
			@lBloodGroupId int = 0,
			@lBloodGroup nvarchar(10),
			@lAdmissionCategoryId int,
			--@lAdmissionCategory nvarchar(100),
			@lReligionId int,
			@lReligion nvarchar(100),
			@lFamilyCast nvarchar(100),
			@lFamilySubCast nvarchar(100),
			@lCastCategoryId int = 0,
			@lCastCategory nvarchar(100),
			@lParentContactNo nvarchar(20),
			@lParentEmail nvarchar(50),
			@lStudentContactNo nvarchar(20),
			@lStudentEmail nvarchar(50),
			@lPresentStreetOne nvarchar(50),
			@lPresentStreetTwo nvarchar(50),
			@lPresentStreetThree nvarchar(50),
			@lPresentCity nvarchar(50),
			@lPresentZipCode nvarchar(20),
			@lPresentState nvarchar(50),
			@lPresentCountry nvarchar(50),
			@lPresentContactNo nvarchar(20),
			--@lEmergencyStreetOne nvarchar(50),
			--@lEmergencyStreetTwo nvarchar(50),
			--@lEmergencyStreetThree nvarchar(50),
			--@lEmergencyCountry nvarchar(50),
			--@lEmergencyState nvarchar(50),
			--@lEmergencyCity nvarchar(50),
			--@lEmergencyZipCode nvarchar(20),
			@lEmergencyContactNo nvarchar(20),
			@lBirthPlace nvarchar(150),
			@lBirthTaluka nvarchar(150),
			@lBirthDistrict nvarchar(150),
			@lBirthDateInWord nvarchar(150)
			
DECLARE @EmployeeId bigint			
--DECLARE @OutClassId int
--DECLARE @OutDivisionId int
DECLARE @OutStudentId bigint 
--DECLARE @OutAdmissionStandardId int
--DECLARE @OutReligionId int
--DECLARE @OutAdmissionCategoryId int		
SET @lAdmissionCategoryId=sCommon.fGetIntIdForOrg(1,@rOrgGroupId)	
			
EXECUTE @lInterrorCode = sp_xml_preparedocument @intDoc OUTPUT, @rXmlDoc
	
	CREATE TABLE #tTable  
	(
		Id bigint identity,	 
		Code nvarchar(20),
		Number bigint,
		RegistrationNumber nvarchar(20),
		FirstName nvarchar(100),
		MiddleName nvarchar(100),
		LastName nvarchar(100),
		ClassId int null,
		Class nvarchar(100),
		DivisionId int null,
		Division nvarchar(100),
		RollNo int,
		AdmissionStandardId int,
		AdmissionStandard nvarchar(100),
		AdmissionDate date,
		PreviousSchool nvarchar(200),
		DOB date,
		GenderId int null,
		Gender nvarchar(10),
		BloodGroupId int null,
		BloodGroup nvarchar(10),
		ReligionId int,
		Religion nvarchar(100),
		--AdmissionCategoryId int,
		--AdmissionCategory nvarchar(100),
		FamilyCast nvarchar(100),
		FamilySubCast nvarchar(100),
		CastCategoryId int,
		CastCategory nvarchar(100),
		ParentContactNo nvarchar(20),
		ParentEmail nvarchar(50),
		StudentContactNo nvarchar(20),
		StudentEmail nvarchar(50),
		PresentStreetOne nvarchar(50),
		PresentStreetTwo nvarchar(50),
		PresentStreetThree nvarchar(50),
		PresentCity nvarchar(50),
		PresentZipCode nvarchar(20),
		PresentState nvarchar(50),
		PresentCountry nvarchar(50),
		PresentContactNo nvarchar(20),
		--EmergencyStreetOne nvarchar(50),
		--EmergencyStreetTwo nvarchar(50),
		--EmergencyStreetThree nvarchar(50),
		--EmergencyCountry nvarchar(50),
		--EmergencyState nvarchar(50),
		--EmergencyCity nvarchar(50),
		--EmergencyZipCode nvarchar(20),
		EmergencyContactNo nvarchar(20),
		BirthPlace nvarchar(150),
		BirthTaluka nvarchar(150),
		BirthDistrict nvarchar(150),
		BirthDateInWord nvarchar(150),
		CreatedUserId bigint
	)
		
	INSERT INTO #tTable
	(
		Code,
		Number,
		RegistrationNumber,
		FirstName,
		MiddleName,
		LastName,
		ClassId,
		Class,
		DivisionId,
		Division,
		RollNo,
		AdmissionStandardId,
		AdmissionStandard,
		AdmissionDate,
		PreviousSchool,
		DOB,
		GenderId,
		Gender,
		BloodGroupId,
		BloodGroup,
		ReligionId,
		Religion,
		--AdmissionCategoryId,
		--AdmissionCategory,
		FamilyCast,
		FamilySubCast,
		CastCategoryId,
		CastCategory,
		ParentContactNo,
		ParentEmail,
		StudentContactNo,
		StudentEmail,
		PresentStreetOne,
		PresentStreetTwo,
		PresentStreetThree,
		PresentCity,
		PresentZipCode,
		PresentState,
		PresentCountry,
		PresentContactNo,
		--EmergencyStreetOne,
		--EmergencyStreetTwo,
		--EmergencyStreetThree,
		--EmergencyCountry,
		--EmergencyState,
		--EmergencyCity,
		--EmergencyZipCode,
		EmergencyContactNo,
		BirthPlace,
		BirthTaluka,
		BirthDistrict,
		BirthDateInWord,
		CreatedUserId			
	)
	SELECT
	 
		Code,
		Number,
		RegistrationNumber,
		FirstName,
		MiddleName,
		LastName,
		0,
		Class,
		0,
		Division,
		RollNo,
		0,
		AdmissionStandard,
		AdmissionDate,
		PreviousSchool,
		DOB,
		0,
		Gender,
		0,
		BloodGroup,
		0,
		Religion,
		--0,
		--AdmissionCategory,
		FamilyCast,
		FamilySubCast,
		0,
		CastCategory,
		ParentContactNo,
		ParentEmail,
		StudentContactNo,
		StudentEmail,
		PresentStreetOne,
		PresentStreetTwo,
		PresentStreetThree,
		PresentCity,
		PresentZipCode,
		PresentState,
		PresentCountry,
		PresentContactNo,
		--EmergencyStreetOne,
		--EmergencyStreetTwo,
		--EmergencyStreetThree,
		--EmergencyCountry,
		--EmergencyState,
		--EmergencyCity,
		--EmergencyZipCode,
		EmergencyContactNo,
		BirthPlace,
		BirthTaluka,
		BirthDistrict,
		BirthDateInWord,
		CreatedUserId	
	FROM OPENXML (@intDoc, '/Table/ImportStudent', 1)
	WITH
	( 
		Code nvarchar(20) '@Code',
		Number bigint '@Number',
		RegistrationNumber nvarchar(20) '@RegistrationNumber',
		FirstName nvarchar(100) '@FirstName',
		MiddleName nvarchar(100) '@MiddleName',
		LastName nvarchar(100) '@LastName',
		Class nvarchar(100) '@Class',
		Division nvarchar(100) '@Division',
		RollNo int '@RollNo',
		AdmissionStandard nvarchar(100) '@AdmissionStandard',
		AdmissionDate date '@AdmissionDate',
		PreviousSchool nvarchar(200) '@PreviousSchool',
		DOB date '@DOB',
		Gender nvarchar(10) '@Gender',
		BloodGroup nvarchar(10) '@BloodGroup', 			
		Religion nvarchar(100) '@Religion',
		--AdmissionCategory nvarchar(100) '@AdmissionCategory',
		FamilyCast nvarchar(100) '@FamilyCast',
		FamilySubCast nvarchar(100) '@FamilySubCast',
		CastCategory nvarchar(100) '@CastCategory',
		ParentContactNo nvarchar(20) '@ParentContactNo',
		ParentEmail nvarchar(50) '@ParentEmail',
		StudentContactNo nvarchar(20) '@StudentContactNo',
		StudentEmail nvarchar(50) '@StudentEmail',
		PresentStreetOne nvarchar(50) '@PresentStreetOne',
		PresentStreetTwo nvarchar(50) '@PresentStreetTwo',
		PresentStreetThree nvarchar(50) '@PresentStreetThree',
		PresentCity nvarchar(50) '@PresentCity',
		PresentZipCode nvarchar(20) '@PresentZipCode',
		PresentState nvarchar(50) '@PresentState',
		PresentCountry nvarchar(50) '@PresentCountry',
		PresentContactNo nvarchar(20) '@PresentContactNo',
		--EmergencyStreetOne nvarchar(50) '@EmergencyStreetOne',
		--EmergencyStreetTwo nvarchar(50) '@EmergencyStreetTwo',
		--EmergencyStreetThree nvarchar(50) '@EmergencyStreetThree',
		--EmergencyCountry nvarchar(50) '@EmergencyCountry',
		--EmergencyState nvarchar(50) '@EmergencyState',
		--EmergencyCity nvarchar(50) '@EmergencyCity',
		--EmergencyZipCode nvarchar(20) '@EmergencyZipCode',
		EmergencyContactNo nvarchar(20) '@EmergencyContactNo',
		 
		BirthPlace nvarchar(150) '@BirthPlace',
		BirthTaluka nvarchar(150) '@BirthTaluka',
		BirthDistrict nvarchar(150) '@BirthDistrict',		
		BirthDateInWord nvarchar(150) '@BirthDateInWord',
		CreatedUserId bigint '@CreatedUserId'
	)
	
	--print 'check'
 
EXEC sp_xml_removedocument @intDoc
			
SELECT @lCount =  COUNT(Id) FROM #tTable

WHILE @lCount > 0 

BEGIN
		SET @lCode = ''
		SET @lNumber = null
		SET @lRegistrationNumber = null
		SET @lFirstName = ''
		SET @lMiddleName = ''
		SET @lLastName = ''
		SET @lClassId = null
		SET @lClass = ''
		SET @lDivisionId = null
		SET @lDivision = ''
		SET @lRollNo = null
		SET @lAdmissionStandardId = null
		SET @lAdmissionStandard = ''
		SET @lAdmissionDate  = ''
		SET @lPreviousSchool = ''
		SET @lDOB = ''
		SET @lGenderId = null
		SET @lGender = ''
		SET @lBloodGroupId = null
		SET @lBloodGroup = ''
		SET @lReligionId  = null
		SET @lReligion = ''
		--SET @lAdmissionCategoryId  = null
		--SET @lAdmissionCategory = ''
		SET @lFamilyCast = ''
		SET @lFamilySubCast = ''
		SET @lCastCategoryId  = null
		SET @lCastCategory = ''
		SET @lParentContactNo = ''
		SET @lParentEmail = ''
		SET @lStudentContactNo = ''
		SET @lStudentEmail = ''
		SET @lPresentStreetOne = ''
		SET @lPresentStreetTwo = ''
		SET @lPresentStreetThree = ''
		SET @lPresentCity = ''
		SET @lPresentZipCode = ''
		SET @lPresentState = ''
		SET @lPresentCountry = ''
		SET @lPresentContactNo = ''
		--SET @lEmergencyStreetOne = ''
		--SET @lEmergencyStreetTwo = ''
		--SET @lEmergencyStreetThree = ''
		--SET @lEmergencyCountry = ''
		--SET @lEmergencyState = ''
		--SET @lEmergencyCity = ''
		--SET @lEmergencyZipCode = ''
		SET @lEmergencyContactNo = ''
		SET @lBirthPlace = ''
		SET @lBirthTaluka = ''
		SET @lBirthDistrict = ''
		SET @lBirthDateInWord = ''
		--SET @OutClassId = null
		--SET @OutDivisionId = null
		SET @OutStudentId = null
		--SET @OutAdmissionStandardId = null
		--SET @OutReligionId = null
		
SELECT		
		@lCode = Code,
		@lNumber=Number,
		@lRegistrationNumber = RegistrationNumber,
		@lFirstName = FirstName,
		@lMiddleName = MiddleName,
		@lLastName = LastName,
		@lClass = Class,
		@lDivision = Division,
		@lRollNo = RollNo,
		@lAdmissionStandard = AdmissionStandard,
		@lAdmissionDate  = CASE WHEN AdmissionDate = '' THEN  NULL ELSE AdmissionDate END,
		@lPreviousSchool = PreviousSchool,
		@lReligion = Religion,
		--@lAdmissionCategory = AdmissionCategory,
		@lFamilyCast = FamilyCast,
		@lFamilySubCast = FamilySubCast,
		@lCastCategory = CastCategory,
		@lDOB = CASE WHEN DOB = '' THEN  NULL ELSE DOB END,
		@lGender = Gender,
		@lBloodGroup = BloodGroup,
		@lParentContactNo = ParentContactNo,
		@lParentEmail = ParentEmail,
		@lStudentContactNo = StudentContactNo,
		@lStudentEmail = StudentEmail,
		@lPresentStreetOne = PresentStreetOne,
		@lPresentStreetTwo = PresentStreetTwo,
		@lPresentStreetThree = PresentStreetThree,
		@lPresentCountry = PresentCountry,
		@lPresentState = PresentState,
		@lPresentCity = PresentCity,
		@lPresentZipCode = PresentZipCode,
		@lPresentContactNo = PresentContactNo,
		--@lEmergencyStreetOne = EmergencyStreetOne,
		--@lEmergencyStreetTwo = EmergencyStreetTwo,
		--@lEmergencyStreetThree = EmergencyStreetThree,
		--@lEmergencyCountry = EmergencyCountry,
		--@lEmergencyState = EmergencyState,
		--@lEmergencyCity = EmergencyCity,
		--@lEmergencyZipCode = EmergencyZipCode,
		@lEmergencyContactNo = EmergencyContactNo,		 
		@lBirthPlace = BirthPlace,
		@lBirthTaluka = BirthTaluka,
		@lBirthDistrict = BirthDistrict,
		@lBirthDateInWord = BirthDateInWord
											 	 
FROM #tTable WHERE Id = @lCount  

	SELECT	@lGenderId = ID FROM sCommon.tGenderMaster WHERE Title = @lGender
	SELECT	@lClassId  = ID FROM sSetup.tClassMaster WHERE Title = @lClass AND OrgId = @rOrgId
	SELECT	@lDivisionId  = ID FROM sSetup.tDivisionMaster WHERE Title = @lDivision AND ClassId=@lClassId AND OrgId = @rOrgId
	SELECT	@lBloodGroupId  = ID FROM sCommon.tBloodGroupMaster WHERE Title = @lBloodGroup
	SELECT	@lAdmissionStandardId  = ID FROM sSetup.tClassMaster WHERE Title = @lAdmissionStandard AND OrgId = @rOrgId
	SELECT	@lReligionId  = ID FROM sCommon.tReligionMaster WHERE Title = @lReligion AND OrgGroupId = @rOrgGroupId
	--SELECT	@lAdmissionCategoryId  = ID FROM tAdmissionCategoryMaster WHERE Title = @lAdmissionCategory AND OrgGroupId = @rOrgGroupId
	SELECT	@lCastCategoryId  = ID FROM sCommon.tCastCategoryMaster WHERE Title = @lCastCategory
	
	IF(@lGenderId IS NULL)
		SET @lGenderId = 0
	IF(@lBloodGroupId IS NULL)
		SET	@lBloodGroupId = 0
	IF(@lCastCategoryId IS NULL)
		SET	@lCastCategoryId = 0
		
--IF 	@lClassId IS NULL  AND @lClass != ''
--BEGIN
--	EXEC [sSetup].[pClassMasterAdd] 0,@lClass,@lClass,@lClass,@lClass,1,0,0,0,@rUserId,@rUserId,@rOrgId,@OutClassId OUTPUT
-- END
--ELSE
--BEGIN
--SET @OutClassId = @lClassId
--END

--IF 	@lAdmissionStandardId IS NULL  AND @lAdmissionStandard != ''
--BEGIN
--	EXEC [sSetup].[pClassMasterAdd] 0,@lAdmissionStandard,@lAdmissionStandard,@lAdmissionStandard,@lAdmissionStandard,1,0,0,0,@rUserId,@rUserId,@rOrgId,0,@OutAdmissionStandardId OUTPUT
-- END
--ELSE
--BEGIN
--SET @OutAdmissionStandardId = @lAdmissionStandardId
--END

--IF 	@lDivisionId IS NULL  AND @lDivision != ''
--BEGIN
--	SET @EmployeeId = sCommon.fGetBigIntIdForOrg(1,@rOrgId)
--	EXEC [sSetup].[pDivisionMasterAdd] 0,@lDivision,@lDivision,@lDivision,@lClassId,@EmployeeId,NULL,0,@rUserId,@rUserId,0,@rOrgId,@OutDivisionId OUTPUT
-- END
--ELSE
--BEGIN
--SET @OutDivisionId = @lDivisionId
--END

--IF 	@lReligionId IS NULL  AND @lReligion != ''
--BEGIN
--	EXEC [sCommon].[pReligionMasterAdd] 0,@lReligion,@lReligion,0,@rUserId,@rOrgId,@OutReligionId OUTPUT
-- END
--ELSE
--BEGIN
--SET @OutReligionId = @lReligionId
--END

--IF 	@lAdmissionCategoryId IS NULL  AND @lAdmissionCategory != ''
--BEGIN
--	EXEC [sStudent].[pAdmissionCategoryMasterAdd] 0,@lAdmissionCategory,@lAdmissionCategory,0,@rUserId,@rOrgId,@OutAdmissionCategoryId OUTPUT
-- END
--ELSE
--BEGIN
--SET @OutAdmissionCategoryId = @lAdmissionCategoryId
--END

BEGIN TRANSACTION

EXEC sStudent.pStudentMasterAdd 0,@lCode,@lRegistrationNumber,@lNumber,@lFirstName,@lMiddleName,@lLastName,@rOrgId,@lAdmissionCategoryId,1,502,@rUserId,@rUserId,@OutStudentId OUTPUT

EXEC sStudent.pAcademicAdd 0,@OutStudentId,@lClassId,@lDivisionId,@lRollNo,@rOrgId,@rBatchId,@rUserId,@rUserId

EXEC sStudent.pStudentCommunicationAdd @OutStudentId,@lPresentStreetOne,@lPresentStreetTwo,@lPresentStreetThree,
			@lPresentCountry,@lPresentState,@lPresentCity,@lPresentZipCode,@lPresentContactNo,NULL,NULL,NULL,NULL,NULL,
			NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,@lEmergencyContactNo,@lParentContactNo,@lParentEmail,
			@lStudentContactNo,@lStudentEmail,@rUserId,@rUserId

EXEC sStudent.pStudentOtherAdd @OutStudentId,NULL,@lDOB,@lGenderId,@lReligionId,@lBloodGroupId,@lBirthPlace,@lBirthTaluka,@lBirthDistrict,@lBirthDateInWord,
			@lFamilyCast,@lFamilySubCast,@lCastCategoryId,NULL,NULL,NULL,NULL,'','',@rUserId,@rUserId,''
  
EXEC [sStudent].[pStudentAdmissionAdd] @OutStudentId,@lPreviousSchool,NULL,NULL,NULL,NULL,
			@lAdmissionStandardId,@lAdmissionDate,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
			NULL,NULL,NULL,NULL,NULL,@rBatchId,@rUserId,@rUserId,1

		SET @lCount = @lCount - 1	
		COMMIT TRANSACTION			
END


	SELECT 1
	END TRY
	BEGIN CATCH
	ROLLBACK TRANSACTION
	SELECT ERROR_MESSAGE(), ERROR_LINE()
	END CATCH
	
END


GO

ALTER PROCEDURE [sAttendance].[pDashletAttendanceStatusGet]	 	 
	   @rAcademicId bigint
	  
	  
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN

DECLARE 
@lTotalAttendance bigint,
@lTotalAbsent bigint,
@lAbsentReason nvarchar(100),
@lAttendanceDate date
 

select @lTotalAttendance = ISNULL(COUNT(AcademicId),0) FROM sAttendance.tStudentRegister SR  
WHERE AcademicId = @rAcademicId  AND SR.AttendanceTypeId = 1
AND SR.IsPresent = 1 



select @lTotalAbsent = ISNULL(COUNT(AcademicId),0) FROM sAttendance.tStudentRegister SR  
WHERE AcademicId = @rAcademicId AND SR.AttendanceTypeId = 1
AND IsPresent = 0

SELECT TOP 1 @lAttendanceDate = AttendanceDate,@lAbsentReason = AR.Title  FROM sAttendance.tStudentRegister SR 
LEFT JOIN sAttendance.tAbsentReasonMaster AR
ON SR.AbsentReasonId = AR.Id 
WHERE SR.AcademicId = @rAcademicId AND SR.AttendanceTypeId = 1
AND IsPresent = 0 ORDER BY SR.AttendanceDate DESC
 
  SELECT
  @lTotalAttendance AS TotalAttendance,
  @lTotalAbsent AS TotalAbsent,
  ISNULL(@lAttendanceDate,'') AS AttendanceDate,
  ISNULL(@lAbsentReason,'') AS AbsentReason
 
END

GO
ALTER PROCEDURE [sAttendance].[pMonthlyRegisterImportAll]
	 	 
	 	 @rBatchId int,
	 	 @rForMonth tinyint,
	 	 @rForYear smallint,
	 	 @rAttendanceTypeId tinyint,
	 	 @rSubjectId int,
	 	 @rCreatedUserId bigint,
	 	 @rClassId int=0,
	 	 @rDivisionId int=0
	 	 
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
	
	BEGIN TRY
	
	BEGIN TRANSACTION

	DELETE MR
	 
		FROM sAttendance.tMonthlyRegister MR 
		INNER JOIN sStudent.tAcademic A ON MR.AcademicId = A.Id
		
		WHERE  
		MR.ForMonth =  @rForMonth
		AND MR.ForYear =  @rForYear	
		AND MR.AttendanceTypeId = @rAttendanceTypeId
		AND	MR.SubjectId=@rSubjectId
		AND A.BatchId = @rBatchId
		AND ((A.ClassId = @rClassId) OR (@rClassId = 0))
		AND ((A.DivisionId = @rDivisionId) OR (@rDivisionId = 0))

	INSERT INTO sAttendance.tMonthlyRegister
	(
		AcademicId,
		AttendanceTypeId,
		SubjectId,
		ForMonth,
		ForYear,
		TotalCount,
		AttendanceCount,
		PresentCount,
		AbsentCount,
		AbsentDays,
		UpdatedUserId,
		UpdatedDateTime
	)
	Select 
		SN.AcademicId,
		@rAttendanceTypeId,
		@rSubjectId,
		MY.MonthNumber AS ForMonth, 
		MY.YearNumber AS ForYear,
		CASE 
			WHEN SS.WorkingDays IS NOT NULL
			THEN ISNULL(SS.WorkingDays,0)
			ELSE ISNULL(MS.WorkingDays,0) END AS TotalCount,
	
			ISNULL(TC.TCount,0) AS AttendanceCount,
			(ISNULL(TC.TCount,0) - ISNULL(AC.ACount,0)) AS PresentCount,
		     ISNULL(AC.ACount,0) As  AbsentCount,

		'' AS AbsentDays,
		@rCreatedUserId,
		sCommon.fGetDate()
		

		
	From 
		sStudent.vStudentName SN 
	LEFT JOIN sSetup.fMonthYearForBatchGET(@rBatchId) AS MY ON MY.BatchId=SN.BatchId
		AND MY.MonthNumber=@rForMonth AND MY.YearNumber=@rForYear
	LEFT JOIN sAttendance.tMonthlySetup MS ON MS.ClassId=SN.ClassId
		AND MS.ForMonth=MY.MonthNumber AND MS.ForYear=MY.YearNumber
	LEFT JOIN sAttendance.tStudentSetup SS ON SS.AcademicId=SN.AcademicId
		AND SS.ForMonth=MY.MonthNumber AND SS.ForYear=MY.YearNumber
	LEFT JOIN
	(
		Select A.Id AS AcademicId, COUNT(1) AS TCount 
		   FROM sAttendance.tStudentRegister SR1
		   INNER JOIN sStudent.tAcademic A ON SR1.AcademicId = A.Id 
		   WHERE 		
			SR1.AttendanceTypeId=@rAttendanceTypeId
			AND ((SR1.SubjectId=@rSubjectId) OR (ISNULL(@rSubjectId,0)=0))
			AND (DATEPART(MONTH,SR1.AttendanceDate)=@rForMonth  )
			AND (DATENAME(YEAR,SR1.AttendanceDate)=@rForYear ) 		 
			AND (A.BatchId=@rBatchId)			 
			AND ((A.ClassId = @rClassId) OR (@rClassId = 0))
			AND ((A.DivisionId = @rDivisionId) OR (@rDivisionId = 0))
			GROUP BY A.Id
    ) TC ON TC.AcademicId = SN.AcademicId
    LEFT JOIN
	(
		Select A.Id AS AcademicId, COUNT(1) AS ACount  
		   FROM sAttendance.tStudentRegister SR1
		   INNER JOIN sStudent.tAcademic A ON SR1.AcademicId = A.Id 
		   WHERE 
		   SR1.IsPresent = 0		
			AND SR1.AttendanceTypeId=@rAttendanceTypeId
			AND ((SR1.SubjectId=@rSubjectId) OR (ISNULL(@rSubjectId,0)=0))
			AND (DATEPART(MONTH,SR1.AttendanceDate)=@rForMonth  )
			AND (DATENAME(YEAR,SR1.AttendanceDate)=@rForYear ) 		 
			AND (A.BatchId=@rBatchId)			 
			AND ((A.ClassId = @rClassId) OR (@rClassId = 0))
			AND ((A.DivisionId = @rDivisionId) OR (@rDivisionId = 0))
			GROUP BY A.Id
    ) AC ON AC.AcademicId = SN.AcademicId
	WHERE
	(MY.BatchId=@rBatchId) 
	 AND SN.BatchId = @rBatchId
	 AND ((SN.ClassId = @rClassId) OR (@rClassId = 0))
	 AND ((SN.DivisionId = @rDivisionId) OR (@rDivisionId = 0))
	
	UPDATE MR
	SET 
		MR.AveragePresence = (convert(decimal(5,2),(convert(decimal(5,2),PresentCount)*100)/(convert(decimal(5,2),AttendanceCount))))
 		FROM sAttendance.tMonthlyRegister MR 
		INNER JOIN sStudent.tAcademic A ON MR.AcademicId = A.Id		
		WHERE 
		MR.ForMonth =  @rForMonth
		AND MR.ForYear =  @rForYear	
		AND MR.AttendanceTypeId = @rAttendanceTypeId
		AND	MR.SubjectId=@rSubjectId
		AND A.BatchId = @rBatchId
		AND ((A.ClassId = @rClassId) OR (@rClassId = 0))
		AND ((A.DivisionId = @rDivisionId) OR (@rDivisionId = 0))
		AND ISNULL(MR.AttendanceCount,0) <> 0
		
		UPDATE MR
	SET 		 
		AbsentDays = [sAttendance].[fAbsentDaysGet](@rForMonth,@rForYear, @rAttendanceTypeId,@rSubjectId,MR.AcademicId )
		FROM sAttendance.tMonthlyRegister MR 
		INNER JOIN sStudent.tAcademic A ON MR.AcademicId = A.Id
		
		WHERE  MR.AbsentCount > 0
		AND MR.ForMonth =  @rForMonth
		AND MR.ForYear =  @rForYear	
		AND MR.AttendanceTypeId = @rAttendanceTypeId
		AND	MR.SubjectId=@rSubjectId
		AND A.BatchId = @rBatchId
		AND ((A.ClassId = @rClassId) OR (@rClassId = 0))
		AND ((A.DivisionId = @rDivisionId) OR (@rDivisionId = 0))
	
	SELECT 1 AS Result

	COMMIT TRANSACTION
		
	END TRY
	
	BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH	
END
	GO	
	
	ALTER PROCEDURE [sAttendance].[pDeleteAttendance]
	 
		@rAttendanceTypeId int=0,	 
		@rClassId int=0,
		@rDivisionId int=0,	
		@rSubjectId int=0,
		@rAcademicId bigint,
		@rBatchId int=0,	 
		@rOrgId int=0,
		@rOrgGroupBatchId int=0,
		@rStatusId int=0, 
	    @rCode nvarchar(20)='',
	    @rDeleteType nvarchar(20)='',
	    @rFromAttendanceDate Date ='',
	    @rToAttendanceDate Date ='',
	    @rRepeatCount tinyint=0
	    
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

BEGIN 	

IF (@rDeleteType = 'DayRegister')

BEGIN

DELETE sAttendance.tStudentRegister
		FROM sAttendance.tStudentRegister SR
		INNER JOIN sStudent.vStudentAcademic vSA 
		ON vSA.AcademicId=SR.AcademicId
	WHERE 
		SR.AttendanceTypeId=@rAttendanceTypeId
		AND ((SR.AttendanceDate >= @rFromAttendanceDate))
		AND ((SR.AttendanceDate <= @rToAttendanceDate))
		AND ((vSA.ClassId=@rClassId) OR (@rClassId=0))
		AND ((vSA.DivisionId=@rDivisionId) OR (@rDivisionId=0))
		AND ((SR.SubjectId=@rSubjectId) OR (@rSubjectId=0))
		AND ((SR.AcademicId=@rAcademicId) OR (@rAcademicId=0))
		AND ((SR.RepeatCount=@rRepeatCount) OR (@rRepeatCount=0))
		AND vSA.BatchId=@rBatchId
END

IF(@rDeleteType = 'MonthlyRegister')

BEGIN

DELETE sAttendance.tMonthlyRegister
		FROM sAttendance.tMonthlyRegister MR
		INNER JOIN sStudent.vStudentAcademic vSA 
		ON vSA.AcademicId=MR.AcademicId
	WHERE 
		MR.AttendanceTypeId=@rAttendanceTypeId
		AND ((vSA.ClassId=@rClassId) OR (@rClassId=0))
		AND ((vSA.DivisionId=@rDivisionId) OR (@rDivisionId=0))
		AND ((MR.SubjectId=@rSubjectId) OR (@rSubjectId=0))
		AND ((MR.AcademicId=@rAcademicId) OR (@rAcademicId=0))
		AND vSA.BatchId=@rBatchId
		
		
		
		DELETE sAttendance.tYearlyRegister
		FROM sAttendance.tYearlyRegister MR
		INNER JOIN sStudent.vStudentAcademic vSA 
		ON vSA.AcademicId=MR.AcademicId
	WHERE 
		 
		  ((vSA.ClassId=@rClassId) OR (@rClassId=0))
		AND ((vSA.DivisionId=@rDivisionId) OR (@rDivisionId=0))		 
		AND ((MR.AcademicId=@rAcademicId) OR (@rAcademicId=0))
		AND vSA.BatchId=@rBatchId
END

IF(@rDeleteType = 'StudentSetup')

BEGIN

DELETE sAttendance.tStudentSetup
		FROM sAttendance.tStudentSetup SS
		INNER JOIN sStudent.vStudentAcademic vSA 
		ON vSA.AcademicId=SS.AcademicId
	WHERE 
	    (vSA.ClassId=@rClassId) OR (@rClassId=0)
		AND ((vSA.DivisionId=@rDivisionId) OR (@rDivisionId=0))
		AND ((SS.AcademicId=@rAcademicId) OR (@rAcademicId=0))
		AND vSA.BatchId=@rBatchId
END

SELECT 1 AS Result

END

GO
ALTER PROCEDURE [sAssessment].[pExamCombineMarkEntryGet]
	
	 @rExamId int,
	 @rAcademicId bigint
	 
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

BEGIN 		

IF EXISTS (SELECT ID from sAssessment.tExamCombine where ExamId = @rExamId)
BEGIN
select  
(case WHEN A.Maxi = 0 then 0
else
ISNULL((A.Obtain*ED.Maximum)/A.Maxi,0) END) AS Obtain,


@rAcademicId AS AcademicId,
ED.Id AS ExamDetailID,
ED.ExamId,
CONVERT(bit, 1) AS  IsPresent
--ISNULL((select IsPresent from sAssessment.tResultDetail rd1 
--		   inner join sAssessment.tResult r1 on r1.Id=rd1.ResultId and r1.ExamId=@rExamId and r1.AcademicId=@rAcademicId
--		   and rd1.ExamDetailId=ED.Id ),0)  AS IsPresent

from
(SELECT 
Sum(Obtain*weightage/100) AS Obtain,Sum(Maximum*weightage/100) AS Maxi,RM.AcademicId,ED.ClassSubjectId
FROM sAssessment.tResult RM 
	INNER JOIN sAssessment.tResultDetail RD ON RD.ResultId=RM.Id
	INNER JOIN sAssessment.tExamDetail ED ON RD.ExamDetailId=ED.Id
	INNER JOIN sAssessment.tExamCombine EC ON ED.ExamId=EC.SubExamId
	INNER JOIN sSetup.vClassSubject V ON V.ClassSubjectId = Ed.ClassSubjectId  	
	WHERE EC.ExamId=@rExamId 
	
GROUP BY RM.AcademicId,ED.ClassSubjectId having RM.AcademicId=@rAcademicId) AS A
INNER JOIN sAssessment.tExamDetail ED ON ED.ClassSubjectId=A.ClassSubjectId
Where ED.ExamId =@rExamId
END
		
END

 