Untitled
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, ' ', ' '), '<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()
EndLeave a Comment