-- http://www.phpmyadmin.net
--




--
-- Database: `vida_lms`
--

DELIMITER $$
--
-- Procedures
--
CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spAcceptDecline`(
     in P_BatchId int,
     in P_StudentId int,
     in P_IsActive bit,
     in P_InstituteId int
 )
BEGIN
  DECLARE new_student_id INT DEFAULT 0;
  DECLARE l_UserId int default 0 ;
  DECLARE l_TeacherId int default 0;
  DECLARE l_new_student_Id int default 0; 
  
     if(P_IsActive=1)
     then 
           SET l_UserId = (SELECT UserId FROM tstudent WHERE id = P_StudentId);
           SET l_TeacherId = (SELECT t.UserId 
								FROM tbatch B
						                inner join tteacher t ON B.TeacherId = t.id  
					           WHERE  B.Id= P_BatchId );
                               
          if NOT exists(SELECT 1 FROM tbatch B
						        inner join tteacher t ON B.TeacherId = t.id  
				          inner join  tstudent s ON S.InstituteId = t.InstituteId
					  WHERE  B.Id= P_BatchId  AND s.Id = P_StudentId)
	   then
         if not exists(select 1 from tstudent where UserId = l_UserId and InstituteId = P_InstituteId)
		 then
         
              Insert into tstudent
                  (UserId,StatusId,InstituteId,CreatedUserId,CreatedDateTime)
		      Values
				  (l_UserId,11,P_InstituteId,l_TeacherId,now());   
                 
               --  SELECT Last_Insert_Id(); 
                  
		      SET new_student_id = Last_Insert_Id();
        
              Update 
                    tassignbatch
               set 
				   StatusId=11 ,
                   StudentId = new_student_id
			  where 
                   BatchId = P_BatchId
			  AND StudentId = P_StudentId;   
        else
           SET new_student_id = (select Id 
                            from tstudent
                            where UserId=l_UserId 
							  AND InstituteId = P_InstituteId);
          Update 
				tassignbatch
               set 
				   StatusId=11 ,
                   StudentId = new_student_id
			  where 
                   BatchId = P_BatchId
			  AND StudentId = P_StudentId;  
         end if;
      else     
             Update
                   tassignbatch
             set 
                  StatusId=11
			 where 
                   BatchId = P_BatchId 
			   AND StudentId = P_StudentId;
             Update 
                   tstudent
                set
                   CreatedUserId =l_TeacherId
               where     
                   Id = P_StudentId;
                   
             SET new_student_id = P_StudentId;
     end if;
             if exists(select 1 from tfeesetup where BatchId = P_BatchId)
             then
                 Insert into tfeesetup
                      
                      (TeacherId,BatchId,StudentId,FeeAmount,IsRecurringMonthly,DateOfFeeCollection,CreatedDateTime,CreatedUserId,DefaultFeeAmount,SetupDate)
				      (select TeacherId,BatchId,new_student_id,DefaultFeeAmount,IsRecurringMonthly,DateOfFeeCollection,now(),CreatedUserId,DefaultFeeAmount,SetupDate from tfeesetup where BatchId = P_BatchId LIMIT 1);
             end if;
              -- select TeacherId from tbatch where Id =P_BatchId;
     else
            Delete from  tassignbatch
            where BatchId = P_BatchId AND StudentId = P_StudentId ;
            SELECT 1 as result ,P_StudentId  as StudentId, Id as TeacherId from tteacher where  InstituteId = P_InstituteId;
     end if;  
    
    SELECT 1 as result ,new_student_id as StudentId , Id as TeacherId from tteacher where UserId = l_TeacherId and InstituteId = P_InstituteId;
  
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spAddBankDetails`(
	in P_AccountId int,
    in P_TeacherId int,
    in P_AccountHolderName varchar(200),
    in P_AccountNumber bigint(20), 
    in P_IFSCCode varchar(45),
    in P_EmailId varchar(100)
 )
BEGIN
	if P_AccountId=0 then
     if exists(select 1 from tbankaccountdetail where TeacherId = P_TeacherId)
     then 
         select -1;
     else    
       Start transaction;
       
       Insert into tbankaccountdetail
              (TeacherId,AccountHolderName,AccountNumber,IFSCCode,EmailId)
       values     
			  (P_TeacherId,P_AccountHolderName,P_AccountNumber,P_IFSCCode,P_EmailId);
              
          select 1;    
       commit;
       
       end if;
	elseif P_AccountId>0 then
		if exists(select 1 from tbankaccountdetail where TeacherId = P_TeacherId)
		then 
			UPDATE `vida_lms`.`tbankaccountdetail`
				SET
				`AccountHolderName` = P_AccountHolderName,
				`AccountNumber` = P_AccountNumber,
				`IFSCCode` = P_IFSCCode,
				`EmailId` = P_EmailId
				WHERE `Id` = P_AccountId;
                
			select 1;

		else
        select -2;
        end if;
    end if;

END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spAddContentAssign`(
    in P_ContentPath varchar(500),
    in P_ContentTitle varchar(100),
    in P_ContentLink varchar(500),
    in P_ContentTypeId int,
    in P_ContentId int,
    in P_Duration varchar(100),
    in P_BatchId int,
    in P_Remark varchar(100)
)
BEGIN
	-- DECLARE Exit HANDLER FOR SQLEXCEPTION
    -- begin
		-- Rollback;
        -- select -1;
	-- end;
    
  --  if not exists(select 1 from tteachercontent where TeacherContentId=P_ContentId and BatchId = P_BatchId)
  --  then
    
	if exists(select 1 from tteachercontent where Id=P_ContentId) then
		if P_ContentTypeId=5 then
			Start transaction;
            
              if not exists(select 1 from tteachercontentassign where TeacherContentId=P_ContentId and BatchId = P_BatchId)
              then
				 if exists (select 1 from tteachercontent where id=P_ContentId and ContentTypeId=5) then
                
					insert 
					into tteachercontentassign(TeacherContentId,BatchId,ContentPath,ContentTitle,ContentTypeId,CreatedDateTime,ContentLink,Duration)
					select 
						Id,P_BatchId,P_ContentPath,ContentTitle,ContentTypeId,now(),ContentLink,Duration
						from tteachercontent
                        where 
							 ContentPath in (select concat(ContentPath,'\\',Id,'\\',ContentTitle) from tteachercontent where id=P_ContentId)
                            and ContentTypeId in (6,7,8,20);
                 end if;
               
				insert 
					into tteachercontentassign(TeacherContentId,BatchId,Remark,ContentPath,ContentTitle,ContentTypeId,CreatedDateTime)
					values(P_ContentId, P_BatchId,P_Remark,P_ContentPath,P_ContentTitle,P_ContentTypeId,now());
                    
				select LAST_INSERT_ID() ;
                 else 
                  select -3;
                 end if; 
			commit;
		elseif P_ContentTypeId in(6,20) then
			Start transaction;
            if not exists(select 1 from tteachercontentassign where TeacherContentId=P_ContentId and BatchId = P_BatchId)
			then
				insert 
					into tteachercontentassign(TeacherContentId,BatchId,Remark,ContentPath,ContentTitle,ContentTypeId,CreatedDateTime)
					values(P_ContentId, P_BatchId,P_Remark,P_ContentPath,P_ContentTitle,P_ContentTypeId,now());
				select LAST_INSERT_ID();
                
               else 
                  select -4;
                 end if;   
			
			commit;
		else
			
		if not exists (select 1 from tteachercontentassign where ContentTitle=P_ContentTitle and ContentLink=P_ContentLink and ContentPath=P_ContentPath and TeacherContentId=P_ContentId )then
			 Start transaction;
			 insert 
				into tteachercontentassign(TeacherContentId,BatchId,Remark,ContentLink,ContentPath,ContentTitle,ContentTypeId,CreatedDateTime,Duration)
				values(P_ContentId, P_BatchId,P_Remark,P_ContentLink,P_ContentPath,P_ContentTitle,P_ContentTypeId,now(),P_Duration);
				select LAST_INSERT_ID();   
			commit;
			 else
			 select -2;
			 end if;
			
		end if;
	else
		select -1;
	end if;
	  --  else
       --   select -3;
 --  end if;
   
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spAddDocumentPath`(IN `P_TeacherId` INT, IN `P_ContentPath` VARCHAR(500), IN `P_ContentTitle` VARCHAR(100), IN `P_ContentLink` VARCHAR(500), IN `P_IsCommon` BIT, IN `P_Duration` VARCHAR(100), IN `P_ContentTypeId` INT)
BEGIN
	if exists(select 1 from tteacher where id=P_TeacherId) then
		
        if P_ContentTypeId in(5,6,20)  then 
		start transaction;
			insert 
				into tteachercontent(TeacherId,ContentTitle,ContentPath,ContentTypeId,CreatedDateTime,IsCommon)
				values(P_TeacherId,P_ContentTitle,P_ContentPath,P_ContentTypeId,now(),P_IsCommon);
			select LAST_INSERT_ID();
		commit;
        else
			
			start transaction;
				Insert into 
					tteacherContent(TeacherId,ContentLink,ContentPath,ContentTitle,ContentTypeId,CreatedDateTime,IsCommon,Duration)
					values(P_Teacherid,P_ContentLink,P_ContentPath,P_ContentTitle,P_ContentTypeId,now(),P_IsCommon,P_Duration);
				select LAST_INSERT_ID();
			commit;
		end if;
    else
		select -1;
	end if;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spAddEtest`(
	in P_Id int  ,
    in P_MainTestTypeId int,
    in P_SubTestTypeId int,
    in P_CorrectAnswerMarks decimal(8,2),
    in P_PenaltyMarks  decimal(8,2),
    in P_IsSubmitted bit,
    in P_Topic longtext,
    in P_Description longtext,
    in P_TestDuration int,
    in P_StartDate date,
    in P_StartTime time,
	in P_ExpiryDate date,
    in P_ExpiryTime time,
    in P_BatchId int,
    in P_SubCategoryId int,
    in P_CreatedUserId int,
    in P_IsEdit bit,
    
    in P_IsMultiple tinyint
    )
BEGIN
DECLARE D_MaxMarks DEC(8,2) DEFAULT 0.0;
IF(P_Id =0) THEN
	Start transaction;
		INSERT INTO `vida_lms`.`tetest`
		(
			`MainTestTypeId`,
			`SubTestTypeId`,
			`CorrectAnswerMarks`,
			`PenaltyMarks`,
			`BatchId`,
			`Description`,
			`IsSubmitted`,
			`SubCategoryId`,
			`CreatedUserId`,
			`CreatedDateTime`,
			`IsMultiple`
		)
		VALUES
		(
			P_MainTestTypeId ,
			P_SubTestTypeId ,
			P_CorrectAnswerMarks ,
			P_PenaltyMarks ,
			P_BatchId ,
			P_Description,
			P_IsSubmitted ,
			P_SubCategoryId ,
			P_CreatedUserId ,
			NOW(),
			 P_IsMultiple
		 );
		select LAST_INSERT_ID();
	commit;
	elseIF(P_Id >0 AND P_IsSubmitted = 1 AND P_IsEdit=0) 
		THEN 
		SELECT SUM(IFNULL(Marks,0.00)) INTO D_MaxMarks FROM tetestquestions WHERE TestId = P_Id;
		UPDATE `vida_lms`.`tetest`
			SET Topic = P_Topic,
				Description = P_Description ,
				Duration = P_TestDuration ,
				StartDate = P_StartDate ,
				StartTime = P_StartTime,
				ExpiryDate = P_ExpiryDate ,
				ExpiryTime = P_ExpiryTime ,
				IsSubmitted =P_IsSubmitted,
				IsMultiple =P_IsMultiple,
				MaxMarks = ifnull(D_MaxMarks,0.00),
                PenaltyMarks=P_PenaltyMarks
			WHERE Id = P_Id;
        select P_Id;
		ELSEIF (P_Id >0 AND P_IsSubmitted = 1 AND P_IsEdit = 1) THEN 
		UPDATE `vida_lms`.`tetest`
			SET Topic = P_Topic,
				Description = P_Description ,
				Duration = P_TestDuration ,
				StartDate = P_StartDate ,
				StartTime = P_StartTime,

				ExpiryDate = P_ExpiryDate ,
				ExpiryTime = P_ExpiryTime ,
				IsSubmitted =P_IsSubmitted,
				IsMultiple =P_IsMultiple,
                PenaltyMarks=P_PenaltyMarks,
				UpdatedUserId = P_CreatedUserId ,
				UpdatedDateTime = NOW()
		WHERE Id = P_Id;
        select P_Id;
	 END IF;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spAddEtestEvalute`(
	in P_TestId int,
    in P_StudentId int,
    in P_AnswerId int,
    in P_Marks decimal(8,2),
    in P_Remarks longtext,
    in P_TeacherEvaluatedFilePath longtext
    
)
BEGIN
declare V_ObtainMarks decimal(8,2) default 0;
	 update teteststudentanswer 
		set ScoredMarks=P_Marks,
			TeacherEvaluatedFilePath = P_TeacherEvaluatedFilePath,
            Remark=P_Remarks
		where Id= P_AnswerId and TestId = P_TestId and StudentId=P_StudentId;
	set V_ObtainMarks =(SELECT SUM(ScoredMarks)  FROM teteststudentanswer WHERE TestId = P_TestId and StudentId=P_StudentId);
    
	update tresult 
		set ObtainMarks = V_ObtainMarks
	where TestId=P_TestId and StudentId=P_StudentId;
   if not exists(select 1 from teteststudentanswer where TestId = P_TestId and StudentId=P_StudentId and ScoredMarks is null) then
		update tresult 
			set IsGenerated = 1
		where TestId=P_TestId and StudentId=P_StudentId;
   end if;
    
    select 1;
		
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spAddETestQuestions`(
P_Id int,
P_TestId int ,
P_QuestionTypeId int,
P_Title longtext,
P_Marks decimal(8,2),
P_IsSubmitted bit ,
-- P_FilePath longtext,
P_IsSingleAnswer bit,
P_ParaId int ,
P_OptionXML longtext
)
BEGIN
DECLARE count int default 0 ;
DECLARE i int default 1;
declare V_OrderIndex int  default 0;
IF(P_Id = 0) THEN
	set V_OrderIndex=(select ifnull(Max(OrderIndex)+1,1) from tetestquestions where TestId=P_TestId);
Start transaction;
	INSERT INTO `vida_lms`.`tetestquestions`
		(
		`TestId`,
		`QuestionTypeId`,
		`Title`,
		`Marks`,
		`IsSubmitted`,
		`IsSingleAnswer`,
		-- `FilePath`,
		`ParaId`,
        OrderIndex)
	VALUES
		(
		P_TestId,
		P_QuestionTypeId,
		P_Title,
		P_Marks,
		P_IsSubmitted,
		P_IsSingleAnswer,
		-- P_FilePath,
		P_ParaId,
        V_OrderIndex);
		SET P_Id = LAST_INSERT_ID();
	commit;
		ELSEIF(P_Id > 0) THEN
			UPDATE `vida_lms`.`tetestquestions`
			SET 
			QuestionTypeId = P_QuestionTypeId,
			Title = P_Title,
			Marks = P_Marks,
			IsSubmitted = P_IsSubmitted,
			IsSingleAnswer= P_IsSingleAnswer,
			-- FilePath= P_FilePath,
			ParaId = P_ParaId
			WHERE Id = P_Id;
end if;
  IF(P_Id > 0  ) THEN
			
			SET count =  ExtractValue(P_OptionXML, 'COUNT(//Option)');
			WHILE i <= Count DO
			IF( ExtractValue(P_OptionXML, '//Option[$i]//OptionId') = 0) THEN
			Start transaction;
			INSERT INTO `vida_lms`.`tetestquestionoptions`
				(
				`QuestionId`,
				`Options`,
				`IsKey`,
                `OrderIndex`
				)
				VALUES
				(
				P_Id,
				ExtractValue(P_OptionXML, '//Option[$i]//OptionTitle'),
                ExtractValue(P_OptionXML, '//Option[$i]//IsKey'),
                ExtractValue(P_OptionXML, '//Option[$i]//OrderIndex')
                );
               
                commit;
			ELSEIF (ExtractValue(P_OptionXML, '//Option[$i]//OptionId') >0) THEN
           
				UPDATE tetestquestionoptions
				SET 
				Options = ExtractValue(P_OptionXML, '//Option[$i]//OptionTitle'),
				IsKey = ExtractValue(P_OptionXML, '//Option[$i]//IsKey'),
				OrderIndex = ExtractValue(P_OptionXML, '//Option[$i]//OrderIndex')
                WHERE Id = (ExtractValue(P_OptionXML, '//Option[$i]//OptionId'));
               
                END IF;
                SET i= i+1;
                
                END WHILE;
			
   END IF;
   SELECT P_Id AS Result;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spAddFeesDetails`(
    in P_FeeId int,
    in P_TeacherId int,
	in P_InstituteId int,
	in P_BatchId int,
    in P_IsRecurringMonthly bit,
    in P_DateOfFeeCollection datetime,
    in P_StudentId int,
    in P_FeesAmount Decimal,
    in P_DefaultFeeAmount Decimal,
    in P_SetupDate Date,
    in P_IsSendNotificationFeeDue bit,
    in P_IsSendSMSFeeDue bit
 )
BEGIN
   if exists(select 1 from tbankaccountdetail where TeacherId=P_TeacherId)
   then
    
    if(P_FeeId = 0)
    then
    
     if not exists(select 1 from tfeesetup where TeacherId=P_TeacherId 
                                           AND BatchId= P_BatchId 
                                           AND StudentId = P_StudentId 
                                           AND Month(SetupDate) = MONTH(P_SetupDate)
                                           AND Year(SetupDate) = YEAR(P_SetupDate)) 
     then
     
	     start transaction;
      
            insert into tfeesetup
      
               (TeacherId,BatchId,IsRecurringMonthly,DateOfFeeCollection,StudentId,FeeAmount,CreatedDateTime,CreatedUserId,DefaultFeeAmount,SetupDate,IsSendSMSFeeDue,IsSendNotificationFeeDue)
         
            values
         
			   (P_TeacherId,P_BatchId,P_IsRecurringMonthly,P_DateOfFeeCollection,P_StudentId,P_FeesAmount,now(),0,P_DefaultFeeAmount,P_SetupDate,P_IsSendSMSFeeDue,P_IsSendNotificationFeeDue);

			select last_insert_id();    
		commit;
      
      else
       
        select -1;
        
	  end if;  
      
      else
             if  exists(select 1 from tfeesetup where TeacherId=P_TeacherId AND BatchId= P_BatchId) 
			 then
             
                UPDATE `vida_lms`.`tfeesetup`
				SET
				`FeeAmount` = P_FeesAmount,
				`IsRecurringMonthly` = P_IsRecurringMonthly,
				`DateOfFeeCollection` = P_DateOfFeeCollection,
                `DefaultFeeAmount` = P_DefaultFeeAmount ,
                `SetupDate` = P_SetupDate,
                IsSendSMSFeeDue=P_IsSendSMSFeeDue,
                IsSendNotificationFeeDue=P_IsSendNotificationFeeDue
				
				WHERE `TeacherId`=P_TeacherId AND `BatchId`= P_BatchId AND `StudentId` = P_StudentId
                And `Id` = P_FeeId;
                
              select P_FeeId;
             
             else
                      select -3;
             end if;
      
      end if;
      
   else
      
	 select -2;
         
   end if;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spAddLink`(
	in P_TeacherId int,
    in P_ContentPath varchar(500),
    in P_ContentLink varchar(500),
    in P_ContentTitle varchar(100)
)
BEGIN
	DECLARE Exit HANDLER FOR SQLEXCEPTION
    begin
		Rollback;
        select -1;
	end;
	start transaction;
		Insert into 
			tteacherContent(TeacherId,ContentLink,ContentPath,ContentTitle,CreatedDateTime)
            values(P_Teacherid,P_ContentLink,P_ContentPath,P_ContentTitle,now());
		select LAST_INSERT_ID();
	commit;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spAddOnlinelecture`(
	in P_LectureId int,
    in P_Date Date,
    in P_FromTime Time,
    in P_ToTime time,
    in P_MeetingId longtext,
    in P_TeacherId int,
    in P_TimeTableId int,
    in P_BatchId int,
    in P_IsMeetingStarted tinyint,
    in P_MettingAccountId int,
    in P_UserId int,
    in P_MeetingPassword longtext 
)
BEGIN
Declare l_LectureId INT DEFAULT 0;

	if P_LectureId=0 then
		INSERT INTO `vida_lms`.`tonlinelecture`
			(`Date`,
			`FromTime`,
			`ToTime`,
			`MeetingId`,
			`TeacherId`,
			`TimeTableId`,
			`BatchId`,
			`IsMeetingStarted`,
			`MettingAccountId`,
			`CreatedDateTime`,
			`CreatedUserId`,
			`MeetingPassword`)
			VALUES
			(P_Date,
			P_FromTime,
			P_ToTime,
			P_MeetingId,
			P_TeacherId,
			P_TimeTableId,
			P_BatchId,
			P_IsMeetingStarted,
			P_MettingAccountId,
			now(),
			P_UserId,
			P_MeetingPassword);
            
		-- select last_insert_id();
        Set l_LectureId = LAST_INSERT_ID();
        
        select 
            1 As StatusCode,
		    l_LectureId as LectureId,
			ifnull(OL.MeetingId,'') as MeetingId,
			ifnull(OL.MeetingPassword,'') as MeetingPassword,
			OLS.IsOnParticipantVideo as IsOnParticipantVideo,
			OLS.IsOnParticipantAudio as IsOnParticipantAudio,
			OLS.IsOnHostVideo as IsOnHostVideo,
			OLS.IsOnHostAudio as IsOnHostAudio,
			OLS.IsOnParticipantScreenShare as IsOnParticipantScreenShare,
			OLS.IsOnHostscreenShare as IsOnHostscreenShare
		from
              tonlinelecture OL
              left join tonlinelecturemeetingsettings OLS on OLS.BatchId = OL.BatchId
		where
              OL.Id =l_LectureId;
         
        
	else
		if exists(select 1 from tonlinelecture where Id=P_LectureId) then
			UPDATE `vida_lms`.`tonlinelecture`
			SET
				`IsMeetingStarted` = P_IsMeetingStarted,
				`UpdatedDateTime` = now(),
				`UpdatedUserId` = P_UserId,
                `MeetingId` = P_MeetingId,
                `MeetingPassword` = P_MeetingPassword
				WHERE `Id` = P_LectureId;
				
		
            select 
            1 As StatusCode,
		    P_LectureId  as LectureId,
			ifnull(OL.MeetingId,'') as MeetingId,
			ifnull(OL.MeetingPassword,'') as MeetingPassword,
			OLS.IsOnParticipantVideo as IsOnParticipantVideo,
			OLS.IsOnParticipantAudio as IsOnParticipantAudio,
			OLS.IsOnHostVideo as IsOnHostVideo,
			OLS.IsOnHostAudio as IsOnHostAudio,
			OLS.IsOnParticipantScreenShare as IsOnParticipantScreenShare,
			OLS.IsOnHostscreenShare as IsOnHostscreenShare
            	from
              tonlinelecture OL
              left join tonlinelecturemeetingsettings OLS on OLS.BatchId = OL.BatchId
		where
              OL.Id =P_LectureId;
        else 
			select -1;
		end if;
	end if;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spAddOnlineLectureAttendence`(
	in P_LectureId int,
    in P_StudentId int,
    in P_BatchId int
)
begin	
	declare V_TimeTableId int  default 0;
	if exists(select 1 from tonlinelecture where id=P_LectureId and BatchId=P_BatchId) then
		if exists(select 1 from tonlinelearningstudentattendance where OnlineLectureId=P_LectureId and StudentId=P_StudentId) then
			UPDATE `vida_lms`.`tonlinelearningstudentattendance`
				SET
				`LastOutDateTime` = now(),
				`UpdatedUserId` = P_StudentId,
				`UpdatedDateTime` = now()
				WHERE OnlineLectureId=P_LectureId and StudentId=P_StudentId;
                select 1;
            
		else
			set V_TimeTableId = (select distinct TimeTableId from tonlinelecture where id=P_LectureId and BatchId=P_BatchId);
			INSERT INTO `vida_lms`.`tonlinelearningstudentattendance`
				(`OnlineLectureId`,
				`StudentId`,
				`FirstInDateTime`,
				`IsPresent`,
				`CreatedDateTime`,
				`CreatedUserId`,
				`AttendanceDate`,
				`TimeTableId`)
				VALUES
				(P_LectureId,
				P_StudentId,
				now(),
				1,
				now(),
				P_StudentId,
				now(),
				0);
                select 1;

		end if;
	else
		select -1;
	end if;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spAddOnlineLectureSettings`(
	in P_AccounSettingId int,
    
    in P_MeetingAccountId int,
    
    in P_IsOnParticipantVideo tinyint,
    in P_IsOnParticipantAudio tinyint,
    in P_IsOnHostVideo tinyint,
    in P_IsOnHostAudio tinyint,
    in P_IsOnParticipantScreenShare tinyint,
    in P_IsOnHostscreenShare tinyint,
    in P_BatchId int
)
BEGIN

	if P_AccounSettingId=0 
    then
    if not exists (select 1 from tonlinelecturemeetingsettings where BatchId = P_BatchId)
    then
		INSERT INTO `vida_lms`.`tonlinelecturemeetingsettings`
			(
            
            `MeetingAccountId`,
            
            `IsOnParticipantVideo`,
			`IsOnParticipantAudio`,
			`IsOnHostVideo`,
			`IsOnHostAudio`,
			`IsOnParticipantScreenShare`,
			`IsOnHostscreenShare`,
			`BatchId`)
			VALUES
			(
            P_MeetingAccountId,
            P_IsOnParticipantVideo,
			P_IsOnParticipantAudio,
			P_IsOnHostVideo,
			P_IsOnHostAudio,
			P_IsOnParticipantScreenShare,
			P_IsOnHostscreenShare,
			P_BatchId);
            select 1;
     else
         select -1;
     end if;
	elseif P_AccounSettingId>0
    then
		UPDATE `vida_lms`.`tonlinelecturemeetingsettings`
			SET
         
			`IsOnParticipantVideo` = P_IsOnParticipantVideo,
			`IsOnParticipantAudio` = P_IsOnParticipantAudio,
			`IsOnHostVideo` = P_IsOnHostVideo,
			`IsOnHostAudio` = P_IsOnHostAudio,
			`IsOnParticipantScreenShare` = P_IsOnParticipantScreenShare,
			`IsOnHostscreenShare` = P_IsOnHostscreenShare,
			`BatchId` = P_BatchId
		WHERE `Id` = P_AccounSettingId;
        select 1;
    end if;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spAddOnlineMeetingAccount`(IN `P_AccountId` INT, IN `P_TeacherId` INT, IN `P_BatchId` INT, IN `P_AccountName` VARCHAR(200), IN `P_AccountEmailId` VARCHAR(200), IN `P_AccountPassword` VARCHAR(200), IN `P_APIKey` VARCHAR(100), IN `P_APISecret` VARCHAR(100), IN `P_WebApiKey` VARCHAR(100), IN `P_WebApiSecret` VARCHAR(100), IN `P_JWTToken` VARCHAR(500), IN `P_UserId` INT)
BEGIN

	if P_AccountId=0 
    then

    
    if not exists (select 1 from tonlinelecturemeetingaccount where TeacherId = P_TeacherId AND BatchId = P_BatchId)
    then
    
    begin
		INSERT INTO `vida_lms`.`tonlinelecturemeetingaccount`
		(`TeacherId`,
		`BatchId`,
		`AccountName`,
		`AccountEmailId`,
		`AccountPassword`,
		`APIKey`,
		`APISecret`,
		`WebApiKey`,
		`WebApiSecret`,
		`JWTToken`,
		`CreatedUserId`,
		`CreatedDateTime`)
		VALUES
		(P_TeacherId,
		P_BatchId,
		P_AccountName,
		P_AccountEmailId,
		P_AccountPassword,
		P_APIKey,
		P_APISecret,
		P_WebApiKey,
		P_WebApiSecret,
		P_JWTToken,
		P_UserId,
		now());
		select LAST_INSERT_ID();
        commit;
	end;
    
    else
       select -1;
    end if;   
    elseif P_AccountId>0 
    then
    begin
		UPDATE `vida_lms`.`tonlinelecturemeetingaccount`
				SET
				`TeacherId` = P_TeacherId,
				`BatchId` = P_BatchId,
				`AccountName` = P_AccountName,
				`AccountEmailId` = P_AccountEmailId,
				`AccountPassword` = P_AccountPassword,
				`APIKey` = P_APIKey,
				`APISecret` = P_APISecret,
				`WebApiKey` = P_WebApiKey,
				`WebApiSecret` = P_WebApiSecret,
				`JWTToken` = P_JWTToken,
				`UpdatedUserId` = P_UserId,
				`UpdatedDateTime` = now()
			WHERE `Id` = P_AccountId;
		select LAST_INSERT_ID();
	end;
    end if;
 commit;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spAddParaQuestion`(
in P_TestId int,
in P_ParaId int,
in P_Title longtext,
in P_Code varchar(45)
)
BEGIN
declare V_OrderIndex int default 0;
if P_ParaId=0 then
	set V_OrderIndex=(select ifnull(Count(*)+1,1) from tparaquestions where TestId=P_TestId);
		INSERT INTO `vida_lms`.`tparaquestions`
		(`TestId`,
		`Title`,
		`Code`,
		OrderIndex
		)
		VALUES
		(P_TestId,
		P_Title,
		P_Code,
		V_OrderIndex);
		select LAST_INSERT_ID();
elseif P_ParaId>0 then
	UPDATE `vida_lms`.`tparaquestions`
SET

`Title` = P_Title,
`Code` = P_Code
WHERE `Id` = P_ParaId and `TestId` = P_TestId;


end if;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spAddQuestionSubmit`(
P_QuestionId int ,
P_IsSubmitted bit 
)
BEGIN
	IF EXISTS (SELECT 1 FROM tetestquestions WHERE Id = P_QuestionId) THEN 
	UPDATE tetestquestions SET IsSubmitted = P_IsSubmitted WHERE Id = P_QuestionId;
    SELECT 1 As Result ;
    ELSE
    SELECT -1 AS Result ;
    END IF ; 
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spAddRegisterUser`(
    in P_UserId int,
    in P_RoleTypeId int,
    IN P_SourceId int,
	IN P_OSTypeId smallint,
	IN P_DeviceToken varchar(200) 
 )
BEGIN
          
          if not exists(select 1 from tregisteredusers where UserId=P_UserId AND SourceId = P_SourceId AND RoleTypeId = P_RoleTypeId)
          then
          
          Start transaction;
          
             Insert Into tregisteredusers
                (UserId,SourceId,RoleTypeId,DeviceToken,OSTypeId,CreatedDateTime)
             Values
				(P_UserId,P_SourceId,P_RoleTypeId,P_DeviceToken,P_OSTypeId,now());
               
          -- select 1;
          Commit;
          
          else
          
             Start transaction;
             
               Update  tregisteredusers
               set
                   DeviceToken = P_DeviceToken
                   
               where
                  UserId = P_UserId AND SourceId = P_SourceId AND RoleTypeId = P_RoleTypeId;
             Commit;
          
          end if;
          
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spAddStudent`(
    in P_InstituteId int,
    in P_TeacherId int,
    in P_MobileNo varchar(20),
    in P_StudentName varchar(100) 
 )
BEGIN
	declare V_UserId int default 0;
    
    if exists(select 1 from tstudent
                            inner join tuser on tuser.Id=tstudent.UserId
                       where tstudent.InstituteId=P_InstituteId 
						  AND tuser.MobileNo = P_MobileNo)
   then
        select tstudent.id 
		   from tstudent
             inner join tuser on tuser.Id=tstudent.UserId
		   where tstudent.InstituteId=P_InstituteId 
			 AND tuser.MobileNo = P_MobileNo;
   
   elseif exists(select 1 from tuser 
                          where MobileNo=P_MobileNo
                           and RoleTypeId=1)
   then
   
   set V_UserId=(select Id from tuser where MobileNo=P_MobileNo and RoleTypeId=1);
        update tuser
        set
			Name=P_StudentName,
            UpdatedDateTime=now()
            where id=V_UserId;
   
   
      if exists(select 1 from tstudent
                            inner join tuser on tuser.Id=tstudent.UserId
                       where tstudent.InstituteId=0 
						  AND tuser.MobileNo = P_MobileNo)
      then 
            update tstudent 
            set 
                 InstituteId = P_InstituteId
            where      
                 UserId=V_UserId AND InstituteId=0 ;
                  select Id from tstudent 
                  where UserId=V_UserId AND InstituteId=P_InstituteId;
      else
      
		
		Insert Into tstudent
			(UserId,StatusId,InstituteId,CreatedUserId,CreatedDateTime)
		Values
			(V_UserId,12,P_InstituteId,P_TeacherId,now());
		
        select Last_Insert_Id();
     end if;   
   else
     start transaction;
     Insert into tuser
         (Name,RoleTypeId,MobileNo,IsActive,CreatedDateTime)
     Values
         (P_StudentName,1,P_MobileNo,1,now());    
	 
     Insert Into tstudent
          (UserId,StatusId,InstituteId,CreatedUserId,CreatedDateTime)  
      Values
           (Last_Insert_Id(),12,P_InstituteId,P_TeacherId,now());
       select Last_Insert_Id();
     Commit;
     end if;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spAddSubcategory`(
	in P_CategoryId int,
    in P_Code varchar(20),
    in P_Title varchar(100)
)
BEGIN
	DECLARE Exit HANDLER FOR SQLEXCEPTION
		Rollback;
        select -1;
	start transaction;
		Insert into 
			tSubCategoryMaster(CategoryId,code,Title) 
            values(P_CategoryId,P_Code,P_Title);
		select LAST_INSERT_ID();
	commit;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spAddSubmitStudentAnswer`(P_AnswerXML longtext , P_TestId int ,P_StudentId int,
P_ExamDate Date, P_StartTime time , P_EndTime time)
BEGIN

DECLARE A_Count int default 0 ;
DECLARE R_Count int default 0 ;
DECLARE i int default 1;
DECLARE IsCorrect tinyint(1) default 0 ;
DECLARE V_ScoredMarks decimal(8,2) default 0.00;
DECLARE V_TotalMarks decimal(8,2) default 0.00;
DECLARE V_TotalQuestion int default 0;
DECLARE V_ObtainMarks decimal(8,2) default 0.00;
DECLARE V_RightAnswer int  default 0;
DECLARE V_WrongAnswer int  default 0;
DECLARE V_SkippedAnswer int default 0;
DECLARE V_SubTestTypeId int ;
DECLARE V_OptionId int default 0 ;
DECLARE V_QuestionId int default 0;
DROP TEMPORARY TABLE IF EXISTS AnswerList;
IF NOT EXISTS (SELECT 1 FROM tresult WHERE TestId = P_TestId AND StudentId = P_StudentId) THEN
SET A_Count =  ExtractValue(P_AnswerXML, 'COUNT(//Answer)');
 
SET V_TotalMarks =  (SELECT SUM(Marks) FROM tetestquestions WHERE TestId = P_TestId);


	CREATE TEMPORARY TABLE AnswerList 
	( QuestionId int , Uniquetext longtext, AnswerId int);


					WHILE i <= A_Count DO


					SET V_OptionId = ExtractValue(P_AnswerXML, '//Answer[$i]//OptionId');
					SET V_QuestionId = ExtractValue(P_AnswerXML, '//Answer[$i]//QuestionId');
					Set IsCorrect = (SELECT IsKey FROM tetestquestionoptions WHERE Id  = V_OptionId AND QuestionId = V_QuestionId );
					IF(IsCorrect = 1) 
					THEN 
						SET V_ScoredMarks = (SELECT Marks FROM tetestquestions WHERE Id = V_QuestionId AND TestId = P_TestId);
					ELSE 
						SET V_ScoredMarks = (SELECT PenaltyMarks  FROM tetest WHERE Id =  P_TestId);
					END IF;

					Start transaction;


						INSERT INTO `vida_lms`.`teteststudentanswer`
					(`TestId`,
					`QuestionId`,
					`StudentId`,
					`Answer`,
					`OptionId`,
					`ScoredMarks`,
					`AnswerStatusId`)
					VALUES
					(P_TestId,
					V_QuestionId,
					P_StudentId,
					ExtractValue(P_AnswerXML, '//Answer[$i]//OptionTitle'),
					V_OptionId,
					V_ScoredMarks,
					ExtractValue(P_AnswerXML, '//Answer[$i]//AnswerStatusId')
					);
					-- SELECT last_insert_id();

					INSERT INTO AnswerList
					(QuestionId ,
					Uniquetext,
					AnswerId )
					values
					(V_QuestionId,
					ExtractValue(P_AnswerXML, '//Answer[$i]//AnswerUniqueText'),
					last_insert_id()
					);

					 SET i= i+1;
					commit;

					END WHILE;

SET V_ObtainMarks =  (SELECT SUM(ScoredMarks)  FROM teteststudentanswer WHERE TestId = P_TestId and StudentId=P_StudentId);
-- SELECT *,ScoredMarks,P_TestId,P_StudentId  FROM teteststudentanswer WHERE TestId = P_TestId and StudentId=P_StudentId;
SET  V_TotalQuestion = (SELECT Count(Id) FROM vida_lms.tetestquestions WHERE TestId = P_TestId GROUP BY TestId) ;
SET V_RightAnswer = (SELECT count(tts.Id)  FROM teteststudentanswer tts
INNER JOIN tetestquestionoptions tqo ON tts.OptionId = tqo.Id WHERE tqo.IsKey = 1 AND tts.TestId = P_TestId and tts.StudentId=P_StudentId) ;
SET V_WrongAnswer = (SELECT Count(tts.Id)  FROM teteststudentanswer tts
INNER JOIN tetestquestionoptions tqo ON tts.OptionId = tqo.Id WHERE tqo.IsKey = 0 AND tts.TestId = P_TestId and tts.StudentId=P_StudentId) ;
SET V_SkippedAnswer = (SELECT Count(tts.Id)  FROM teteststudentanswer tts
INNER JOIN tetestquestionoptions tqo ON tts.OptionId = tqo.Id WHERE  tts.TestId = P_TestId AND tts.OptionId = 0 AND tts.AnswerStatusId = 15 and tts.StudentId=P_StudentId);
SET V_SubTestTypeId = (SELECT SubTestTypeId  FROM tetest WHERE Id = P_TestId); 
-- select V_TotalQuestion,V_RightAnswer,V_WrongAnswer,V_SkippedAnswer,V_SubTestTypeId;
	
			IF( V_SubTestTypeId = 1 ) THEN 
					INSERT INTO `vida_lms`.`tresult`
					(
					`TestId`,
					`StudentId`,
					`ExamDate`,
					`TotalMarks`,
					`ObtainMarks`,
					`StartTime`,
					`EndTime`,
					`TimeTaken`,
					`TotalQuestion`,
					`RighAnswers`,
					`WrongAnswers`,
					`SkippedQuestions`,
					`IsGenerated`)
					VALUES
					(
					P_TestId,
					P_StudentId,
					P_ExamDate,
					V_TotalMarks,
					V_ObtainMarks,
					P_StartTime,
					P_EndTime,
					TIMEDIFF( P_EndTime,P_StartTime),
					V_TotalQuestion,
					V_RightAnswer,
					V_WrongAnswer,
					V_SkippedAnswer,
					1);
					SELECT 1 As Result;
					
			 ELSE 
             
				update teteststudentanswer
					set ScoredMarks=null
				where TestId=P_TestId and StudentId=P_StudentId;
                
					INSERT INTO `vida_lms`.`tresult`
					(
					`TestId`,
					`StudentId`,
					`ExamDate`,
					`TotalMarks`,
					`StartTime`,
					`EndTime`,
					`TimeTaken`,
					`TotalQuestion`,
					`RighAnswers`,
					`WrongAnswers`,
					`SkippedQuestions`,
					`IsGenerated`)
					VALUES
					(
					P_TestId,
					P_StudentId,
					P_ExamDate,
					V_TotalMarks,
					P_StartTime,
					P_EndTime,
					TIMEDIFF( P_EndTime,P_StartTime),
					V_TotalQuestion,
					V_RightAnswer,
					V_WrongAnswer,
					V_SkippedAnswer,
					0);
                    SELECT 1 As Result;
                    SELECT * FROM AnswerList where not IFNULL(Uniquetext,"")="" ;
				END IF;	
ELSE 
	SELECT -1 As Result ;
END IF;
DROP TEMPORARY TABLE IF EXISTS AnswerList;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spAddTimeTable`(
	in P_BatchId int,
	in P_TimeTableXml longtext,
    in P_TimeTableId int,
    in P_Day varchar(45),
    in P_FromTime time,
    in P_ToTime time,
    in P_FromDate Date,
    in P_IsSession tinyint,
	in P_Title longtext,
    in P_AnouncementMessage longtext,
    in P_Subject varchar(50)
)
BEGIN
	declare count int default 0;
    declare i int default 1;
    declare daycount int default 0;
   
    if(P_IsSession=0)
    then
		SET count =  ExtractValue(P_TimeTableXml, 'COUNT(//TimeTable)');
            WHILE i <= Count DO
		      IF(  ExtractValue(P_TimeTableXml, '//TimeTable[$i]//TimeTableId') = 0) 
              THEN
		                 	INSERT INTO `vida_lms`.`ttimetable`
			                        	(
                                           `Day`,
			                        	   `FromTime`,
				                           `ToTime`,
				                           `BatchId`,
				                           `Subject`,
                                           `IsSession`
	                                    )
				           VALUES
				                  (
					                   ExtractValue(P_TimeTableXml, '//TimeTable[$i]//Day'),
                                       ExtractValue(P_TimeTableXml, '//TimeTable[$i]//FromTime'),
                                       ExtractValue(P_TimeTableXml, '//TimeTable[$i]//ToTime'),
									   P_BatchId,
                                       ExtractValue(P_TimeTableXml, '//TimeTable[$i]//Subject'),
									   0 
                                  );
							 SELECT  TeacherId  As result From tbatch where Id = P_BatchId;

                           

			          elseif( ExtractValue(P_TimeTableXml, '//TimeTable[$i]//TimeTableId') > 0) 
                      THEN
		            	UPDATE `vida_lms`.`ttimetable`
				        SET
				           `Day` = ExtractValue(P_TimeTableXml, '//TimeTable[$i]//Day'),
				           `FromTime` = ExtractValue(P_TimeTableXml, '//TimeTable[$i]//FromTime'),
				           `ToTime` = ExtractValue(P_TimeTableXml, '//TimeTable[$i]//ToTime'),
				           `BatchId` = P_BatchId,
				           `Subject` = ExtractValue(P_TimeTableXml, '//TimeTable[$i]//Subject')
					   WHERE `Id` = ExtractValue(P_TimeTableXml, '//TimeTable[$i]//TimeTableId')
				       AND IsSession = 0;
                     
                      end if;
		    	SET i= i+1;
	        END WHILE;
     SELECT  TeacherId  As result From tbatch where Id = P_BatchId;

    else
      if(P_TimeTableId=0)
      then
                 Insert into ttimetable 
			                (BatchId,Day,FromTime,ToTime,FromDate,Subject,IsSession,AnouncementMessage,Title)
                 Values
                            (P_BatchId,DAYNAME(P_FromDate),P_FromTime,P_ToTime,P_FromDate,P_Subject,1,P_AnouncementMessage,P_Title);
               SELECT  TeacherId  As result From tbatch where Id = P_BatchId;

		 
       else
                Update ttimetable
				Set
                       Day = DAYNAME(P_FromDate),
                       FromTime = P_FromTime,
                       ToTime = P_ToTime,
                       Subject =P_Subject,
                       IsSession = 1,
					   FromDate = P_FromDate,
                       AnouncementMessage =P_AnouncementMessage,
                       Title = P_Title
				where
			         Id = P_TimeTableId
                     AND IsSession = 1;
               SELECT  TeacherId  As result From tbatch where Id = P_BatchId;

         END if;
	  End if;
	
     
    
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spAddTimeTable_Validation`(
	in P_BatchId int,
	in P_TimeTableXml longtext,
    in P_TimeTableId int,
    in P_Day varchar(45),
    in P_FromTime time,
    in P_ToTime time,
    in P_FromDate Date,
    in P_IsSession tinyint,
     in P_Title longtext,
     in P_AnouncementMessage longtext,
   in P_Subject varchar(50)
)
BEGIN
	declare count int default 0;
    declare i int default 1;
    declare daycount int default 0;
   
    if(P_IsSession=0)
    then
       SET count =  ExtractValue(P_TimeTableXml, 'COUNT(//TimeTable)');
            WHILE i <= Count DO
		       IF(ExtractValue(P_TimeTableXml, '//TimeTable[$i]//TimeTableId') = 0) 
               THEN
					SET daycount = (Select Count(*) from ttimetable where  Day = ExtractValue(P_TimeTableXml, '//TimeTable[$i]//Day') and BatchId=P_BatchId and IsSession= 0) ;
                  if (daycount < 3)
				  then
                  
                       if exists(select 1 from ttimetable where Day = ExtractValue(P_TimeTableXml, '//TimeTable[$i]//Day') AND BatchId = P_BatchId AND ((CAST(ExtractValue(P_TimeTableXml, '//TimeTable[$i]//FromTime')  AS Time) BETWEEN FromTime AND ToTime OR CAST(ExtractValue(P_TimeTableXml, '//TimeTable[$i]//ToTime')  AS Time) BETWEEN FromTime AND ToTime)))
					   then
                         select -2 as result , ExtractValue(P_TimeTableXml, '//TimeTable[$i]//Day') as Day;
		              end if;
                      else 
                      select -3 as result, ExtractValue(P_TimeTableXml, '//TimeTable[$i]//Day') as Day;
					end if; 
               end if;
            SET i= i+1;
	       END WHILE;
  select 1 as result;
    else
       
     if(P_TimeTableId=0)
	 then
     
		 if exists(select 1 from ttimetable where Day = ExtractValue(P_TimeTableXml, '//TimeTable[$i]//Day')
                   AND BatchId = P_BatchId 
                   AND  FromDate = P_FromDate
				   AND P_FromTime
                   AND IsSession = 1
				   AND(
				   P_FromTime BETWEEN FromTime AND ToTime OR 
                   P_ToTime  BETWEEN FromTime AND ToTime))
		 then
                select -1  as result;
           END if;
       else
            if exists(select 1 from ttimetable where Day = ExtractValue(P_TimeTableXml, '//TimeTable[$i]//Day')
                   AND BatchId = P_BatchId 
                   AND  FromDate = P_FromDate
				   AND P_FromTime
                   AND IsSession = 1 AND(
				   P_FromTime BETWEEN FromTime AND ToTime OR 
                   P_ToTime  BETWEEN FromTime AND ToTime))
		   then
               select -1  as result;
         END if;
	  End if;

	End if;
     SELECT 1 AS result;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spAddViewHistory`(
	in P_StatusId int,
    in P_DocumentId int,
    in P_StudentId int
    
)
BEGIN
	if not exists(select 1 from tviewhistory where StatusId=P_StatusId and DocumentId=P_DocumentId and StudentId=P_StudentId) then
		INSERT INTO `vida_lms`.`tviewhistory`
			(`StatusId`,

			`DocumentId`,
			`StudentId`,
            ViewCount,
			`CreatedDateTime`)
			VALUES
			(P_StatusId,
			P_DocumentId,
			P_StudentId,
            1,
			now());
            select 1;
	else 
    
         Update tviewhistory
         set
             
             ViewCount = ViewCount+1
          
          where
            
             StatusId=P_StatusId and 
             DocumentId=P_DocumentId and 
             StudentId=P_StudentId;
              
             
		select 1;
	end if;

END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spAlertFeeOverDueData`(
 in P_Template varchar(500),
   in P_MailSubject varchar(100),
   in P_AlertId int
)
BEGIN
SELECT   
		f.Id,
	    f.TeacherId,
        f.StudentId,
        U.MobileNo,
		T.UserId As UserId ,
		IFNULL(S.EmailId,'') AS EmailId,
		replace(replace(replace(P_Template,'[BatchName]',B.Title),'[DueMonth]',MONTHNAME(now())),'[Amount]',f.FeeAmount)  AS MsgText,
		P_MailSubject AS Subject,
        U.name As UserName,
		1 AS ReceiverRoleTypeId,
		S.Id AS ReceiverSourceId ,   
        S.UserId As ReceiverUserId ,
        S.InstituteId As InstituteId  ,
        B.Id as  BatchId ,
        B.Title as BatchName
		    FROM 
                 tfeesetup F
                 left join tpaymenttransaction PT on PT.FeeSetupId =  F.Id  
                 left join ttransactionstatusmaster SM on SM.Id = PT.TransactionStatusId
                  LEFT JOIN tbatch B ON f.BatchId = B.Id
			left join tteacher T on T.Id=B.TeacherId
            LEFT JOIN tstudent S on f.StudentId=S.Id 
            LEFT JOIN tuser U on S.UserId=U.Id   
                 
            WHERE   
				Date(F.DateofFeeCollection)<=Date(now()) 
              and Month(F.DateofFeeCollection) = MONTH(Now())
              AND   Year(F.DateofFeeCollection) = YEAR(now())
              and (IFNULL(PT.TransactionStatusId, 0) = 0 OR IFNULL(PT.TransactionStatusId, 0)=3)
              AND ((P_AlertId=3 AND F.IsSendNotificationFeeDue=1) OR (P_AlertId=2 AND F.IsSendSMSFeeDue=1))
              AND S.StatusId = 11;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spAlertGetAssignBatch`(
   in P_Template varchar(500),
   in P_MailSubject varchar(100),
   in P_BatchId int,
   in P_TeacherId int,
   in P_StudentId int,

    in P_IsActive bit,
   in  P_RoleTypeId int 
)
BEGIN
 
 if(P_RoleTypeId = 1)
 then
 
 if(P_IsActive = true)
 then
      SELECT   
		B.Id As BatchId,
		B.Title as BatchName ,
        B.TeacherId,
        AB.StudentId,
        AB.StatusId,
        SM.Title AS StatusTitle,
        T.UserId As UserId ,
        U.MobileNo,
		IFNULL(S.EmailId, '') AS EmailId,
		replace(replace(replace(P_Template,'[BatchName]',B.Title),'[TeacherName]',U1.Name),'[StudentName]',U.Name) AS MsgText,
		P_MailSubject AS Subject,
		1 AS ReceiverRoleTypeId,
		S.Id AS ReceiverSourceId ,
        S.UserId As ReceiverUserId ,
        T.InstituteId AS InstituteId
        
	 FROM 
		  tbatch B
            LEFT JOIN tassignbatch AB ON AB.BatchId = B.Id
		    LEFT JOIN tstatusmaster SM  ON SM.Id = AB.StatusId AND StatusTypeId = 4
            LEFT JOIN tstudent S on AB.StudentId=S.Id 
            left join tteacher T on T.Id=B.TeacherId
			LEFT JOIN tuser U on S.UserId=U.Id  
			LEFT JOIN tuser U1 on T.UserId=U1.Id  
      
	 WHERE
            
            (B.Id = P_BatchId OR P_BatchId =0)
		AND	(B.TeacherId =P_TeacherId OR P_TeacherId = 0)
		AND (S.Id =P_StudentId OR P_StudentId =0)
        AND S.StatusId = 11;
	
    else
       
        SELECT   
		B.Id As BatchId,
		B.Title as BatchName,
        B.TeacherId,
        S.Id,
        T.UserId As UserId ,
        U.MobileNo,
		IFNULL(S.EmailId, '') AS EmailId,
		replace(replace(replace(P_Template,'[BatchName]',B.Title),'[TeacherName]',U1.Name),'[StudentName]',U.Name) AS MsgText,
		P_MailSubject AS Subject,
		1 AS ReceiverRoleTypeId,
		S.Id AS ReceiverSourceId ,
        S.UserId As ReceiverUserId ,
        T.InstituteId AS InstituteId
        
	 FROM 
		  tbatch B
            
            left join tteacher T on T.Id=B.TeacherId
            LEFT JOIN tstudent S on T.InstituteId=S.InstituteId 
			LEFT JOIN tuser U on S.UserId=U.Id  
			LEFT JOIN tuser U1 on T.UserId=U1.Id  
      
	 WHERE
            
            (B.Id = P_BatchId OR P_BatchId =0)
		AND	(B.TeacherId =P_TeacherId OR P_TeacherId = 0)
		AND (S.Id =P_StudentId OR P_StudentId =0);
	
       
    end if;
else
         SELECT   
		B.Id As BatchId ,
		B.Title as BatchName ,
        B.TeacherId,
        S.Id,
        T.UserId As UserId ,
        U.MobileNo,
		IFNULL(S.EmailId, '') AS EmailId,
		replace(replace(P_Template,'[BatchName]',B.Title),'[StudentName]',U.Name) AS MsgText,
		P_MailSubject AS Subject,
		2 AS ReceiverRoleTypeId,
		T.Id AS ReceiverSourceId ,
        T.UserId As ReceiverUserId ,
        T.InstituteId AS InstituteId
        
	 FROM 
		  tbatch B
            
            left join tteacher T on T.Id=B.TeacherId
            LEFT JOIN tstudent S on T.InstituteId=S.InstituteId 
			LEFT JOIN tuser U on S.UserId=U.Id  
			LEFT JOIN tuser U1 on T.UserId=U1.Id  
      
	 WHERE
            
            (B.Id = P_BatchId OR P_BatchId =0)
		AND	(B.TeacherId =P_TeacherId OR P_TeacherId = 0)
		AND (S.Id =P_StudentId OR P_StudentId =0);
end if;
    
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spAlertGetFeeSetup`(
	  in P_Template varchar(500),
      in P_MailSubject varchar(100),
      in P_FeeId int,
      in P_BatchId int,
      in P_TeacherId int,
      in P_StudentId int 
)
BEGIN
	SELECT   
		f.Id,
	    f.TeacherId,
        f.StudentId,
        U.MobileNo,
		T.UserId As UserId ,
		IFNULL(S.EmailId,'') AS EmailId,
		replace(replace(replace(P_Template,'[BatchName]',B.Title),'[DueMonth]',month(now())),'[Amount]',f.FeeAmount) AS MsgText,
		P_MailSubject AS Subject,
		1 AS ReceiverRoleTypeId,
		S.Id AS ReceiverSourceId ,   

        S.UserId As ReceiverUserId ,
        T.InstituteId As InstituteId ,
        B.Id as BatchId ,
        B.Title as BatchName
	 FROM 
		  tfeesetup f
            LEFT JOIN tbatch B ON f.BatchId = B.Id
			left join tteacher T on T.Id=B.TeacherId
            LEFT JOIN tstudent S on f.StudentId=S.Id 
            LEFT JOIN tuser U on S.UserId=U.Id  
            LEFT JOIN tuser U1 on T.UserId=U1.Id 
            
      
	 WHERE
            
            f.BatchId = P_BatchId 
		AND f.Id = P_FeeId 
		AND	f.TeacherId = P_TeacherId 
		AND S.Id = P_StudentId 
        AND S.StatusId = 11;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spAlertGetHomeworkData`(
	 in P_HomeworkId int,
     in P_Template varchar(500),
     in P_TeacherId int,
     in P_BatchId int,
     in P_MailSubject varchar(100),
     in P_HomeworkStatusId int,
	 in P_RoleTypeId int 
)
BEGIN

    if(P_RoleTypeId=1)
    then
	SELECT 
		H.Id,
        H.Title,
        H.TeacherId,
        H.BatchId As BatchId,
         B.Title as BatchName ,
        H.HomeworkDateTime,
        HA.StudentId,
        HA.StatusId,
        SM.Title AS StatusTitle,
        U.MobileNo,
		IFNULL(S.EmailId, '') AS EmailId,
		Replace(Replace(replace(replace(P_Template,'[BatchName]',B.Title),'[TeacherName]',U1.Name),'[HomeworkTitle]',H.Title),'[Submissiondate]',ifnull(Date(H.HomeworkDateTime),"")) AS MsgText,
		P_MailSubject AS Subject,
        T.UserId As UserId ,
		1 AS ReceiverRoleTypeId,
		S.Id AS ReceiverSourceId ,
        S.UserId As ReceiverUserId , 
         T.InstituteId As InstituteId 
        
	FROM 
		thomework H
	LEFT JOIN thomeworkassign HA ON HA.HomeworkId = H.Id
    LEFT JOIN tstatusmaster SM  ON SM.Id = HA.StatusId AND StatusTypeId = 1
    LEFT JOIN tbatch B ON H.BatchId = B.Id
     left join tteacher T on T.Id=B.TeacherId
    LEFT JOIN tuser U1 on T.UserId=U1.Id 
    LEFT JOIN tstudent S ON HA.StudentId = S.Id
    LEFT JOIN tuser U ON U.Id = S.UserId
	WHERE
		H.Id = P_HomeworkId 
		AND H.TeacherId = P_TeacherId
		AND H.BatchId = P_BatchId
		AND ((HA.StatusId = P_HomeworkStatusId) OR (P_HomeworkStatusId = 0))
        AND S.StatusId = 11;
        
     elseif(P_RoleTypeId=2)  
     then
     
     SELECT 
		DISTINCT(H.Id),
        H.Title,
        H.TeacherId,
        H.BatchId As BatchId ,
        B.Title as BatchName ,
        H.HomeworkDateTime,
        HA.StatusId,
        SM.Title AS StatusTitle,
        U.MobileNo,
		IFNULL(T.EmailId, '') AS EmailId,
		Replace(Replace(replace(replace(P_Template,'[BatchName]',B.Title),'[TeacherName]',U.Name),'[HomeworkTitle]',H.Title),'[Submissiondate]',ifnull(Date(H.HomeworkDateTime),"")) AS MsgText,
		P_MailSubject AS Subject,
        S.UserId as UserId,
		2 AS ReceiverRoleTypeId,
		T.Id AS ReceiverSourceId ,
        T.UserId As ReceiverUserId ,
        T.InstituteId AS InstituteId
	FROM 
		thomework H
	LEFT JOIN thomeworkassign HA ON HA.HomeworkId = H.Id
    LEFT JOIN tteacher T ON H.TeacherId = T.Id
    LEFT JOIN tstatusmaster SM  ON SM.Id = HA.StatusId AND StatusTypeId = 1
    LEFT JOIN tbatch B ON H.BatchId = B.Id
    LEFT JOIN tuser U ON U.Id = T.UserId
    left join tstudent S on HA.StudentId=S.Id
	WHERE
		H.Id = P_HomeworkId 
		AND H.TeacherId = P_TeacherId
		AND H.BatchId = P_BatchId
		AND ((HA.StatusId = P_HomeworkStatusId) OR (P_HomeworkStatusId = 0));
        
     end if;  
        
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spAlertGetInstitute`(
     in P_Template varchar(500), 
     in P_MailSubject varchar(100),
	 in P_InstituteId int,
    -- in P_TeacherId int,
     in P_StudentId int
  )
BEGIN
       SELECT   
		 I.Id,
		 I.Title,
       --  T.TeacherId,
         S.Id,
       -- StatusId,
       -- Title AS StatusTitle,
         U.MobileNo,
		 IFNULL(S.EmailId, '') AS EmailId,
		 replace(replace(P_Template,'[InstituteName]',I.Title),'[StudentName]',U.Name) AS MsgText,
		 P_MailSubject AS Subject,
		 2 AS ReceiverRoleTypeId,
		 T.Id AS ReceiverSourceId ,
         T.UserId as UserId ,
         T.UserId As ReceiverUserId ,
         T.InstituteId AS InstituteId ,
         B.Id as BatchId ,
         B.Title as BatchName
        
	 FROM 
		  tinstitute I
             left join tteacher T on T.InstituteId=I.Id
             LEft join tbatch B on B.Id = T.BatchId
			 LEFT JOIN tstudent S on S.InstituteId=I.Id  
            LEFT JOIN tuser U on S.UserId=U.Id  
			LEFT JOIN tuser U1 on T.UserId=U1.Id  
      
	 WHERE
            
            (I.Id = P_InstituteId OR P_InstituteId =0)
		-- AND	(T.Id =P_TeacherId OR P_TeacherId = 0)
		AND (S.Id =P_StudentId OR P_StudentId =0);
       

END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spAlertGetNoticeBoardData`(
	in P_NoticeBoardId int,
     in P_Template varchar(500),
     in P_TeacherId int,
     in P_BatchId int,
     in P_MailSubject varchar(100),
     in P_RoleTypeId int 
)
BEGIN
	if(P_RoleTypeId=1) then 
		select 
			N.Id,
			N.Remark as Title,
			B.TeacherId,
            B.Id As BatchId ,
            B.Title as BatchName ,
			N.BatchId,
			BA.StudentId,
			U.MobileNo,
			IFNULL(S.EmailId, '') AS EmailId,
			replace(replace(P_Template,'[BatchName]',B.Title),'[TeacherName]',U1.Name) AS MsgText,
			P_MailSubject AS Subject,
			P_RoleTypeId AS ReceiverRoleTypeId,
			S.Id AS ReceiverSourceId,
            T.UserId As UserId ,
            S.UserId As ReceiverUserId ,
            T.InstituteId AS InstituteId
            
		from tnoticeboard N
		left join tbatch B on N.BatchId=B.id 
		left join tteacher T on B.TeacherId=T.Id
        left join tuser U1 on T.UserId=U1.Id 
		left join tassignbatch BA on B.Id = BA.BatchId and BA.StatusId=11
		left join tstudent S on BA.StudentId=S.Id 
		left join tuser U on S.UserId=U.Id 
		Where 
			N.Id=P_NoticeBoardId
			and B.Id=P_BatchId
			and B.TeacherId=P_TeacherId
			and S.StatusId = 11;
	elseif (P_RoleTypeId=2) then 
		select 
			N.Id,
			N.Remark as Title,
			B.TeacherId as TeacherId,
			N.BatchId as BatchId,
            B.Title as BatchName ,
			U.MobileNo as MobileNo,
			IFNULL(T.EmailId, '') AS EmailId,
			replace(replace(P_Template,'[BatchName]',B.Title),'[TeacherName]',U.Name) AS MsgText,
			P_MailSubject AS Subject,
			P_RoleTypeId AS ReceiverRoleTypeId,
			T.Id AS ReceiverSourceId,
            T.UserId As UserId ,
            T.UserId As ReceiverUserId ,
             T.InstituteId AS InstituteId
             
		from tnoticeboard N
		left join tbatch B on N.BatchId=B.id 
		left join tteacher T on B.TeacherId=T.Id
		left join tuser U on T.UserId=U.Id 
		Where 
			N.Id=P_NoticeBoardId
			and B.Id=P_BatchId
			and T.Id=P_TeacherId;
    end if;
        
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spAlertGetNotification`(
      in P_Template varchar(500),
      in P_MailSubject varchar(100),
      in P_BatchId varchar(45),
      in P_TeacherId int,
	in P_NotificationId int
)
BEGIN
   
   SELECT   
		 N.Id,
		 N.Title,
         N.NotificationText,
         N.TeacherId,
        -- S.StudentId,
       -- StatusId,
       -- Title AS StatusTitle,
         U1.MobileNo,
		 IFNULL(S.EmailId, '') AS EmailId,
		replace(replace(replace(P_Template,'[BatchName]',N.Title),'[TeacherName]',U1.Name),'[NotificationText]',N.NotificationText) AS MsgText,
		 P_MailSubject AS Subject,
		 1 AS ReceiverRoleTypeId,
		 S.Id AS ReceiverSourceId ,
         T.UserId As UserId ,
         S.UserId As ReceiverUserId ,
         T.InstituteId AS InstituteId  ,
         B.Id as BatchId ,
         B.Title as BatchName
        
	 FROM 
		  tnotification N
           left join tbatch B on B.Id = N.BatchId
            left join tteacher T on T.Id=N.TeacherId
            LEFT JOIN tassignbatch AB ON AB.BatchId = N.BatchId
			 LEFT JOIN tstudent S on S.Id=AB.StudentId 
           LEFT JOIN tuser U on S.UserId=U.Id  
			LEFT JOIN tuser U1 on T.UserId=U1.Id  
      
	 WHERE
            
            (FIND_IN_SET(B.Id, P_BatchId) > 0 OR P_BatchId ='0')
		AND	(T.Id =P_TeacherId OR P_TeacherId = 0)
		AND (N.Id =P_NotificationId );
   
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spAlertGetOnlineLectureData`(
	in P_LectureId int,
	in P_Template varchar(500),
	in P_TeacherId int,
	in P_BatchId int,
	in P_MailSubject varchar(100),
    in P_RoleTypeId int
)
BEGIN
	if(P_RoleTypeId=1) then
		select 
			OL.Id,
			B.TeacherId,
			B.Id as BatchId,
            B.Title as BatchName,
			-- BA.StudentId,
			U.MobileNo,
			IFNULL(S.EmailId, '') AS EmailId,
			replace(replace(P_Template,'[BatchName]',B.Title),'[TeacherName]',U.Name) AS MsgText,
			P_MailSubject AS Subject,
			P_RoleTypeId AS ReceiverRoleTypeId,
			S.Id AS ReceiverSourceId,
            T.UserId As UserId,
            S.UserId As ReceiverUserId ,
             T.InstituteId AS InstituteId
            
            
		from tonlinelecture OL 
		left join tteacher T on OL.TeacherId=T.Id
		left join tbatch B on B.TeacherId=T.id
		left join tassignbatch BA on B.Id = BA.BatchId and BA.StatusId=11
		left join tstudent S on BA.StudentId=S.Id 
		left join tuser U on T.UserId=U.Id 
		Where 
			OL.Id=P_LectureId
			and B.Id=P_BatchId
			and B.TeacherId=P_TeacherId
			 and S.StatusId = 11;
	-- elseif(P_RoleTypeId=2) then
	-- select 
			-- OL.Id,
		    -- T.Id as TeacherId,
	    	-- B.Id as BatchId,
			-- U.MobileNo,
			-- IFNULL(T.EmailId, '') AS EmailId,
			-- replace(replace(P_Template,'[BatchName]',B.Title),'[TeacherName]',U.Name) AS MsgText,
			-- P_MailSubject AS Subject,
			-- P_RoleTypeId AS ReceiverRoleTypeId,
			-- T.Id AS ReceiverSourceId,
            -- U.Id as UserId ,
			-- T.InstituteId AS InstituteId
		-- from tonlinelecture OL 
		-- left join tteacher T on OL.TeacherId=T.Id
		-- left join tbatch B on B.TeacherId=T.id
		-- left join tuser U on T.UserId=U.Id 
		-- Where 
			-- OL.Id=P_LectureId
			-- and B.Id=P_BatchId
			-- and T.Id=P_TeacherId;
    end if;
         
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spAlertGetStudyMaterialData`(
	in P_ContentId int,
    in P_CommentId int,
    in P_RefferenceCommentId int,
     in P_Template varchar(500),
     -- in P_TeacherId int,
      in P_BatchId int,
     in P_MailSubject varchar(100),
     in P_UserId int,
     in P_RoleTypeId int 
)
BEGIN
if(P_CommentId=0)then
	select 
		SM.Id,
		SM.Remark as Title,
		B.TeacherId,
		SM.BatchId As BatchId ,
        B.Title as BatchName ,
		BA.StudentId,
		U.MobileNo,
		IFNULL(S.EmailId, '') AS EmailId,
		replace(P_Template,'[BatchName]',B.Title) AS MsgText,
		P_MailSubject AS Subject,
		1 AS ReceiverRoleTypeId,
		S.Id AS ReceiverSourceId,
		U.Id as UserId,
        S.UserId As ReceiverUserId ,
		 T.InstituteId AS InstituteId
	from tteachercontentassign SM
	left join tbatch B on SM.BatchId=B.id 
	left join tteacher T on B.TeacherId=T.Id
	left join tassignbatch BA on B.Id = BA.BatchId and BA.StatusId=11
	left join tstudent S on BA.StudentId=S.Id 
	left join tuser U on S.UserId=U.Id 
	Where 
		SM.Id=P_ContentId
		and B.Id=P_BatchId
		and T.UserId=P_UserId
		 and S.StatusId = 11;
elseif(P_CommentId>0) then 
	if(P_RefferenceCommentId=0) then
	if(P_RoleTypeId=1) then
			select 
				SM.Id,
				SM.Remark as Title,
				B.TeacherId,
				SM.BatchId As BatchId ,
                B.Title as BatchName ,
				-- BA.StudentId,
				U.MobileNo,
				IFNULL(S.EmailId, '') AS EmailId,
				replace(P_Template,'[UserName]',U1.Name) AS MsgText,
				P_MailSubject AS Subject,
				1 AS ReceiverRoleTypeId,
				S.Id AS ReceiverSourceId,
				U.Id as UserId ,
                S.UserId As ReceiverUserId ,
                 T.InstituteId AS InstituteId
			from tstudymaterialcomment SMC
            left join tteachercontentassign SM on SMC.AssignContentId=SM.Id
			left join tbatch B on SM.BatchId=B.id 
			left join tteacher T on B.TeacherId=T.Id
            left join tuser U1 on T.UserId=U1.Id 
			left join tassignbatch BA on B.Id = BA.BatchId and BA.StatusId=11
			left join tstudent S on BA.StudentId=S.Id 
			left join tuser U on S.UserId=U.Id 
			Where 
				SMC.Id = P_CommentId
				and SM.Id=P_ContentId
				-- and B.Id=P_BatchId
				and T.UserId=P_UserId
				 and S.StatusId = 11;
       elseif(P_RoleTypeId=2) then
		select 
			SM.Id ,
			SM.Remark as Title,
			B.TeacherId,
			SM.BatchId As BatchId ,
            B.Title as BatchName ,
			S.Id,
			U1.MobileNo,
			IFNULL(S.EmailId, '') AS EmailId,
			replace(P_Template,'[UserName]',U.Name) AS MsgText,
			P_MailSubject AS Subject,
			2 AS ReceiverRoleTypeId,
			T.Id AS ReceiverSourceId,
            T.UserId As ReceiverUserId ,
            U1.Id as UserId ,
             T.InstituteId AS InstituteId
		from tstudymaterialcomment SMC
		left join tteachercontentassign SM on SMC.AssignContentId=SM.Id
		left join tbatch B on SM.BatchId=B.id 
        left Join tassignbatch AB ON AB.BatchId=B.Id
		left join tteacher T on B.TeacherId=T.Id 

        left join tuser U1 on T.UserId=U1.Id 
		left join tstudent S on SMC.UserId=S.UserId  AND AB.StudentId=S.Id
		left join tuser U on S.UserId=U.Id 
		Where 
			SMC.Id = P_CommentId
			and SM.Id=P_ContentId
			-- and B.Id=P_BatchId
			and S.UserId=P_UserId;
       end if;
	elseif (P_RefferenceCommentId>0) then
    
		if exists(select 1 from tstudymaterialcomment SMC where SMC.Id=P_RefferenceCommentId And SMC.RoleTypeId=1 And SMC.UserId!=P_UserId)then
			
            if exists(select 1 from tstudymaterialcomment SMC where SMC.Id=P_CommentId And SMC.RoleTypeId=1 ) then 
				select 
					SM.Id ,
					SM.Remark as Title,
					
					SM.BatchId As BatchId ,
                    B.Title as BatchName ,
					-- BA.StudentId,
					U.MobileNo,
					IFNULL(S.EmailId, '') AS EmailId,
					replace(P_Template,'[UserName]',U1.Name) AS MsgText,
					P_MailSubject AS Subject,
					1 AS ReceiverRoleTypeId,
					S1.Id AS ReceiverSourceId,
                    S1.UserId As ReceiverUserId ,
					U.Id as UserId ,
					 S1.InstituteId AS InstituteId
				from tstudymaterialcomment SMC
				left join tteachercontentassign SM on SMC.AssignContentId=SM.Id
                left join tbatch B on SM.BatchId=B.id 
				left join tassignbatch BA on B.Id = BA.BatchId and BA.StatusId=11
                Left join tstudent S1 on BA.StudentId=S1.Id And S1.UserId in ( select UserId from tstudymaterialcomment SMC where SMC.Id=P_RefferenceCommentId And SMC.RoleTypeId=1 ) 
				left join tuser U1 on S1.UserId=U1.Id 
				left join tstudent S on SMC.UserId=S.UserId And S1.InstituteId=S.InstituteId
				left join tuser U on S.UserId=U.Id 
				Where 
					SMC.Id = P_CommentId
					and SM.Id=P_ContentId
					-- and B.Id=P_BatchId
					and SMC.UserId=P_UserId
					 and S.StatusId = 11;
			elseif exists(select 1 from tstudymaterialcomment SMC where SMC.Id=P_CommentId And SMC.RoleTypeId=2) then
            
            select 
					SM.Id ,
					SM.Remark as Title,
					
					SM.BatchId As BatchId ,
                    B.Title as BatchName,
					U.MobileNo,
					IFNULL(S.EmailId, '') AS EmailId,
					replace(P_Template,'[UserName]',U.Name) AS MsgText,
					P_MailSubject AS Subject,
					1 AS ReceiverRoleTypeId,
					S.Id AS ReceiverSourceId,
                    S.UserId As ReceiverUserId ,
					U1.Id as UserId ,
					 T.InstituteId AS InstituteId
				from tstudymaterialcomment SMC
				left join tteachercontentassign SM on SMC.AssignContentId=SM.Id
				left join tbatch B on SM.BatchId=B.Id 
                left join tassignbatch BA on B.Id = BA.BatchId and BA.StatusId=11
                Left join tstudent S on BA.StudentId=S.Id And S.UserId in ( select UserId from tstudymaterialcomment SMC where SMC.Id=P_RefferenceCommentId And SMC.RoleTypeId=1 ) 
                left join tteacher T on SMC.UserId=T.UserId And  T.InstituteId=S.InstituteId
				left join tuser U1 on T.UserId=U1.Id 
				left join tuser U on S.UserId=U.Id 
				Where 
					SMC.Id = P_CommentId
					and SM.Id=P_ContentId
					-- and B.Id=P_BatchId
					and T.UserId=P_UserId;
				
			end if;
         elseif(select 1 from tstudymaterialcomment SMC where SMC.Id=P_RefferenceCommentId And SMC.RoleTypeId=2) then 
       
         if(P_RoleTypeId=2) then
         
		select 
			SM.Id,
			SM.Remark as Title,
			 B.TeacherId,
			SM.BatchId As BatchId ,
            B.Title as BatchName,
			-- BA.StudentId,
			U.MobileNo,
			IFNULL(S.EmailId, '') AS EmailId,
			replace(P_Template,'[UserName]',U.Name) AS MsgText,
			P_MailSubject AS Subject,
			2 AS ReceiverRoleTypeId,
			T.Id AS ReceiverSourceId,
            T.UserId As ReceiverUserId ,
            U1.Id as UserId ,
             T.InstituteId AS InstituteId
		from tstudymaterialcomment SMC
		left join tteachercontentassign SM on SMC.AssignContentId=SM.Id
        left join tbatch B on SM.BatchId=B.Id 
		left join tteacher T on B.TeacherId=T.Id And T.UserId in (select UserId from tstudymaterialcomment SMC where SMC.Id=P_RefferenceCommentId And SMC.RoleTypeId=2)
        left join tuser U1 on T.UserId=U1.Id 
		left join tstudent S on SMC.UserId=S.UserId And T.InstituteId=S.InstituteId
		left join tuser U on S.UserId=U.Id 
		Where 
			SMC.Id = P_CommentId
			and SM.Id=P_ContentId
			-- and B.Id=P_BatchId
			and T.UserId=P_UserId
			 and S.StatusId = 11;
             end if;
		end if;
	end if;
end if;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spAlertGetTestData`(
	in P_TestId int,
	in P_Template varchar(500),
	in P_TeacherId int,
	in P_BatchId int,
	in P_MailSubject varchar(100),
    in P_StudentId int,
    in P_RoleTypeId int 
)
BEGIN
	if P_RoleTypeId=1 then
		if (P_StudentId=0) then
		select 
			ET.Id,
			B.TeacherId,
			B.Id as BatchId,
            B.Title as BatchName ,
			S.Id as StudentId,
			U.MobileNo as MobileNo,
			ET.Topic as Title,
			IFNULL(S.EmailId, '') AS EmailId,
		   replace(
					replace(
							P_Template,'[TeacherName]',U.Name
					),
					'[HoldTime]',case 
								when floor(hour(timediff(concat(ET.StartDate," ",ET.StartTime),now()))/24)>0 then Concat(floor(hour(timediff(concat(ET.StartDate," ",ET.StartTime),now()))/24)," Days ") 
								when hour(timediff(concat(ET.StartDate," ",ET.StartTime),now()))>0 then Concat(floor(hour(timediff(concat(ET.StartDate," ",ET.StartTime),now())))," Hours ")
								else Concat(date_Format(timediff(concat(ET.StartDate," ",ET.StartTime),now()),"%i")," Minute ")  end) AS MsgText,
			P_MailSubject AS Subject,
			1 AS ReceiverRoleTypeId,
			S.Id AS ReceiverSourceId,
            S.UserId As ReceiverUserId ,
            U.Id as UserId ,
             T.InstituteId AS InstituteId
		from tetest ET 
		left join tbatch B on B.Id=ET.BatchId
		left join tteacher T on B.TeacherId=T.Id
		left join tassignbatch BA on B.Id = BA.BatchId and BA.StatusId=11
		left join tstudent S on BA.StudentId=S.Id 
		left join tuser U on T.UserId=U.Id 
		Where 
			ET.Id=P_TestId
			and B.Id=P_BatchId
			and B.TeacherId=P_TeacherId
			 and S.StatusId = 11;
			 
		elseif (P_StudentId>0) then 
		select 
			ET.Id,
			B.TeacherId,
			B.Id as BatchId,
            B.Title as BatchName ,
			S.Id as StudentId,
			U.MobileNo as MobileNo,
			ET.Topic as Title,
			IFNULL(S.EmailId, '') AS EmailId,
           replace(P_Template,'[TestTopic]',ET.Topic) AS MsgText,
			P_MailSubject AS Subject,
			1 AS ReceiverRoleTypeId,
			S.Id AS ReceiverSourceId,
            S.UserId As ReceiverUserId ,
            U.Id as UserId ,
             T.InstituteId AS InstituteId
		from tetest ET 
		left join tbatch B on B.Id=ET.BatchId
		left join tteacher T on B.TeacherId=T.Id
		left join tassignbatch BA on B.Id = BA.BatchId and BA.StatusId=11
		left join tstudent S on BA.StudentId=S.Id 
		left join tuser U on S.UserId=U.Id 
		Where 
			ET.Id=P_TestId
			and B.Id=P_BatchId
            and S.Id=P_StudentId
			 and S.StatusId = 11;
	end if;
        
        
	else
		select 
			ET.Id,
			B.TeacherId,
			B.Id as BatchId,
            B.Title as BatchName ,
			S.Id as TeacherId,
			U.MobileNo as MobileNo,
			ET.Topic as Title,
			IFNULL(T.EmailId, '') AS EmailId,
            Case When (ET.MainTestTypeId=2 And ET.SubTestTypeId=1) then
		   replace(replace(P_Template,'[TestTopic]',ET.Topic),'Click here to evaluate.','Click here to Show.')
           else replace(replace(P_Template,'[TestTopic]',ET.Topic),'[StudentName]',U.Name) end AS MsgText,
			P_MailSubject AS Subject,
			2 AS ReceiverRoleTypeId,
			T.Id AS ReceiverSourceId,
            T.UserId As ReceiverUserId ,
            U.Id as UserId ,
             T.InstituteId AS InstituteId
		from tetest ET 
		left join tbatch B on B.Id=ET.BatchId
		left join tteacher T on B.TeacherId=T.Id
		left join tassignbatch BA on B.Id = BA.BatchId and BA.StatusId=11
		left join tstudent S on BA.StudentId=S.Id 
		left join tuser U on S.UserId=U.Id 
		Where 
			ET.Id=P_TestId
			and B.Id=P_BatchId
            and S.Id=P_StudentId
			 and S.StatusId = 11;
	end if;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spAlertGetTimeTable`(
   in P_Template varchar(500),
   in P_MailSubject varchar(100),
   in P_BatchId int,
   in P_TeacherId int,
   in P_IsSession bit,
   in P_TimeTableId int 
   -- in P_StudentId int
)
BEGIN
 
 if(P_TimeTableId = 0)
 then
    if(P_IsSession=1)
    then
     SELECT   
		TT.Id,
        B.Title as BatchName ,
        T.Id,
       -- S.StudentId,
       -- StatusId,
       -- Title AS StatusTitle,
        U.MobileNo,
		IFNULL(S.EmailId, '') AS EmailId,
		replace(replace(replace(P_Template,'[TeacherName]',U1.Name),'[FromDate]',ifnull(TT.FromDate,'')),'[BatchName]',B.Title) AS MsgText,
		P_MailSubject AS Subject,
		1 AS ReceiverRoleTypeId,
		S.Id AS ReceiverSourceId ,
        S.UserId As ReceiverUserId ,
		T.UserId As UserId ,
        T.InstituteId AS InstituteId ,
        B.Id As BatchId
        
	 FROM 
		  ttimetable TT
             Left join  tbatch B on B.Id = TT.BatchId
             left join tteacher T on T.Id=B.TeacherId
			 LEFT JOIN tassignbatch AB ON AB.BatchId = B.Id
		     LEFT JOIN tstudent S on AB.StudentId=S.Id 
            
            LEFT JOIN tuser U on S.UserId=U.Id  

			LEFT JOIN tuser U1 on T.UserId=U1.Id  
      
	 WHERE
            
            (B.Id = P_BatchId OR P_BatchId =0)
		AND	(T.Id =P_TeacherId OR P_TeacherId = 0)
        AND (TT.IsSession =1);
	
    
    else
    Select
      B.Title as BatchName ,
        T.Id,
       -- S.StudentId,
       -- StatusId,
       -- Title AS StatusTitle,
        U.MobileNo,
		IFNULL(S.EmailId, '') AS EmailId,
		replace(replace(P_Template,'[TeacherName]',U1.Name),'[BatchName]',B.Title) AS MsgText,
		P_MailSubject AS Subject,
		1 AS ReceiverRoleTypeId,
		S.Id AS ReceiverSourceId ,
        S.UserId As ReceiverUserId ,
		T.UserId As UserId ,
        T.InstituteId AS InstituteId ,
        B.Id As BatchId
	 FROM 
		   tbatch B 
             
             left join tteacher T on T.Id=B.TeacherId
              LEFT JOIN tassignbatch AB ON AB.BatchId = B.Id
		     LEFT JOIN tstudent S on AB.StudentId=S.Id 
             
            LEFT JOIN tuser U on S.UserId=U.Id  
			LEFT JOIN tuser U1 on T.UserId=U1.Id  
      
	 WHERE
            
            (B.Id = P_BatchId OR P_BatchId =0)
		AND	(T.Id =P_TeacherId OR P_TeacherId = 0);
        
     end if;   
   else
   
    if(P_IsSession=1)
    then
     SELECT   
		TT.Id,
		B.Title as BatchName ,
        T.Id,
       -- S.StudentId,
       -- StatusId,
       -- Title AS StatusTitle,
        U.MobileNo,
		IFNULL(S.EmailId, '') AS EmailId,
		replace(replace(replace(P_Template,'[TeacherName]',U1.Name),'[FromDate]',ifnull(TT.FromDate,'')),'[BatchName]',B.Title) AS MsgText,
		P_MailSubject AS Subject,
		1 AS ReceiverRoleTypeId,
		S.Id AS ReceiverSourceId ,
        S.UserId As ReceiverUserId ,
		T.UserId As UserId ,
        T.InstituteId AS InstituteId ,
        B.Id As BatchId
	 FROM 
		  ttimetable TT
             Left join  tbatch B on B.Id = TT.BatchId
             left join tteacher T on T.Id=B.TeacherId
              LEFT JOIN tassignbatch AB ON AB.BatchId = B.Id
		     LEFT JOIN tstudent S on AB.StudentId=S.Id 
            
            LEFT JOIN tuser U on S.UserId=U.Id  
			LEFT JOIN tuser U1 on T.UserId=U1.Id  
      
	 WHERE
            
            (B.Id = P_BatchId OR P_BatchId =0)
		AND	(T.Id =P_TeacherId OR P_TeacherId = 0)
        AND (TT.IsSession =1);
	
    
    else
    Select
      	B.Title as BatchName,
        T.Id,
       -- S.StudentId,
       -- StatusId,
       -- Title AS StatusTitle,
        U.MobileNo,
		IFNULL(S.EmailId, '') AS EmailId,
		replace(replace(P_Template,'[TeacherName]',U1.Name),'[BatchName]',B.Title) AS MsgText,
		P_MailSubject AS Subject,
		1 AS ReceiverRoleTypeId,
		S.Id AS ReceiverSourceId ,
        S.UserId As ReceiverUserId ,
          T.UserId As UserId ,
        T.InstituteId AS InstituteId
        
	 FROM 
		   tbatch B 
             left join tteacher T on T.Id=B.TeacherId
              LEFT JOIN tassignbatch AB ON AB.BatchId = B.Id
		     LEFT JOIN tstudent S on AB.StudentId=S.Id 
            
            LEFT JOIN tuser U on S.UserId=U.Id  
			LEFT JOIN tuser U1 on T.UserId=U1.Id  
      
	 WHERE
            
            (B.Id = P_BatchId OR P_BatchId =0)
		AND	(T.Id =P_TeacherId OR P_TeacherId = 0);
        
     end if;   
     
   end if;

END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spAlertHomeworkNotSubmitedGet`(
 in P_Template varchar(500),
   in P_MailSubject varchar(100),
	in P_AlertId int
)
BEGIN
SELECT 
		H.Id,
        H.Title,

        H.TeacherId,
        H.BatchId As BatchId ,
        B.Title As BatchName,
        H.HomeworkDateTime,
        HA.StudentId,
        HA.StatusId,
        SM.Title AS StatusTitle,
        U.MobileNo,
		IFNULL(S.EmailId, '') AS EmailId,
		Replace(Replace(replace(P_Template,'[BatchName]',B.Title),'[HomeworkTitle]',H.Title),'[HomeworkTopic]',H.Title) AS MsgText,
		P_MailSubject AS Subject,
        T.UserId As UserId , 
        U.name As UserName,
		1 AS ReceiverRoleTypeId,
		S.Id AS ReceiverSourceId ,
        S.UserId As ReceiverUserId , 
         S.InstituteId As InstituteId 
        
	FROM 
		thomework H
	LEFT JOIN thomeworkassign HA ON HA.HomeworkId = H.Id
    LEFT JOIN tstatusmaster SM  ON SM.Id = HA.StatusId AND StatusTypeId = 2
    left Join thomeworksubmittedanswer HSA ON HA.HomeworkId=HSA.HomeworkId AND HA.StudentId=HSA.StudentId
    LEFT JOIN tbatch B ON H.BatchId = B.Id
     left join tteacher T on T.Id=B.TeacherId
    LEFT JOIN tstudent S ON HA.StudentId = S.Id
    LEFT JOIN tuser U ON U.Id = S.UserId
	WHERE
		H.HomeworkDateTime<=now()
        And ifnull(HSA.StudentId,0)=0 And H.IsSentAlert=0
		AND ((P_AlertId=3 AND H.IsSendNotificationHomeworkNotSubmit=1) OR (P_AlertId=2 AND H.IsSendSMSHomeworkNotSubmit=1))
        AND S.StatusId = 11;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spAlertLogAdd`(
in P_AlertXML longtext, 
in P_InstituteId int
)
BEGIN
DECLARE AL_Count int default 0;
DECLARE i int default 1;
DECLARE FromId int default 0;
DECLARE ToId int default 0;
DECLARE InstituteIdVar int default 0;
SET FromId = (SELECT MAX(Id) FROM vida_lms.talertlog) + 1;
SET AL_Count = ExtractValue(P_AlertXML, 'COUNT(//Table//AlertLog)');
WHILE i <= AL_Count DO
	-- Start transaction;
    
    IF(ExtractValue(P_AlertXML, '//Table//AlertLog[$i]//ReceiverRoleTypeId') = 1) THEN 
	SET InstituteIdVar = (Select InstituteId FROM tstudent WHERE Id =  ExtractValue(P_AlertXML, '//Table//AlertLog[$i]//ReceiverSourceId'));
	ELSE
	SET InstituteIdVar =  (SELECT InstituteId FROM tTeacher WHERE Id =  ExtractValue(P_AlertXML, '//Table//AlertLog[$i]//ReceiverSourceId'));
    END IF;
	INSERT INTO vida_lms.`talertlog`
	(`ContactId`,
	`AlertMessage`,
	`AlertSubject`,
	`EventId`,
    `CreatedUserId`,
	`CreatedDateTime`,
	`AlertModeId`,
    `InstituteId`,
	`ReceiverRoleTypeId`,
	`ReceiverSourceId`)
	VALUES
	(ExtractValue(P_AlertXML, '//Table//AlertLog[$i]//ContactId'),
	ExtractValue(P_AlertXML, '//Table//AlertLog[$i]//AlertMessage'),
	ExtractValue(P_AlertXML, '//Table//AlertLog[$i]//AlertSubject'),

    ExtractValue(P_AlertXML, '//Table//AlertLog[$i]//EventId'),
    ExtractValue(P_AlertXML, '//Table//AlertLog[$i]//CreatedUserId'),
    NOW(),
	ExtractValue(P_AlertXML, '//Table//AlertLog[$i]//AlertModeId'),
    InstituteIdVar,
    ExtractValue(P_AlertXML, '//Table//AlertLog[$i]//ReceiverRoleTypeId'),
    ExtractValue(P_AlertXML, '//Table//AlertLog[$i]//ReceiverSourceId')
    
	);
	SET i= i+1;
-- commit;
END WHILE;

SET ToId = (SELECT MAX(Id) FROM vida_lms.talertlog);
SELECT 1 As Result, FromId, ToId;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spAssignBatch`(
in P_BatchId int,
    in P_StudentId varchar(100),
    in P_StatusId int
)
BEGIN
	declare P_Values varchar(500) default '';
    declare P_Insert varchar(500) default '';     
	declare V_Count int default 0;
    declare S_Count int default 0;
    declare V_i int default 0;
    declare S_i int default 1;
    declare V_StudentId int default 0;
    declare StudentIdConcate longtext default '';
-- DECLARE Exit HANDLER FOR SQLEXCEPTION
 -- SELECT "-1";
	if not exists( select 1 from tbatch,tstudent where tbatch.Id = P_BatchId  ) then
	begin
		select "-3";
	end;
    else
		begin
        drop table if exists t;
		CREATE Temporary table t( txt text );
		insert into t values(P_StudentId);
		drop temporary table if exists temp;
		create temporary table temp( Id INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY(Id), INDEX(Id) , val char(255) );
		set @sql = concat("insert into temp (val) values ('",replace(( select group_concat(distinct txt) as data from t), ",", "'),('"),"');");
		prepare stmt1 from @sql;
		execute stmt1;
        Set S_Count = (select Count( IFNULL(val,0)) from temp WHERE IFNULL(val,0) !=0);
         drop table if exists tempdata;
         create temporary table tempdata( TeacherId int,BatchId int,StudentId int);
        WHILE S_i <= S_Count do 
        SET v_i = 0;
        SET V_StudentId = (SELECT  IFNULL(t.val,0) FROM temp t WHERE  t.Id = S_i);
			if not exists(select 1 from tAssignBatch where BatchId=P_BatchId  and StudentId = V_StudentId and V_StudentId != 0) 
            then
				begin
                        SET P_Values = REPLACE(V_StudentId, ',', CONCAT(',',P_BatchId,',',P_StatusId, "),("));
		             	SET P_Values = CONCAT('(', P_Values, ',',P_BatchId,',',P_StatusId,")");
			            SET @Insert = CONCAT('INSERT INTO tassignbatch(StudentId,BatchId,StatusId) VALUES',P_Values);
			            PREPARE stmt FROM @Insert;
			            EXECUTE stmt;
                         Insert Into tempdata
                                 (TeacherId,BatchId,StudentId)
								  (select  B.TeacherId,P_BatchId,V_StudentId from tassignbatch AB inner join tBatch B ON B.Id = AB.BatchId where AB.BatchId = P_BatchId AND AB.StudentId = V_StudentId);
                                  
                        if exists (select 1 from tfeesetup where BatchId=P_BatchId) then
							
                            set V_count = (select count(*) from tassignbatch where BatchId=P_BatchId and StudentId = V_StudentId );
                            
                            while V_i<V_Count do
							
								Insert into tfeesetup
								  (TeacherId,BatchId,StudentId,FeeAmount,IsRecurringMonthly,DateOfFeeCollection,CreatedDateTime,CreatedUserId,DefaultFeeAmount,SetupDate)
								  (select  TeacherId,BatchId,V_StudentId,DefaultFeeAmount,IsRecurringMonthly,DateOfFeeCollection,now(),CreatedUserId,DefaultFeeAmount,SetupDate from tfeesetup where BatchId = P_BatchId AND Month(SetupDate) = Month(Now()) LIMIT 1);
								  SET V_i= V_i+1;
                                  
							
							END WHILE;
						end if;
				end;
			end if;

            SET S_i = S_i + 1;
           END WHILE;
           end;
           SELECT 1 as Result ;
         --  select TeacherId from tbatch where Id =P_BatchId;
           select * from  tempdata;
           drop table if exists tempdata;
           end if;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spAutoFeeSetupMonthly`(
    in P_TeacherId int,
    in P_BatchId int
 )
BEGIN
  
   
       Insert into  tfeesetup
         (
	       TeacherId,
	       BatchId,
	       StudentId,
	       FeeAmount, 
		   IsRecurringMonthly,
	       DateOfFeeCollection,
	       CreatedDateTime,
	       CreatedUserId,
	       DefaultFeeAmount,
	       SetupDate,
           IsSendSMSFeeDue,
           IsSendNotificationFeeDue
		 )
       select 
		    F.TeacherId ,
            F.BatchId , 
            F.StudentId,
            F.FeeAmount ,
            1  ,
            (DateOfFeeCollection + INTERVAL 1 MONTH) as DateOfFeeCollection,
            (CreatedDateTime + INTERVAL 1 MONTH) as CreatedDateTime , 
            F.CreatedUserId as CreatedUserId ,
            F.DefaultFeeAmount as DefaultFeeAmount,
            (SetupDate + INTERVAL 1 MONTH) as SetupDate,
            IsSendSMSFeeDue,
           IsSendNotificationFeeDue
	  from
         tfeesetup f
	  Where
           TeacherId = P_TeacherId
       And BatchId = P_BatchId
       and Month(F.setupdate)=Month(now()- interval 1 month)
         And Year(F.setupdate)=year(now()- interval 1 month);  

    
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spChatDetailsGet`(
   in P_ToRoleTypeId int,
   in P_ToUserId int ,
   in P_ToSourceId int ,
   in P_FromRoleTypeId int ,
   in P_FromUserId int ,
   in P_FromSourceId int ,
   in P_ChatGroupId int 
)
BEGIN
  
   if(P_ChatGroupId = 0)
   then 
       
       SELECT 
             ifnull(C.Id,0) as ChatId ,
             ifnull(C.ToRoleTypeId,0) as ToRoleTypeId,
             ifnull(C.ToUserId,0) as ToUserId, 
             ifnull(C.ToSourceId,0) as ToSourceId,
             ifnull(C.FromRoleTypeId,0) as FromRoleTypeId, 
             ifnull(C.FromUserId,0) as FromUserId, 
             ifnull(C.FromSourceId,0) as FromSourceId,
			 ifnull(C.ChatDetail,"") as ChatDetail, 
             ifnull(C.ContentPath,"") as ContentPath,
             ifnull(C.ChatGroupId,0) as ChatGroupId, 
		     ifnull(C.CreatedUserId,0) as CreatedUserId,
             ifnull(C.CreatedDateTime,'') as CreatedDateTime ,
             ifnull(CG.BatchId,0) as BatchId ,
             ifnull(B.Title,"") as BatchName ,
             ifnull(U1.Name,"") as ToUserName,
			 ifnull(U2.Name,"") as FromUserName ,
             ifnull(CG.Title,"") as GroupTitle
         From 
           tchat C 
           Left join tchatgroupmaster CG on CG.Id = C.ChatGroupId
           Left join tbatch B on B.Id = CG.BatchId
           Left join tuser U1 on U1.Id = C.ToUserId
           Left join tuser U2 on U2.Id = C.FromUserId
	 Where
              (( C.ToRoleTypeId = P_ToRoleTypeId 
           AND C.ToUserId = P_ToUserId 
           AND C.ToSourceId = P_ToSourceId
		   AND C.FromRoleTypeId = P_FromRoleTypeId
		   AND C.FromUserId = P_FromUserId
		   AND C.FromSourceId = P_FromSourceId ) OR 
           (C.FromRoleTypeId = P_ToRoleTypeId
           AND C.FromUserId = P_ToUserId 
           AND C.FromSourceId = P_ToSourceId
		   AND C.ToRoleTypeId = P_FromRoleTypeId
		   AND C.ToUserId = P_FromUserId
		   AND C.ToSourceId = P_FromSourceId))
           AND (P_ChatGroupId = 0)
           ;
          
   elseif(P_ChatGroupId > 0)
   then
        SELECT 
           ifnull(C.Id,0) as ChatId ,
             ifnull(C.ToRoleTypeId,0) as ToRoleTypeId,
             ifnull(C.ToUserId,0) as ToUserId, 
             ifnull(C.ToSourceId,0) as ToSourceId,
             ifnull(C.FromRoleTypeId,0) as FromRoleTypeId, 
             ifnull(C.FromUserId,0) as FromUserId, 
             ifnull(C.FromSourceId,0) as FromSourceId,
			 ifnull(C.ChatDetail,"") as ChatDetail, 
             ifnull(C.ContentPath,"") as ContentPath,
             ifnull(C.ChatGroupId,0) as ChatGroupId, 
		     ifnull(C.CreatedUserId,0) as CreatedUserId,
             ifnull(C.CreatedDateTime,'') as CreatedDateTime ,
             ifnull(CG.BatchId,0) as BatchId ,
             ifnull(B.Title,"") as BatchName ,
             ifnull(U1.Name,"") as ToUserName,
			 ifnull(U2.Name,"") as FromUserName ,
              ifnull(CG.Title,"") as GroupTitle
         From 
           tchat C 
           Left join tchatgroupmaster CG on CG.Id = C.ChatGroupId
           Left join tbatch B on B.Id = CG.BatchId
           Left join tuser U1 on U1.Id = C.ToUserId
           Left join tuser U2 on U2.Id = C.FromUserId
	 Where
            (CG.Id = P_ChatGroupId );
            
   end if;
   
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spClassListGet`(IN `P_TeacherId` INT)
    NO SQL
SELECT tsubcategorymaster.Id As SubCategoryId, tsubcategorymaster.Code As SubCategoryName

 From 

tsubcategorymaster 

INNER join tteacher  ON tsubcategorymaster.Id = tteacher.SubcategoryIds

WHERE tteacher.Id = P_TeacherId$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spConnectSignalR`(P_UserName varchar(50), P_ConnectionId varchar(1000))
BEGIN
DECLARE UserId int default 0;
Select UserId = Id from tsignalrconnection SR INNER JOIN tuser U  ON SR.UserId = U.Id WHERE U.UserName = P_UserName;
if (UserId > 0) THEN
	begin 
    update tsignalrconnection set ConnectionId = P_ConnectionId ,IsConnected = 1 where UserId = UserId;
    SELECT UserId As Result;
	end ;
	else 
	begin 
	Insert into tsignalrconnection(UserId, ConnectionId, IsConnected,ConnectionDateTime) Values(UserId  + P_ConnectionId  , 1,GetDate());
    SELECT UserId As Result;
	end;
    end if;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spCopyTest`(
	in P_TestId int,
    in P_BatcaXml longtext
)
BEGIN
	Declare Count int default 0;
    declare i int default 1;
    declare P_Id int default 0;
    declare P_ParaId int default 0;
    DROP TEMPORARY TABLE IF EXISTS T_Result;
    
    
    CREATE TEMPORARY TABLE T_Result(BatchId int, OldQuestionPath longtext, OldOptionPath longtext, NewQuestionId int, NewOptionId Int);
    SET count =  ExtractValue(P_BatcaXml, 'COUNT(//Batch)');
	WHILE i <= Count DO
		DROP TEMPORARY TABLE IF EXISTS TEMPtparaquestionsIds;
    DROP TEMPORARY TABLE IF EXISTS TEMPtetestquestionsIds;
		INSERT INTO `vida_lms`.`tetest`
		(`MainTestTypeId`,
		`SubTestTypeId`,
		`CorrectAnswerMarks`,
		`PenaltyMarks`,
		`BatchId`,
		`Topic`,
		`Description`,
		`Duration`,
		`StartDate`,
		`StartTime`,
		`ExpiryDate`,
		`ExpiryTime`,
		`Maxmarks`,
		`IsSubmitted`,
		`SubCategoryId`,
		`CreatedUserId`,
		`CreatedDateTime`)
		(SELECT 
		`tetest`.`MainTestTypeId`,
		`tetest`.`SubTestTypeId`,
		`tetest`.`CorrectAnswerMarks`,
		`tetest`.`PenaltyMarks`,
		ExtractValue(P_BatcaXml, '//Batch[$i]//BatchId'),
		`tetest`.`Topic`,
		`tetest`.`Description`,
		`tetest`.`Duration`,
		`tetest`.`StartDate`,
		`tetest`.`StartTime`,
		`tetest`.`ExpiryDate`,
		`tetest`.`ExpiryTime`,
		`tetest`.`Maxmarks`,
		`tetest`.`IsSubmitted`,
		`tetest`.`SubCategoryId`,
		`tetest`.`CreatedUserId`,
		now()
		FROM `vida_lms`.`tetest`
		where id=P_TestId);
		SET P_Id = LAST_INSERT_ID();
        
        INSERT INTO `vida_lms`.`tparaquestions`
			(`TestId`,
			`Title`,
			`Code`)
			(SELECT 
				P_Id,
				`tparaquestions`.`Title`,
				`tparaquestions`.`Code`
			FROM `vida_lms`.`tparaquestions`
            where id=P_TestId);
		CREATE TEMPORARY TABLE TEMPtparaquestionsIds
        (SELECT 
				tpold.`id` as Id1,
				tpnew.`id` as Id2
			FROM `vida_lms`.`tparaquestions` tpold
            inner join tparaquestions tpnew on tpold.TestId=P_TestId and tpnew.TestId=P_Id
            where tpnew.id=P_Id);
        
        
        INSERT INTO `vida_lms`.`tetestquestions`
		(`TestId`,
		`QuestionTypeId`,
		`Title`,
		`Marks`,
		`IsSubmitted`,
		`IsSingleAnswer`,
		`ParaId`)
		(SELECT 
			P_Id,
			`tetestquestions`.`QuestionTypeId`,
			`tetestquestions`.`Title`,
			`tetestquestions`.`Marks`,
			`tetestquestions`.`IsSubmitted`,
			`tetestquestions`.`IsSingleAnswer`,
			TEMPtparaquestionsIds.Id2
		FROM `vida_lms`.`tetestquestions`
        left join TEMPtparaquestionsIds on tetestquestions.ParaId=TEMPtparaquestionsIds.Id1
        where TestId=P_TestId);
        
		CREATE TEMPORARY TABLE TEMPtetestquestionsIds
        (SELECT 
			oldtoq.`id` as questionsid1,
            newtoq.id as questionsid2,
            oldtoq.TestId as TestId,
            oldtoq.FilePath as path
		FROM `vida_lms`.`tetestquestions` oldtoq
        inner join tetestquestions newtoq on oldtoq.TestId=P_TestId and newtoq.TestId=P_Id
        where newtoq.TestId=P_Id);
        
        INSERT INTO `vida_lms`.`tetestquestionoptions`
			(`QuestionId`,
			`Options`,
			`IsKey`,
			`OrderIndex`)
			(SELECT
				temp.questionsid2,
				`tetestquestionoptions`.`Options`,
				`tetestquestionoptions`.`IsKey`,
				`tetestquestionoptions`.`OrderIndex`
			FROM `vida_lms`.`tetestquestionoptions`
            inner join TEMPtetestquestionsIds temp on temp.questionsid1=tetestquestionoptions.QuestionId
            where temp.TestId=P_TestId);
            
          /*  select *,ExtractValue(P_BatcaXml, '//Batch[$i]//BatchId')  
		FROM tetestquestionoptions tqo
        inner join TEMPtetestquestionsIds temptqo on temptqo.questionsid1=tqo.QuestionId
        inner join tetestquestionoptions tqo1 on temptqo.questionsid2=tqo1.QuestionId
        where temptqo.TestId=P_TestId and (temptqo.Path is not null or tqo.FilePath is not null);*/
          insert into T_Result
          (select ExtractValue(P_BatcaXml, '//Batch[$i]//BatchId'),temptqo.path ,tqo.FilePath ,temptqo.questionsid2 ,tqo1.id  
		FROM tetestquestionoptions tqo
        inner join TEMPtetestquestionsIds temptqo on temptqo.questionsid1=tqo.QuestionId
        inner join tetestquestionoptions tqo1 on temptqo.questionsid2=tqo1.QuestionId and tqo1.OrderIndex=tqo.OrderIndex
        where temptqo.TestId=P_TestId and (temptqo.Path is not null or tqo.FilePath is not null) );
		SET i= i+1;
	END WHILE;
    
    select * from  T_Result;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spCreateAdmin`(
	in P_Code varchar(10),in P_UserId int
)
BEGIN
	declare TeacherId int default 0;
    declare TeacherCode varchar(10) default '';
    declare IsSuperAdmin int default 0;

	if exists(select * from tadmin where UserId=P_UserId) then
		begin
			select id as AdminId,code as AdminCode from tadmin where UserId=P_UserId;
        end;
	else
    if (select Count(*) from tuser)=1 then
		Insert into 
			tteacher(Code,UserId,CreatedDateTime,UpdatedDateTime,InstituteId) 
		values(P_Code,P_UserId,now(),now(),0);
        set TeacherId = LAST_INSERT_ID();
        set TeacherCode = P_code;
        set IsSuperAdmin=1;
	end if;
		Insert into 
			tadmin(Code,UserId,IsSuperAdmin,InstituteId,CreatedDateTime,UpdatedDateTime) 
		values(P_Code,P_UserId,IsSuperAdmin,0,now(),now());
  
    select LAST_INSERT_ID() as AdminId,P_Code as AdminCode,TeacherId,TeacherCode;
    
	end if;	
	select 5;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spCreateBatch`(
     in P_TeacherId int(11),
     in P_StudentId varchar(100),
	 in P_InstituteId int(11),
     in P_Code  varchar(20),
     in P_Title varchar(100),
     in P_BatchDate date
)
BEGIN
    
    declare P_BatchId int default 0;
	declare P_Values varchar(500) default '';
    declare P_Insert varchar(500) default '';
    declare P_UserId  int default 0;
    
    if not exists(select 1 from tteacher where Id=P_TeacherId )  
    then
         
         Begin 
            select -1 ;  
		 END ;
         
      elseif exists  ( select 1 from tbatch where TeacherId =  P_TeacherId AND Title =P_Title ) 
       then
         
         Begin
            select -2 ;
		 END ;
         
	  elseif not exists ( select 1 from tinstitute where Id =  P_InstituteId) 
       then
         
         Begin
            select -3 ;
		 END ;
         
      else   
     
    BEGIN
    start transaction;
    INSERT INTO  tbatch
      (
          TeacherId,Code,Title,BatchDate,CreatedDateTime,UpdatedDateTime
      )
    VALUES  
      (
          P_TeacherId,P_Code,P_Title,P_BatchDate,now(),now()
      );
	 set P_BatchId=LAST_INSERT_ID();
     set P_UserId=(Select UserId from tteacher where Id = P_TeacherId);
     Insert INTO tchatgroupmaster
       (
          Title , CreatedUserId , CreatedDateTime , BatchId
       )
     Values 
       (
          P_Title , P_UserId ,now(),P_BatchId
       );
      if not P_StudentId='0' 
      then
       
          SET P_Values = REPLACE(P_StudentId, ',', CONCAT(',',P_BatchId,",",11, "),("));
			SET P_Values = CONCAT('(', P_Values, ',',P_BatchId,",",11, ")");
			SET @Insert = CONCAT('INSERT INTO tassignbatch(StudentId,BatchId,StatusId) VALUES',P_Values);
			PREPARE stmt FROM @Insert;
			EXECUTE stmt;
            select P_BatchId;
      else
		select P_BatchId;
      end if;
    COMMIT;
     end;
    
	 end if;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spCreateChatList`(
	in P_FromUserId int,
	in P_FromRoleTypeId int,
	in P_ToUserId int,
	in P_ToRoleTypeId int,
	in P_GroupId int,
	in P_ChatDetail varchar(1000),
	in P_BatchId int,
    in P_ContentPath varchar(500)
)
BEGIN
declare ChatId int default 0;
	if exists(select 1 from tchatgroupmaster where BatchId=P_BatchId or P_GroupId=0) then
		if P_ContentPath is null then
			begin
				insert into tchat(ChatGroupId,ToRoleTypeId,ToUserId,FromRoleTypeId,FromUserId,ChatDetail,CreatedUserId,CreatedDateTime)
				values(P_GroupId,P_ToRoleTypeId,P_ToUserId,P_FromRoleTypeId,P_FromUserId,P_ChatDetail,(select ifnull(CreatedUserId,0) from tchatgroupmaster where Id=P_GroupId),now());
				select LAST_INSERT_ID();
			end;
		else
			begin
				insert into tchat(ChatGroupId,ToRoleTypeId,ToUserId,FromRoleTypeId,FromUserId,CreatedUserId,CreatedDateTime)
				values(P_GroupId,P_ToRoleTypeId,P_ToUserId,P_FromRoleTypeId,P_FromUserId,(select ifnull(CreatedUserId,0) from tchatgroupmaster where Id=P_GroupId),now());
                set ChatId= LAST_INSERT_ID();
                UPDATE tchat 
                set ContentPath=Concat(P_ContentPath,'\\',ChatId,'\\',P_ChatDetail)
                where id=ChatId;
				select ChatId;
			end;
		end if;
	else 
		select -1;
	end if;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spCreateComment`(
    in P_RefferenceCommentId int,
	in P_UserId int,
    in P_RoleTypeId int,
    in P_AssignContentId int,
	in P_CommentMessage varchar(1000)
 )
BEGIN

    start transaction; 
    
	Insert Into tstudymaterialcomment
    
       (ReferenceCommentId,UserId,RoleTypeId,AssignContentId,CommentDetail,CreatedDateTime)
       
     Values
        
        (P_RefferenceCommentId,P_UserId,P_RoleTypeId,P_AssignContentId,P_CommentMessage,now());  
        
        select  LAST_INSERT_ID();
    
    commit;
    

END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spCreateHomeWork`( 
   in P_StudentIds LONGTEXT,
   in P_TeacherId int,
   in P_BatchId int,
   in P_StatusId int,
   in P_Title varchar(20),
   in P_Notes varchar(45),
   in P_SubmissionDateTime datetime,
   in P_IsSendNotificationHomeworkNotSubmit bit,
   in P_IsSendSMSHomeworkNotSubmit bit
  
)
BEGIN 
	declare P_HomeWorkId int default 0;
	declare P_Values varchar(500) default '';
    declare P_Insert varchar(500) default '';     
        
		if  exists(select 1  from tstudent,tteacher,tbatch where tbatch.Id=P_BatchId AND tteacher.Id = P_TeacherId AND FIND_IN_SET(tstudent.Id,P_StudentIds))
		then
        
         Start Transaction;
         
         BEGIN
          INSERT INTO thomework
          
           (StudentId,TeacherId,BatchId,Title,Note,HomeWorkDateTime,CreatedDateTime,UpdatedDateTime,IsSendNotificationHomeworkNotSubmit,IsSendSMSHomeworkNotSubmit)
           
          Values
          
          (P_StudentIds,P_TeacherId,P_BatchId,P_Title,P_Notes,P_SubmissionDateTime,now(),now(),P_IsSendNotificationHomeworkNotSubmit,P_IsSendSMSHomeworkNotSubmit);
          
          set P_HomeWorkId=LAST_INSERT_ID();
          SET P_Values = REPLACE(P_StudentIds, ',', CONCAT(',',P_HomeworkId,',',P_StatusId, '),('));
			SET P_Values = CONCAT('(', P_Values, ',',P_HomeworkId,',',P_StatusId, ')');
			SET @Insert = CONCAT('INSERT INTO thomeWorkAssign(StudentId,HomeworkId,StatusId) VALUES',P_Values);
			PREPARE stmt FROM @Insert;
			EXECUTE stmt;
          
          SELECT P_HomeWorkId;
          commit;
          end;
          
          
     
     else 
	    Select -1;
	 end if;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spCreateInstitute`(
	in P_UserId int,
	in P_UserName varchar(100),
    in P_Code varchar(20),
    in P_Title varchar(100),
    in P_RoleTypeId int
)
BEGIN

Declare l_InstituteId INT DEFAULT 0;

  
	if exists(select 1 from tuser where id=P_UserId and P_RoleTypeId=RoleTypeId) 
    then    
    if P_UserName is not null 
    then 
    
		Update tuser
			set Name =P_UserName,
            UpdatedDateTime = now()
            where id=P_UserId;
		
	end if;
    
		if exists(select 1 from tinstitute where lower(Title)= lower(trim(P_Title))and P_UserId = CreatedUserId) then
			begin  
				select -2 As StatusCode;
			end;
            
            
		elseif (P_RoleTypeId=2 or P_RoleTypeId=3) and exists(select 1 from tteacher where UserId= P_UserId)
        then 
        
			    Insert into  
					tinstitute(CreatedUserId,Code,Title,CreatedDateTime) 
				values
                    (P_UserId,P_Code,P_Title,now());
                    
                 Set l_InstituteId = LAST_INSERT_ID();
			if exists (select 1 from tteacher where P_UserId=UserId and InstituteId=0) 
            then  
             
				Update tteacher 
                set InstituteId=LAST_INSERT_ID(),
                StatusId=11, DateOfJoining=now()
                where UserId=P_UserId and InstituteId=0;
                
			else  
            
           Insert into 
				   tteacher(StatusId,Code,UserId,EmailId,YearOfExperience,DateOfBirth,CreatedDateTime,UpdatedDateTime,InstituteId,FacebookLink,InstagramLink,GenderId,GoogleMapLink,BloodGroupId,AadharCardNumber,DateOfJoining,About) 
		   -- values
			 	  -- (11,P_Code,P_UserId,now(),now(),LAST_INSERT_ID(), now());
		   select
                 11,P_Code,P_UserId,EmailId,YearOfExperience,DateOfBirth,now(),now(),l_InstituteId,FacebookLink,InstagramLink,GenderId,GoogleMapLink,BloodGroupId,AadharCardNumber,now(),About
		    from  tteacher 
            where  (UserId = P_UserId)  Limit 1;
                  
			end if;
            
		-- select 1;
           
          select             
			   1 AS StatusCode , 
              tinstitute.Id as InstituteId ,
              tinstitute.Title as InstituteName,
              -- ifnull(tstudent.Id,'') as StudentId,
               tteacher.Id as TeacherId ,
              tteacher.Code as TeacherCode
	      from
             tinstitute 
			   Left Join tteacher on tteacher.InstituteId = tinstitute.Id
            --   Left Join tstudent on tstudent.InstituteId = tinstitute.Id
          where
               tinstitute.Id =l_InstituteId;
               SELECT l_InstituteId;
	          -- AND tstudent.UserId = P_UserId ;  
           
		else
        
			select -5  As StatusCode;
            
		end if;
        
	   Update tteacher
			 set
            UpdatedDateTime = now()
            where UserId=P_UserId;
	else
    
		if not exists(select 1 from tuser where id=P_UserId ) 
        then 
        
			select -3  As StatusCode;

            
		elseif not exists(select 1 from tuser where id=P_UserId and  P_RoleTypeId=RoleTypeId) 
        then 
        
			select -4  As StatusCode;
		
		end if;
	end if;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spCreateNoticeBoard`(
    IN `P_BatchId` INT, 
    IN `P_Remark` VARCHAR(500)
    )
BEGIN

DECLARE Exit HANDLER FOR SQLEXCEPTION

    begin

		SELECT -1;

	end;
	if exists(select 1 from tbatch where Id=P_BatchId) then

		START TRANSACTION;

		INSERT 
			INTO tnoticeboard(BatchId,Remark,CreatedDateTime,UpdatedDateTime) 
            VALUES(P_BatchId,P_Remark,now(),now());

		select Concat(TeacherId,'\\',LAST_INSERT_ID())
        from tbatch 
        where Id =P_BatchId;

		COMMIT;
	else
		select -2;
	end if;

end$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spCreateNotification`(
    IN `P_TeacherId` INT, 
	IN `P_BatchId` VARCHAR(45), 
    IN `P_Title` VARCHAR(45), 
    IN `P_NotificationText` VARCHAR(1000), 
    IN `P_NotificationDate` DATETIME)
BEGIN
  declare P_NotificationId int default 0; 
   
    start transaction;
    
       Insert Into tnotification
           (TeacherId,BatchId,Title,NotificationText,NotificationDate,CreatedDateTime,UpdatedDateTime)
	  values 
           (P_TeacherId,P_BatchId,P_Title,P_NotificationText,P_NotificationDate,now(),now());
           
     
       set P_NotificationId=LAST_INSERT_ID();
         
        select P_NotificationId;
   
    commit;
end$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spCreatePaymentTransaction`(
     in P_FeeId int,
	 in P_StudentId int,
	 in P_PaymentAmount int,
     in P_TransactionStatusId int,
     in P_PaymentRemark varchar(500),
     in P_OSTypeId smallint,
     in P_DateOfFeeCollection Date
)
BEGIN
	IF EXISTS(SELECT 1 FROM tfeesetup where Id = P_FeeId AND StudentId = P_StudentId)
    THEN 
		Start transaction;

		 
		INSERT INTO tpaymenttransaction
			(FeeSetupId,
			StudentId,
			PayAmount,
			PayDateTime,
			PaymentRemark,
			TransactionStatusId,
            DateOfFeeCollection)
		VALUES
			(P_FeeId,
            P_StudentId,
            P_PaymentAmount,
            NOW(),
            P_PaymentRemark,
            P_TransactionStatusId,
            P_DateOfFeeCollection);
		
        SELECT last_insert_id() AS Result;
		Commit;
	else
		select -1 AS Result;
	end if;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spCreateStudent`(
	in P_Code varchar(10),in P_UserId int
)
BEGIN
	if exists(select * from tstudent where UserId=P_UserId) then
		begin
			select P_UserId;
        end;
		
	else
    
	Insert into 
		tstudent(Code,UserId,InstituteId,CreatedDateTime,UpdatedDateTime) 
	values(P_Code,P_UserId,0,now(),now());
    select LAST_INSERT_ID();
    
    end if;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spCreateSubmitHomeWork`(
    in P_HomeWorkId int,
    in P_StudentId int
 )
BEGIN
     
      if exists(select 1 from thomework where Id = P_HomeWorkId AND FIND_IN_SET(P_StudentId,StudentId)>0)
      then
      if not exists (select 1 from thomeworksubmittedanswer where HomeworkId=P_HomeWorkId and StudentId=P_StudentId)then
      Start transaction;
      
		  Insert Into thomeworksubmittedanswer 
			   (HomeWorkId,StudentId)
			   
		   Values (P_HomeWorkId,P_StudentId) ;   
		select 1;
      commit;
      else
		select 1;
      end if;
      else
         
         if not exists(select 1 from thomework where Id = P_HomeWorkId AND   FIND_IN_SET(P_StudentId,StudentId)>0)
         then 
         select -2;
         
         else
            
             select -1;
          end if;
           
         
         
         
	 end if;
     
     
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spCreateTeacher`(
	in P_Code varchar(10),in P_UserId int
)
BEGIN
	if exists(select * from tteacher where UserId=P_UserId ) then
		begin
			select P_UserId;
        end;
		
	else
    Insert into 
			tteacher(Code,UserId,CreatedDateTime,UpdatedDateTime,InstituteId) 
		values(P_Code,P_UserId,now(),now(),0);
		select LAST_INSERT_ID();
        
	end if;
	
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spCreateUser`(
  IN P_Name varchar(100),
  IN P_RoleTypeId smallint,
  IN P_MobileNo varchar(20),
  IN P_IsActive bit
 -- IN P_OTP varchar(10),
 -- IN P_OSTypeId smallint,
 -- IN P_DeviceToken varchar(200)
)
BEGIN 
    
    DECLARE l_user_id INT DEFAULT 0;
    
	if exists(select 1 from tuser where MobileNo=P_MobileNo and  RoleTypeId=P_RoleTypeId) then
		select Id As UserId, ifnull(Name,'') as UserName,MobileNo as MobileNo ,RoleTypeId,1 as StatusCode
        from tuser where MobileNo=P_MobileNo and RoleTypeId=P_RoleTypeId;
	ELSE
		start transaction;
			Insert into 
				tuser (
					Name,
					RoleTypeId,
                    MobileNo,
                    IsActive,
                    OTPExpiryDate,
                    CreatedDatetime,
                    UpdatedDateTime
                --    DeviceToken,
                 --   OSTypeId
				) 
				values(
					P_Name,
					P_RoleTypeId,
                    P_MobileNo,
                    P_IsActive,
                    ADDTIME(now(), '1000'),
                    now(),
                    now()
                  --  P_DeviceToken,
                  --  P_OSTypeId
				);

			SET l_user_id = LAST_INSERT_ID();
            
          --   call spAddRegisterUser(l_user_id,P_OSTypeId,P_DeviceToken);
			
            SELECT
				Id as UserId, 
                ifnull(Name,'') as UserName, 
                RoleTypeId, 
                MobileNo,
                1 AS StatusCode
			FROM 
				tuser 
			WHERE Id = l_user_id;
            
		COMMIT;
        
        -- select 1;
        
	END if;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spDeleteAssignTeacherContent`(
	in P_ContentId int
)
BEGIN
	DECLARE Exit HANDLER FOR SQLEXCEPTION
    begin
		SELECT -1;
	end;
    start transaction;
		Delete from tteachercontentassign
        where Id=P_ContentId;
		select 1;
    commit;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spDeleteBatch`(IN `P_BatchId` INT)
BEGIN
  
     start transaction;
   
      Delete  
      From tchatgroupmaster
      Where BatchId = P_BatchId;
      
      Delete  
      From tetest
      Where BatchId = P_BatchId;
      
      Delete 
      From tfeesetup
      Where BatchId = P_BatchId;
      
      Delete  
      From thomework
      Where BatchId = P_BatchId;
      
      Delete 
      From tnoticeboard
      Where BatchId = P_BatchId;
      
      Delete 
      From tonlinelecture
      Where BatchId = P_BatchId;
      
      Delete  
      From tonlinelecturemeetingaccount
      Where BatchId = P_BatchId;
      
      Delete
      From tonlinelecturemeetingsettings
      Where BatchId = P_BatchId;
      
      Delete  
      From tteachercontentassign
      Where BatchId = P_BatchId;
      
      Delete
      From ttimetable
      Where BatchId = P_BatchId;
      
	UPDATE tnotification
	SET  BatchId = REPLACE (REPLACE (REPLACE(BatchId,concat(P_BatchId,','),'') , concat(',',P_BatchId) ,''), P_BatchId,'')
	WHERE   FIND_IN_SET (P_BatchId ,BatchId );
      
      Delete
      From tassignbatch
      Where BatchId = P_BatchId;
      
      DELETE FROM tnotification WHERE BatchId = '';
      
      Delete
      From tbatch
      Where Id = P_BatchId;
   
	
   
   
    --  DELETE tbatch , tassignbatch
    --  FROM tbatch 
    --  LEFT JOIN tassignbatch on tbatch.Id=tassignbatch.BatchId
    --  WHERE tbatch.Id = P_BatchId ;
      commit;
      select 1;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spDeleteComment`(
    in P_UserId int,
    in P_CommentId int
 )
BEGIN
 
	if exists(select 1 from tstudymaterialcomment where  Id =P_CommentId AND UserId = P_UserId)
	then
		-- SET SQL_SAFE_UPDATES=0;
		DELETE FROM tstudymaterialcomment
		WHERE Id = P_CommentId OR ReferenceCommentId = P_CommentId;
    
		select 1;
		commit;
    else
		select -1;
	end if;
 
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spDeleteContentPath`(
	in P_ContentId int
)
BEGIN
	declare P_TeacherContentId int; 
	DECLARE Exit HANDLER FOR SQLEXCEPTION
		begin
			Rollback;
			select -1;
		end;
	if exists(select 1 from tteacherContent where Id=P_ContentId and ContentLink is null) then
		start transaction;
			Delete from tteacherContent
			where Id=P_ContentId;
			delete from tteachercontentassign
			where TeacherContentId=P_ContentId;
			select 1;
		commit;
	else
		start transaction;
        set P_TeacherContentId = (select * from tteachercontent where REGEXP_LIKE(ContentPath,Concat("^Content.AssignContent.[a-zA-a0-9]*",P_ContentId,"$")));
			Delete from tteacherContent
				where Id=P_ContentId or id in (select P_TeacherContentId);
			delete from tteachercontentassign
				where TeacherContentId in (select P_TeacherContentId);
                select 1;
		commit;
	end if;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spDeleteDoubtSession`(
     in P_TimeTableId int
     
  )
BEGIN
  
    Delete From ttimetable
    Where
	    Id = P_TimeTableId;
       
	select 1;
    commit;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spDeleteETestQuestions`(
	in P_QuestionId int
)
BEGIN
	declare QuestionPath varchar(500) default '' ;
	start transaction;
		if not exists ( select 1 from tetestquestions where Id =P_QuestionId and IsSubmitted=1) then
        set QuestionPath = (select Filepath from tetestquestions where id=P_QuestionId);
			DELETE tqo 
				FROM tetestquestionoptions tqo 
					where tqo.QuestionId=P_QuestionId;
			DELETE ttq 
				FROM tetestquestions ttq 
				WHERE ttq.Id =P_QuestionId;
				SELECT QuestionPath;
		else 
			select -1;
		end if;
    commit ;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spDeleteHomeWork`(IN `P_HomeWorkId` INT)
    NO SQL
BEGIN
	DELETE From thomework WHERE Id = P_HomeWorkId;
    DELETE From thomeworkassign WHERE HomeWorkId = P_HomeWorkId;
	SELECT 1;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spDeleteInstitute`(
	in P_InstituteId int 
)
BEGIN

	-- delete content releted to Institute
	delete tta,ttc 
    from 
		tteachercontent ttc
	left join tteachercontentassign tta on tta.TeacherContentId=ttc.Id
    left join tstudymaterialcomment tsc on tsc.AssignContentId=tta.TeacherContentId
    inner join tteacher tt on tt.Id=ttc.TeacherId 
    where tt.InstituteId=P_InstituteId;
    
    -- delete test relted to Institute
    delete tpq
    from tetest te
    left Join tparaquestions tpq on tpq.testId=te.Id
    inner join tteacher tt on tt.Id=te.CreatedUserId
    where tt.InstituteId=P_InstituteId;
    
    delete te,ttq,ttqo,tsa,tr
    from tetest te
    left join tetestquestions ttq on ttq.TestId=te.Id
    left join tetestquestionoptions ttqo on ttqo.QuestionId=ttq.Id
    left join teteststudentanswer tsa on tsa.TestId=te.Id
    left join tresult tr on tr.TestId=te.Id 
    inner join tteacher tt on tt.Id=te.CreatedUserId
    where tt.InstituteId=P_InstituteId;
    
    -- delete homework releted to institute
    delete th,tha,thsa
    from thomework th
    inner join tteacher tt on tt.Id=th.TeacherId
    left join thomeworkassign tha on tha.HomeworkId=th.Id 
    left join thomeworksubmittedanswer thsa on thsa.HomeworkId=th.Id
    where tt.InstituteId=P_InstituteId;
    
    -- delete noticeboard releted to institute
    delete tn
    from tbatch tb
    inner join tnoticeboard tn on tn.BatchId=tb.Id
    inner join tteacher tt on tt.Id=tb.TeacherId
	where tt.InstituteId=P_InstituteId;
    
    -- delete online lecture releted to institute

    delete tola, tols,tol,tolsa
    from tbatch tb
    left join tonlinelecture tol on tol.BatchId=tb.Id
    left join tonlinelearningstudentattendance tolsa on tolsa.OnlineLectureId=tol.Id
    left join tonlinelecturemeetingaccount tola on tola.BatchId=tb.id
    left join tonlinelecturemeetingsettings tols on tols.BatchId=tb.Id
    inner join tteacher tt on tt.Id=tb.TeacherId
	where tt.InstituteId=P_InstituteId;
  
    -- delete timetable releted to institute
    delete ttm 
    from ttimetable ttm
    left join tbatch tb on ttm.BatchId=tb.Id
    inner join tteacher tt on tt.Id=tb.TeacherId
	where tt.InstituteId=P_InstituteId;
    
	-- delete chat releted to institute
    
    delete tc,tcg
    from tteacher tt
    left join tbatch tb on tt.Id=tb.TeacherId
    inner join tchatgroupmaster tcg on tcg.BatchId=tb.Id 
    inner join tchat tc on tc.CreatedUserId=tt.UserId
    where tt.InstituteId=P_InstituteId;
        
    -- delete users releted to institute
    delete tn 
     from tteacher tt
     inner join tnotification tn on tn.TeacherId=tt.Id
     where tt.InstituteId=P_InstituteId;
    
    -- delete batch releted to institute
    delete tb,tab
    from tinstitute ti
    left join tteacher tt on ti.Id=tt.InstituteId
    left join tbatch tb on tt.Id=tb.TeacherId
    left join tassignbatch tab on tab.BatchId=tb.Id
    where ti.id=P_InstituteId;
    -- delete users releted to institute
    
    delete ta
    from tinstitute ti
    left join tadmin ta on ti.Id=ta.InstituteId
    where ti.id=P_InstituteId;
    
    delete tt
    from tinstitute ti
    left join tteacher tt on ti.Id=tt.InstituteId
    where ti.id=P_InstituteId;
    
    delete ti,ts
    from tinstitute ti
    left join tstudent ts on ti.Id=ts.InstituteId
    where ti.id=P_InstituteId;
    
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spDeleteNoticeBoard`(
 in P_NoticeId int 
)
BEGIN
	DECLARE Exit HANDLER FOR SQLEXCEPTION
    begin
		SELECT -1;
	end;
    if not exists(select 1 from tnoticeboard where id=P_NoticeId) then 
		select -2;
	else
		start transaction;
			Delete from tnoticeboard
			where Id=P_NoticeId;
			select 1;
		commit;
	end if;
    
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spDeleteNotification`(
      in P_NotificationId int
 )
BEGIN

     Delete from tnotification
     where 
            Id=P_NotificationId;
            select 1;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spDeleteOnlinelecture`(
	in P_LectureId int
)
BEGIN
	if exists(select 1 from tonlinelecture where Id=P_LectureId) then
		DELETE FROM `vida_lms`.`tonlinelecture`
			WHERE P_LectureId;
	else 
		select -1;
	end if;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spDeleteOnlineMeetingAccount`(
	in P_AccountId int
)
BEGIN
	 if exists(select 1 from tonlinelecturemeetingaccount where id = P_AccountId) 
     then
		DELETE
               tonlinelecturemeetingaccount , 
               tonlinelecturemeetingsettings 
         From    
            tonlinelecturemeetingaccount
            LEFT JOIN  tonlinelecturemeetingsettings  ON tonlinelecturemeetingaccount.BatchId = tonlinelecturemeetingsettings.BatchId 
		 WHERE 
               tonlinelecturemeetingaccount.Id=P_AccountId; 
        Commit;
		  select 1;
	  else  
	    select -1;
 end if;

END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spDeleteStudent`(
      in P_StudentId int,
      in P_BatchId int,
      in P_InstituteId int
 )
BEGIN
        
       if(P_BatchId=0)
       then
        
	  if exists(select 1 from tstudent,tbatch,tinstitute where tstudent.Id = P_StudentId AND  tinstitute.Id = P_InstituteId)
      then
 
       start transaction;
    
       Delete from tstudent 
       where tstudent.Id = P_StudentId;
        
       call spUnassignBatch(P_BatchId,P_StudentId); 
     
       
       select 1;
       commit;
       
       else 
       
           select -2;
           
       end if;
       
	else
			if exists(select 1 from tstudent,tbatch,tinstitute where tstudent.Id = P_StudentId AND tbatch.Id = P_BatchId AND tinstitute.Id = P_InstituteId)
      then
             call spUnassignBatch(P_BatchId,P_StudentId); 
             else
				select -2;
			end if;
     
    end if;

END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spDeleteSubCategory`(
	in P_SubcategoryId int 
)
BEGIN
	DECLARE Exit HANDLER FOR SQLEXCEPTION
		SELECT "-1";
    
		if not exists(select 1 from tSubCategoryMaster where id=P_SubCategoryId) then
			begin
				select -3;
			end;
		else
			start transaction;
			Delete from tSubCategoryMaster where id=P_SubCategoryId;
			commit;
		select 1;
        END IF;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spDeleteSubmitHomeWork`(
    in P_HomeWorkId int,
    in P_StudentId int
 )
BEGIN

    
    DELETE From thomeworksubmittedanswer WHERE HomeWorkId = P_HomeWorkId AND StudentId = P_StudentId;
	SELECT 1;

END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spDeleteTeacherContent`(
	in P_TeacherId Int,
    in P_ContentId int,
    in P_FolderPath varchar(500),
    in P_ContentTypeId int,
    in P_AssignContent int
)
BEGIN
	declare P_TeacherContentId varchar(500);
	declare P_Path varchar(500);
    declare P_AssignPath varchar(500);
    declare P_BatchId int;
    DROP TEMPORARY TABLE IF EXISTS T_AssignPath;
    if P_AssignContent=1 then
		if  exists(select 1 from tteachercontent where Id =P_ContentId and TeacherId=P_TeacherId and P_ContentTypeId in(5,6,7,8,20)) then
			if P_ContentTypeId=5 then

			begin
				start transaction;
                
					set P_Path =(select concat(replace(ContentPath,'\\','+'),'+',Id,'+',ContentTitle,'+%')
						from tteacherContent where id=P_ContentId); 
                        
					CREATE TEMPORARY TABLE T_AssignPath
                    select concat(replace(ContentPath,'\\','+'),'+',Id,'+',ContentTitle,'+%') as path
						from tteachercontentassign where TeacherContentId=P_ContentId;
					/*set P_AssignPath=(select concat(replace(ContentPath,'\\','+'),'+',Id,'+',ContentTitle,'+%')
						from tteachercontentassign where TeacherContentId=P_ContentId);*/
					delete from tteacherContent
					where Id=P_ContentId or 
						concat(replace(ContentPath,'\\','+'),'+') like P_Path;
                        
					 delete tteachercontentassign from tteachercontentassign, T_AssignPath 
					 where TeacherContentId=P_ContentId or 
						concat(replace(tteachercontentassign.ContentPath,'\\','+'),'+') like T_AssignPath.path;
						
                        
					 select 1;
				commit;
			end;
			elseif P_ContentTypeId in(6,7,8,20) then
			begin
				start transaction;
				Delete from tteacherContent
				where Id=P_ContentId;
				delete from tteachercontentassign
				where TeacherContentId=P_ContentId;
				select 1;
				commit;
			end;
			end if;
		else
			if not exists(select 1 from tteachercontent where Id =P_ContentId ) then 
				select -2;
			 elseif not exists(select 1 from tteachercontent where Id =P_ContentId and TeacherId=P_TeacherId) then
				select -3;
			else
				select -4;
			end if;
		end if;
	elseif P_AssignContent=2 then
		if  exists(select 1 from tteachercontent inner join tteachercontentassign on tteachercontent.id=tteachercontentassign.TeacherContentId where tteachercontentassign.Id =P_ContentId and tteachercontent.TeacherId=P_TeacherId and P_ContentTypeId in(5,6,7,8,20)) then
			if P_ContentTypeId=5 then
			begin
				start transaction;
					set P_Path =(select concat(replace(ContentPath,'\\','+'),'+',Id,'+',ContentTitle,'+%')
						from tteachercontentassign where Id=P_ContentId);
					 delete from tteachercontentassign
					 where Id=P_ContentId or 
						concat(replace(ContentPath,'\\','+'),'+') like P_Path;
						
					 select 1;
				commit;
			end;
			elseif P_ContentTypeId in(6,7,8,20) then
			begin
				start transaction;
					delete from tteachercontentassign
					where Id=P_ContentId;
				select 1;
				commit;
			end;
			end if;
		else
			if not exists(select 1 from tteachercontentassign where Id =P_ContentId ) then 
				select -2;
			 elseif not exists(select 1 from tteachercontentassign inner join tteachercontent on tteachercontent.id=tteachercontentassign.TeacherContentId where tteachercontentassign.Id =P_ContentId and tteachercontent.TeacherId=P_TeacherId) then
				select -3;
			else
				select -4;
			end if;
		end if;
    end if;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spDeleteTest`(P_TestId int)
BEGIN
	DECLARE QuestionCount int default 0 ;
DECLARE OptionCount int default 0;

   
	IF EXISTS (SELECT 1 FROM tetest T WHERE 
    CASE WHEN IFNULL(T.ExpiryDate,'1900-01-01') = '1900-01-01' THEN 
    ((T.StartTime + INTERVAL T.Duration MINUTE < TIME_FORMAT(CURTIME(), "%T") AND T.StartDate = CURDATE()) OR 
    (T.StartDate < CURDATE()))
    ELSE ((T.ExpiryTime < TIME_FORMAT(CURTIME(), "%T") AND T.ExpiryDate = CURDATE()) OR (T.ExpiryDate < CURDATE())) END 
    AND T.IsSubmitted = 1 and T.id=P_TestId )
    then
    SELECT -1 As Result;
    ELSE 
    begin
    start transaction;
    set QuestionCount = (select Count(Id) FROM tetestquestions WHERE TestId = P_TestId Group by TestId);
    IF(QuestionCount >=0) THEN 
    CREATE TEMPORARY TABLE QuestionIdTable  SELECT Id,FilePath From tetestquestions WHERE TestId = P_TestId ;
	 CREATE TEMPORARY TABLE OptionIdTable SELECT tto.Id,tto.FilePath From tetestquestionoptions tto
     INNER JOIN tetestquestions ttq ON ttq.Id = tto.QuestionId
     WHERE TestId = P_TestId ;
        DELETE tqo FROM tetestquestionoptions tqo INNER JOIN  QuestionIdTable QID ON QID.Id = tqo.QuestionId;
        DELETE ttq FROM tetestquestions ttq INNER JOIN QuestionIdTable QID ON QID.Id = ttq.Id
        WHERE ttq.TestId =P_TestId;
		delete tpq from tparaquestions tpq inner join tetest te on te.id=tpq.TestId
        where te.id=P_TestId;
        DELETE tetest FROM tetest WHERE Id = P_TestId;
        SELECT 1 As Result;
		SELECT * FROM QuestionIdTable WHERE IFNull(FilePath,'') !='';
		SELECT * FROM OptionIdTable WHERE IFNull(FilePath,'') !='';
        END if;
        DELETE tetest FROM tetest WHERE Id = P_TestId;
       select 1 as Result;
    commit ;
	
    
	
    DROP TEMPORARY TABLE IF EXISTS QuestionIdTable;
    DROP TEMPORARY TABLE IF EXISTS OptionIdTable; 
    end;
    END if;

end$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spDeleteUser`(
in P_UserId int
)
BEGIN
	declare roleid int;
   
    set roleid = (select roletypeid from tuser where id=P_UserId);
    start transaction;
		if roleid=1 then
		delete tuser,tstudent,tassignbatch
			from  tuser
			left join tstudent on tuser.id=tstudent.Userid 
			left join tassignbatch on tstudent.id = tassignbatch.studentid
			where tuser.id=P_UserId;	
        elseif roleid=2 then
			delete tuser,tinstitute,tteacher,tbatch
				from  tuser
				left join tteacher on tuser.id=tteacher.Userid
                left join tinstitute on tinstitute.id=tteacher.InstituteId
				left join tbatch on tteacher.id = tbatch.TeacherId
			where tuser.id=P_UserId;
		else 
			select -2;
		end if;
    commit;
    select "1";
    
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spGetAssignBatch`(
   in P_BatchId int
)
BEGIN

 -- declare statusid int ;

  -- set statusid = (Select StatusId from tassignbatch where )
 
 --  if(StatusId=11)
  -- then
    select tstudent.id as StudentId,
   -- ifnull(tstudent.firstname,'') as FirstName,
 --   ifnull(tstudent.lastname,'') as LastName, 
    ifnull(tuser.Name,'') as UserName
    
    from tassignbatch 
    inner join tstudent on tstudent.id=tassignbatch.studentid
    inner join tuser on tuser.id=tstudent.UserId
   where tassignbatch.BatchId=P_BatchId AND
   tassignbatch.StatusId =11 ;
   -- end if;       

END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spGetAssignLinkList`(
	in P_BatchId int,
    in P_TeacherId int,
    in P_FolderName varchar(500)
)
BEGIN
	declare P_ContentPath varchar(500);
	if(P_Foldername is null) then
		set P_ContentPath = (select contentpath from tteachercontent where id 
					in (select distinct(teachercontentId) from tteachercontentassign where Batchid=P_BatchId));
	else
		set P_ContentPath = (select replace(concat(contentpath,'\\',P_FolderName,'%'),'\\','_') from tteachercontent where id 
					in (select distinct(teachercontentId) from tteachercontentassign where Batchid=P_BatchId));
	end if;
	SELECT Id, ContentLink as Path,ContentTitle as Title,CreatedDatetime as CreateTime 
    FROM tteachercontent 
    where
		(Contentpath in (select P_ContentPath) or Contentpath like (select P_ContentPath) )
		and contentlink is not null 
		and TeacherId=P_TeacherId;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spGetBankDetails`(
     in P_TeacherId int,
     in P_AccountId int
)
BEGIN

      Select
      
           Id as AccountId,
           TeacherId ,
           AccountHolderName ,
           AccountNumber ,
		   IFSCCode ,
           EmailId
           
       FROM     
			tbankaccountdetail
           
       WHERE    
             (TeacherId = P_TeacherId OR P_TeacherId=0) 
		 AND (Id = P_AccountId OR P_AccountId=0); 
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spGetBatch`(IN `P_BatchId` INT(11), IN `P_IntituteId` INT(11), IN `P_TeacherId` INT(11), IN `P_StudentId` INT, IN `P_IsOrderBy` INT)
BEGIN

  if P_StudentId=0 then 
       select 
            tbatch.Id as BatchId,
			tbatch.code as BatchCode,
			tbatch.title as Title,
            tbatch.BatchDate as BatchDate,
            tbatch.CreatedDateTime,
            
        (select COUNT(BatchId) 
                from tassignbatch
	            where StatusId = 11
                AND BatchId = tbatch.Id) as JoinedStudentCount,
                
		(select COUNT(BatchId) 
                from tassignbatch
	            where StatusId = 12 
                AND BatchId = tbatch.Id) as NotJoinedStudentCount
        
     from tbatch 
     left join tteacher on tteacher.Id=tbatch.TeacherId
     left join tassignbatch on tassignbatch.BatchId=tbatch.Id 
       
       where
            (tbatch.TeacherId = P_TeacherId) 
	    AND (tteacher.InstituteId=P_IntituteId) 
        AND (tbatch.Id = P_BatchId OR P_BatchId=0) 
       
     group by tbatch.Id
       order by case when P_IsOrderBy=0 then tbatch.id
          when P_IsOrderBy=1 then tbatch.title end asc,
        case when P_IsOrderBy=2 then  tbatch.CreatedDateTime end desc;
       
  else
    select 
      tbatch.Id as BatchId,
            tbatch.code as BatchCode,
            tbatch.title as Title,
            tbatch.Batchdate as Batchdate,
            tbatch.CreatedDateTime,
            
            (select COUNT(BatchId) 
                from tassignbatch
	            where StatusId = 11
                AND BatchId = tbatch.Id) as JoinedStudentCount
     from tbatch 
     left join tassignbatch on tassignbatch.BatchId=tbatch.Id 
     left join tstudent on tstudent.Id=tassignbatch.StudentId
     
     where (tassignbatch.StudentId = P_StudentId) 
     AND (tstudent.InstituteId=P_IntituteId) 
     AND (tbatch.Id = P_BatchId OR P_BatchId=0) 
     AND  tassignbatch.StatusId = 11

     group by tbatch.Id
       order by case when P_IsOrderBy=0 then tbatch.id
          when P_IsOrderBy=1 then tbatch.title end asc,
        case when P_IsOrderBy=2 then  tbatch.CreatedDateTime end desc;
  end if;

END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spGetBatchRequest`(
     in P_InstituteId int,
     in P_BatchId int,
     in P_TeacherId int
  )
BEGIN
 
       select  
                tstudent.Id as StudentId,
			  -- tstudent.FirstName as UserName,
                ifnull(tuser.Name ,'') as UserName,
                tbatch.Id as BatchId ,
                tbatch.Title as BatchName,
                tstatusmaster.Id as StatusId,
                tstatusmaster.Title as StatusTitle
		
	  from 
      
           tstudent
            
           inner join tassignbatch on tassignbatch.StudentId = tstudent.Id
           inner join tbatch on tbatch.Id = tassignbatch.BatchId
           inner join tstatusmaster on tstatusmaster.Id = tassignbatch.StatusId
           inner join tteacher on tteacher.Id = tbatch.TeacherId
           inner join tuser on tuser.Id = tstudent.UserId
       where 
            
            tteacher.Id = P_TeacherId ANd tbatch.Id= P_BatchId AND tteacher.InstituteId=  P_InstituteId AND tassignbatch.StatusId = 12;
		   
           
      
      
 
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spGetCategory`(

)
BEGIN
	select * from tcategoryMaster;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spGetChapter`(IN `P_SubjectId` INT)
    NO SQL
BEGIN

      DECLARE Exit HANDLER FOR SQLEXCEPTION
		SELECT -1;
        
	select  
           Id as ChapterId , Title as ChapterName, Code As ChapterCode
           
    from 
          tchaptermaster
          
	where     
          (SubjectId = P_SubjectId  OR P_SubjectId =0) ;

END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spGetChat`(
in P_RoleTypeId int,
   in P_UserId int ,
   in P_SourceId int 
)
BEGIN
	SELECT myMessages.FromUserId AS FromUserId, 
		myMessages.FromRoleTypeId AS FromRoleTypeId,
        myMessages.FromSourceId AS FromSourceId,
        myMessages.ToRoleTypeId AS ToRoleTypeId,
        myMessages.ToSourceId AS ToSourceId,
       myMessages.ToUserid AS ToUserid,
       ifnull(groupList.lastMessage,'') AS CreateddateTime, 
		case when ifnull(myMessages.Chatdetail,'')!='' then myMessages.Chatdetail
        else myMessages.ContentPath end AS Chatdetail,
		0 as ChatGroupId, 
	 ifnull(U1.Name,"") as ToUserName,
	 ifnull(U2.Name,"") as FromUserName ,
	 "" as GroupTitle
	FROM (SELECT IF(Fromuserid =P_UserId  , FromuserId, ToUserId) AS FromUserId,
				IF(FromRoleTypeId =P_RoleTypeId  , FromRoleTypeId, ToRoleTypeId) AS FromRoleTypeId,  
				IF(FromSourceId = P_SourceId, FromSourceId, ToSourceId) AS FromSourceId, 
				IF(Touserid =P_UserId  , Fromuserid, ToUserid) AS TouserId,
				IF(ToRoleTypeId =P_RoleTypeId  , FromRoleTypeId, ToRoleTypeId) AS ToRoleTypeId,  
				IF(ToSourceId =P_SourceId, FromSourceId, ToSourceId) AS ToSourceId,  
				Createddatetime, 
				Chatdetail,
                ifnull(ContentPath,"") as ContentPath
			   FROM tchat Where ChatGroupId=0 ) myMessages 
			INNER JOIN (SELECT ToUserid,ToRoleTypeId, ToSourceId,
					   MAX(Createddatetime) AS lastMessage 
				FROM (SELECT IF(TouserId =P_UserId  , FromuserId, ToUserId) AS TouserId,
						IF(ToRoleTypeId =P_RoleTypeId , FromRoleTypeId, ToRoleTypeId) AS ToRoleTypeId,  
						IF(ToSourceId = P_SourceId, FromSourceId, ToSourceId) AS ToSourceId,  
					  Createddatetime, 
					  Chatdetail,
                      ifnull(ContentPath,"") as ContentPath
					  FROM tchat 
					  WHERE ((Fromuserid = P_UserId AND FromRoleTypeId =P_RoleTypeId AND FromSourceId = P_SourceId) 
						 OR (ToUserId =P_UserId AND ToRoleTypeId =P_RoleTypeId AND ToSourceId =P_SourceId) ) AND ChatGroupId=0 ) AS myMessages2 
				GROUP BY ToUserid) groupList 
			ON myMessages.ToUserid = groupList.ToUserid 
            AND myMessages.ToRoleTypeId = groupList.ToRoleTypeId 
            AND myMessages.ToSourceId = groupList.ToSourceId 
		   AND myMessages.Createddatetime = groupList.lastMessage
           Left join tuser U1 on U1.Id = myMessages.ToUserId
           Left join tuser U2 on U2.Id = myMessages.FromUserId
           order by CreateddateTime desc;
        
			SELECT ifnull(C.FromUserId,0) AS FromUserId, 
				ifnull(C.FromRoleTypeId,0) AS FromRoleTypeId,
				ifnull(C.FromSourceId,0) AS FromSourceId,
				ifnull(C.ToRoleTypeId,0) AS ToRoleTypeId,
				ifnull(C.ToSourceId,0) AS ToSourceId,
			   ifnull(C.ToUserid,0) AS ToUserid,
			   ifnull(C.CreateddateTime,'') AS CreateddateTime, 
			   case when ifnull(C.Chatdetail,'')!='' then C.Chatdetail else ifnull(C.ContentPath,'') end AS Chatdetail,
			ifnull(CGM.Id,0) as ChatGroupId, 
			 ifnull(U1.Name,"") as ToUserName,
			 ifnull(U2.Name,"") as FromUserName ,
			ifnull(CGM.Title,"") as GroupTitle
            FROM tchatgroupmaster CGM
			left join  tchat C On C.ChatGroupId=CGM.Id
			Inner join tbatch B on B.Id =CGM.BatchId
			Left join tteacher T on T.Id = B.TeacherId
            Left join tuser U1 on U1.Id = C.ToUserId
           Left join tuser U2 on U2.Id = C.FromUserId
			where CGM.Id!=0 And T.UserId=P_UserId And T.Id=P_SourceId And P_RoleTypeId=2
			AND (C.CreateddateTime in (select Max(CreateddateTime) AS CreateddateTime From tchat C1 Where C1.ChatgroupId=C.ChatGroupId ) OR ifnull(C.id,0)=0)
		union
			SELECT ifnull(C.FromUserId,0) AS FromUserId, 
				ifnull(C.FromRoleTypeId,0) AS FromRoleTypeId,
				ifnull(C.FromSourceId,0) AS FromSourceId,
				ifnull(C.ToRoleTypeId,0) AS ToRoleTypeId,
				ifnull(C.ToSourceId,0) AS ToSourceId,
			   ifnull(C.ToUserid,0) AS ToUserid,
			   ifnull(C.CreateddateTime,'') AS CreateddateTime,
			   case when ifnull(C.Chatdetail,'')!='' then C.Chatdetail else ifnull(C.ContentPath,'') end AS Chatdetail,
			ifnull(CGM.Id,0) as ChatGroupId, 
			 ifnull(U1.Name,"") as ToUserName,
			 ifnull(U2.Name,"") as FromUserName ,
			ifnull(CGM.Title,"") as GroupTitle 
            FROM tchatgroupmaster CGM
			left join  vida_lms.tchat C On C.ChatGroupId=CGM.Id
			Inner join tassignbatch AB on AB.BatchId =CGM.BatchId
			Left join tStudent S on S.Id = AB.StudentId
            Left join tuser U1 on U1.Id = C.ToUserId
           Left join tuser U2 on U2.Id = C.FromUserId
			where CGM.Id!=0 And S.UserId=P_UserId And S.Id=P_SourceId And P_RoleTypeId=1 
			AND (C.CreateddateTime in (select Max(CreateddateTime) AS CreateddateTime From tchat C1 Where C1.ChatgroupId=C.ChatGroupId ) OR ifnull(C.Id,0)=0) 
            order by CreateddateTime desc;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spGetChatGroupList`(
    in P_RoleTypeId int,
    in P_InstituteId int
)
BEGIN
	if exists (select 1 from tinstitute where tinstitute.Id=P_InstituteId) then

		select 
			tchatgroupmaster.Id as ChatGroupId,
            tchatgroupmaster.Title as ChatGroupName,
             ifnull(tchat.CreatedDateTime,'') as LastMessageTime, 
            if(ChatDetail is null,
            if(strcmp(substring_index(ContentPath,'.',-1),"jpg" or "png" or "jpeg"),"Image", 
            if(strcmp(substring_index(ContentPath,'.',-1),"MP4" ),"Video",
            if(strcmp(substring_index(ContentPath,'.',-1),"MP3" ),"Audio","Document"))),ChatDetail) as MessageType
        From tchatgroupmaster 
        left join tchat on tchat.ChatGroupId=tchatgroupmaster.Id
        left Join tbatch on tchatgroupmaster.BatchId=tbatch.Id
        left join tteacher on tteacher.Id=tbatch.TeacherId
        where (tteacher.InstituteId=P_InstituteId) 
        and tchatgroupmaster.CreatedDateTime <= now() 
        and (tchat.CreatedDateTime = (select max(tchat.CreatedDateTime) from tchat where ChatGroupId=tchatgroupmaster.id)
         OR Ifnull(tchat.CreatedDateTime,'')='');
	else
		select -1 as ChatGroupId;
	end if;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spGetCode`(in P_TableName varchar(40))
BEGIN
	SET @getID = CONCAT('SELECT code FROM ',P_TableName);
    PREPARE stmt FROM @getID;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spGetComment`(
    
    in P_AssignContentId int,
    in P_CommentId int
)
BEGIN

      Select UserId , tstudymaterialcomment.Id as CommentId , tuser.name as UserName , 
      (select Count(1) from tstudymaterialcomment ts where ts.ReferenceCommentId=tstudymaterialcomment.id) as NumberofComment,
                  CommentDetail as CommentMessage , 
                  tstudymaterialcomment.CreatedDateTime
                  
      from tstudymaterialcomment
      Left Join tuser on UserId =tuser.Id 
      where ((tstudymaterialcomment.Id = P_CommentId or ReferenceCommentId=P_CommentId )
              OR (P_CommentId = 0 AND ReferenceCommentId=0)) 
												   
                  
      AND AssignContentId = P_AssignContentId
      Group by tstudymaterialcomment.Id;

END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spGetconfigurationmaster`()
BEGIN
    select
      Id ,
      Title ,
      ItemKey ,
	  ItemValue 
    from
        tconfigurationmaster ; 
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spGetContentList`(IN P_TeacherId INT,
in P_AssignContent int,
in P_FolderName varchar(500), 
in P_IsOrderBy int,
in P_ContentTypeId int)
BEGIN 
	
	if P_AssignContent=1 then
		if  exists(select 1 from tteachercontent where  TeacherId=P_TeacherId ) then
			
				 SELECT Id,
					ContentTypeId as ContentTypeId,
					Concat(ContentPath,'\\',id) as Path,
					ContentTitle as Title,
					ContentLink as Link,
					CreatedDatetime as CreatedDateTime,
                    IsCommon as IsCommon,
                    Duration as Duration
				FROM tteachercontent 
                 
				WHERE TeacherId=P_TeacherId
                and ContentPath=P_FolderName 
                and IsCommon=1
				and (ContentTypeId = P_ContentTypeId OR P_ContentTypeId=0)
                -- order by case when P_IsOrderBy=0 then tteachercontent.id
				-- when P_IsOrderBy=1 then tteachercontent.ContentTitle
				-- when P_IsOrderBy=2 then  tteachercontent.CreatedDateTime end;
                      order by case when P_IsOrderBy=0 then tteachercontent.ContentTypeId
					
					when P_IsOrderBy=2 then  tteachercontent.CreatedDateTime end desc,
                     case when P_IsOrderBy=1 then tteachercontent.ContentTitle end asc;
			
		-- else
			
			-- select -1 as Id;
			
		 end if;
	elseif P_AssignContent=2 then
		if  exists(select 1 from tteachercontentassign inner join tteachercontent on tteachercontent.id=tteachercontentassign.TeacherContentId where  tteachercontent.TeacherId=P_TeacherId ) then
			
				 SELECT 
                    tteachercontentassign.Id,
					tteachercontentassign.ContentTypeId as ContentTypeId,
					Concat(tteachercontentassign.ContentPath,'\\',tteachercontentassign.id) as Path,
					tteachercontentassign.ContentTitle as Title,
					tteachercontentassign.ContentLink as Link,
					tteachercontentassign.CreatedDatetime as CreatedDateTime ,
					tteachercontent.IsCommon as IsCommon ,
                    tteachercontentassign.Duration as Duration
				FROM tteachercontentassign 
                
                   Left join tteachercontent on tteachercontent.Id = tteachercontentassign.TeacherContentId
                   
				WHERE tteachercontentassign.ContentPath=P_FolderName
                and (tteachercontentassign.ContentTypeId = P_ContentTypeId OR P_ContentTypeId=0)
                order by case when P_IsOrderBy=0 then tteachercontentassign.ContentTypeId
					
					when P_IsOrderBy=2 then  tteachercontentassign.CreatedDateTime end desc,
                     case when P_IsOrderBy=1 then tteachercontentassign.ContentTitle end asc;
			
		-- else
			-- select -1 as Id;
			
		end if;
    end if;
    
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spGetContentPath`(
	in P_ContentId int,
    in P_AssignContent int
)
BEGIN
	DECLARE Exit HANDLER FOR SQLEXCEPTION
    begin
		Rollback;
        select -1;
	end;
    if P_AssignContent=0 then
		if exists(select 1 from tteachercontent where Id=P_ContentId ) then
			Select concat(ContentPath,'\\',id,'\\',ContentTitle) From tteachercontent where id=P_ContentId ;
		else
			select -2;
		end if;
        
	elseif P_AssignContent=1 then
		if exists(select 1 from tteachercontentassign where Id=P_ContentId) then
			Select 
				concat(tteachercontentassign.ContentPath,'\\',tteachercontentassign.id,'\\',tteachercontentassign.ContentTitle) 
			From tteachercontent 
            inner join tteachercontentassign 
            on tteachercontentassign.TeacherContentId=tteachercontent.id
			where tteachercontentassign.id=P_ContentId ;
		else 
			select -2;
		end if;
	elseif P_AssignContent=2 then
		if exists(select 1 from tteachercontentassign  where TeacherContentId=P_ContentId) then
			Select concat(ContentPath,'\\',id,'\\',ContentTitle) From tteachercontentassign where TeacherContentId=P_ContentId;
		else 
			select -2;
		end if;
	elseif P_AssignContent=3 then
		if exists(select 1 from tteachercontentassign inner join tteachercontent on tteachercontent.Id=tteachercontentassign.TeacherContentId where tteachercontentassign.Id=P_ContentId) then
			Select concat(tteachercontent.ContentPath,'\\',tteachercontent.id,'\\',tteachercontent.ContentTitle) From tteachercontentassign inner join tteachercontent on tteachercontent.Id=tteachercontentassign.TeacherContentId where tteachercontentassign.Id=P_ContentId;
		else 
			select -2;
		end if;
    
    end if;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spGetContentTitle`(
	in P_ContentId int,
    in P_ContentPath varchar(500)
 )
BEGIN
	if exists(select 1 from tteachercontent where id=P_ContentId and Contentlink is null) then
		Select concat(ContentPath,'\\',ContentTitle) as ContentPath from tteachercontent where id= P_ContentId;
	else
		start transaction;
			insert into tteachercontent(ContentPath,ContentTitle,ContentLink,TeacherId,CreatedDateTime)
				select P_ContentPath,ContentTitle,ContentLink,TeacherId,CreatedDateTime
				from tteachercontent 
				where id= P_ContentId;
			Select ContentPath as ContentPath, id as ContentId from tteachercontent where id= LAST_INSERT_ID();
		commit;
	end if;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spGetDoubtSession`(
     in P_TimeTableId varchar(45) 
 )
BEGIN
     
     Select
       Id as TimeTableId,
       
		cast(ttimetable.FromTime as char) As FromTime,
        cast(ttimetable.ToTime as char) AS ToTime ,
        cast(ttimetable.FromDate as char) AS FromDate,
        Title ,
        Subject ,
        IsSession ,
        AnouncementMessage,
        BatchId 
        

     From 
         ttimetable
	 Where
         (FIND_IN_SET(Id,P_TimeTableId) or IFNULL(P_TimeTableId, '')='' or P_TimeTableId = "0") 
         AND IsSession = 1 ;
         -- (FIND_IN_SET(TimeTableId,P_TimeTableId)) ;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spGetEtest`(
   P_SubCategoryId int ,
   P_BatchId int ,
   P_TeacherId int,
   P_StudentId int,
   P_FromDate Date,
   P_ToDate Date,
   P_IsStatus int ,
   P_TestId int)
BEGIN
 
    DECLARE _prev int default null;
    DECLARE cnt int default 0;
     
IF(P_StudentId = 0 ) THEN
-- Complete 
	SELECT
    t.Id As TestId,
    ifnull(T.Topic,"") as Topic,
    T.Description,
    T.MainTestTypeId as MainTestTypeId,
    cast(T.StartTime as char) as StartTime,
    cast(T.StartDate as char) AS StartDate,
    T.Duration ,
    T.MaxMarks ,
	IFNULL(C.StudentSubmitCount,0) As StudentSubmitCount,
    IFNULL(A.AverageMarks,0.00) As AverageMarks,
    IFNULL(Ts.TotalStudentCount,0) AS TotalStudentCount,
    IFNULL(R1.IsGenerated,0)As IsGenerated,
    IFNULL(T.IsMultiple,0)As IsMultiple,
    IFNULL(T.CorrectAnswerMarks,0.00) As CorrectAnswerMarks,
    IFNULL(T.PenaltyMarks,0.00) As PenaltyMarks,
    P_QuestionCount As QuestionCount,
      T.ExpiryDate,
      t.ExpiryTime
    FROM
    tetest T
     
     LEFT JOIN 
    (SELECT COUNT(Id) As StudentSubmitCount, TestId FROM tresult  GROUP BY TestId) C ON C.TestId = T.Id

    LEFT JOIN
    (SELECT AVG(ObtainMarks) As AverageMarks ,TestId FROM tresult GROUP BY TestId) A ON A.TestId = T.Id
    LEFT JOIN (SELECT COUNT(StudentId) As TotalStudentCount,BatchId FROM tassignbatch WHERE BatchId = P_BatchId) Ts ON Ts.BatchId = T.BatchId
    LEFT JOIN (SELECT Count(Id) as P_QuestionCount, TestId FROM tetestquestions GROUP BY TestId) Q ON Q.TestId = T.Id
    LEFT JOIN tresult R1 ON R1.TestId=T.Id
    
    WHERE 
    CASE WHEN IFNULL(T.ExpiryDate,'1900-01-01') = '1900-01-01' THEN 
    ((T.StartTime + INTERVAL T.Duration MINUTE < TIME_FORMAT(CURTIME(), "%T") AND T.StartDate = CURDATE()) OR 
    (T.StartDate < CURDATE()))
    ELSE ((T.ExpiryTime < TIME_FORMAT(CURTIME(), "%T") AND T.ExpiryDate = CURDATE()) OR (T.ExpiryDate < CURDATE())) END
     AND ((P_IsStatus = 1 AND ((T.StartDate >= P_FromDate) AND (T.StartDate <=P_ToDate))) OR (P_IsStatus = 0)  OR (P_IsStatus > 1))                                     
    AND T.IsSubmitted = 1 
    AND T.BatchId = P_BatchId
    
    AND ((T.Id = P_TestId ) OR (P_TestId =0))
     ORDER BY T.CreatedDateTime DESC;
     -- Student evauation list
        SELECT 
        T.Id As TestId ,
        T.MainTestTypeId as MainTestTypeId,
        SA.Id as AnswerId,
        S.Id As StudentId,
        ifnull(S.Firstname ,'') AS StudentName,
        IFNULL(R.ObtainMarks,0) As ObtainMarks,
        IFNULL(R.TotalMarks,-1) As TotalMarks,
        IFNULL(Remark,'') As Remark,
        IFNULL(T.CorrectAnswerMarks,0.00) As CorrectAnswerMarks,
		IFNULL(T.PenaltyMarks,0.00) As PenaltyMarks,
        IFNULL(StudentAnswerFilePath,'') As StudentAnswerFilePath,
        IFNULL(TeacherEvaluatedFilePath,'') As TeacherEvaluatedFilePath,
		IFNULL(T.IsMultiple,0)As IsMultiple
        
        FROM 
      tetest T
     inner JOIN tresult R ON R.TestId = T.Id 
     INNER JOIN tstudent S ON S.Id = R.StudentId 
	 INNER JOIN teteststudentanswer SA ON SA.StudentId = R.StudentId AND R.TestId  = SA.TestId
     WHERE (T.Id = P_TestId OR (P_TestId = 0)
      AND T.BatchId = P_BatchId );
     
     -- Upcoming List
    SELECT 
    T.Id as TestId,
    T.MainTestTypeId as MainTestTypeId,
    ifnull(T.Topic,"") as Topic,
    t.Description,
    cast(T.StartTime as char) as StartTime,
    cast(T.StartDate as char) as StartDate,
    T.Duration,
    T.MaxMarks,
    IFNULL(T.CorrectAnswerMarks,0.00) As CorrectAnswerMarks,
    IFNULL(T.PenaltyMarks,0.00) As PenaltyMarks,
    cast(T.ExpiryDate as char) as ExpiryDate,
    cast(t.ExpiryTime as char) as ExpiryTime,
    IFNULL(T.IsMultiple,0)As IsMultiple
    FROM
    tetest T

    WHERE 
    ((T.StartTime > TIME_FORMAT(CURTIME(), "%T") AND T.StartDate = CURDATE()) 
    OR (T.StartDate > CURDATE()))
     AND ((P_IsStatus = 0 AND ((T.StartDate >= P_FromDate) AND (T.StartDate <=P_ToDate))) OR (P_IsStatus = 1) OR (P_IsStatus > 1))
     AND T.IsSubmitted = 1 
    AND ((T.Id = P_TestId ) OR (P_TestId =0))
     AND T.BatchId = P_BatchId
     ORDER BY T.CreatedDateTime DESC;
     
     
        -- Ongoing List
    SELECT 
    T.Id as TestId,
    T.MainTestTypeId as MainTestTypeId,
    ifnull(T.Topic,"") as Topic,
    t.Description,
    cast(T.StartTime as char) as StartTime,
    cast(T.StartDate as char) as StartDate,
    T.Duration,
    T.MaxMarks,
    IFNULL(T.CorrectAnswerMarks,0.00) As CorrectAnswerMarks,
    IFNULL(T.PenaltyMarks,0.00) As PenaltyMarks,
    cast(T.ExpiryDate as char) as ExpiryDate,
    cast(t.ExpiryTime as char) as ExpiryTime,
    IFNULL(T.IsMultiple,0)As IsMultiple,
    IFNULL(C.StudentSubmitCount,0) As StudentSubmitCount,
    IFNULL(Ts.TotalStudentCount,0) AS TotalStudentCount
    FROM
    tetest T
    left join (SELECT COUNT(Id) As StudentSubmitCount, TestId FROM tresult  GROUP BY TestId) C ON C.TestId = T.Id
    LEFT JOIN (SELECT COUNT(StudentId) As TotalStudentCount,BatchId FROM tassignbatch WHERE BatchId = P_BatchId) Ts ON Ts.BatchId = T.BatchId
    WHERE T.StartTime < TIME_FORMAT(CURTIME(), "%T") and
    CASE WHEN IFNULL(T.ExpiryDate,'1900-01-01') = '1900-01-01' THEN 
    ((T.StartTime+ INTERVAL T.Duration MINUTE> TIME_FORMAT(CURTIME(), "%T")) AND T.StartDate = CURDATE())
    ELSE (T.ExpiryTime > TIME_FORMAT(CURTIME(), "%T") AND T.ExpiryDate = CURDATE()) END 
    AND T.IsSubmitted = 1 
    AND ((T.Id = P_TestId ) OR (P_TestId =0))
     AND T.BatchId = P_BatchId
     ORDER BY T.CreatedDateTime DESC;
     
    -- Draft List
    SELECT 
    T.Id As TestId,
    T.MainTestTypeId as MainTestTypeId,
    ifnull(T.Topic,"") as Topic,
    Cast(T.StartTime as char) as StartTime,
    Cast(T.StartDate as char) as StartDate,
    T.Duration,
    IFNULL(T.CorrectAnswerMarks,0.00) As CorrectAnswerMarks,
    IFNULL(T.PenaltyMarks,0.00) As PenaltyMarks,
    T.CorrectAnswerMarks as DefaultMarks,
    Cast(T.CreatedDateTime as char) as CreatedDateTime,
    ifnull(tq.MaxMarks,0) as MaxMarks,
    IFNULL(T.IsMultiple,0)As IsMultiple
    FROM
    tetest T
    LEFT join(select sum(Marks) as MaxMarks,TestId from tetestquestions group by TestId) tq on tq.TestId=t.id
    WHERE  T.IsSubmitted = 0
    AND ((T.Id = P_TestId ) OR (P_TestId =0))
     AND T.BatchId = P_BatchId
    ORDER BY T.CreatedDateTime DESC;
    
     ELSEIF (P_StudentId > 0 ) THEN
     BEGIN
     
		-- Complete List

	SELECT 
    ifnull(R.StudentId, P_StudentId) As StudentId,
    T.MainTestTypeId as MainTestTypeId,
    if(R.StudentId is null,"Not Attempted","Attempted") As IsStudentAttempt,
    t.Id As TestId,
    ifnull(T.Topic,"") as Topic,
    T.Description,
    cast(T.StartTime as char) as StartTime,
    cast(T.StartDate as char) AS StartDate,
    T.ExpiryDate,
	T.ExpiryTime,
    T.Duration ,
    T.MaxMarks ,
    IFNULL(T.CorrectAnswerMarks,0.00) As CorrectAnswerMarks,
    IFNULL(T.PenaltyMarks,0.00) As PenaltyMarks,
	IFNULL(C.StudentSubmitCount,0) As StudentSubmitCount,
    IFNULL(A.AverageMarks,0.00) As AverageMarks,
    IFNULL(T.TotalStudentCount,0) AS TotalStudentCount,
    P_QuestionCount As QuestionCount,
	IFNULL(R1.IsGenerated,0)As IsGenerated,
    ifnull(R.ObtainMarks,-1) as ObtainMarks,
    IFNULL(T.IsMultiple,0)As IsMultiple,
	(SELECT 
         (SELECT COUNT(*) + 1 FROM tresult R WHERE R.ObtainMarks>R1.ObtainMarks AND R.TestId = T.Id ) AS Rankno FROM tresult R1
         WHERE R1.TestId = T.Id AND R1.StudentId = P_StudentId
         ORDER BY R1.ObtainMarks DESC) As RankNo
    FROM
    tetest T
     left JOIN tresult R1 ON R1.TestId = T.Id AND R1.StudentId = P_StudentId
     LEFT JOIN 
    (SELECT COUNT(Id) As StudentSubmitCount, TestId FROM tresult  GROUP BY TestId) C ON C.TestId = T.Id
    LEFT JOIN
    (SELECT AVG(ObtainMarks) As AverageMarks ,TestId FROM tresult GROUP BY TestId) A ON A.TestId = T.Id
    LEFT JOIN (SELECT COUNT(StudentId) As TotalStudentCount FROM tassignbatch WHERE BatchId = P_BatchId) T ON T.BatchId = T.BatchId
    LEFT JOIN (SELECT Count(Id) as P_QuestionCount, TestId FROM tetestquestions GROUP BY TestId) Q ON Q.TestId = T.Id
	 LEFT JOIN tresult R ON R.TestId=T.Id and R.StudentId=P_StudentId
   WHERE 
    CASE WHEN IFNULL(T.ExpiryDate,'1900-01-01') = '1900-01-01' THEN 
    ((T.StartTime + INTERVAL T.Duration MINUTE < TIME_FORMAT(CURTIME(), "%T") AND T.StartDate = CURDATE()) OR 
    (T.StartDate < CURDATE()))
    ELSE ((T.ExpiryTime < TIME_FORMAT(CURTIME(), "%T") AND T.ExpiryDate = CURDATE()) OR (T.ExpiryDate < CURDATE())) END 
     AND ((P_IsStatus = 1 AND ((T.StartDate >= P_FromDate) AND (T.StartDate <=P_ToDate))) OR (P_IsStatus = 0) OR (P_IsStatus > 1))

    AND T.IsSubmitted = 1 
    AND ((T.Id = P_TestId ) OR (P_TestId =0))
     AND T.BatchId = P_BatchId
     And (R.StudentId=P_StudentId OR ifnull(R.StudentId, 0)=0)
     ORDER BY T.CreatedDateTime DESC;
     
 -- Student evauation list
        SELECT 
        T.Id As TestId ,
        T.MainTestTypeId as MainTestTypeId,
        S.Id As StudentId ,
        ifnull(S.Firstname + '' + S.LastName,'') AS StudentName,
        IFNULL(R.ObtainMarks,0) As ObtainMarks,
        IFNULL(R.TotalMarks,-1) As TotalMarks,
        IFNULL(Remark,'') As Remark,
        IFNULL(T.CorrectAnswerMarks,0.00) As CorrectAnswerMarks,
		IFNULL(T.PenaltyMarks,0.00) As PenaltyMarks,
        IFNULL(StudentAnswerFilePath,'') As StudentAnswerFilePath,
        IFNULL(TeacherEvaluatedFilePath,'') As TeacherEvaluatedFilePath,
        IFNULL(T.IsMultiple,0)As IsMultiple
        
        FROM 
      tetest T
     inner JOIN tresult R ON R.TestId = T.Id 
     INNER JOIN tstudent S ON S.Id = R.StudentId 
	 INNER JOIN teteststudentanswer SA ON SA.StudentId = R.StudentId AND R.TestId  = SA.TestId
     WHERE (T.Id = P_TestId OR (P_TestId = 0)) and S.Id=P_StudentId
      AND T.BatchId = P_BatchId
      group by SA.TestId;
     

    -- Upcoming List
    SELECT 
    T.Id as TestId,
    T.MainTestTypeId as MainTestTypeId,
    ifnull(T.Topic,"") as Topic,
    T.Description,
    cast(T.StartTime as char) as StartTime,
    cast(T.StartDate as char) as StartDate,
    T.Duration,
    T.MaxMarks,
    IFNULL(T.CorrectAnswerMarks,0.00) As CorrectAnswerMarks,
    IFNULL(T.PenaltyMarks,0.00) As PenaltyMarks,
    cast(T.ExpiryDate as char) as ExpiryDate,
    cast(T.ExpiryTime as char) as ExpiryTime,
    IFNULL(T.IsMultiple,0)As IsMultiple
    FROM
    tetest T
    WHERE 
    ((T.StartTime > TIME_FORMAT(CURTIME(), "%T") AND T.StartDate = CURDATE()) 
    OR (T.StartDate > CURDATE()))
    AND ((P_IsStatus = 0 AND ((T.StartDate >= P_FromDate) AND (T.StartDate <=P_ToDate))) OR (P_IsStatus = 1)OR (P_IsStatus > 1))
     AND T.IsSubmitted = 1 
    AND ((T.Id = P_TestId ) OR (P_TestId =0))
     AND T.BatchId = P_BatchId
     ORDER BY T.CreatedDateTime DESC;
     
      -- Ongoing List
    SELECT 
    T.Id as TestId,
    T.MainTestTypeId as MainTestTypeId,
    ifnull(T.Topic,"") as Topic,
    T.Description,
    cast(T.StartTime as char) as StartTime,
    cast(T.StartDate as char) as StartDate,
    T.Duration,
    T.MaxMarks,
    IFNULL(T.CorrectAnswerMarks,0.00) As CorrectAnswerMarks,
    IFNULL(T.PenaltyMarks,0.00) As PenaltyMarks,
    cast(T.ExpiryDate as char) as ExpiryDate,
    cast(T.ExpiryTime as char) as ExpiryTime,
    IFNULL(T.IsMultiple,0)As IsMultiple,
    case when IFNULL(S.Id,0)=P_StudentId then
     true else false end as IsGenerated
    FROM
    tetest T
    left JOIN tresult R ON R.TestId = T.Id and R.StudentId=P_StudentId
    left JOIN tstudent S ON S.Id = R.StudentId
    WHERE T.StartTime < TIME_FORMAT(CURTIME(), "%T") and
    CASE WHEN IFNULL(T.ExpiryDate,'1900-01-01') = '1900-01-01' THEN 
    ((T.StartTime+ INTERVAL T.Duration MINUTE> TIME_FORMAT(CURTIME(), "%T")) AND T.StartDate = CURDATE())
    ELSE (T.ExpiryTime > TIME_FORMAT(CURTIME(), "%T") AND T.ExpiryDate = CURDATE()) END 
    AND T.IsSubmitted = 1 
    AND ((T.Id = P_TestId ) OR (P_TestId =0))
     AND T.BatchId = P_BatchId
     ORDER BY T.CreatedDateTime DESC;
     END;
     END IF;

END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spGetETestDetails`(
in P_TestId int
)
BEGIN
	select
	T.Id As TestId,
    ifnull(T.Topic,"") as Topic,
    T.MainTestTypeId as MainTestTypeId,
    T.Description,
    cast(T.StartTime as char) as StartTime,
    cast(T.StartDate as char) AS StartDate,
    T.Duration ,
    T.MaxMarks ,
    IFNULL(T.CorrectAnswerMarks,0.00) As CorrectAnswerMarks,
    IFNULL(T.PenaltyMarks,0.00) As PenaltyMarks,
	IFNULL(C.StudentSubmitCount,0) As StudentSubmitCount,
    IFNULL(Ts.TotalStudentCount,0) AS TotalStudentCount,
    P_QuestionCount As QuestionCount
    from tetest T
    LEFT JOIN 
    (SELECT COUNT(Id) As StudentSubmitCount, TestId FROM tresult  GROUP BY TestId) C ON C.TestId = T.Id
    LEFT JOIN (SELECT COUNT(StudentId) As TotalStudentCount,BatchId FROM tassignbatch where StatusId=11 group by BatchId) Ts ON Ts.BatchId = T.BatchId
    LEFT JOIN (SELECT Count(Id) as P_QuestionCount, TestId FROM tetestquestions GROUP BY TestId) Q ON Q.TestId = T.Id
    WHERE 
    ((T.Id = P_TestId ));
    
    select 
		S.Id AS StudentId,
		ifnull(U.Name,'') as StudentName,
		R.ObtainMarks as ObtainMarks,
        ifnull(R.IsGenerated,0) as IsGenerated,
		(SELECT(SELECT COUNT(*) + 1 FROM tresult R WHERE R.ObtainMarks>R1.ObtainMarks AND R.TestId = T.Id ) AS Rankno FROM tresult R1
		WHERE R1.TestId = T.Id AND R1.StudentId =S.Id
		ORDER BY R1.ObtainMarks DESC) As RankNo
	from
      tetest T
     left JOIN tresult R ON R.TestId = T.Id 
     inner JOIN tstudent S ON S.Id = R.StudentId
     Left Join tuser U ON U.Id = S.UserId
	 left JOIN (select * from teteststudentanswer GROUP BY TestId) SA ON SA.StudentId = R.StudentId AND R.TestId  = SA.TestId
     WHERE (T.Id = P_TestId );
        

END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spGetEtestQuestion`(
   P_TestId int,
   in P_StudentId int,
   in P_OrderIndex int
   )
BEGIN
	SELECT 
			ttq.Id as Id,
			ttq.TestId as TestId,
			ttq.QuestionTypeId as QuestionTypeId,
			ttq.Title as Title,
			ttq.Marks as Marks,
			ttq.IsSubmitted,0 as IsSubmitted,
			ttq.IsSingleAnswer as IsSingleAnswer,
            -- IFNULL(tsa.AnswerStatusId,0) as IsStudentAttempt,
            IFNull(tsa.OptionId,0) as StudentAnswerId,
            IFNull(tsa.Id,0) as FinalAnswerId,
            IFNull(tsa.Answer,'') as StudentAnswer,
			IFNULL(ttq.FilePath,'') As FilePath,
			IFNULL(ttq.ParaId,0) As ParaId,
            ifnull(ttq.OrderIndex ,0) as OrderIndex,
            ifnull(tsa.StudentAnswerFilePath,'') as StudentAnswerFilePath,
            ifnull(tsa.TeacherEvaluatedFilePath,'') as TeacherEvaluatedFilePath,
            ifnull((select tsm.id from tstatusmaster tsm where tsa.AnswerStatusId=tsm.Id),0) as IsStudentAttempt,
            ifnull(tsa.ScoredMarks,0)as ObtainMarks,
            ifnull(tsa.Remark,'') as Remarks
            -- (select OptionId from  teteststudentanswer tsa where StudentId=P_StudentId and ttq.Id = tsa.QuestionId )
		FROM tetestquestions ttq
        left join (select OptionId,AnswerStatusId,QuestionId,Answer,StudentAnswerFilePath,TeacherEvaluatedFilePath,Id,ScoredMarks,Remark from  teteststudentanswer where StudentId=P_StudentId) tsa  on ttq.Id = tsa.QuestionId 
        --  left join  tsm on tsa.AnswerStatusId=tsm.Id
		WHERE ttq.TestId = P_TestId and (ttq.OrderIndex=P_OrderIndex or P_OrderIndex=0);
        
	
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spGetFeeDetail`(

    in P_BatchId int,
    in P_StudentId int,
    in P_TeacherId int,
    in P_start date,
    in P_end date,
    in P_MonthXML longtext
)
BEGIN
DECLARE A_Count int default 0 ;
DECLARE i int default 1 ;

     if(P_StudentId != 0) 
     then
     DROP TEMPORARY TABLE IF EXISTS DueDateList;
          CREATE TEMPORARY TABLE DueDateList 
			( DueDate datetime );
           SET A_Count =  ExtractValue(P_MonthXML, 'COUNT(//Month)');
           WHILE i <= A_Count DO
				INSERT INTO DueDateList
					(DueDate)
					values
					(ExtractValue(P_MonthXML, '//Month[$i]//DueDate'));
			SET i= i+1;
			END WHILE;
            
          select 
             tfeesetup.Id as FeeId ,
			 tfeesetup.StudentId,
             CASE 
             -- WHEN tpaymenttransaction.PaymentType = 'Cash' AND IFNULL(ttransactionstatusmaster.Id, 0) = 2 THEN 'Paid Via Cash'
			 WHEN IFNULL(tpaymenttransaction.TransactionStatusId, 0) = 0 THEN 'Not Paid'
			 ELSE ttransactionstatusmaster.Code
			 END AS PaymentStatus,
             tpaymenttransaction.TransactionStatusId as PaymentStatusId,
			 tfeesetup.FeeAmount,
             case when tfeesetup.IsRecurringMonthly=1  then
             ifnull(cast(date_add(date_add(tfeesetup.DateOfFeeCollection,INTERVAL month(DL.DueDate)-month(tfeesetup.DateOfFeeCollection) month),INTERVAL YEAR(DL.DueDate)-YEAR(tfeesetup.DateOfFeeCollection) YEAR) as char),'') 
			else "" end as DueDate,
			
             case when IFNULL(ttransactionstatusmaster.Id, 0) = 2 or IFNULL(ttransactionstatusmaster.Id, 0) = 6 THEN ifnull(cast(tpaymenttransaction.PayDateTime as char),'') else "" end as PayDateTime,
             tpaymenttransaction.Id as PaymenttransactionId,
             
			-- case when tfeesetup.IsRecurringMonthly = 1 then
			-- ifnull(cast(tfeesetup.DateOfFeeCollection as char),'')
			-- else "" end as CreatedDateTime
            case when tfeesetup.IsRecurringMonthly=1  then
            ifnull(cast(date_add(date_add(tfeesetup.DateOfFeeCollection,INTERVAL month(DL.DueDate)-month(tfeesetup.DateOfFeeCollection) month),INTERVAL YEAR(DL.DueDate)-YEAR(tfeesetup.DateOfFeeCollection) YEAR) as char),'') 
			else now() end as CreatedDateTime
            
			-- ifnull(cast(tfeesetup.DateOfFeeCollection as char),'')  as CreatedDateTime
          from  
                 tfeesetup
                 left join DueDateList DL on true
                 left join tpaymenttransaction on tpaymenttransaction.FeeSetupId =  tfeesetup.Id and 
                 case when tfeesetup.IsRecurringMonthly=0 then true 
					else Date_format(tpaymenttransaction.DateOfFeeCollection,"%Y-%m")= Date_format(date_add(date_add(tfeesetup.DateOfFeeCollection,
                                                         INTERVAL month(DL.DueDate)-month(tfeesetup.DateOfFeeCollection) month),
                                                         INTERVAL YEAR(DL.DueDate)-YEAR(tfeesetup.DateOfFeeCollection) YEAR),"%Y-%m") end
                 left join ttransactionstatusmaster on ttransactionstatusmaster.Id = tpaymenttransaction.TransactionStatusId  
          where   
				 (tfeesetup.BatchId  = P_BatchId OR P_BatchId = 0)
			AND  (tfeesetup.StudentId = P_StudentId OR P_StudentId = 0)
            AND  (tfeesetup.TeacherId = P_TeacherId OR P_TeacherId = 0)
            order by DL.DueDate desc;
            
     else
            if not exists(Select 1 from tbankaccountdetail where TeacherId = P_TeacherId)
            then
               select false as Result;
            else
              select true as Result;
				select 
                    tfeesetup.Id as FeeId,
                    tfeesetup.StudentId,
					CASE
                    -- WHEN tpaymenttransaction.PaymentType = 'Cash' AND IFNULL(ttransactionstatusmaster.Id, 0) = 2 THEN 'Paid Via Cash'
					 WHEN IFNULL(tpaymenttransaction.TransactionStatusId, 0) = 0 THEN 'Not Paid'
					 ELSE ttransactionstatusmaster.Code
					 END AS TransactionStatus,
                     tpaymenttransaction.TransactionStatusId as TransactionStatusId,
                     tfeesetup.FeeAmount as Amount,
                    case when tfeesetup.IsRecurringMonthly=1  then
                    ifnull(cast(date_add(date_add(tfeesetup.DateOfFeeCollection,INTERVAL month(P_start)-month(tfeesetup.DateOfFeeCollection) month),INTERVAL YEAR(P_start)-YEAR(tfeesetup.DateOfFeeCollection) YEAR) as char),'') else ""  end as DueDate,
					case when IFNULL(ttransactionstatusmaster.Id, 0) = 2 or IFNULL(ttransactionstatusmaster.Id, 0) = 6 THEN ifnull(cast(tpaymenttransaction.PayDateTime as char),'') else "" end as PayDateTime,
                    tpaymenttransaction.Id as PaymenttransactionId,
                    ifnull(tstudent.FirstName,'') as StudentName,           
                    -- ttransactionstatusmaster.Id as TransactionStatusId,				
                    tfeesetup.BatchId,
                    ifnull(tbatch.Title,'') as BatchName,
                    
                    -- case when tfeesetup.IsRecurringMonthly = 1 then
					-- ifnull(cast(tfeesetup.DateOfFeeCollection as char),'')
					-- else "" end as CreatedDateTime
                    case when tfeesetup.IsRecurringMonthly=1  then
                    ifnull(cast(date_add(date_add(tfeesetup.DateOfFeeCollection,INTERVAL month(P_start)-month(tfeesetup.DateOfFeeCollection) month),INTERVAL YEAR(P_start)-YEAR(tfeesetup.DateOfFeeCollection) YEAR) as char),'') 
                    else now() end as CreatedDateTime
                    -- ifnull(cast(tfeesetup.DateOfFeeCollection as char ),'') as CreatedDateTime
				From
                     tfeesetup
                     left join tstudent on tstudent.Id = tfeesetup.StudentId
                     left join tpaymenttransaction on tpaymenttransaction.FeeSetupId =  tfeesetup.Id and
                     case when tfeesetup.IsRecurringMonthly=0 then true 
                     else Date_format(tpaymenttransaction.DateOfFeeCollection,"%Y-%m")=Date_format(date_add(date_add(tfeesetup.DateOfFeeCollection,
                     INTERVAL month(P_start)-month(tfeesetup.DateOfFeeCollection) month),
                     INTERVAL YEAR(P_start)-YEAR(tfeesetup.DateOfFeeCollection) YEAR),"%Y-%m") end 
					 left join ttransactionstatusmaster on ttransactionstatusmaster.Id = tpaymenttransaction.TransactionStatusId
					 left join tbatch on tbatch.Id = tfeesetup.BatchId
                where     
                     (tfeesetup.BatchId  = P_BatchId OR P_BatchId = 0)
			    AND  (tfeesetup.TeacherId = P_TeacherId OR P_TeacherId = 0)
                AND 
                
                case WHEN tfeesetup.IsRecurringMonthly=0  
                THEN P_start <= tfeesetup.CreatedDateTime  and Date_format(Date_add(Date_add(tfeesetup.CreatedDateTime,interval -day(tfeesetup.CreatedDateTime)-1 day),INTERVAL 1 month),"%Y-%m-%d")<= P_end
                else 
                
                case when  not IFNULL(ttransactionstatusmaster.Id, 0) = 0 
                then (P_start <= date_add(date_add(tfeesetup.DateOfFeeCollection,INTERVAL month(P_start)-month(tfeesetup.DateOfFeeCollection) month),INTERVAL YEAR(P_start)-YEAR(tfeesetup.DateOfFeeCollection) YEAR)
                AND date_add(date_add(tfeesetup.DateOfFeeCollection,INTERVAL month(P_start)-month(tfeesetup.DateOfFeeCollection) month),INTERVAL YEAR(P_start)-YEAR(tfeesetup.DateOfFeeCollection) YEAR) <= P_end)
                else  DATE_FORMAT(tfeesetup.CreatedDateTime,"%Y-%m") <=  DATE_FORMAT(P_start,"%Y-%m") end end
                order by date_add(date_add(tfeesetup.DateOfFeeCollection,INTERVAL month(P_start)-month(tfeesetup.DateOfFeeCollection) month),INTERVAL YEAR(P_start)-YEAR(tfeesetup.DateOfFeeCollection) YEAR) desc;
		    end if;
                
     end if;
     
	commit;

    
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spGetFeeDetailsNew`(
    in P_BatchId int,
    in P_StudentId int,
    in P_TeacherId int ,
   -- in P_Month Date,
   -- in P_Year Date
	in P_Month int,
    in P_Year int
    
)
BEGIN
    DECLARE l_StartDate date ; 
    DECLARE l_EndDate date ; 
	DECLARE l_StartDatestring nvarchar(50) default '';
    DECLARE l_FeeSetupAllow bool;
	-- DECLARE ISSetup ;
  --  Declare Setupmonth date;
	SET l_StartDatestring =CONCAT('01,', P_Month,',',P_Year);
	SET l_StartDate =STR_TO_DATE(l_StartDatestring,'%d,%m,%Y');
	SET l_EndDate = LAST_DAY( l_StartDate);
    
   -- set setupmonth = Month(curdate());
   -- SET  ISSetup = (DATE_ADD( 'Month(curdate());', INTERVAL 1 month ));
          
     if(P_StudentId != 0) 
     then
     
            Select
                 F.Id as FeeId,
                 F.StudentId as StudentId,
                 F.FeeAmount as FeeAmount,
                -- F.IsRecurringMonthly as IsRecurringMonthly ,
                
			      CASE WHEN F.IsRecurringMonthly=1 
			      THEN
				      cast(ifnull(F.DateOfFeeCollection,'') as char)
                      else ''
			      END as DueDate,
                  
				 case when F.IsRecurringMonthly=1  then
                cast(F.DateOfFeeCollection as char) else now() end AS CreatedDateTime,
                -- F.DefaultFeeAmount as DefaultFeeAmount ,
                -- F.SetupDate as SetupDate ,
                 CASE WHEN IFNULL(PT.TransactionStatusId, 0) = 0 THEN 'Not Paid' ELSE SM.Code END AS PaymentStatus,
				 PT.TransactionStatusId as PaymentStatusId,
                 CASE WHEN IFNULL(SM.Id, 0) = 2 or  IFNULL(SM.Id, 0) = 6  THEN ifnull(cast(PT.PayDateTime as char),'') ELSE""  END as PayDateTime,
                 PT.Id as PaymenttransactionId
                 
		    FROM 
                 tfeesetup F
                 left join tpaymenttransaction PT on PT.FeeSetupId =  F.Id  
                 left join ttransactionstatusmaster SM on SM.Id = PT.TransactionStatusId  
                 
            WHERE   
				   (F.BatchId  = P_BatchId OR P_BatchId = 0)
			  AND  (F.StudentId = P_StudentId OR P_StudentId = 0)
              AND  (F.TeacherId = P_TeacherId OR P_TeacherId = 0)
              AND   Month(F.SetupDate) = MONTH(l_StartDate)
              AND   Year(F.SetupDate) = YEAR(l_EndDate);
               
	 else
      if EXISTS (SELECT 1 FROM tfeesetup WHERE Month(SetupDate) = MONTH(CURRENT_DATE()) + 1 AND TeacherId = P_TeacherId AND BatchId = P_BatchId ORDER BY SetupDate DESC LIMIT 1)
             THEN
				SET l_FeeSetupAllow = false;
               
                else
                SET l_FeeSetupAllow =true;
                end if;
       if not exists(Select 1 from tbankaccountdetail where TeacherId = P_TeacherId)
            then
               select false as Result, l_FeeSetupAllow As FeeSetupAllow;
            else
              select true as Result, l_FeeSetupAllow As FeeSetupAllow;
              
			
               
         Select
                 F.Id as FeeId,
                 F.StudentId as StudentId,
                 U.Name as StudentName,
                 F.FeeAmount as Amount,
                
                -- F.IsRecurringMonthly as IsRecurringMonthly ,
               --  Cast(F.DateOfFeeCollection as char) DateOfFeeCollection,
              --  Cast(F.CreatedDateTime as char) CreatedDateTime ,
              
                 F.DefaultFeeAmount as DefaultFeeAmount ,
                 Cast(F.SetupDate as char) SetupDate ,
                
			   CASE WHEN F.IsRecurringMonthly=1 
			      THEN
				      cast(ifnull(F.DateOfFeeCollection,'') as char)
                      else ''
			      END as DueDate,
                  
				 case when F.IsRecurringMonthly=1  then
                cast(F.DateOfFeeCollection as char) else now() end AS CreatedDateTime,
                 
                 CASE WHEN 
                           IFNULL(PT.TransactionStatusId, 0) = 0 
                             THEN 'Not Paid'
			     ELSE
						   SM.Code 
			     END AS TransactionStatus,
				 PT.TransactionStatusId as TransactionStatusId,
                 CASE WHEN 
                           IFNULL(SM.Id, 0) = 2 or 
						   IFNULL(SM.Id, 0) = 6  
                             THEN ifnull(cast(PT.PayDateTime as char),'') 
				 ELSE
                       "" 
                 END as PayDateTime,
                 PT.Id as PaymenttransactionId ,
                 B.Id as BatchId,
                 ifnull(B.Title ,'')as BatchName,
                 IF(F.IsSendNotificationFeeDue=1,true,false) as IsSendNotificationFeeDue,
                 IF(F.IsSendSMSFeeDue=1,true,false) AS IsSendSMSFeeDue,
				 U.Id AS UserId
                 
		    FROM 
                 tfeesetup F
                 left join tpaymenttransaction PT on PT.FeeSetupId =  F.Id  
                 left join ttransactionstatusmaster SM on SM.Id = PT.TransactionStatusId  
				 inner join tstudent S on S.Id = F.StudentId
                 Left join tuser U on U.Id = S.UserId
                -- Left join tassignbatch AB on AB.StudentId = F.StudentId
                 LEft join tbatch B on B.Id = F.BatchId
            WHERE   
				   (F.BatchId  = P_BatchId OR P_BatchId = 0)
              AND  (F.TeacherId = P_TeacherId OR P_TeacherId = 0)
			  AND  Month(F.SetupDate) = MONTH(l_StartDate)
			  AND Year(F.SetupDate) = YEAR(l_EndDate)
			  AND case WHEN F.IsRecurringMonthly=0
              THEN (F.CreatedDateTime >= l_StartDate   and F.CreatedDateTime <= l_EndDate)
                else 
                 (F.DateOfFeeCollection >= l_StartDate AND  F.DateOfFeeCollection <= l_EndDate)
				 END
			 order by  PT.PayDateTime DESC  , F.DateOfFeeCollection ASC;
             
     end if;
     end if;

   commit;
   
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spGetFeeDetailTest`(

    in P_BatchId int,
    in P_StudentId int,
    in P_TeacherId int,
    in P_start date,
    in P_end date,
    in P_MonthXML longtext
)
BEGIN
DECLARE A_Count int default 0 ;
DECLARE i int default 1 ;

     if(P_StudentId != 0) 
     then 
          select 
            *
          from  
                 tfeesetup FS
                 left join tpaymenttransaction tt on tt.FeeSetupId =  FS.Id 
                 left join ttransactionstatusmaster tts on tts.Id = tt.TransactionStatusId  
          where   
				 (tfeesetup.BatchId  = P_BatchId OR P_BatchId = 0)
			AND  (tfeesetup.StudentId = P_StudentId OR P_StudentId = 0)
            AND  (tfeesetup.TeacherId = P_TeacherId OR P_TeacherId = 0)
            AND  Month(tt.SetupDate) = MONTH(P_start)
            AND Year(tt.SetupDate) = YEAR(P_start)
            order by DL.DueDate desc;
            
     else
            if not exists(Select 1 from tbankaccountdetail where TeacherId = P_TeacherId)
            then
               select false as Result;
            else
              select *
				From
                     tfeesetup FS
                     left join tstudent S on S.Id = FS.StudentId
                     left join tpaymenttransaction PT on PT.FeeSetupId =  FS.Id 
					 left join ttransactionstatusmaster TSM on TSM.Id = PT.TransactionStatusId
					 left join tbatch B on B.Id = FS.BatchId
                where     
                     (FS.BatchId  = P_BatchId OR P_BatchId = 0)
			    AND  (FS.TeacherId = P_TeacherId OR P_TeacherId = 0)
                AND  Month(tt.SetupDate) = MONTH(P_start)
				AND Year(tt.SetupDate) = YEAR(P_start)
                AND case WHEN FS.IsRecurringMonthly=0  
                THEN (FS.CreatedDateTime >= P_Start   and FS.CreatedDateTime <= P_end)
                else 
                 (FS.DateOfFeeCollection >= P_Start AND  FS.DateOfFeeCollection <= P_end )
                 END
                
                order by FS.DateOfFeeCollection DESC;
		    end if;
                
     end if;
     
	commit;

    
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spGetHomeWork`(
     in P_BatchId varchar(1000),
     in P_HomeWorkId int,
     in P_StudentId int,
     in P_TeacherId int,
	 in P_StartDate Date,
     in P_EndDate Date
  )
BEGIN
	
    if(P_StudentId=0)
    then
    
     SELECT  
		H.id as HomeWorkId,
        H.Title as Title,
        tuser.Name as TeacherName,
        
        tbatch.Title as BatchName,
        Note as Notes,
        cast(Homeworkdatetime as char) as SubmissionDate,
       IsSendNotificationHomeworkNotSubmit as IsSendNotificationHomeworkNot,
       IsSendSMSHomeworkNotSubmit as IsSendSMSHomeworkNot
     FROM thomework H
     inner JOIN tteacher on H.TeacherId= tteacher.Id 
     left join tbatch on tbatch.Id = H.BatchId
     inner Join tuser on tuser.Id = tteacher.UserId 
     -- inner JOIN tstudent on FIND_IN_SET(tstudent.id,thomework.studentId) 
     -- inner JOIN thomeworksubmittedanswer on thomeworksubmittedanswer.HomeWorkId=thomework.Id
     
     WHERE (FIND_IN_SET(BatchId,REPLACE(P_BatchId,' ', '')) or IFNULL(P_BatchId, '')='' or P_BatchId = "0") 
     AND (H.Id=P_HomeWorkId or P_HomeWorkId=0)
     AND (P_StartDate <= date(H.Homeworkdatetime) AND date(H.Homeworkdatetime) <= P_EndDate )
     and (H.TeacherId=P_TeacherId)
     ORDER BY H.Homeworkdatetime DESC;
	
     else
     
      SELECT 
           thomework.id as HomeWorkId,
           thomework.Title as Title,
		   U1.Name as TeacherName,
             tbatch.Id as BatchId,
            tbatch.Title as BatchName,
		   thomework.Note as Notes,
           tstudent.Id as StudentId,
           U2.Name as StudentName,
           cast(Homeworkdatetime as char) as SubmissionDate,
             thomeworkassign.StatusId as Status,
             ifnull(tstatusmaster.Title,"") as StatusTitle
           
	  FROM thomework
	      inner JOIN tteacher on TeacherId= tteacher.Id 
          left join tbatch on tbatch.Id = thomework.BatchId
           inner Join tuser U1 on U1.Id = tteacher.UserId 
		  inner JOIN thomeworkassign on thomeworkassign.HomeWorkId=thomework.Id
          inner Join tstudent on tstudent.Id = thomeworkassign.StudentId
           inner Join tuser U2 on U2.Id = tstudent.UserId 
          inner join tstatusmaster on thomeworkassign.StatusId=tstatusmaster.Id

        where 
			(FIND_IN_SET(BatchId,REPLACE(P_BatchId,' ', '')) or IFNULL(P_BatchId, '')='' or P_BatchId = "0") and
             FIND_IN_SET(P_StudentId,thomework.studentId) and thomeworkassign.StudentId=P_StudentId
              AND (P_StartDate <= Date(thomework.Homeworkdatetime) AND Date(thomework.Homeworkdatetime) <= P_EndDate )
             AND (thomework.Id=P_HomeWorkId or P_HomeWorkId=0)
               ORDER BY thomework.Homeworkdatetime DESC;
     
     end if;
     
     Commit;
     
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spGetInstitute`(
   in P_UserId int ,
   in P_RoleTypeId int
)
BEGIN
	if(P_RoleTypeId = 2) 
    then
		select
           tinstitute.Id as Id ,
           tinstitute.Code as Code,
	       tinstitute.Title as Title,
           tuser.Id as UserId,
           tteacher.Id as TeacherId
		from 
              tinstitute
        Inner join tteacher on tteacher.InstituteId = tinstitute.Id
	    Inner join tuser on tuser.Id = tteacher.UserId
		where 
              tuser.RoleTypeId=P_RoleTypeId 
		AND  
             tuser.Id=P_UserId;
          
	elseif(P_RoleTypeId = 3) 
    then
    
	  select
            tinstitute.Id as Id ,
            tinstitute.Code as Code,
	        tinstitute.Title as Title,
            tuser.Id as UserId,
            tadmin.Id as AdminId
           
      from 
			tinstitute
    
       Inner join tadmin on tadmin.InstituteId = tinstitute.Id
	   Inner join tuser on tuser.Id = tadmin.UserId
    
       where
            tuser.RoleTypeId=P_RoleTypeId 
	   AND 
            tuser.Id=P_UserId;
    
     elseif(P_RoleTypeId = 1)
     then
        
          select distinct
            tinstitute.Id as Id ,
            tinstitute.Code as Code,
	        tinstitute.Title as Title,
            tuser.Id as UserId,
            tstudent.Id as StudentId,
            tteacher.Id as TeacherId
           
      from 
			tinstitute
    
       
       Inner join tstudent on tstudent.InstituteId = tinstitute.Id
	   Inner join tuser on tuser.Id = tstudent.UserId
       Inner join tteacher on tteacher.InstituteId = tinstitute.Id
       where
            tuser.RoleTypeId=P_RoleTypeId 
	   AND 
            tuser.Id=P_UserId;
    
		
	 end if;

          
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spGetInstituteDetails`(
	in P_StudentId int
)
BEGIN
	 select tinstitute.title as InstituteTitle,
			tteacher.code as TeacherCode,
            tteacher.Id as TeacherId
     from tstudent
      inner join tassignbatch on tstudent.id=tassignbatch.studentid
      inner join tbatch on tassignbatch.batchid= tbatch.id
      inner join tteacher on tbatch.teacherid=tteacher.id
      inner join tinstitute on tinstitute.id=tteacher.IntituteId
      where tstudent.id=P_StudentId;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spGetJoinInstitute`(
	in P_InstituteCode varchar(10),
	in P_UserName varchar(100),
	in P_UserId int
)
BEGIN
 -- teacherId.studentId,instituteName,teacherCode
  DECLARE institute_id INT DEFAULT 0;
  
    if exists(select 1 from tinstitute where code = P_InstituteCode)   
    then
  
		 if exists(select 1 from tstudent           
		              inner join tuser on  tuser.Id = tstudent.UserId    
                      inner join tinstitute on tstudent.InstituteId = tinstitute.Id 
		     where tinstitute.Code = P_InstituteCode
			 AND tstudent.UserId = P_UserId)
         then

                        -- select 1;
                        -- SELECT 1 AS StatusCode, (select Id from tinstitute where code = P_InstituteCode) AS InstituteId;
                        -- SELECT 1 AS StatusCode, institute_id AS InstituteId;
						--   SELECT 1 AS StatusCode, (select InstituteId from tinstitute where code = P_InstituteCode) AS InstituteId;
  
          select             
			   1 AS StatusCode , 
               ifnull(tinstitute.Id,'') as InstituteId ,
               ifnull(tinstitute.Title,'') as InstituteName,
               ifnull(tstudent.Id,'') as StudentId,
               tteacher.Id as TeacherId ,
               ifnull(tteacher.Code,'') as TeacherCode
	      from
             tinstitute 
			   Left Join tteacher on tteacher.InstituteId = tinstitute.Id
               Left Join tstudent on tstudent.InstituteId = tinstitute.Id
          where
               tinstitute.Code =P_InstituteCode
	           AND tstudent.UserId = P_UserId ;
                      --  AND UserName = P_UserName   
       
      else
         start transaction;
         
         if P_UserName is not null then
			  Update tuser
				  set Name=P_UserName,
				  UpdatedDateTime=now()
				  where Id=P_UserId;
         end if;
         
         SET institute_id = (select Id from tinstitute where code=P_InstituteCode);
         
		 if exists(Select 1 from tstudent where UserId = P_UserId AND InstituteId=0)
         then
                   /* Update tuser
                   set InstituteId=institute_id,
					UpdatedDateTime=now()
                   where Id=P_UserId;*/
          
          Update 
               tstudent
          set
					-- FirstName=ifnull(P_UserName,(select name from tuser Where id=P_UserId)),
               InstituteId=institute_id,
               StatusId=11,
               DateOfJoining=now(),
               UpdatedDateTime=now()
           where 
				UserId=P_UserId 
			AND InstituteId=0;
                         --     select 2;
                         --  SELECT 2 AS StatusCode, institute_id AS InstituteId;
           select 
              2 AS StatusCode ,
			  ifnull(tinstitute.Id,'') as InstituteId ,
              ifnull(tinstitute.Title,'') as InstituteName,
              ifnull(tstudent.Id,'') as StudentId,
              tteacher.Id as TeacherId ,
              ifnull(tteacher.Code,'') as TeacherCode
	       from
              tinstitute 
	          Left Join tteacher on tteacher.InstituteId = tinstitute.Id
              Left Join tstudent on tstudent.InstituteId = tinstitute.Id
           where
                tinstitute.Code =P_InstituteCode
			AND tstudent.UserId = P_UserId ;
              
              
           elseif exists(select 1 from tuser where Id = P_UserId and RoleTypeId=1) AND 
                  exists (select 1 from tstudent where UserId = P_UserId)
           then
               Insert into tstudent
					 (UserId,InstituteId,CreatedDateTime,UpdatedDateTime,DateOfJoining) 
	           values
                      (P_UserId,institute_id,now(),now(),now());
                      
                    --  select 2;
			select 
               2 AS StatusCode ,
			   ifnull(tinstitute.Id,'') as InstituteId ,
               ifnull(tinstitute.Title,'') as InstituteName,
               ifnull(tstudent.Id,'') as StudentId,
               tteacher.Id as TeacherId ,
               ifnull(tteacher.Code,'') as TeacherCode
	       from
              tinstitute 
	          Left Join tteacher on tteacher.InstituteId = tinstitute.Id
              Left Join tstudent on tstudent.InstituteId = tinstitute.Id
           where
               tinstitute.Code =P_InstituteCode
	           AND tstudent.UserId = P_UserId ;
             else
               select -2 as StatusCode, 0 AS InstituteId;
			
            end if;         
         Commit;
     end if;
     
     else
         select -1 as StatusCode, 0 AS InstituteId;
       
     end if;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spGetLinkList`(IN `P_TeacherId` INT,in P_FolderName varchar(500))
    NO SQL
BEGIN

	DECLARE Exit HANDLER FOR SQLEXCEPTION

		SELECT "-1";

     SELECT Id, ContentLink as Path,ContentTitle as Title,CreatedDatetime as CreatedDateTime FROM tteachercontent WHERE ContentLink is not null and TeacherId = P_TeacherId and ContentPath=P_FolderName;

     

END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spGetLogDetails`(
	in P_UserId int,
    in P_InstituteId int
)
BEGIN
	select 
		id as UserLogId,
		UserId as UserId,
		ifnull(LoggedInIp,'') as LoggedInIp,
        ifnull(LoggedInDateTime,'') as LoggedInDateTime,
        ifnull(LoggedOutDateTime,'') as LoggedOutDateTime,
        OSTypeId as OSTypeId,
        InstituteId as InstituteId
    from tuserlog where UserId=P_UserId and InstituteId=P_InstituteId;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spGetMonthList`(
	in P_TeacherId int,
    in P_BatchId int,
    in P_StudentId int
)
BEGIN
	declare V_startMonth datetime default now();
    set V_startMonth =(select CreatedDateTime 
    from tfeesetup 
    where (TeacherId=P_TeacherId or P_TeacherId=0)  and BatchId=P_BatchId and(StudentId=P_StudentId or P_StudentId=0)
    order by DateOfFeeCollection asc
    limit 1);
    
    select 
		DATE_FORMAT(m1, '%m') as Month,
        DATE_FORMAT(m1, '%Y') as Year

		from
		(
		select 
		(V_startMonth - INTERVAL DAYOFMONTH(V_startMonth)-1 DAY) 
		+INTERVAL m MONTH as m1
		from
		(
		select @rownum:=@rownum+1 as m from
		(select 1 union select 2 union select 3 union select 4) t1,
		(select 1 union select 2 union select 3 union select 4) t2,
		(select 1 union select 2 union select 3 union select 4) t3,
		(select 1 union select 2 union select 3 union select 4) t4,
		(select @rownum:=-1) t0
		) d1
		) d2 
		where m1<=now()
		order by m1;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spGetMonthListNew`(
   	in P_TeacherId int,
    in P_BatchId int,
    in P_StudentId int
)
BEGIN
--  Declare lLowestMonth date;
  Declare start_date date;
  Declare end_date date;
  
 -- Declare lCurrentMonth date default now();
  
    set start_date = (select
						      SetupDate
						 From
						       tfeesetup
					    where (TeacherId=P_TeacherId or P_TeacherId=0)  
                          and (BatchId=P_BatchId or P_BatchId=0)
					      and(StudentId=P_StudentId or P_StudentId=0)  
						order by SetupDate asc LIMIT 1
                     );
                    
     set end_date = (SELECT date(now()));

     SET @months = -1;
     select 
        DATE_FORMAT(m1,'%m') AS Month ,
         DATE_FORMAT(m1,'%Y') AS Year
           from (
                    select (date_add(start_date, INTERVAL (@months := @months +1) month)) as m1
					from mysql.help_topic a limit 0,1000) a
	 where a.m1 between start_date and last_day(end_date);
     
     
     
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spGetNoticeBoard`(
	in P_BatchId int,
    in P_NoticeId int
)
BEGIN
SELECT 
	tnoticeboard.Id as NoticeBoardId,
	tnoticeboard.Remark as Remark,	
   -- ifnull(tteacher.FirstName,'') as TeacherName,
    ifnull(tuser.Name,'') as TeacherName,
    tnoticeboard.createddatetime as CreatedDateTime,
    tteacher.Id  as TeacherId
	/*CAST(tnoticeboard.createddatetime AS CHAR) as CreatedDateTime*/

FROM tnoticeboard inner join tbatch on tbatch.id=tnoticeboard.batchid
inner join tteacher on tteacher.id = tbatch.teacherid
left join tuser on tuser.Id = tteacher.UserId
where tnoticeboard.BatchId=P_BatchId and (tnoticeboard.Id=P_NoticeId or P_NoticeId=0)
order by tnoticeboard.CreatedDateTime desc;

END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spGetNotification`(
   -- in P_BatchId int,
    in P_TeacherId int,
    in P_NotificationId int ,
    in P_StudentId int
)
BEGIN

   if (P_StudentId >0)
   then
	   select 
              N.Id as Id ,
              N.TeacherId as TeacherId ,
               ifnull(tuser.Name,"")as TeacherName,
              N.Title as Title ,
              N.NotificationText as NotificationText ,
			  N.NotificationDate as NotificationDate
       from 
            tnotification N
            inner join tBatch B ON FIND_IN_SET(B.Id, N.BatchId) > 0
            inner join tassignbatch AB ON AB.BatchId = B.Id 
            left join tteacher on tteacher.Id = B.TeacherId
            left join tuser on tuser.Id = tteacher.UserId
            inner join tstudent S ON S.Id = AB.StudentId 
       where
            (N.Id = P_NotificationId OR P_NotificationId = 0)
        AND (N.TeacherId = P_TeacherId OR P_TeacherId = 0)
        AND (AB.StudentId = P_StudentId OR P_StudentId = 0)
        order by N.NotificationDate desc;

     else
     
         select 
              N.Id as Id ,
              N.TeacherId as TeacherId ,
			  ifnull(N.BatchId,"") as BatchId ,
              ifnull(tuser.Name,"")as TeacherName,
              N.Title as Title ,
              N.NotificationText as NotificationText ,
			  N.NotificationDate as NotificationDate
              
		 from tnotification N
              left join tteacher on tteacher.Id = N.TeacherId
              left join tuser on tuser.Id = tteacher.UserId
             where
                 (N.Id = P_NotificationId OR P_NotificationId = 0)AND
          --      (BatchId = P_BatchId OR P_BatchId = 0)AND
                  (N.TeacherId = P_TeacherId OR P_TeacherId = 0)
                  order by N.NotificationDate desc;
             
     end if;
     commit;
              
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spGetNotification_Template`()
BEGIN
   
   select 
           Id ,
           Title ,
		   ifnull(Remark,"") as Remark,
		   Template ,
		   ifnull(MailSuject,"") as MailSuject ,
		   ifnull(SMSTemplateId,"") as SMSTemplateId
     from 
           teventmaster ;
           
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spGetOnlineLectureAttendence`(
	in P_LectureId int
)
BEGIN
	declare V_BatchaId int default 0;
    set V_BatchaId = (select distinct BatchId from tonlinelearningstudentattendance tolsa 
						inner join ttimetable tt  on tolsa.TimeTableId=tt.Id
                        where tolsa.OnlineLectureId=P_LectureId);
                        
    select ts.Id as StudentId, 
    ifnull(U.Name,"") as UserName,
    ifnull(tolsa.IsPresent,0) as StstusId, 
    if(ifnull(tolsa.IsPresent,0)=1,"Present","Absent") as StstusTitle
    
    from tassignbatch tsb
    inner join tstudent ts on tsb.StudentId=ts.id
    left join tuser U on U.Id = ts.UserId
    left join ttimetable tt  on tsb.BatchId=tt.BatchId
    left join tonlinelearningstudentattendance tolsa on tolsa.TimeTableId=tt.id and tolsa.StudentId=ts.Id
    
    
    where 
		tsb.BatchId=V_BatchaId;

END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spGetOnlineLectureSettings`(
   IN `P_BatchId` INT
)
BEGIN
	SELECT id as AccounSettingId,
    
    MeetingAccountId as P_MeetingAccountId,
    
    IsOnParticipantVideo as  IsOnParticipantVideo,
    IsOnParticipantAudio as IsOnParticipantAudio,
    IsOnHostVideo as IsOnHostVideo,
    IsOnHostAudio as IsOnHostAudio,
    IsOnParticipantScreenShare as  IsOnParticipantScreenShare,
    IsOnHostscreenShare as  IsOnHostscreenShare,
    BatchId as  BatchId
		FROM tonlinelecturemeetingsettings
		WHERE 
			BatchId=P_BatchId;
            
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spGetOnlineMeetingAccount`(
   
    in P_TeacherId int,
    in P_BatchId int
)
BEGIN
	-- if exists(select 1 from tteacher where  Id=P_TeacherId) 
    if exists(select 1 from tbatch where  Id = P_BatchId AND  TeacherId=P_TeacherId)
     then
		SELECT 
			Id as AccountId,
			TeacherId as TeacherId,
			BatchId as BatchId,
			AccountName as AccountName,
			AccountEmailId as AccountEmailId, 
			AccountPassword as AccountPassword,
			APIKey as APIKey,
			APISecret as APISecret,
			WebApiKey as WebApiKey,
			WebApiSecret as WebApiSecret,
			JWTToken as JWTToken
		FROM 
			tonlinelecturemeetingaccount
		WHERE 
			    (TeacherId=P_TeacherId)
            AND (BatchId=P_BatchId);
	else
		select -1 as AccountId;
	end if;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spGetOption`(
   P_QuestionId int)
BEGIN
	SELECT 
	Id,
    QuestionId,
    Options,
    FilePath,
    IsKey
    FROM tetestquestionoptions
    WHERE QuestionId = P_QuestionId;
   
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spGetParaQuestion`(
	in P_TestId int,
    in P_ParaId int
)
BEGIN
	select Id as ParaId,Title as Title, Code as Code, OrderIndex as OrderIndex
    from tparaquestions
    where TestId=P_TestId and (Id=P_ParaId or P_ParaId=0);
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spGetRoleType`(
)
BEGIN	
	Select Id, Title, IsObsolete
    from troletypemaster;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spGetStaticData`(
    in P_StatusTypeId int
 )
BEGIN
 
       select tstatusmaster.Id As Id,
			  tstatusmaster.Code As Code,
			  tstatusmaster.Title As Title,
              tstatusmaster.IsObsolete As IsObsolete,
              tstatustypemaster.Title As StatusType
       from  tstatustypemaster 
       Inner Join tstatusmaster 
       on tstatusmaster.StatusTypeId = tstatustypemaster.Id
       
       where 
             tstatustypemaster.Id = P_StatusTypeId or P_StatusTypeId=0;
             
 
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spGetStaticTable`(
     in P_Id int,
   
     in P_StaticTableId int
 )
BEGIN

         if (P_StaticTableId=1)
         then
         
             select 
                    Id as Id,
                    Code as Code,
                    Title as Title,
                    IsObsolete as IsObsolete
			 from 
                   tbloodgroupmaster
             where
			     Id = P_Id OR P_Id =0;
                 
         elseif(P_StaticTableId=2)
		 then
             
             select 
                    Id as Id,
                    Code as Code,
                    Title as Title,
                    IsObsolete as IsObsolete
			 from 
                   tgendermaster
             where
			     Id = P_Id OR P_Id =0;
       
         end if;

END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spGetStudent`(
	 in P_BatchId int(11),
     in P_IntituteId int(11),
     in P_StudentId int(11),
     in P_TeacherId int(11),
	in P_IsOrderBy int
 )
BEGIN

 select 
	distinct
       tstudent.UserId,
       tuser.RoleTypeId,
       ifnull(tuser.name,"") as UserName,
       Ifnull(tuser.MobileNo,'') as MobileNo,
       tstudent.Id AS StudentId,
       IFNULL(tstudent.Code,'') AS StudentCode,
       tstudent.StatusId as StatusId,
       Ifnull(tstatusmaster.Title,'') as StatusTitle,
     --  IFNULL(tstudent.FirstName,'') as FirstName,
      -- IFNULL(tstudent.LastName,'') as LastName,
       IFNULL(tstudent.EmailId,'') as EmailId,
       IFNull(tstudent.RollNo,0) as RollNo,
       cast(tstudent.DateOfBirth as char) as DateOfBirth,
       -- tbatch.Id As BatchId,
	   tteacher.Id AS TeacherId,
       -- tstudent.CreatedDateTime,
       tstudent.DateOfJoining as CreatedDateTime
       -- tbatch.Code As BatchCode,
       -- tbatch.Title AS BatchTitle,
       -- tbatch.BatchDate,
       -- tbatch.CreatedDateTime
 
   from tstudent 
   left join tuser on tuser.Id = tstudent.UserId
   left join tteacher on tteacher.InstituteId=tstudent.InstituteId
   left join tassignbatch on tassignbatch.StudentId=tstudent.Id
   left join tstatusmaster on tstatusmaster.id=tstudent.StatusId   
   -- inner join tuser on tuser.Id = tstudent.UserId
   -- inner join tassignbatch on tassignbatch.StudentId=tstudent.Id
   -- left join tbatch on tassignbatch.BatchId=tbatch.Id
   -- Inner join tteacher on tteacher.Id=tbatch.TeacherId
   -- inner join tstatusmaster on tstatusmaster.id=tstudent.StatusId
   where 
   -- (tbatch.TeacherId = P_TeacherId OR P_TeacherId=0) 
   (tteacher.Id = P_TeacherId OR P_TeacherId=0) 
   AND (tteacher.InstituteId=P_IntituteId OR P_IntituteId=0) 
   AND (tstudent.Id=P_StudentId OR P_StudentId=0) 
   AND
   case when ifnull(tassignbatch.BatchId,0)!=0 then (tassignbatch.BatchId = P_BatchId OR P_BatchId=0) 
   and (tassignbatch.StatusId=11) 
   else P_BatchId=0 end
   
   order by case when P_IsOrderBy=0 then tstudent.id
					when P_IsOrderBy=1 then tuser.Name end asc,
				case when P_IsOrderBy=2 then  tstudent.CreatedDateTime end desc;

END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spGetSubCategory`(
	in P_CategoryId int
)
BEGIN
	DECLARE Exit HANDLER FOR SQLEXCEPTION
		SELECT -1;
	select 
		tSubCategoryMaster.Id as SubCategoryId,
		tSubCategoryMaster.CategoryId as CategoryId,
		tSubCategoryMaster.Code as SubCategoryCode,
		tSubCategoryMaster.Title as Title
	from tSubCategoryMaster 
	where (CategoryId=P_CategoryId or P_CategoryId=0);
	
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spGetSubject`(IN `P_SubCategoryId` INT)
    NO SQL
BEGIN



      DECLARE Exit HANDLER FOR SQLEXCEPTION

		SELECT -1;

        

	select  

           Id as SubjectId , Title as SubjectName, Code As SubjectCode

           

    from 

          tsubjectmaster

          

	where     

          (SubCategoryId = P_SubCategoryId  OR P_SubCategoryId =0) ;



END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spGetTimeTable`(
    in P_BatchId int,
    in P_TeacherId int ,
   -- in P_FromDate date,
   -- in P_ToDate date 
   in P_StudentId int,
   in P_TimeTableId int

)
BEGIN
   -- drop TEMPORARY table if exists DateList; 
	-- CREATE TEMPORARY TABLE IF NOT EXISTS DateList AS 
  -- ( select DayName(selected_date) As Day,selected_date as date from (select adddate('2021-03-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) As selected_date from
-- (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
-- (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
-- (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
-- (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
-- (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
-- where selected_date between P_FromDate and P_ToDate);
    
    
	SELECT 
         tt.Id as TimeTableId,
      --   tt.Day As Day, cast(tt.FromTime as char) As FromTime, 
        -- cast(tt.ToTime as char) AS ToTime, 
         tt.BatchId As BatchId,
       --  tb.title as BatchName,
       --  Subject as Subject,
         ifnull(tol.Id,0) as LectureId,
        -- d.Date as Date ,
         
		tol.Id as LectureId,
		ifnull(tol.MeetingId,'') as MeetingId,
		ifnull(tol.MeetingPassword,'') as MeetingPassword,
		OLS.IsOnParticipantVideo as IsOnParticipantVideo,
		OLS.IsOnParticipantAudio as IsOnParticipantAudio,
		OLS.IsOnHostVideo as IsOnHostVideo,
		OLS.IsOnHostAudio as IsOnHostAudio,
		OLS.IsOnParticipantScreenShare as IsOnParticipantScreenShare,
		OLS.IsOnHostscreenShare as IsOnHostscreenShare
         
    FROM ttimetable tt
        inner join tbatch tb on tb.id=tt.BatchId
        left join tassignbatch AB on AB.BatchId = tb.Id and P_TeacherId=0
      --  inner join DateList  d on d.day=tt.day
        left join tonlinelecture tol on tol.TimeTableId = tt.Id  
       -- left join tonlinelecture OL on OL.BatchId = tt.BatchId
        left join tonlinelecturemeetingsettings OLS on OLS.BatchId = tt.BatchId
    WHERE 
		    (tt.BatchId=P_BatchId)
        And (tb.TeacherId = P_TeacherId or P_TeacherId = 0)    
        AND (tt.Id = P_TimeTableId)
        AND (AB.StudentId = P_StudentId or P_StudentId = 0);
    
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spGetTimeTableNew`(
    in P_BatchId int,
    in P_StudentId int,
    in P_InstituteId int,
    in P_FromDate Date,
    in P_ToDate Date
 )
BEGIN 
IF (P_StudentId > 0)
THEN 

    Select 
       TT.Id as TimeTableId ,
       cast(TT.FromTime as char) As FromTime,
       cast(TT.ToTime as char) AS ToTime ,
	   cast(ifnull(TT.FromDate,'') as char) AS FromDate,
       B.Id as BatchId,
       B.Title as BatchName,
       TT.Subject ,
       ifnull(TT.Title,'') as Title,
	   TT.IsSession,
       ifnull(TT.AnouncementMessage,'') as AnouncementMessage
    From 
		ttimetable TT
		inner join  tbatch B on B.Id = TT.BatchId
        inner join tassignbatch AB on AB.BatchId = B.Id
        inner join tteacher T on T.Id = B.TeacherId 
        left join tonlinelecture OL on OL.TimeTableId=TT.Id
     --   inner join DateList on DateList.Day=ttimetable.Day
	Where 
          (TT.BatchId = P_BatchId OR P_BatchId = 0)
      AND (AB.StudentId = P_StudentId)    
      AND ((T.InstituteId = P_InstituteId AND TT.Day = DayName(now()) AND !(tt.Totime<Time(now()))) OR P_InstituteId = 0)
	  AND (IsSession = 0)
	And (TT.Day = 'Monday')
     order by TT.FromTime ASC;
     
     -- Tuesday list
     
     Select 
	   TT.Id as TimeTableId ,
       cast(TT.FromTime as char) As FromTime,
       cast(TT.ToTime as char) AS ToTime ,
       B.Id as BatchId,
       B.Title as BatchName,
       TT.Subject ,
       ifnull(TT.Title,'') as Title,
	    cast(ifnull(TT.FromDate,'') as char) AS FromDate,
       TT.IsSession,
       ifnull(TT.AnouncementMessage,'') as AnouncementMessage
    From 
		ttimetable  TT
         inner join  tbatch B on B.Id = TT.BatchId
		 inner join tassignbatch AB on AB.BatchId = B.Id
	     inner join tteacher T on T.Id = B.TeacherId 
		 left join tonlinelecture OL on OL.TimeTableId=TT.Id
          -- inner join DateList on DateList.Day=ttimetable.Day
	Where 
          (TT.BatchId = P_BatchId OR P_BatchId = 0)
      AND (AB.StudentId = P_StudentId)    
      AND ((T.InstituteId = P_InstituteId AND TT.Day = DayName(now()) AND !(tt.Totime<Time(now()))) OR P_InstituteId = 0)
	  AND IsSession = 0
	 And TT.Day = 'Tuesday'
      order by TT.FromTime ASC;
     
     -- Wednesday list
     
     Select 
	   TT.Id as TimeTableId ,
       cast(TT.FromTime as char) As FromTime,
       cast(TT.ToTime as char) AS ToTime ,
       B.Id as BatchId,
       B.Title as BatchName,
	   TT.Subject ,
       ifnull(TT.Title,'') as Title,
	  cast(ifnull(TT.FromDate,'') as char) AS FromDate,
       TT.IsSession,
       ifnull(TT.AnouncementMessage,'') as AnouncementMessage
    From 
		ttimetable TT
		 inner join  tbatch B on B.Id = TT.BatchId
           inner join tassignbatch AB on AB.BatchId = B.Id
	     inner join tteacher T on T.Id = B.TeacherId 
         --  inner join DateList on DateList.Day=ttimetable.Day
         left join tonlinelecture OL on OL.TimeTableId=TT.Id
	Where 
          (TT.BatchId = P_BatchId OR P_BatchId = 0)
      AND (AB.StudentId = P_StudentId)    
      AND ((T.InstituteId = P_InstituteId AND TT.Day = DayName(now()) AND !(tt.Totime<Time(now()))) OR P_InstituteId = 0)
	  AND IsSession = 0
	 And TT.Day = 'Wednesday'
      order by TT.FromTime ASC;
     
     -- Thursday list
     
     Select 
	   TT.Id as TimeTableId ,
       cast(TT.FromTime as char) As FromTime,
       cast(TT.ToTime as char) AS ToTime ,
       B.Id as BatchId,
       B.Title as BatchName,
	   TT.Subject ,
       ifnull(TT.Title,'') as Title,
	 cast(ifnull(TT.FromDate,'') as char) AS FromDate,
        TT.IsSession,
       ifnull(TT.AnouncementMessage,'') as AnouncementMessage
    From 
		ttimetable TT
		 inner join  tbatch B  on B.Id = TT.BatchId
           inner join tassignbatch AB on AB.BatchId = B.Id
	     inner join tteacher T on T.Id = B.TeacherId 
        --   inner join DateList on DateList.Day=ttimetable.Day
        left join tonlinelecture OL on OL.TimeTableId=TT.Id
	Where 
          (TT.BatchId = P_BatchId OR P_BatchId = 0)
      AND (AB.StudentId = P_StudentId)    
      AND ((T.InstituteId = P_InstituteId AND TT.Day = DayName(now()) AND !(tt.Totime<Time(now()))) OR P_InstituteId = 0)
	  AND IsSession = 0
	  And TT.Day = 'Thursday'
      order by TT.FromTime ASC;
     
     -- Friday list
     
     Select 
       TT.Id as TimeTableId ,
       cast(TT.FromTime as char) As FromTime,
       cast(TT.ToTime as char) AS ToTime ,
       B.Id as BatchId,
       B.Title as BatchName,
       TT.Subject ,
       ifnull(TT.Title,'') as Title,
	 cast(ifnull(TT.FromDate,'') as char) AS FromDate,
       ifnull(TT.AnouncementMessage,'') as AnouncementMessage
    From 
		ttimetable TT
         inner join  tbatch B on B.Id = TT.BatchId
           inner join tassignbatch AB on AB.BatchId = B.Id
	     inner join tteacher T on T.Id = B.TeacherId 
        --   inner join DateList on DateList.Day=ttimetable.Day
        left join tonlinelecture OL on OL.TimeTableId=TT.Id
	Where 
          (TT.BatchId = P_BatchId OR P_BatchId = 0)
      AND (AB.StudentId = P_StudentId)    
      AND ((T.InstituteId = P_InstituteId AND TT.Day = DayName(now()) AND !(tt.Totime<Time(now()))) OR P_InstituteId = 0)
	  AND IsSession = 0
	 And TT.Day = 'Friday'
      order by TT.FromTime ASC;
     
     -- Saturday list
     
     Select 
       TT.Id as TimeTableId ,
       cast(TT.FromTime as char) As FromTime,
       cast(TT.ToTime as char) AS ToTime ,
       B.Id as BatchId,
       B.Title as BatchName,
	   TT.Subject ,
       ifnull(TT.Title,'') as Title,
	  cast(ifnull(TT.FromDate,'') as char) AS FromDate,
       TT.IsSession,
       ifnull(TT.AnouncementMessage,'') as AnouncementMessage
    From 
		ttimetable TT
          inner join  tbatch B on B.Id = TT.BatchId
		  inner join tassignbatch AB on AB.BatchId = B.Id
	     inner join tteacher T on T.Id = B.TeacherId 
         --  inner join DateList on DateList.Day=ttimetable.Day
         left join tonlinelecture OL on OL.TimeTableId=TT.Id
	Where 
          (TT.BatchId = P_BatchId OR P_BatchId = 0)
      AND (AB.StudentId = P_StudentId)    
      AND ((T.InstituteId = P_InstituteId AND TT.Day = DayName(now()) AND !(tt.Totime<Time(now()))) OR P_InstituteId = 0)
	  AND IsSession = 0
	 And TT.Day = 'Saturday'
      order by TT.FromTime ASC;
     
     -- Sunday list
     
     Select 
       TT.Id as TimeTableId ,
       cast(TT.FromTime as char) As FromTime,
       cast(TT.ToTime as char) AS ToTime ,
       B.Id as BatchId,
       B.Title as BatchName,
       TT.Subject ,
       ifnull(TT.Title,'') as Title,
	 cast(ifnull(TT.FromDate,'') as char) AS FromDate,
       TT.IsSession,
       ifnull(TT.AnouncementMessage,'') as AnouncementMessage
    From 
		ttimetable TT
       inner join  tbatch B on B.Id = TT.BatchId
         inner join tassignbatch AB on AB.BatchId = B.Id
	     inner join tteacher T on T.Id = B.TeacherId 
         --  inner join DateList on DateList.Day=ttimetable.Day
         left join tonlinelecture OL on OL.TimeTableId=TT.Id
	Where 
         (TT.BatchId = P_BatchId OR P_BatchId = 0)
      AND (AB.StudentId = P_StudentId)    
      AND ((T.InstituteId = P_InstituteId AND TT.Day = DayName(now()) AND !(tt.Totime<Time(now()))) OR P_InstituteId = 0)
	  AND IsSession = 0
	 And TT.Day = 'Sunday'
      order by TT.FromTime ASC;
         
	-- -- -- --  Doubt Session  -- -- -- -- --
    
       -- Monday list
   
    Select 
       TT.Id as TimeTableId,
       cast(TT.FromTime as char) As FromTime,
       cast(TT.ToTime as char) AS ToTime ,
       B.Id as BatchId,
       B.Title as BatchName,
       TT.Subject ,
       TT.Title,
	   ifnull(cast(TT.FromDate as char),'') AS FromDate,
       TT.IsSession,
       ifnull(TT.AnouncementMessage,'') as AnouncementMessage
    From 
		ttimetable TT
		  inner join  tbatch B on B.Id = TT.BatchId
            inner join tassignbatch AB on AB.BatchId = B.Id
		  inner join tteacher T on T.Id = B.TeacherId
          left join tonlinelecture OL on OL.TimeTableId=TT.Id
	Where 
        (TT.BatchId = P_BatchId or P_BatchId = 0)
        AND (AB.StudentId = P_StudentId) 
        ANd ((T.InstituteId = P_InstituteId AND !(tt.Totime<Time(now()))) or P_InstituteId =0)
         AND TT.IsSession = 1 
         AND TT.FromDate>=P_FromDate
         AND TT.FromDate<=P_ToDate
	     And DAYNAME(TT.FromDate)= 'Monday';
     
     -- Tuesday list
     
     Select 
	   TT.Id as TimeTableId ,
       cast(TT.FromTime as char) As FromTime,
       cast(TT.ToTime as char) AS ToTime ,
       B.Id as BatchId,
       B.Title as BatchName,
       TT.Subject ,
       TT.Title,
       ifnull(cast(TT.FromDate as char),'') AS FromDate,
       TT.IsSession,
       ifnull(TT.AnouncementMessage,'') as AnouncementMessage
    From 
		ttimetable TT
		inner join  tbatch B on B.Id = TT.BatchId
          inner join tassignbatch AB on AB.BatchId = B.Id
		  inner join tteacher T on T.Id = B.TeacherId
          left join tonlinelecture OL on OL.TimeTableId=TT.Id
	Where 
        (TT.BatchId = P_BatchId or P_BatchId = 0)
        AND (AB.StudentId = P_StudentId) 
        ANd ((T.InstituteId = P_InstituteId AND !(tt.Totime<Time(now()))) or P_InstituteId =0)
         AND TT.IsSession = 1 
         AND TT.FromDate>=P_FromDate
         AND TT.FromDate<=P_ToDate
	     And DAYNAME(TT.FromDate)= 'Tuesday';
	
     -- Wednesday list
     
     Select 
	   TT.Id as TimeTableId ,
       cast(TT.FromTime as char) As FromTime,
       cast(TT.ToTime as char) AS ToTime ,
       B.Id as BatchId,
       B.Title as BatchName,
       TT.Subject ,
       TT.Title,
       ifnull(cast(TT.FromDate as char),'') AS FromDate,
       TT.IsSession,
       ifnull(TT.AnouncementMessage,'') as AnouncementMessage
    From 
		ttimetable TT
		inner join  tbatch B on B.Id = TT.BatchId
          inner join tassignbatch AB on AB.BatchId = B.Id
		  inner join tteacher T on T.Id = B.TeacherId
          left join tonlinelecture OL on OL.TimeTableId=TT.Id
	Where 
        (TT.BatchId = P_BatchId or P_BatchId = 0)
        AND (AB.StudentId = P_StudentId ) 
        ANd ((T.InstituteId = P_InstituteId AND !(tt.Totime<Time(now()))) or P_InstituteId =0)
         AND TT.IsSession = 1 
         AND TT.FromDate>=P_FromDate
         AND TT.FromDate<=P_ToDate
	     And DAYNAME(TT.FromDate)= 'Wednesday';
	
     
     -- Thursday list
     
     Select 
	   TT.Id as TimeTableId ,
       cast(TT.FromTime as char) As FromTime,
       cast(TT.ToTime as char) AS ToTime ,
       B.Id as BatchId,
       B.Title as BatchName,
       TT.Subject ,
       TT.Title,
       ifnull(cast(TT.FromDate as char),'') AS FromDate,
	   TT.IsSession,
       ifnull(TT.AnouncementMessage,'') as AnouncementMessage
    From 
		ttimetable TT
		 inner join  tbatch B on B.Id = TT.BatchId
        inner join tassignbatch AB on AB.BatchId = B.Id
		  inner join tteacher T on T.Id = B.TeacherId
          left join tonlinelecture OL on OL.TimeTableId=TT.Id
	Where 
        (TT.BatchId = P_BatchId or P_BatchId = 0)
        AND (AB.StudentId = P_StudentId ) 
        ANd ((T.InstituteId = P_InstituteId AND !(tt.Totime<Time(now()))) or P_InstituteId =0)
         AND TT.IsSession = 1 
         AND TT.FromDate>=P_FromDate
         AND TT.FromDate<=P_ToDate
	     And DAYNAME(TT.FromDate)= 'Thursday';
	
     -- Friday list
     
     Select 
       TT.Id as TimeTableId ,
       cast(TT.FromTime as char) As FromTime,
       cast(TT.ToTime as char) AS ToTime ,
       B.Id as BatchId,
       B.Title as BatchName,
       TT.Subject ,
       TT.Title,
       ifnull(cast(TT.FromDate as char),'') AS FromDate,
       TT.IsSession,
       ifnull(TT.AnouncementMessage,'') as AnouncementMessage
    From 
		ttimetable TT
          inner join  tbatch B on B.Id = TT.BatchId
            inner join tassignbatch AB on AB.BatchId = B.Id
		  inner join tteacher T on TT.Id = B.TeacherId
          left join tonlinelecture OL on OL.TimeTableId=TT.Id
	Where 
        (TT.BatchId = P_BatchId or P_BatchId = 0)
        AND (AB.StudentId = P_StudentId) 
        ANd ((T.InstituteId = P_InstituteId AND !(tt.Totime<Time(now()))) or P_InstituteId =0)
         AND TT.IsSession = 1 
         AND TT.FromDate>=P_FromDate
         AND TT.FromDate<=P_ToDate
	     And DAYNAME(TT.FromDate)=  'Friday';
	
     -- Saturday list
     
     Select 
       TT.Id as TimeTableId ,
       cast(TT.FromTime as char) As FromTime,
       cast(TT.ToTime as char) AS ToTime ,
       B.Id as BatchId,
       B.Title as BatchName,
       TT.Subject ,
       TT.Title,
       ifnull(cast(TT.FromDate as char),'') AS FromDate,
       TT.IsSession,
       ifnull(TT.AnouncementMessage,'') as AnouncementMessage
    From 
		ttimetable TT
         inner join  tbatch B on B.Id = TT.BatchId
           inner join tassignbatch AB on AB.BatchId = B.Id
		  inner join tteacher T on T.Id = B.TeacherId
          left join tonlinelecture OL on OL.TimeTableId=TT.Id
	Where 
        (TT.BatchId = P_BatchId or P_BatchId = 0)
        AND (AB.StudentId = P_StudentId) 
        ANd ((T.InstituteId = P_InstituteId AND !(tt.Totime<Time(now()))) or P_InstituteId =0)
         AND TT.IsSession = 1 
         AND TT.FromDate>=P_FromDate
         AND TT.FromDate<=P_ToDate
	     And DAYNAME(TT.FromDate)= 'Saturday';

     -- Sunday list
     
     Select 
       TT.Id as TimeTableId ,
       cast(TT.FromTime as char) As FromTime,
       cast(TT.ToTime as char) AS ToTime ,
       B.Id as BatchId,
       B.Title as BatchName,
       TT.Subject ,
       TT.Title,
      ifnull(cast(TT.FromDate as char),'') AS FromDate,
       TT.IsSession,
       ifnull(TT.AnouncementMessage,'') as AnouncementMessage
    From 
		ttimetable TT
      inner join  tbatch B  on B.Id = TT.BatchId
        inner join tassignbatch AB on AB.BatchId = B.Id
		  inner join tteacher T on TT.Id = B.TeacherId
          left join tonlinelecture OL on OL.TimeTableId=TT.Id
	Where 
        (TT.BatchId = P_BatchId or P_BatchId = 0)
        AND (AB.StudentId = P_StudentId) 
        ANd ((T.InstituteId = P_InstituteId AND !(tt.Totime<Time(now()))) or P_InstituteId =0)
         AND TT.IsSession = 1 
         AND TT.FromDate>=P_FromDate
         AND TT.FromDate<=P_ToDate
	     And DAYNAME(TT.FromDate)= 'Sunday';
         
         
         --  Teacher Side  --
         
         ELSE
         
           Select 
       TT.Id as TimeTableId ,
       cast(TT.FromTime as char) As FromTime,
       cast(TT.ToTime as char) AS ToTime ,
	   cast(TT.FromDate as char) AS FromDate,
       B.Id as BatchId,
       B.Title as BatchName,
       TT.Subject ,
       ifnull(TT.Title,'') as Title,
	   TT.IsSession,
       ifnull(TT.AnouncementMessage,'') as AnouncementMessage
    From 
		ttimetable TT 
		inner join  tbatch B on B.Id = TT.BatchId
        left join tteacher T  on T.Id = B.TeacherId 
        left join tonlinelecture OL on OL.TimeTableId=TT.Id
     --   inner join DateList on DateList.Day=ttimetable.Day
	Where 
          (TT.BatchId = P_BatchId OR P_BatchId = 0)
      AND ((T.InstituteId = P_InstituteId AND TT.Day = DayName(now()) AND !(tt.Totime<Time(now()))) OR P_InstituteId = 0)
	  AND (IsSession = 0)
	And (TT.Day = 'Monday')
     order by TT.FromTime ASC;
     
     -- Tuesday list
     
     Select 
	   TT.Id as TimeTableId ,
       cast(TT.FromTime as char) As FromTime,
       cast(TT.ToTime as char) AS ToTime ,
       B.Id as BatchId,
       B.Title as BatchName,
       TT.Subject ,
       ifnull(TT.Title,'') as Title,
	   cast(TT.FromDate as char) AS FromDate,
       TT.IsSession,
       ifnull(TT.AnouncementMessage,'') as AnouncementMessage
    From 
		ttimetable TT
         inner join  tbatch B on B.Id = TT.BatchId
	     left join tteacher T on T.Id = B.TeacherId 
         left join tonlinelecture OL on OL.TimeTableId=TT.Id
          -- inner join DateList on DateList.Day=ttimetable.Day
	Where 
          (TT.BatchId = P_BatchId OR P_BatchId = 0)
      AND ((T.InstituteId = P_InstituteId AND TT.Day = DayName(now()) AND !(tt.Totime<Time(now()))) OR P_InstituteId = 0)
	  AND IsSession = 0
	 And TT.Day = 'Tuesday'
      order by TT.FromTime ASC;
     
     -- Wednesday list
     
     Select 
	   TT.Id as TimeTableId ,
       cast(TT.FromTime as char) As FromTime,
       cast(TT.ToTime as char) AS ToTime ,
       B.Id as BatchId,
       B.Title as BatchName,
	   TT.Subject ,
       ifnull(TT.Title,'') as Title,
	   Ifnull(cast(TT.FromDate as char),'') AS FromDate,
       TT.IsSession,
       ifnull(TT.AnouncementMessage,'') as AnouncementMessage
    From 
		ttimetable TT
		 inner join  tbatch B on B.Id = TT.BatchId
	     left join tteacher T on T.Id = B.TeacherId 
         left join tonlinelecture OL on OL.TimeTableId=TT.Id
         --  inner join DateList on DateList.Day=ttimetable.Day
	Where 
          (TT.BatchId = P_BatchId OR P_BatchId = 0)
      AND ((T.InstituteId = P_InstituteId AND TT.Day = DayName(now()) AND !(tt.Totime<Time(now()))) OR P_InstituteId = 0)
	  AND IsSession = 0
	 And TT.Day = 'Wednesday'
      order by TT.FromTime ASC;
     
     -- Thursday list
     
     Select 
	   TT.Id as TimeTableId ,
       cast(TT.FromTime as char) As FromTime,
       cast(TT.ToTime as char) AS ToTime ,
       B.Id as BatchId,
       B.Title as BatchName,
	   TT.Subject ,
       ifnull(TT.Title,'') as Title,
	   cast(TT.FromDate as char) AS FromDate,
        TT.IsSession,
       ifnull(TT.AnouncementMessage,'') as AnouncementMessage
    From 
		ttimetable TT
		 inner join  tbatch B on B.Id = TT.BatchId
	     left join tteacher T on T.Id = B.TeacherId 
         left join tonlinelecture OL on OL.TimeTableId=TT.Id
        --   inner join DateList on DateList.Day=ttimetable.Day
	Where 
          (TT.BatchId = P_BatchId OR P_BatchId = 0)
      AND ((T.InstituteId = P_InstituteId AND TT.Day = DayName(now()) AND !(tt.Totime<Time(now()))) OR P_InstituteId = 0)
	  AND IsSession = 0
	 And TT.Day = 'Thursday'
      order by TT.FromTime ASC;
     
     -- Friday list
     
     Select 
       TT.Id as TimeTableId ,
       cast(TT.FromTime as char) As FromTime,
       cast(TT.ToTime as char) AS ToTime ,
       B.Id as BatchId,
       B.Title as BatchName,
       TT.Subject ,
       ifnull(TT.Title,'') as Title,
	   ifnull(TT.FromDate,'') AS FromDate,
       ifnull(TT.AnouncementMessage,'') as AnouncementMessage
    From 
		ttimetable TT
         inner join  tbatch B on B.Id = TT.BatchId
	     left join tteacher T on T.Id = B.TeacherId 
         left join tonlinelecture OL on OL.TimeTableId=TT.Id
        --   inner join DateList on DateList.Day=ttimetable.Day
	Where 
          (TT.BatchId = P_BatchId OR P_BatchId = 0)
      AND ((T.InstituteId = P_InstituteId AND TT.Day = DayName(now()) AND !(tt.Totime<Time(now()))) OR P_InstituteId = 0)
	  AND IsSession = 0
	 And TT.Day = 'Friday'
      order by TT.FromTime ASC;
     
     -- Saturday list
     
     Select 
       TT.Id as TimeTableId ,
       cast(TT.FromTime as char) As FromTime,
       cast(TT.ToTime as char) AS ToTime ,
       B.Id as BatchId,
       B.Title as BatchName,
	   TT.Subject ,
       ifnull(TT.Title,'') as Title,
	   cast(TT.FromDate as char) AS FromDate,
       TT.IsSession,
       ifnull(TT.AnouncementMessage,'') as AnouncementMessage
    From 
		ttimetable TT
          inner join  tbatch B on B.Id = TT.BatchId
	     left join tteacher T on T.Id = B.TeacherId 
         left join tonlinelecture OL on OL.TimeTableId=TT.Id
         --  inner join DateList on DateList.Day=ttimetable.Day
	Where 
          (TT.BatchId = P_BatchId OR P_BatchId = 0)
      AND ((T.InstituteId = P_InstituteId AND TT.Day = DayName(now()) AND !(tt.Totime<Time(now()))) OR P_InstituteId = 0)
	  AND IsSession = 0
	 And TT.Day = 'Saturday'
      order by TT.FromTime ASC;
     
     -- Sunday list
     
     Select 
       TT.Id as TimeTableId ,
       cast(TT.FromTime as char) As FromTime,
       cast(TT.ToTime as char) AS ToTime ,
       B.Id as BatchId,
       B.Title as BatchName,
       TT.Subject ,
       ifnull(TT.Title,'') as Title,
	   cast(TT.FromDate as char) AS FromDate,
       TT.IsSession,
       ifnull(TT.AnouncementMessage,'') as AnouncementMessage
    From 
		ttimetable TT
       inner join  tbatch B on B.Id = TT.BatchId
	     left join tteacher T on TT.Id = B.TeacherId 
         left join tonlinelecture OL on OL.TimeTableId=TT.Id
         --  inner join DateList on DateList.Day=ttimetable.Day
	Where 
          (TT.BatchId = P_BatchId OR P_BatchId = 0)
      AND ((T.InstituteId = P_InstituteId AND TT.Day = DayName(now()) AND !(tt.Totime<Time(now()))) OR P_InstituteId = 0)
	  AND IsSession = 0
	 And TT.Day = 'Sunday'
      order by TT.FromTime ASC;
         
	-- -- -- --  Doubt Session  -- -- -- -- --
    
       -- Monday list
   
    Select 
       TT.Id as TimeTableId,
       cast(TT.FromTime as char) As FromTime,
       cast(TT.ToTime as char) AS ToTime ,
       B.Id as BatchId,
       B.Title as BatchName,
       TT.Subject ,
       TT.Title,
	   ifnull(cast(TT.FromDate as char),'') AS FromDate,
       TT.IsSession,
       ifnull(TT.AnouncementMessage,'') as AnouncementMessage
    From 
		ttimetable TT
		  inner join  tbatch B on B.Id = TT.BatchId
		  left join tteacher T on T.Id = B.TeacherId
          left join tonlinelecture OL on OL.TimeTableId=TT.Id
	Where 
        (TT.BatchId = P_BatchId or P_BatchId = 0)
        ANd ((T.InstituteId = P_InstituteId AND TT.Day = DayName(now()) AND !(tt.Totime<Time(now()))) or P_InstituteId =0)
         AND TT.IsSession = 1 
         AND TT.FromDate>=P_FromDate
         AND TT.FromDate<=P_ToDate
	     And DAYNAME(TT.FromDate)= 'Monday';
     
     -- Tuesday list
     
     Select 
	   TT.Id as TimeTableId ,
       cast(TT.FromTime as char) As FromTime,
       cast(TT.ToTime as char) AS ToTime ,
       B.Id as BatchId,
       B.Title as BatchName,
       TT.Subject ,
       TT.Title,
       ifnull(cast(TT.FromDate as char),"") AS FromDate,
       TT.IsSession,
       ifnull(TT.AnouncementMessage,'') as AnouncementMessage
    From 
		ttimetable TT
		inner join  tbatch B on B.Id = TT.BatchId
		  left join tteacher T on T.Id = B.TeacherId
          left join tonlinelecture OL on OL.TimeTableId=TT.Id
	Where 
        (TT.BatchId = P_BatchId or P_BatchId = 0)
        ANd ((T.InstituteId = P_InstituteId AND !(tt.Totime<Time(now()))) or P_InstituteId =0)
         AND TT.IsSession = 1 
         AND TT.FromDate>=P_FromDate
         AND TT.FromDate<=P_ToDate
	     And DAYNAME(TT.FromDate)= 'Tuesday';
	
     -- Wednesday list
     
     Select 
	   TT.Id as TimeTableId ,
       cast(TT.FromTime as char) As FromTime,
       cast(TT.ToTime as char) AS ToTime ,
       B.Id as BatchId,
       B.Title as BatchName,
       TT.Subject ,
       TT.Title,
       ifnull(cast(TT.FromDate as char),'') AS FromDate,
       TT.IsSession,
       ifnull(TT.AnouncementMessage,'') as AnouncementMessage
    From 
		ttimetable TT
		inner join  tbatch B on B.Id = TT.BatchId
		  left join tteacher T on T.Id = B.TeacherId
          left join tonlinelecture OL on OL.TimeTableId=TT.Id
	Where 
        (TT.BatchId = P_BatchId or P_BatchId = 0)
        ANd ((T.InstituteId = P_InstituteId AND !(tt.Totime<Time(now()))) or P_InstituteId =0)
         AND TT.IsSession = 1 
         AND TT.FromDate>=P_FromDate
         AND TT.FromDate<=P_ToDate
	     And DAYNAME(TT.FromDate)= 'Wednesday';
	
     
     -- Thursday list
     
     Select 
	   TT.Id as TimeTableId ,
       cast(TT.FromTime as char) As FromTime,
       cast(TT.ToTime as char) AS ToTime ,
       B.Id as BatchId,
       B.Title as BatchName,
       TT.Subject ,
       TT.Title,
       ifnull(cast(TT.FromDate as char),'') AS FromDate,
	   TT.IsSession,
       ifnull(TT.AnouncementMessage,'') as AnouncementMessage
    From 
		ttimetable TT
		 inner join  tbatch B on B.Id = TT.BatchId
		  left join tteacher T on T.Id = B.TeacherId
          left join tonlinelecture OL on OL.TimeTableId=TT.Id
	Where 
        (TT.BatchId = P_BatchId or P_BatchId = 0)
        ANd ((T.InstituteId = P_InstituteId AND !(tt.Totime<Time(now()))) or P_InstituteId =0)
         AND TT.IsSession = 1 
         AND TT.FromDate>=P_FromDate
         AND TT.FromDate<=P_ToDate
	     And DAYNAME(TT.FromDate)= 'Thursday';
	
     -- Friday list
     
     Select 
       TT.Id as TimeTableId ,
       cast(TT.FromTime as char) As FromTime,
       cast(TT.ToTime as char) AS ToTime ,
       B.Id as BatchId,
       B.Title as BatchName,
       TT.Subject ,
       TT.Title,
       ifnull(cast(TT.FromDate as char),'') AS FromDate,
       TT.IsSession,
       ifnull(TT.AnouncementMessage,'') as AnouncementMessage
    From 
		ttimetable TT
          inner join  tbatch  B on B.Id = TT.BatchId
		  left join tteacher T on T.Id = B.TeacherId
          left join tonlinelecture OL on OL.TimeTableId=TT.Id
	Where 
        (TT.BatchId = P_BatchId or P_BatchId = 0)
        ANd ((T.InstituteId = P_InstituteId AND !(tt.Totime<Time(now()))) or P_InstituteId =0)
         AND TT.IsSession = 1 
         AND TT.FromDate>=P_FromDate
         AND TT.FromDate<=P_ToDate
	     And DAYNAME(TT.FromDate)=  'Friday';
	
     -- Saturday list
     
     Select 
       TT.Id as TimeTableId ,
       cast(TT.FromTime as char) As FromTime,
       cast(TT.ToTime as char) AS ToTime ,
       B.Id as BatchId,
       B.Title as BatchName,
       TT.Subject ,
       TT.Title,
       ifnull(cast(TT.FromDate as char),'') AS FromDate,
       TT.IsSession,
       ifnull(TT.AnouncementMessage,'') as AnouncementMessage
    From 
		ttimetable TT
         inner join  tbatch B  on B.Id = TT.BatchId
		  left join tteacher T on T.Id = B.TeacherId
          left join tonlinelecture OL on OL.TimeTableId=TT.Id
	Where 
        (TT.BatchId = P_BatchId or P_BatchId = 0)
        ANd ((T.InstituteId = P_InstituteId AND !(tt.Totime<Time(now()))) or P_InstituteId =0)
         AND TT.IsSession = 1 
         AND TT.FromDate>=P_FromDate
         AND TT.FromDate<=P_ToDate
	     And DAYNAME(TT.FromDate)= 'Saturday';

     -- Sunday list
     
     Select 
       TT.Id as TimeTableId ,
       cast(TT.FromTime as char) As FromTime,
       cast(TT.ToTime as char) AS ToTime ,
       B.Id as BatchId,
       B.Title as BatchName,
       TT.Subject ,
       TT.Title,
      Ifnull(cast(TT.FromDate as char),"") AS FromDate,
       TT.IsSession,
       ifnull(TT.AnouncementMessage,'') as AnouncementMessage
    From 
		ttimetable  TT
      inner join  tbatch B on B.Id = TT.BatchId
		  left join tteacher T on T.Id = B.TeacherId
          left join tonlinelecture OL on OL.TimeTableId=TT.Id
	Where 
        (TT.BatchId = P_BatchId or P_BatchId = 0)
        ANd ((T.InstituteId = P_InstituteId AND !(tt.Totime<Time(now()))) or P_InstituteId =0)
         AND TT.IsSession = 1 
         AND TT.FromDate>=P_FromDate
         AND TT.FromDate<=P_ToDate
	     And DAYNAME(TT.FromDate)= 'Sunday';
         
         
         END IF;
         
         
     
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spGetTopic`(IN `P_ChapterId` INT)
    NO SQL
BEGIN



      DECLARE Exit HANDLER FOR SQLEXCEPTION

		SELECT -1;

        

	select  

           Id as TopicId, Title as TopicName, Code As TopicCode

           

    from 

          ttopicMaster

          

	where     

          (ChapterId= P_ChapterId OR P_ChapterId = 0) ;



END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spGetUser`(
in P_UserId int
)
BEGIN
	select * from tuser where Id=P_UserId;

END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spGetUserById`(
P_UserID int,
P_InstituteId int
)
BEGIN
	declare roleid int;
	set roleid = (select roletypeid from tuser where id=P_UserId);
  
   -- Student Side --
	if (roleid=1) then
		select 
			tuser.id as UserId,
            ifnull(tuser.Name,'') as UserName,
            tuser.roletypeid as RoleTypeId,
            tuser.MobileNo as MobileNo,
            tstudent.id as StudentId,
            ifnull(tstudent.code,'') as StudentCode,
           -- ifnull(tstudent.firstname,'') as FirstName,
		   -- ifnull(tstudent.Lastname,'') as LastName,
            ifnull(tstudent.EmailId ,'') as EmailId,
            tstudent.rollno as RollNo, 
            CAST(tstudent.DateofBirth AS CHAR) as DateOfBirth,
            /*tstudent.DateofBirth as DateOfBirth */
            tstudent.GenderId as GenderId,
            ifnull(tgendermaster.Title,'') as GenderTitle,
			tstudent.BloodGroupId as BloodGroupId,
            ifnull(tbloodgroupmaster.Title,'') as BloodGroupTitle,
			ifnull(tstudent.AadharCardNumber,'') as AadharCardNumber,
			cast(tstudent.DateOfJoining as char) as DateOfJoining,
            -- ifnull(tstudent.About,'') as About,
			ifnull(PermenantAddress,'') as PermenantAddress,
            ifnull(tinstitute.Code,'') as InstituteCode,
            ifnull(tinstitute.Title,'') as InstituteTitle,
            tinstitute.Id as InstituteId,
			 ifnull(FatherName,'') as FatherName,
			ifnull(FatherContactNumber,'') as FatherContactNumber,
			ifnull(MotherName,'') as MotherName,
			ifnull(MotherContactNumber,'') as MotherContactNumber,
            tteacher.id as TeacherId,
            Ifnull(U1.Name,'') as TeacherName,
            tteacher.UserId as TeacherUserId ,
            tuser.is_user_deleted as is_user_deleted
            
		from tuser 
		left join tstudent
            on tuser.id=tstudent.userId
		left join tbloodgroupmaster on tstudent.BloodGroupId=tbloodgroupmaster.id
        left join tgendermaster on tgendermaster.Id=tstudent.GenderId
        inner join tinstitute on tinstitute.Id= tstudent.InstituteId
        left join tteacher on tstudent.InstituteId=tteacher.InstituteId
        left join tuser U1 on U1.Id = tteacher.UserId
		where tuser.id=P_UserId and tstudent.InstituteId=P_instituteId;
        
       -- Teacher Side -- 
	elseif roleid=2 then
		select
			tuser.id as UserId,
            ifnull(tuser.Name,'') as UserName,
            tuser.roletypeid as RoleTypeId,
            tuser.MobileNo as MobileNo,
            tteacher.id as TeacherId,
            ifnull(tteacher.code,'') as TeacherCode,
           -- ifnull(tteacher.firstname,'') as FirstName,
           -- ifnull(tteacher.Lastname,'') as LastName,
            ifnull(tteacher.EmailId,'') as EmailId,
            cast(tteacher.DateofBirth as char) as DateOfBirth,
            ifnull(tinstitute.Code,'') as InstituteCode,
            ifnull(tinstitute.Title,'') as InstituteTitle,
            tinstitute.Id as InstituteId,
            tteacher.YearOfExperience as YearOfExperience,
            ifnull(tteacher.About,'') as About,
			ifnull(Group_Concat(tsubcategorymaster.title),'') as SubcategoryTitles,
			ifnull(group_Concat(tsubcategorymaster.id),'') as SubcategoryIds,
            ifnull(tteacher.SubjectId,'') as SubjectIds,
            GenderId as GenderId,
            ifnull(tgendermaster.Title,'') as GenderTitle,
			BloodGroupId as BloodGroupId,
            ifnull(tbloodgroupmaster.Title,'') as BloodGroupTitle,
			ifnull(AadharCardNumber,'') as AadharCardNumber,
			cast(DateOfJoining as char) as DateOfJoining,
            ifnull(FacebookLink,'') as FacebookLink,
            ifnull( InstagramLink,'') as InstagramLink,
            ifnull( GoogleMapLink,"") as GoogleMapLink ,
              tuser.is_user_deleted as is_user_deleted
		from tuser 
		left join tteacher 
			on tuser.id=tteacher.userId
		left join tinstitute 
			on tteacher.InstituteId=tinstitute.Id
		left join tsubcategorymaster 
			on FIND_IN_SET(tsubcategorymaster.id,tteacher.Subcategoryids)
		left join tbloodgroupmaster on tteacher.BloodGroupId=tbloodgroupmaster.id
        left join tgendermaster on tgendermaster.Id=tteacher.GenderId
		where tuser.id=P_UserId and tteacher.InstituteId=P_InstituteId;
        
        -- Admin Side --
        
	elseif roleid=3 then
		select
			tuser.id as UserId,
            ifnull(tuser.Name,'') as UserName,
            tuser.roletypeid as RoleTypeId,
            tuser.MobileNo as MobileNo,
            tadmin.id as AdminId,
            ifnull(tadmin.code,'') as AdminCode,
          --  ifnull(tadmin.firstname,'') as FirstName,
          -- ifnull(tadmin.Lastname,'') as LastName,
            ifnull(tadmin.EmailId,'') as EmailId,
            ifnull(tinstitute.Code,'') as InstituteCode,
            ifnull(tinstitute.Title,'') as InstituteTitle,
            tinstitute.Id as InstituteId,
            tadmin.IsSuperAdmin as IsSuperAdmin ,
              tuser.is_user_deleted as is_user_deleted
		from tuser 
		left join tadmin 
			on tuser.id=tadmin.userId
		left join tinstitute 
			on tadmin.InstituteId=tinstitute.Id
		where tuser.id=P_UserId and tadmin.InstituteId=P_InstituteId;
    end if;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spGetViewHistory`(IN `P_DocumentId` INT, IN `P_StatusId` INT)
BEGIN
	select 
         ts.Id as StudentId, 
         ts.UserId as UserId, 
         ifnull(tu.Name,'') as UserName , 
         tvh.ViewCount as ViewCount
    from tviewhistory tvh
    inner join tstudent ts on tvh.StudentId=ts.Id 
    left join tuser tu on tu.Id = ts.UserId
    where tvh.StatusId=P_StatusId
    and tvh.DocumentId=P_DocumentId;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spGroupParticipantsList`(
   in P_ChatGroupId int
)
BEGIN
		
        Select 
               CG.Id as GroupId ,
               CG.Title as GroupName ,
               CG.BatchId as BatchId , 
               ifnull(CG.CreatedDateTime,'') as CreatedDateTime ,
               B.Title as BatchName ,
               B.TeacherId as ParticipantSourceId ,
               U1.Id  as ParticipantId ,
               U1.Name as ParticipantName ,
               U1.RoleTypeId as ParticipantRoleTypeId 
		from 
               tchatgroupmaster CG
			   inner Join tbatch B on B.Id = CG.BatchId
               inner Join tteacher T on T.Id = B.TeacherId
               inner Join tuser U1 on U1.Id = T.UserId
        where
            CG.Id = P_ChatGroupId
            
		Union
               Select 
               CG.Id as GroupId ,
               CG.Title as GroupName ,
               CG.BatchId as BatchId ,
               ifnull(CG.CreatedDateTime,'') as CreatedDateTime ,
               B.Title as BatchName ,
               AB.StudentId as ParticipantSourceId,
               U1.Id  as ParticipantId ,
               U1.Name as ParticipantName ,
               U1.RoleTypeId as ParticipantRoleTypeId 
		from 
               tchatgroupmaster CG
			   inner Join tbatch B on B.Id = CG.BatchId
               inner Join tassignbatch AB on AB.BatchId = B.Id
               inner Join tstudent S on S.Id = AB.StudentId
               inner Join tuser U1 on U1.Id = S.UserId
        where
             CG.Id = P_ChatGroupId;
         
               
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spJoinBatch`(IN `P_BatchCode` VARCHAR(10), IN `P_UserId` INT, IN `P_InstituteId` INT, IN `P_StudentId` INT)
BEGIN
	DECLARE l_batch_id INT DEFAULT 0;
    DECLARE l_Institute_id int Default 0;
  --  SET l_Institute_id = (SELECT 
                 --       t.InstituteId
				 --  FROM tbatch B 
                      --  inner join tteacher t ON B.TeacherId = t.id
						-- WHERE B.Code= P_BatchCode );
      start transaction;
      
	SET l_batch_id = (Select Id from tbatch where Code=P_BatchCode);
	  if (l_batch_id > 0)
      then
      
		-- if exists( SELECT s.UserId FROM vida_lms.tbatch B inner join vida_lms.tteacher t ON B.TeacherId = t.id  
				   -- inner join vida_lms.tstudent s ON S.InstituteId = t.InstituteId
				   -- WHERE  B.Code= P_BatchCode  AND s.UserId = P_UserId)
		-- then 
				
		     begin
			   if exists(select 1 from tassignbatch where BatchId=l_batch_id AND StudentId = P_StudentId And StatusId = 11) 
               then
			          begin
				            select -2;
					  end;
		       else
               
                  if exists(select 1 from tassignbatch where BatchId=l_batch_id AND StudentId = P_StudentId And StatusId = 12) 
                  then
                      select -3;
                  else
                  
			         begin
			            insert into tassignbatch
				             (BatchId,StudentId,StatusId)
					     values
						   (l_batch_id,P_StudentId,12);
				      
			           select l_batch_id;
              -- Select concat(TeacherId ,'\\',l_batch_id) From tbatch where Id=P_BatchId ;
				    end;
                    end if;
			   end if;
	        end;
         
         
       --   else
		       
             --  Insert into tstudent
              --    (UserId,StatusId,InstituteId,CreatedUserId,CreatedDateTime)
            --   Values
				--   (P_UserId,11,l_Institute_id,P_UserId,now());
                   
              --   select Last_Insert_Id();
                 
             --   insert into tassignbatch
				    --       (BatchId,StudentId,StatusId)
					  -- values
					  -- (l_batch_id,P_StudentId,12);
				  
			          -- select l_batch_id;  
                 
		--  end if;
      
      else
		   Select -1;
      end if;
      
      commit;

END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spLoginUser`(
	in P_MobileNo varchar(100),
    in P_RoleTypeId int
)
BEGIN

	if exists(select 1 from tuser where MobileNo=P_MobileNo and  RoleTypeId=P_RoleTypeId) then
		if exists(select 1 from tuser where MobileNo=P_MobileNo and  RoleTypeId=P_RoleTypeId and Name is not null) then
		select Id As UserId, Name as UserName,MobileNo as MobileNo ,RoleTypeId
        from tuser where MobileNo=P_MobileNo and  RoleTypeId=P_RoleTypeId;
        else
			select -1 as UserId;
		end if;
	
	elseif not exists(select 1 from tuser where MobileNo=P_MobileNo and  RoleTypeId=P_RoleTypeId) then
		select "-4" as UserId;
	else
		select "-3" as UserId;
	end if;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spMarkFeeAsPaidViaCash`(
     in P_FeeId int,
	 in P_StudentId int,
     in P_PaymenttransactionId int,
	 in P_PaymentAmount int,
	 in P_PaymentDate Datetime,
     in P_TransactionStatusId int,
     in P_PaymentRemark varchar(500),
     in P_DateOfFeeCollection date
 )
BEGIN
 
    if exists(select 1 from tfeesetup where Id = P_FeeId AND  StudentId = P_StudentId )
    then
 
		  if(P_PaymenttransactionId = 0)
		  then
		  
			 Start transaction;
		 
				Insert Into tpaymenttransaction
			 
					 (FeeSetupId,StudentId,PayAmount,PayDateTime,PaymentRemark,TransactionStatusId,PaymentType,DateOfFeeCollection)
			 
				Values
			  
					 (P_FeeId,P_StudentId,P_PaymentAmount,P_PaymentDate,P_PaymentRemark,6,'Cash',P_DateOfFeeCollection);
				
				select 1;
				commit;
            else 
             Start transaction;
				
                 Update 
		                tpaymenttransaction
                 set 
						PaymentRemark=P_PaymentRemark,
                        TransactionStatusId=CASE WHEN P_TransactionStatusId = 0 THEN 6 WHEN P_TransactionStatusId = 6 THEN 0 ELSE 0 END
                        where 
                        FeeSetupId=P_FeeId and
						StudentId=P_StudentId and
                        Id=P_PaymenttransactionId and
                        Date_format(DateOfFeeCollection,"%Y-%m") = Date_format(P_DateOfFeeCollection,"%Y-%m");
       select 1;   
       
     Commit;
    end if;
      end if;
      
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spPaymentTransactionUpdate`(
    in P_TransactionResponse varchar(1000),
	in P_BankName varchar(100),
    in P_CardNumber varchar(100),
    in P_PaymentType varchar(50),
	in P_PaymentTransactionId int,
    in P_MerchantTransactionId bigint,
    in P_BankTransactionId bigint
)
BEGIN
	DECLARE Exit HANDLER FOR SQLEXCEPTION
    begin
		Rollback;
		SELECT -1;
	end;
	
    UPDATE tpaymenttransaction SET
    	MerchantTransactionId = P_MerchantTransactionId,
	    BankTransactionId = P_BankTransactionId,
	    BankName = P_BankName,
	    PaymentType = P_PaymentType,

	    CardNumber = P_CardNumber,
	    TransactionResponse = P_TransactionResponse
	WHERE Id = P_PaymentTransactionId;
       
	SELECT P_PaymentTransactionId AS Result;
	
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spRegisteredUsersGet`(
    in P_RoleTypeId int,
    in P_SourceId int,
    in P_OSTypeId smallint
)
BEGIN
	SELECT 
		IFNULL(DeviceToken,'') AS DeviceToken
    FROM tregisteredusers RU
	LEFT JOIN tteacher T ON RU.SourceId = T.Id
    WHERE 
       	(T.Id=P_SourceId) 
        AND (P_RoleTypeId=2)
		AND RU.OSTypeId=P_OSTypeId 
        AND RU.DeviceToken IS NOT NULL AND RU.DeviceToken != '' 
		AND (RU.RoleTypeId=P_RoleTypeId OR P_RoleTypeId=0)
        
	UNION ALL
    
    SELECT 
		IFNULL(DeviceToken,'') AS DeviceToken
    FROM tregisteredusers RU
	LEFT JOIN tstudent S ON RU.SourceId = S.Id
    WHERE 
       	(S.Id=P_SourceId) 
        AND (P_RoleTypeId=1)
		AND RU.OSTypeId=P_OSTypeId 
        AND RU.DeviceToken IS NOT NULL AND RU.DeviceToken != '' 
		AND (RU.RoleTypeId=P_RoleTypeId OR P_RoleTypeId=0);
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spRenameContentName`(
	in P_ContentId int,
	in P_SubPath varchar(500),
    in P_OldPath varchar(100),
    in P_NewTitle varchar(100),
	in P_NewLink varchar(100),
    in P_NewDuration varchar(100),
    in P_ContentTypeId int,
    in P_AssignContent int
)
BEGIN
	declare P_Path varchar(500);
    if P_AssignContent=1 then
		if  exists(select 1 from tteachercontent where Id =P_ContentId and P_ContentTypeId in (5,6,7,8,20)) then
		
			if P_ContentTypeId=5 then 
			begin
            set P_Path=(select concat(replace(ContentPath,'\\','+'),'+',Id,'+',ContentTitle,'+%')
					from tteacherContent where id=P_ContentId);
			start transaction;
				Update tteachercontent
					set ContentTitle=P_NewTitle
					where Id =P_ContentId;
				Update tteachercontent
					set ContentPath = 
						replace(Concat(ContentPath,'\\'),Concat('\\',P_ContentId,'\\',P_OldPath,'\\'),Concat('\\',P_ContentId,'\\',P_NewTitle))
					where concat(replace(ContentPath,'\\','+'),'+') like P_Path;
				
				select 1;
				commit;
				end;
			else
				start transaction;
					update tteachercontent
						set ContentTitle=P_NewTitle ,
							ContentLink = P_NewLink,
                            Duration = P_NewDuration
                        
					where Id=P_ContentId;
					select 1;
				commit;
			end if;
		else
			if P_ContentTypeId in (5,6,7,8,20) then
				select -2;
			else
				select -3;
			end if;
		end if;
	elseif P_AssignContent=2 then
		if  exists(select 1 from tteachercontentassign where Id =P_ContentId and P_ContentTypeId in (5,6,7,8,20)) then
			if P_ContentTypeId=5 then 
			begin
            set P_Path=(select concat(replace(ContentPath,'\\','+'),'+',Id,'+',ContentTitle,'+%')
					from tteachercontentassign where id=P_ContentId);
			start transaction;
				Update tteachercontentassign
					set ContentTitle=P_NewTitle,
                        ContentLink = P_NewLink,
						UpdatedDateTime=now()
					where Id =P_ContentId;
				Update tteachercontentassign
					set ContentPath = 
						replace(Concat(ContentPath,'\\'),Concat('\\',P_ContentId,'\\',P_OldPath,'\\'),Concat('\\',P_ContentId,'\\',P_NewTitle))
					where concat(replace(ContentPath,'\\','+'),'+') like P_Path;
				
				select 1;
				commit;
				end;
			else
				start transaction;
					update tteachercontentassign
						set ContentTitle=P_NewTitle,
                            ContentLink = P_NewLink,
                            Duration = P_NewDuration,
							UpdatedDateTime=now()
					where Id=P_ContentId;
					select 1;
				commit;
			end if;
        end if;
	else
		if P_ContentTypeIdin (5,6,7,8,20) then
				select -2;
			else
				select -3;
			end if;
    end if;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spRenameTitle`(
	in P_ContentId int,
    in P_NewTitle varchar(500)
)
BEGIN
	DECLARE Exit HANDLER FOR SQLEXCEPTION
		begin
			Rollback;
			select -1;
		end;
	start transaction;
		update tteachercontent
			set ContentTitle=P_NewTitle
		where Id=P_ContentId;
        select 1;
    commit;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spSelectInstitute`(
      in P_UserId int,
      in P_InstituteId int,
      IN P_OSTypeId smallint,
      IN P_DeviceToken varchar(200)
 )
BEGIN
      

       DECLARE roletype INT DEFAULT 0;
       DECLARE V_SourceId INT DEFAULT 0;
       SET roletype = (select RoleTypeId from tuser where Id=P_UserId);
       
       if (roletype=1)
       then
	       
            if exists(select 1 from tstudent where UserId = P_UserId AND InstituteId = P_InstituteId)
            then 
	
            update tstudent
				set StatusId=11
			where UserId = P_UserId AND InstituteId = P_InstituteId and (StatusId=12 or StatusId is null);
               select 1;
               set V_SourceId=(select Id from tstudent where UserId = P_UserId AND InstituteId = P_InstituteId);
			else 
                select -2;
			end if;
       elseif(roletype=2)
       then
            
			if exists(select 1 from tteacher where UserId = P_UserId AND InstituteId = P_InstituteId)
            then
				update tteacher
                set 
                DateOfJoining=now(),
					UpdatedDateTime=now()
				where UserId = P_UserId and InstituteId = P_InstituteId and UpdatedDateTime is null;
               select 1;
               set V_SourceId=(select Id from tteacher where UserId = P_UserId AND InstituteId = P_InstituteId);
			else 
             
                select -1;
             
			end if;
            
       end if;
         
        if(V_SourceId !=0)
        then
            
	        call spAddRegisterUser(P_UserId,roletype,V_SourceId,P_OSTypeId,P_DeviceToken);   
      
         end if;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spSignalRConnection`(
P_UserId int ,
P_ConnectionId nvarchar(500)
)
begin
if exists  (Select 1 from tsignalrconnection where UserId = P_UserId) then
				begin
                 update tsignalrconnection set IsConnected = 0 , DisconnectedDateTime = Now() where UserId = P_UserId;
                 Insert into tsignalrconnection
                (UserId, ConnectionId, IsConnected,ConnectionDateTime) 
                Values( P_UserId, P_ConnectionId, 1,Now()) ;
                 end;
                else 
				begin
                Insert into tsignalrconnection
                (UserId, ConnectionId, IsConnected,ConnectionDateTime) 
                Values( P_UserId, P_ConnectionId, 1,Now()) ;
                end;
                 SELECT 1 As RESELT;
                 end if;
                 end$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spSignalRSendMessage`(
P_FromRoleTypeId int ,
P_FromUserId int,
P_FromSourceId int ,
P_ToRoleTypeId int ,
P_ToUserId int ,
P_ToSourceId int,
P_message longtext,
P_FileName nvarchar(100),
P_ChatGroupId int ,
P_CreatedUserId int ,
P_ContentPath varchar(200),
P_ConnectionId nvarchar(500) 
)
BEGIN
 declare l_ChatId int default 0;
   if(P_ChatGroupId=0) then
    INSERT INTO tchat
    (FromRoleTypeId, FromUserId, FromSourceId ,ToRoleTypeId , ToUserId, ToSourceId ,ChatDetail, Contentpath, ChatGroupId,CreatedUserId,CreatedDateTime) 
    values 
    (P_FromRoleTypeId,P_FromUserId ,P_FromSourceId,P_ToRoleTypeId,P_ToUserId,P_ToSourceId,P_message,P_ContentPath , P_ChatGroupId, P_CreatedUserId, Now());
     set l_ChatId=LAST_INSERT_ID();
      SELECT 
           SR.ConnectionId, 
           l_ChatId as ChatId,
           U.Name As UserName 
      FROM `vida_lms`.`tsignalrconnection` SR INNER JOIN tuser U ON U.Id = SR.UserId
      WHERE SR.UserId = P_ToUserId 
        AND IsConnected = 1 
        And SR.SourceId = P_ToSourceId 
        And SR.RoleTypeId = P_ToRoleTypeId
        ORDER BY ConnectionDateTime DESC;
  
  elseif (P_ChatGroupId>0) then
	  INSERT INTO tchat
		(FromRoleTypeId, FromUserId, FromSourceId ,ToRoleTypeId , ToUserId, ToSourceId ,ChatDetail, Contentpath, ChatGroupId,CreatedUserId,CreatedDateTime) 
		values 
		(P_FromRoleTypeId,P_FromUserId ,P_FromSourceId,0,0,0,P_message,P_ContentPath , P_ChatGroupId, P_CreatedUserId, Now());
		set l_ChatId=LAST_INSERT_ID();
		  SELECT 
			   SR.ConnectionId, 
			   U.Name As UserName,
               l_ChatId as ChatId,
               CG.Title As GroupName
               from tchatgroupmaster CG 
               left Join tassignbatch AB On CG.BatchId=AB.BatchId
               left Join tStudent S ON S.Id=AB.StudentId
               left join tuser U ON S.UserId=U.id
               Inner Join tsignalrconnection SR ON SR.SourceId=AB.StudentId AND SR.RoleTypeId=1
               where CG.Id=P_ChatGroupId And SR.UserId!=P_FromUserId AND SR.RoleTypeId=1 And IsConnected = 1 
		union 
		SELECT 
			   SR.ConnectionId, 
			   U.Name As UserName,
               l_ChatId as ChatId,
               CG.Title As GroupName
               from tchatgroupmaster CG 
               left Join tbatch B On CG.BatchId=B.Id
               left Join tteacher T ON T.Id=B.TeacherId
               left join tuser U ON T.UserId=U.id
               Inner Join tsignalrconnection SR ON SR.SourceId=B.TeacherId AND SR.RoleTypeId=2
               where CG.Id=P_ChatGroupId And SR.UserId!=P_FromUserId AND SR.RoleTypeId=2 And IsConnected = 1;
			
  end if;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spSMSLogGet`(
	in P_FromId INT,
    in P_ToId INT
)
BEGIN
	SELECT 
		Id AS MsgID, 
		ContactId AS MobileNo,
		AlertMessage AS MsgText
	FROM
		talertlog
	WHERE 
		(Id >= P_FromId) AND (Id <= P_ToId);
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spStudentBatchData`(
     in P_StudentId int,
     in P_TeacherId int,
     in P_BatchId int
 )
BEGIN
   
      SELECT 
      
            Id As BatchId,
            Code As BatchCode,
	        Title AS BatchTitle,
            BatchDate,
            CreatedDateTime
         
        From tbatch 
        
		inner join tassignbatch on tassignbatch.BatchId = tbatch.Id
		 
        WHERE
           
             (tassignbatch.StudentId = P_StudentId)
		 and (tbatch.TeacherId = P_TeacherId)  
		 and (tassignbatch.BatchId= P_BatchId or P_BatchId=0);
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spStudentList`(
    in P_BatchId int,
    in P_HomeWorkId int
)
BEGIN

 SELECT 
       tstudent.Id As StudentId,
	   ifnull(tuser.Name , '')as StudentName,
       thomeworkassign.StatusId as StatusId,
       tstatusmaster.Title as StatusTitle
       
     from thomeworkassign
     
     inner join tstudent on thomeworkassign.StudentId=tstudent.Id
     
     inner join thomework on thomeworkassign.HomeWorkId=thomework.Id
     
     inner join tstatusmaster on thomeworkassign.StatusId=tstatusmaster.Id
     inner join tuser on tuser.Id = tstudent.UserId
     
     Where 
     (thomework.BatchId=P_BatchId OR  P_BatchId = 0)
     AND (thomework.Id = P_HomeWorkId OR P_HomeWorkId = 0);


END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spsubmitpath`(
   in P_HomeWorkId int 
 )
BEGIN
    
      declare exit HANDLER for SQLEXCEPTION
	  begin
		  Rollback;
          select -1;
	  end;
      
      
		if exists(select 1 from thomework where Id=P_HomeWorkId) then
			Select concat(TeacherId,'\\',BatchId) From tHomeWork where Id=P_HomeWorkId ;
		else
			select -2;
		end if;
      
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spTruncateTable`()
BEGIN

	SET FOREIGN_KEY_CHECKS = 0; -- to ignor foreign key error
	truncate table tstudymaterialcomment;
    truncate table tteachercontentassign;
    truncate table tteachercontent;
    truncate table tparaquestions;
    truncate table tresult;
    truncate table teteststudentanswer;
    truncate table tetestquestionoptions;
    truncate table tetestquestions;
    truncate table tetest;
    truncate table thomeworksubmittedanswer;
    truncate table thomeworkassign;
    truncate table thomework;
    truncate table tnoticeboard;
    truncate table tonlinelecturemeetingsettings;
    truncate table tonlinelecturemeetingaccount;
    truncate table tonlinelearningstudentattendance;
    truncate table tonlinelecture;
    truncate table ttimetable;
    truncate table tchatgroupmaster;
    truncate table tchat;
    truncate table tnotification;
    truncate table tassignbatch;
    truncate table tbatch;
    truncate table tadmin;
    truncate table tteacher;
    truncate table tstudent;
    truncate table tinstitute;
    truncate table tuser;
    truncate table tuserlog;
    SET FOREIGN_KEY_CHECKS = 1; 
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spUnassignBatch`(
	in P_BatchId int,
    In P_StudentId int
)
BEGIN
	DECLARE Exit HANDLER FOR SQLEXCEPTION
		SELECT "-1";
	if exists(select 1 from tAssignBatch where BatchId=P_BatchId and StudentId=P_StudentId) then
		begin
			Delete FROM tAssignBatch 
				where StudentId=P_StudentId and batchId=P_BatchId;
			select "1";
            
            Delete from tfeesetup
                   where StudentId=P_StudentId and batchId=P_BatchId;
		end;
        
     elseif exists(select 1 from tAssignBatch where StudentId=P_StudentId) 
     then
       
      Delete FROM tAssignBatch 
				where StudentId=P_StudentId;
                
        Delete from tfeesetup
                   where StudentId=P_StudentId;        
			select "1";
      
	else 
		begin
			select "-3";
		end;	
	end if;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spUpdateBatch`(
     in P_TeacherId int(11),
	 in P_InstituteId int(11),
	 in P_BatchId int,
     in P_Title varchar(100),
     in P_BatchDate date
 )
BEGIN
  
       if exists(select 1 from tbatch,tteacher where TeacherId = P_TeacherId AND tteacher.InstituteId = P_InstituteId)
       then
     
          start transaction;
     
             Update tbatch  
             set 
             
				 Title =P_Title,
				 BatchDate =P_BatchDate,
				 UpdatedDateTime = Now()
              where 
              
                  Id = P_BatchId;
            
             select 1;
             
          commit;
     
         else
        
           select-1;
   
     end if;
  
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spUpdateHomeWork`(
   in P_StudentIds LONGTEXT,
   in P_HomeWorkId int, 
   in P_Title varchar(45),
   in P_Notes varchar(45),
   in P_SubmissionDateTime datetime,
   in P_StatusId int,
   in P_IsSendNotificationHomeworkNotSubmit bit,
   in P_IsSendSMSHomeworkNotSubmit bit
   
)
BEGIN
	declare P_BatchId int;
	declare P_Values varchar(500) default '';
    declare P_Insert varchar(500) default '';   

     if exists(select 1 from thomework  where Id = P_HomeWorkId )
     then

          DELETE From thomeworkassign WHERE HomeWorkId = P_HomeWorkId;
	 
	      UPDATE thomework  
                 set 
                     StudentId = P_StudentIds,
					 Title  = P_Title,
                     Note  = P_Notes,
                     HomeworkDateTime =P_SubmissionDateTime,
                     IsSendNotificationHomeworkNotSubmit=IsSendNotificationHomeworkNotSubmit,
                     IsSendSMSHomeworkNotSubmit=IsSendSMSHomeworkNotSubmit,
                     UpdatedDateTime=now()
	            where
                     thomework.Id = P_HomeWorkId;
                     
        set P_BatchId = (select BatchId from thomework where Id = P_HomeWorkId);
        SET P_Values = REPLACE(P_StudentIds, ',', CONCAT(',',P_HomeworkId,',',P_StatusId, '),('));
			SET P_Values = CONCAT('(', P_Values, ',',P_HomeworkId,',',P_StatusId, ')');
			SET @Insert = CONCAT('INSERT INTO thomeworkassign(StudentId,HomeworkId,StatusId) VALUES', P_Values);
			PREPARE stmt FROM @Insert;
			EXECUTE stmt;
          
          SELECT 1;
          commit;
     
     else
       select -1;
       
          end if;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spUpdateNoticeBoard`(
	in P_NoticeId int,
    in P_Remark varchar(500)
)
BEGIN
	DECLARE Exit HANDLER FOR SQLEXCEPTION
    begin
		SELECT -1;
	end;
    if not exists(select 1 from tnoticeboard where id=P_NoticeId) then 
		select "-2";
	else
		Update tnoticeboard set
			Remark=P_Remark,
            UpdatedDateTime =now()
		where id=P_NoticeId;
        select concat(tbatch.TeacherId,'\\',tnoticeboard.BatchId) 
        from tnoticeboard 
        inner join tbatch on tnoticeboard.BatchId=tbatch.Id 
        where tnoticeboard.id=P_NoticeId;
    end if;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spUpdateOtp`(
 in P_UserId int,
 in P_OTP varchar(10)
)
BEGIN
	DECLARE Exit HANDLER FOR SQLEXCEPTION
    begin
		Rollback;
		SELECT -1;
	end;
    if not exists(select 1 from tuser where id=P_UserId) then
		begin
			select -2;
		end;
    else
		Update tuser set
			OTP=P_OTP,
            OTPExpiryDate=ADDTIME(now(), '1000')
		where Id=P_UserId;
        select 1;
	end if;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spUpdateStatus`( 
    in P_HomeWorkId int,
   -- in P_IsApprove Bit,
    in P_StudentId varchar(500)
    
 )
BEGIN
 -- declare V_StatusId int default 0;
 
--    end if;
       if exists(select 1 from thomework where Id=P_HomeWorkId)
       then
          
	      Start transaction;
          
  Update thomeworkassign
          set StatusId = 2
		   Where HomeWorkId = P_HomeWorkId;
          
          Update thomeworkassign
            set StatusId = 1
          Where HomeWorkId = P_HomeWorkId
          AND find_in_set(StudentId, P_StudentId) > 0;
          
          select 1;
       
       commit;
  
    end if;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spUpdateStudent`(
	P_UserId int, 
    P_UpdateId int,
    P_StudentId int,
    P_BatchId int, 
    -- P_FirstName varchar(100),
    -- P_LastName varchar(100),
    in P_UserName varchar(100),
    P_EmailId varchar(100),
    P_RollNo int,
    P_DateOfJoining date,
    -- P_About varchar(100),
    P_FatherName varchar(100), 
    P_FatherContactNumber varchar(100),
    P_MotherName varchar(100),
    P_MotherContactNumber varchar(100),
    
    P_GenderId int,
    P_BloodGroupId int,
    P_DateOfBirth date,
    P_AadharCardNumber varchar(100),
    P_PermenantAddress varchar(100)
)
BEGIN
	
	if(P_UpdateId=21)
    then
         if exists(select 1 from tstudent where UserId=P_UserId)
         then
    --  if exists(select 1 from tassignbatch inner join tstudent on tassignbatch.StudentId =tstudent.Id 
    --    where tstudent.UserId=P_UserID)
	-- then
              start transaction;
				  Update tuser
					set 
						Name=P_UserName,
						UpdatedDateTime = now()
					where id=P_UserId;
		          Update 
                         tstudent
			      set 
                         -- FirstName=P_UserName, 
				         -- Lastname=P_LastName, 
                         EmailId= P_EmailId,
                         RollNo=P_RollNo,
                         -- About=P_About,
						 DateOfJoining = P_DateOfJoining,
						 UpdatedDateTime = Now()
				  where   
                          tstudent.Id=P_StudentId  AND
                          tstudent.UserId=P_UserId;
                select 1;
			 commit;
        -- else
       
        -- select -4;
        -- end if;
           else
               select -1;
           end if;
           
	   elseif(P_UpdateId=22)
       then
             start transaction;
             Update
                    tstudent
             Set
					FatherName = P_FatherName,
                    FatherContactNumber = P_FatherContactNumber,
                    MotherName = P_MotherName,
                    MotherContactNumber = P_MotherContactNumber
			 where 
                    tstudent.Id=P_StudentId  AND
					tstudent.UserId=P_UserId;
                select 1;
			 commit;
	   elseif(P_UpdateId=23)
       then
             start transaction; 
			 Update
                    tstudent
             Set
					GenderId = P_GenderId,
                    BloodGroupId = P_BloodGroupId,
                    AadharCardNumber = P_AadharCardNumber,
					DateOFBirth=P_DateOFBirth,
                    PermenantAddress =P_PermenantAddress
			 where 
                    tstudent.Id=P_StudentId  AND
					tstudent.UserId=P_UserId;
                select 1;
			 commit;
        end if;
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spUpdateTeacher`(
	in P_UserID int,
    in P_UpadateId int,
    in P_TeacherId int, 
    -- in P_FirstName varchar(100),
    -- in P_LastName varchar(100),
    in P_UserName varchar(100),
    in P_EmailId varchar(100),
	in P_InstituteTitle varchar(100),
    in P_InstituteId int,
    in P_SubcategoryIds varchar(100),
    in P_SubjectIds varchar(100),
    in P_YearOfExperience int,
	in P_FacebookLink varchar(100),
    in P_InstagramLink varchar(100),
    in P_GoogleMapLink varchar(100),
    in P_DateOfBirth date,
    P_About varchar(100),
	P_GenderId int,
    P_BloodGroupId int,
    P_AadharCardNumber varchar(100),
    P_DateOfJoining date
)
BEGIN
	DECLARE Exit HANDLER FOR SQLEXCEPTION
    begin
		Rollback;
		SELECT -1;
	end;
	DECLARE CONTINUE HANDLER FOR NOT FOUND
    begin
		Rollback;
		select -3;
	end;
    if not exists(select 1 from tteacher where id=P_TeacherId) then
		begin
			select -2;
		end;
	else
		if P_UpadateId=9 
        then
	
		     start transaction;
             update 
					tinstitute
                    set 
                         Title=P_InstituteTitle,
						 UpdatedDateTime =now()
					where 
						id=P_InstituteId;
			      update
                         tteacher
				  set
						SubjectId=P_SubjectIds,
					     SubCategoryIds = P_SubCategoryIds,
					     About = P_About,
					     UpdatedDateTime = now()
				  where 
                         tteacher.id = P_TeacherId;
				
			     
			      
			  select 1;
			  commit;
	
        elseif P_UpadateId=21 
        then
       
	         start transaction;
				Update tuser
				set 
					Name=P_UserName,
                    UpdatedDateTime = now()
				where id=P_UserID;
                
			    update
                      tteacher
                      
				set
                     -- FirstName= P_UserName,
					  -- LastName=P_LastName,
					  EmailId=P_EmailId,
					  YearOfExperience = P_YearOfExperience,
					  DateOfBirth = P_DateOfBirth,
                      GenderId = P_GenderId,
                        BloodGroupId = P_BloodGroupId,
                        AadharCardNumber = P_AadharCardNumber,
                        DateOfJoining = P_DateOfJoining,
					  UpdatedDateTime = now()
                    
				where 
                       tteacher.id = P_TeacherId;
			  
		    select 1;
	     	commit;
        
        
        elseif P_UpadateId=10 
        then
               start transaction;
                  update 
						 tteacher
                  set 
                        FacebookLink  = P_FacebookLink,
                        InstagramLink  = P_InstagramLink,
			            GoogleMapLink = P_GoogleMapLink
             
                  where  
					    Id=P_TeacherId;
                       
                  select 1;
                commit;
        
		/*elseif P_UpadateId=23 
        then
               start transaction;
               
				  update 
                          tteacher
                  set 
                        GenderId = P_GenderId,
                        BloodGroupId = P_BloodGroupId,
                        AadharCardNumber = P_AadharCardNumber,
                        DateOfJoining = P_DateOfJoining 
                    
                  where  
                         Id=P_TeacherId;
                  select 1;
                   commit;*/
        end if;
        
	end if;
        
	
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spUserLogAdd`( 
     in P_UserId int,
     in P_LoggedInIp varchar(50),
     in P_OSTypeId int,
     in P_InstituteId int
 )
BEGIN
  
          start transaction;
          
            Insert into tuserlog
            
              (UserId,LoggedInIp,OSTypeId,LoggedInDateTime,InstituteId)
              
           VALUES 
             
              (P_UserId,P_LoggedInIp,P_OSTypeId,now(),P_InstituteId);
              
              SELECT 1;
          commit;
  
END$$

CREATE DEFINER=`vida_lms_dev`@`%` PROCEDURE `spUserLogOff`(
     in P_UserId int,
     in P_InstituteId int,
     in P_hostAddress varchar(15)
 )
BEGIN
  declare P_DateTime datetime;
  declare roletype int;
	set P_DateTime = (SELECT MAX(LoggedInDateTime) 

							FROM tUserLog WHERE UserId = P_UserId and InstituteId=P_InstituteId

							AND LoggedInIp = P_hostAddress);
	UPDATE tUserLog	
	
		SET LoggedOutDateTime = now()

		WHERE UserId = P_UserId and InstituteId=P_InstituteId AND		

		LoggedInDateTime=P_DateTime;
         
      set  roletype = (select RoletypeId from tuser where Id = P_UserId);
       if(roletype = 1) 
       then
            delete TR
            from tregisteredusers TR
              Left Join tstudent S on S.Id = TR.SourceId 
            where S.UserId  = P_UserId and S.InstituteId = P_InstituteId;
       elseif (roletype =2)
       then
            delete TR 
            from tregisteredusers TR
              Left Join tteacher T on T.Id = TR.SourceId 
            where T.UserId  = P_UserId and T.InstituteId = P_InstituteId;
       end if;
    
        
	select P_DateTime;
 
END$$

DELIMITER ;

-- --------------------------------------------------------

--
-- Table structure for table `tadmin`
--

CREATE TABLE IF NOT EXISTS `tadmin` (
  `Id` int(11) NOT NULL,
  `Code` varchar(10) NOT NULL,
  `UserId` int(11) NOT NULL,
  `FirstName` varchar(100) DEFAULT NULL,
  `LastName` varchar(100) DEFAULT NULL,
  `EmailId` varchar(100) DEFAULT NULL,
  `InstituteId` int(11) NOT NULL,
  `IsSuperAdmin` bit(1) DEFAULT NULL,
  `CreatedDateTime` datetime NOT NULL,
  `UpdatedDateTime` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `talertlog`
--

CREATE TABLE IF NOT EXISTS `talertlog` (
  `Id` int(11) NOT NULL,
  `ContactId` varchar(200) NOT NULL,
  `AlertMessage` varchar(1000) NOT NULL,
  `AlertSubject` varchar(255) NOT NULL,
  `EventId` int(11) NOT NULL,
  `AlertModeId` tinyint(4) DEFAULT NULL,
  `InstituteId` int(11) DEFAULT NULL,
  `ReceiverRoleTypeId` smallint(6) DEFAULT NULL,
  `ReceiverSourceId` int(11) DEFAULT NULL COMMENT 'StudentId or TecaherId',
  `CreatedUserId` int(11) NOT NULL,
  `CreatedDateTime` datetime NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `talertlog`
--

INSERT INTO `talertlog` (`Id`, `ContactId`, `AlertMessage`, `AlertSubject`, `EventId`, `AlertModeId`, `InstituteId`, `ReceiverRoleTypeId`, `ReceiverSourceId`, `CreatedUserId`, `CreatedDateTime`) VALUES
(1, '9033117790', '339164 is your One Time Password (OTP) for verification at vClass App. Sapphire Software Solution', 'OTP', 29, 2, NULL, 2, 0, 306, '2023-06-02 19:50:37'),
(2, '8320255912', '508780 is your One Time Password (OTP) for verification at vClass App. Sapphire Software Solution', 'OTP', 29, 2, NULL, 1, 0, 460, '2023-06-02 19:51:04'),
(3, '9033117790', '449790 is your One Time Password (OTP) for verification at vClass App. Sapphire Software Solution', 'OTP', 29, 2, NULL, 2, 0, 306, '2023-06-02 19:51:42'),
(4, '9033117790', '863496 is your One Time Password (OTP) for verification at vClass App. Sapphire Software Solution', 'OTP', 29, 2, NULL, 2, 0, 306, '2023-06-02 19:54:40'),
(5, '9033117790', '703771 is your One Time Password (OTP) for verification at vClass App. Sapphire Software Solution', 'OTP', 29, 2, NULL, 2, 0, 306, '2023-06-02 20:30:29'),
(6, '9033117790', '984755 is your One Time Password (OTP) for verification at vClass App. Sapphire Software Solution', 'OTP', 29, 2, NULL, 2, 0, 306, '2023-06-05 10:53:49'),
(7, '9033117790', '645007 is your One Time Password (OTP) for verification at vClass App. Sapphire Software Solution', 'OTP', 29, 2, NULL, 2, 0, 306, '2023-06-05 11:05:33'),
(8, '9033117790', '568647 is your One Time Password (OTP) for verification at vClass App. Sapphire Software Solution', 'OTP', 29, 2, NULL, 2, 0, 306, '2023-06-05 11:15:08'),
(9, 'd_V2eRYKQdGmW9og55CKEz:APA91bHcocuNfsL3Mc7mekROAn5vS0YTCSPbUla4NBX65lGRnRtXeCRUeDV5XZtk4QHi76vfjn2r6o0l6Tc2C_YAOop0NUjflZwCrIqdzrigAm1lSJYPpHYVUkSzQ-4XIbGEW2_YBQLJ', 'Pratik1 Add Time Table For Pratik112.', 'Timetable', 39, 3, 134, 1, 418, 454, '2023-06-05 13:25:10'),
(10, 'dPYhWWkMQuyIboq4Szxhkv:APA91bHF_nnmPXVnH3og6262u5DLf7iE7IZBozXILjwGiBr5JQhbclTtXhl-IEP8g_AruSL3u4PwTWBdLj3v3Ch6sfd9soi0nNotHK0BpbpUyoZzI0nRkj-cNvl7WMq4Oa_WFPwzc4EB', 'Pratik1 Add Time Table For Pratik112.', 'Timetable', 39, 3, 134, 1, 420, 454, '2023-06-05 13:25:10'),
(11, 'dPYhWWkMQuyIboq4Szxhkv:APA91bHF_nnmPXVnH3og6262u5DLf7iE7IZBozXILjwGiBr5JQhbclTtXhl-IEP8g_AruSL3u4PwTWBdLj3v3Ch6sfd9soi0nNotHK0BpbpUyoZzI0nRkj-cNvl7WMq4Oa_WFPwzc4EB', 'Pratik1 Update Time Table For Pratik112.', 'Timetable Update', 43, 3, 134, 1, 420, 454, '2023-06-05 14:33:10'),
(12, 'dPYhWWkMQuyIboq4Szxhkv:APA91bHF_nnmPXVnH3og6262u5DLf7iE7IZBozXILjwGiBr5JQhbclTtXhl-IEP8g_AruSL3u4PwTWBdLj3v3Ch6sfd9soi0nNotHK0BpbpUyoZzI0nRkj-cNvl7WMq4Oa_WFPwzc4EB', 'Pratik1 Update Time Table For Pratik112.', 'Timetable Update', 43, 3, 134, 1, 420, 454, '2023-06-05 15:17:46'),
(13, '9913471325', '971575 is your One Time Password (OTP) for verification at vClass App. Sapphire Software Solution', 'OTP', 29, 2, NULL, 1, 0, 461, '2023-06-05 15:55:29'),
(14, '9979742365', '466213 is your One Time Password (OTP) for verification at vClass App. Sapphire Software Solution', 'OTP', 29, 2, NULL, 1, 0, 462, '2023-06-05 16:01:44'),
(15, 'dPYhWWkMQuyIboq4Szxhkv:APA91bHF_nnmPXVnH3og6262u5DLf7iE7IZBozXILjwGiBr5JQhbclTtXhl-IEP8g_AruSL3u4PwTWBdLj3v3Ch6sfd9soi0nNotHK0BpbpUyoZzI0nRkj-cNvl7WMq4Oa_WFPwzc4EB', 'Pratik1 Update Time Table For Pratik112.', 'Timetable Update', 43, 3, 134, 1, 420, 454, '2023-06-05 16:57:16'),
(16, 'dPYhWWkMQuyIboq4Szxhkv:APA91bHF_nnmPXVnH3og6262u5DLf7iE7IZBozXILjwGiBr5JQhbclTtXhl-IEP8g_AruSL3u4PwTWBdLj3v3Ch6sfd9soi0nNotHK0BpbpUyoZzI0nRkj-cNvl7WMq4Oa_WFPwzc4EB', 'Pratik1 Update Time Table For Pratik112.', 'Timetable Update', 43, 3, 134, 1, 420, 454, '2023-06-05 19:12:20'),
(17, '9723935442', '803619 is your One Time Password (OTP) for verification at vClass App. Sapphire Software Solution', 'OTP', 29, 2, NULL, 2, 0, 304, '2023-06-06 11:00:55'),
(18, '9723935442', '964474 is your One Time Password (OTP) for verification at vClass App. Sapphire Software Solution', 'OTP', 29, 2, NULL, 2, 0, 304, '2023-06-06 11:01:46'),
(19, '8780939013', '157286 is your One Time Password (OTP) for verification at vClass App. Sapphire Software Solution', 'OTP', 29, 2, NULL, 2, 0, 454, '2023-06-06 11:02:42'),
(20, 'dPYhWWkMQuyIboq4Szxhkv:APA91bHF_nnmPXVnH3og6262u5DLf7iE7IZBozXILjwGiBr5JQhbclTtXhl-IEP8g_AruSL3u4PwTWBdLj3v3Ch6sfd9soi0nNotHK0BpbpUyoZzI0nRkj-cNvl7WMq4Oa_WFPwzc4EB', 'Pratik1 Add Time Table For Pratik112.', 'Timetable', 39, 3, 134, 1, 420, 454, '2023-06-06 11:05:42');

-- --------------------------------------------------------

--
