Untitled

 avatar
unknown
plain_text
a month ago
17 kB
3
Indexable
ALTER Procedure [dbo].[Proc_Announcement]
	@P_Condition		Varchar(50),
	@P_SNo				varchar(max)	= NULL,
	@P_pagesize			int				= NULL,
	@P_pagenumber		int				= NULL,
	@P_ColSearchContent Varchar(250)	= NULL,
	@P_ColSortActive	Varchar(250)	= NULL,
	@P_ColSortDirection Varchar(250)	= NULL,
	@P_LongStr			varchar(Max)	= NULL,
	@P_LongSt			varchar(Max)	= NULL,
	@P_CreatedBy        Varchar(50)		= NULL,
	@P_ProcMessage		Varchar(Max)	= NULL OutPut,
	@P_ProcReturn		Integer 		= NULL OutPut

As
Begin Transaction
Declare @RetryCounter INT
Set @RetryCounter = 1
Retry:
Begin Transaction
Begin Try
	Set NoCount On
	Set @P_ProcMessage='Operation failed'
	Set @P_ProcReturn=0

	DECLARE 
	
	@SNo as int=0,					
	@UserSNo as int=0,				
	@Offset as int= 0,				
	@SortActive as varchar(max)='',
	@SortDirection as varchar(100)='',
    @FirebaseFlag as int=0,
	@Type varchar(50),
	@BaseUrl as varchar(250)='',
	@SchoolName as varchar(100)=''
	
	--Exec Proc_Announcement @P_Condition='GetType'
	IF @P_Condition='GetType'
	BEGIN
		If Object_ID('TempDB.Dbo.#SType','U') is not null
		Begin
			Drop Table #SType
		End
		Create Table #SType
		(
			SNo varchar(20),
			Type varchar(20)
		)
		Insert into #SType(SNo,Type)
		values('Faculty','Faculty'),('Staff','Staff'),('ClassTeacher','ClassTeacher')
		select * from #SType
	END

	--Exec Proc_Announcement @P_Condition='CreateAnnouncement', @P_LongStr='Teacher~Subject!~Description^', @P_CreatedBy='Z#'
	if(@P_Condition='CreateAnnouncement')
	Begin
	        Select @Type=col1 from Dbo.Fn_StringToTable(@P_LongStr,'~','^')
			
			

			if @Type='Staff'
			begin
			INSERT INTO Tbl_Announcement(Type,Subject,Description,CreatedBy,CreatedOn,ModifiedBy,ModifiedOn)
			Select col1,col2,col3,Dbo.Fn_Decrypt(@P_CreatedBy),GetDate(),Dbo.Fn_Decrypt(@P_CreatedBy),GetDate() from Dbo.Fn_StringToTable(@P_LongStr,'~','^')
			set @SNo=Scope_Identity()
			INSERT INTO Tbl_AnnouncementDetail(AnnouncementSNo,UserSNo,Email,CreatedBy,CreatedOn,ModifiedBy,ModifiedOn)
			Select @SNo,U.SNo,U.EmailId,Dbo.Fn_Decrypt(@P_CreatedBy),getdate(),Dbo.Fn_Decrypt(@P_CreatedBy),getdate()
			from Tbl_User U inner join Tbl_Staff S on U.StaffSNo=S.SNo and U.Status=1 and U.IsActive=1 and S.Status=1 and S.IsActive=1 
			Select Dbo.Fn_Encrypt(A.SNo) as 'SNoEnc',A.SNo,A.Type,A.Subject,A.Description from Tbl_Announcement A where A.SNo=@SNo
            Set @P_ProcMessage=Cast(@@RowCount as varchar)+'-Record(s) saved.'
			set @P_ProcReturn=1
			end
			else if @Type='Faculty'
			begin
			INSERT INTO Tbl_Announcement(Type,Subject,Description,CreatedBy,CreatedOn,ModifiedBy,ModifiedOn)
			Select col1,col2,col3,Dbo.Fn_Decrypt(@P_CreatedBy),GetDate(),Dbo.Fn_Decrypt(@P_CreatedBy),GetDate() from Dbo.Fn_StringToTable(@P_LongStr,'~','^')
			set @SNo=Scope_Identity()
			INSERT INTO Tbl_AnnouncementDetail(AnnouncementSNo,UserSNo,Email,CreatedBy,CreatedOn,ModifiedBy,ModifiedOn)
			Select @SNo,U.SNo,U.EmailId,Dbo.Fn_Decrypt(@P_CreatedBy),getdate(),Dbo.Fn_Decrypt(@P_CreatedBy),getdate()
			from Tbl_User U inner join Tbl_Teacher T on U.TeacherSNo=T.SNo and U.Status=1 and U.IsActive=1 and T.Status=1 and T.IsActive=1 
			Select Dbo.Fn_Encrypt(A.SNo) as 'SNoEnc',A.SNo,A.Type,A.Subject,A.Description from Tbl_Announcement A where A.SNo=@SNo
			Set @P_ProcMessage=Cast(@@RowCount as varchar)+'-Record(s) saved.'
			set @P_ProcReturn=1
			end
			else if @Type='ClassTeacher'
			begin
			INSERT INTO Tbl_Announcement(Type,Subject,Description,CreatedBy,CreatedOn,ModifiedBy,ModifiedOn)
			Select col1,col2,col3,Dbo.Fn_Decrypt(@P_CreatedBy),GetDate(),Dbo.Fn_Decrypt(@P_CreatedBy),GetDate() from Dbo.Fn_StringToTable(@P_LongStr,'~','^')
			set @SNo=Scope_Identity()
			    INSERT INTO Tbl_AnnouncementDetail(AnnouncementSNo,UserSNo,Email,CreatedBy,CreatedOn,ModifiedBy,ModifiedOn)
			Select distinct @SNo,U.SNo,T.EmailId,Dbo.Fn_Decrypt(@P_CreatedBy),getdate(),Dbo.Fn_Decrypt(@P_CreatedBy),getdate()
			from Tbl_Teacher T inner join Tbl_Division D on D.TeacherSNo=T.SNo and D.Status=1 and D.IsActive=1 and T.Status=1 and T.IsActive=1
			inner join Tbl_User U on T.SNo=U.TeacherSNo and U.Status=1 and U.IsActive=1 
			Select Dbo.Fn_Encrypt(A.SNo) as 'SNoEnc',A.SNo,A.Type,A.Subject,A.Description from Tbl_Announcement A where A.SNo=@SNo
			Set @P_ProcMessage=Cast(@@RowCount as varchar)+'-Record(s) saved.'
			set @P_ProcReturn=1
			end

			
   End
		

	--Exec Proc_Announcement @P_Condition='UpdateAnnouncementAttachment',@P_LongStr='/Uploads/SC001/Faculty/44/D00_44_20240830123201.jpg',@P_SNo='T#T#'
	if @P_Condition='UpdateAnnouncementAttachment'
	begin
		set @SNo=0
		set @SNo=Dbo.Fn_Decrypt(@P_SNo)
		Update Tbl_Announcement set Attachment=@P_LongStr where SNo=@SNo

		Set @P_ProcMessage=Cast(@@RowCount as varchar)+' record(s) updated.'
		Set @P_ProcReturn=1

		

	End

 
	--Exec Proc_Announcement @P_Condition='DeleteAnnouncement',@P_SNo='AnnouncementSNo'
	if @P_Condition='DeleteAnnouncement'
	Begin			
		set @SNo=0
		set @SNo=Dbo.Fn_Decrypt(@P_SNo)
		if exists(select 1 from Tbl_Announcement where SNo=@SNo and Status=1)
		Begin
			Update Tbl_Announcement set Status=0,ModifiedBy=Dbo.Fn_Decrypt(@P_CreatedBy),ModifiedOn=Getdate() where SNo=@SNo
			Update Tbl_AnnouncementDetail set Status=0,ModifiedBy=Dbo.Fn_Decrypt(@P_CreatedBy),ModifiedOn=Getdate() where AnnouncementSNo=@SNo
			Set @P_ProcReturn=1	
			Set @P_ProcMessage='Announcement Data Deleted'
		End
		Else
		Begin
			Set @P_ProcReturn=0
			Set @P_ProcMessage='No Data found'
		End
	End
	
	--Exec Proc_Announcement @P_Condition='GetAnnouncementByPage',@P_pagenumber=1,@P_pagesize=5,@P_ColSearchContent='JIPSY  K RAPHEL',@P_ColSortActive='',@P_ColSortDirection='',@P_SNo='Z#'
	if(@P_Condition='GetAnnouncementByPage')
	Begin

		Set @Offset=0
        Set @Offset=(@P_pagenumber-1)*@P_pagesize
		
		if (isnull(@P_ColSortActive,'')<>'' and isnull(@P_ColSortDirection,'')<>'')
		Begin
			set @SortActive=''+@P_ColSortActive+' '+@P_ColSortDirection+'
			offset '+cast(@Offset as varchar(10))+' rows fetch first '+cast(@P_pagesize as varchar(10))+' rows only'
		End 
		else
		BEGIN
			set @SortActive='AnnouncementSNo desc offset '+cast(@Offset as varchar(10))
			+' rows fetch first '+cast(@P_pagesize as varchar(10))+' rows only'
		END

		
		
		

		If Object_ID('TempDB.Dbo.#AnnouncementTb','U') is not null
        Begin
            Drop Table #AnnouncementTb
        End
        Create Table #AnnouncementTb
        (
            SNo int IDENTITY(1,1),
            AnnouncementSNo int,
            SNoEnc varchar(200),
            Type varchar(100),
            Subject varchar(100),
           	CreatedBy varchar(20),
			UserSNo int,
			TotalCount int,
			SendCount int,
			Pending int
			)
		insert into #AnnouncementTb(SNoEnc,AnnouncementSNo,Type,Subject,TotalCount,SendCount,Pending)
		Select Dbo.Fn_Encrypt(AD.AnnouncementSNo), AD.AnnouncementSNo,A.Type,A.Subject ,
		count(case when AD.Email!='' then 1 end) as TotalCount,
		count(case when AD.EmailFlag=1 and AD.Email!='' then 1  end) as SendCount,
		count(case when AD.EmailFlag=0 and AD.Email!=''  then 1  end) as Pending
		from Tbl_Announcement A
		inner join Tbl_AnnouncementDetail AD on AD.AnnouncementSNo=A.SNo and AD.Status=1
		
		left join Tbl_User U on U.SNo=AD.UserSNo
		where A.Status=1
		group by A.Type,A.Subject,AD.AnnouncementSNo,A.SNo
		
	
		Exec('
		Select SNoEnc,AnnouncementSNo,Type,Subject ,
		TotalCount,SendCount,Pending,TotalRow=count(*) over()
			from #AnnouncementTb
			where 
			(Type like ''%'+@P_ColSearchContent+'%'' 
			or Subject like ''%'+@P_ColSearchContent+'%''
			
			)'
			+'
			group by Type,Subject,AnnouncementSno,SNoEnc,TotalCount,SendCount,Pending
			order by '+@SortActive+'')
	

		Set @P_ProcMessage=Cast(@@RowCount as varchar)+' record(s) selected.'
		Set @P_ProcReturn=1

 

	End
	--exec Proc_Announcement @P_Condition='AnnouncementDetailswithStatus',@P_SNo='T#L#',@P_pagenumber=1,@P_pagesize=25,@P_ColSearchContent='',@P_ColSortActive='SNo',@P_ColSortDirection='asc'

	if(@P_Condition='AnnouncementDetailswithStatus')
	Begin
		Set @Offset=0
        Set @Offset=(@P_pagenumber-1)*@P_pagesize
		Select @SNo=dbo.Fn_Decrypt(@P_SNo)
		--Select @SNo=@P_SNo
		Set @SNo =cast (@SNo as varchar(100))
	
				exec('
				SELECT * FROM (
				SELECT ROW_NUMBER() OVER(PARTITION BY U.UserName ORDER BY U.UserName) as row_num,
				Dbo.Fn_Encrypt(AD.SNo) as ''SNoEnc'',
				AD.SNo, AD.AnnouncementSNo,A.Type,A.Subject,A.Description,A.Attachment,AD.EmailFlag,
				U.UserName,case when isnull(AD.Email,'''')='''' then '''' else AD.Email end as EmailID,TotalRow=Count(*) over(),AD.FirebaseFlag,isnull(F.Firebase_token,'''')as Firebase_token,
				isnull(F.Device_id,'''')as Device_id

				FROM Tbl_Announcement A 
				INNER JOIN Tbl_AnnouncementDetail AD ON AD.AnnouncementSNo=A.SNo 
				
				inner JOIN Tbl_User U ON U.SNo=AD.UserSNo 
				LEFT JOIN Tbl_Firebasemsg F ON F.UserSNo=U.SNo 
				WHERE AD.AnnouncementSNo='''+@SNo+''' 
				AND (U.UserName LIKE ''%'+@P_ColSearchContent+ '%'')
				) AS Sub
				WHERE row_num = 1
				ORDER BY '+@P_ColSortActive+' '+@P_ColSortDirection+'
				OFFSET '+@Offset+' ROWS FETCH FIRST '+@P_pagesize+' ROWS ONLY'
				)
		Set @P_ProcMessage=Cast(@@RowCount as varchar)+' record(s) selected.'
		Set @P_ProcReturn=1
	End

	--Exec Proc_Announcement  @P_Condition='UpdateEmailStatus',@P_SNo=''
	if(@P_Condition='UpdateEmailStatus')
	Begin
		set @SNo=0
		set @SNo=Dbo.Fn_Decrypt(@P_SNo)
		Update Tbl_AnnouncementDetail set EmailFlag=1 where SNo=@SNo
	End

	--Exec Proc_Announcement  @P_Condition='UpdateReadStatus',@P_SNo=''
	if(@P_Condition='UpdateReadStatus')
	Begin
	   
		set @SNo=0
		set @SNo=Dbo.Fn_Decrypt(@P_SNo)
		Update Tbl_AnnouncementDetail set ReadStatus=1,ReadStatusOn=getdate() where SNo=@SNo
	End

	--Exec Proc_Announcement  @P_Condition='UpdateFirebaseFlag',@P_SNo=''
	--if(@P_Condition='UpdateFirebaseFlag')
	--Begin
	   
	--	set @SNo=0
	--	set @SNo=Dbo.Fn_Decrypt(@P_SNo)
	--	Update Tbl_AnnouncementDetail set FirebaseFlag=1 where SNo=@SNo
	--End
--exec Proc_Announcement @P_Condition=N'Updatefirebasemessageinfo',@P_SNo=N'T#X#M#X#',@P_LongStr=N'idTP1A.220624.014|Ncampus - Circulars:  VII - A  meeting|anniversary 2023.  Regards, MONTFORT SCHOOL ANAKKARA|Z#G#T#P#|1|^',@P_CreatedBy=N'Z#',
	if(@P_Condition='Updatefirebasemessageinfo')
	Begin
	    declare @user varchar(50),@Description as varchar(1000)='',@Subject as varchar(200)
		set @SNo=@P_SNo
		--set @SNo=Dbo.Fn_Decrypt(@P_SNo)
		Set @user=''
		Set @subject=''
		Set @Description=''
		Set @UserSNo=''
       

 	   

		Select @user=(Col1), @Subject=Col2, @Description=Col3, @UserSNo=Dbo.Fn_Decrypt(col4), @FirebaseFlag=col5
		from Dbo.Fn_StringToTable(@P_LongStr,'|','^')
		if(@FirebaseFlag=1)
		begin

        insert into  tbl_info(Entity,ToNo,Subject,Message,Response,IsSent,CreatedBy,CreatedOn,UserSNo,IsErr)
		values('Firebase',@user,@Subject,@Description,'Success','True',Dbo.Fn_Decrypt(@P_CreatedBy),Getdate(),@UserSNo,0)
		Update Tbl_AnnouncementDetail set FirebaseFlag=1 where SNo=@SNo
        end
		else
		begin

		insert into  tbl_info(Entity,ToNo,Subject,Message,Response,IsSent,CreatedBy,CreatedOn,UserSNo,IsErr)
		values('Firebase',@user,@Subject,@Description,'Failed',0,Dbo.Fn_Decrypt(@P_CreatedBy),Getdate(),@UserSNo,1)
		Update Tbl_AnnouncementDetail set FirebaseFlag=0 where SNo=@SNo
		end



	End
----exec Proc_Announcement @P_Condition=N'ShareAnnouncementMail',@P_LongStr=N'Z#D#',@P_CreatedBy=N'Z#'
	if(@P_Condition='ShareAnnouncementMail')
	Begin
		set @SNo=0
		select @SNo=Dbo.Fn_Decrypt(X.Col1) from Dbo.Fn_StringToTable(@P_LongStr,'|','^') X
		set @BaseUrl=(select KeyValue from Tbl_Settings where Key1='API' and Key2='SchoolBaseApiAddress')
		Select top 1 @SchoolName=SchoolName from Tbl_School

 

		Select Dbo.Fn_Encrypt(AD.SNo) as 'SNoEnc',Dbo.Fn_Encrypt(A.SNo) as 'ASNoenc',U.UserName,isnull(AD.Email,'') as 'Email',
		A.Type,A.Subject,
		--(A.Description+'<br/><br/>Regards,<br/> '+@SchoolName)as Description ,
		Replace(Replace(A.Description, '&nbsp;', ' '), '<br/>', '\n') + '\n\nRegards,\n' + @SchoolName as Description,
		case when A.Attachment <>'' and A.Attachment is not NULL Then @BaseUrl+A.Attachment 
		when A.Attachment ='' or A.Attachment is NULL Then '' End as Attachment,TotalRow=Count(*) over() 
		from Tbl_Announcement A
		inner join Tbl_AnnouncementDetail AD on AD.AnnouncementSNo=A.SNo and AD.Status=1 and AD.EmailFlag=0
		
		inner join Tbl_User U on U.SNo=AD.UserSNo
	   	where A.SNo=@SNo and A.Status=1
		
		

 

		

		Select Dbo.Fn_Encrypt(AD.SNo) as 'SNoEnc',Dbo.Fn_Encrypt(A.SNo) as 'ASNoenc',U.UserName,isnull(AD.Email,'') as 'Email',
		A.Type,A.Subject,@SchoolName as SchoolName,
		(A.Description)as Description ,U.SNo as 'UserSNo',
		case when A.Attachment <>'' and A.Attachment is not NULL Then @BaseUrl+A.Attachment 
		when A.Attachment ='' or A.Attachment is NULL Then '' End as Attachment,TotalRow=Count(*) over() ,F.Firebase_token,F.Device_id,U.SNo
		from Tbl_Announcement A 
		inner join Tbl_AnnouncementDetail AD on AD.AnnouncementSNo=A.SNo and AD.Status=1 and AD.EmailFlag=0
		
		inner join Tbl_User U on U.SNo=AD.UserSNo 
		inner join Tbl_Firebasemsg F on F.UserSNo=U.SNo 
	   	where A.SNo=@SNo and A.Status=1
		


		Set @P_ProcMessage='Selected'
		Set @P_ProcReturn=1


 

	End


	--Exec Proc_Announcement @P_Condition='AnnouncementView',@P_LongStr='U#|Staff^',@P_pagenumber=1,@P_pagesize=5
	if @P_Condition='AnnouncementView'
	Begin
		Select @UserSNo=Dbo.Fn_Decrypt(col1)from Dbo.Fn_StringToTable(@P_LongStr,'|','^')
		Set @Offset=0
		Set @Offset=(@P_pagenumber-1)*@P_pagesize
		Exec('Select Dbo.Fn_Encrypt(AD.AnnouncementSNo) as ''SNoEnc'', A.SNo,Dbo.Fn_Encrypt(A.SNo) as ''SNo'',A.Subject,A.Type,A.Description,
		AD.ReadStatus,TotalRow=Count(*) over() 
		from Tbl_AnnouncementDetail AD inner join Tbl_Announcement A on A.SNo=AD.AnnouncementSNo and A.Status=1 
		where AD.UserSNo='+@UserSNo+' and AD.Status=1 order by A.SNo desc offset '+@Offset+' rows fetch first '+@P_pagesize+' rows only')
		Set @P_ProcMessage=Cast(@@RowCount as varchar)+' record(s) Selected.'
		Set @P_ProcReturn=1
	End

 

	----Exec Proc_Announcement @P_Condition='AnnouncementDetailedview',@P_LongStr='D#O#|Z#U#Z#M#P#^'
	if @P_Condition='AnnouncementDetailview'
	begin

		set @BaseUrl=(select KeyValue from Tbl_Settings where Key1='API' and Key2='SchoolBaseApiAddress')
		Select @SNo=Dbo.Fn_Decrypt(Col1),@UserSNo=Dbo.Fn_Decrypt(col2) from Dbo.Fn_StringToTable(@P_LongStr,'|','^')
		Update Tbl_AnnouncementDetail set ReadStatus=1,ReadStatusOn=getdate() where AnnouncementSNo=@SNo and UserSNo=@UserSNo
		Select distinct Dbo.Fn_Encrypt(AD.AnnouncementSNo) as SNoEnc, A.SNo,A.Subject,A.Type,A.Description,
		case when A.Attachment <>'' and A.Attachment is not NULL Then @BaseUrl+A.Attachment 
		when A.Attachment ='' or A.Attachment is NULL Then '' End as Attachment
		
		from Tbl_AnnouncementDetail AD inner join Tbl_Announcement A on A.SNo=AD.AnnouncementSNo and A.Status=1 where AD.UserSNo=@UserSNo and A.SNo=@SNo and A.Status=1

		Set @P_ProcReturn=1	
	End
	--Exec Proc_Announcement @P_Condition='GetUnReadMsgCount',@P_SNo='usersno'
	if @P_Condition='GetUnReadMsgCount'
	Begin
			set @SNo=0
			select @SNo=Dbo.Fn_Decrypt(@P_SNo)
			

		Select (Select  count(*) from Tbl_AnnouncementDetail AD  
		inner join Tbl_Announcement A on A.SNo=AD.AnnouncementSNo and A.Status=1 where AD.UserSNo=@SNo and  AD.ReadStatus=0 and AD.Status=1) AS TotalUnreadMsgs,  count(*)  AS UnreadMsgs,A.Type from Tbl_AnnouncementDetail AD  
		inner join Tbl_Announcement A on A.SNo=AD.AnnouncementSNo and A.Status=1 where AD.UserSNo=@SNo and  AD.ReadStatus=0 and AD.Status=1 group by A.Type
		
	    
	End


Commit Transaction
	Set NoCount Off
End Try
Begin Catch
	RollBack Transaction
	Declare @DoRetry bit;
	Declare @ErrorMessage varchar(Max)
	Set @doRetry = 0;
	Set @ErrorMessage = Error_Message()
	If Error_Message() = 1205 -- Deadlock Error Number
	Begin
		Set @doRetry = 1;
	End
	If @DoRetry = 1
	Begin
		Set @RetryCounter = @RetryCounter + 1
		If (@RetryCounter > 5)				
		Begin
			RaisError(@ErrorMessage, 18, 1) -- Raise Error Message if still deadlock occurred after three retries
		End
		Else
		Begin
			WaitFor Delay '00:00:00.500' -- Wait for 100 ms
			GoTo Retry	-- Go to Label RETRY
		End
	End
	Else
	Begin
		RaisError(@ErrorMessage,18, 1)
	End
End Catch

IF(@@Error<>0)
Begin
	RollBack
	Set @P_ProcReturn=0
	Set @P_ProcMessage=ERROR_MESSAGE()
End
Else
Begin
	Commit
	Return (1) 
End

 

if @P_ProcMessage=''
Begin 
	Set @P_ProcMessage=ERROR_MESSAGE()
End
Leave a Comment