Untitled
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, ' ', ' '), '<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