Vider une boîte de messages

Version du 18 avril 2019 à 08:43

Statistique par boite à message : SQL Server:

select t2.Name, count(*) from dbo.TDBFMESSAGE t0 WITH (NOLOCK)
join TDBFMESSAGEBOXASSMESSAGE t1 on t1.oiddbfMessage = t0.oid
join TDBFMESSAGEBOX t2 on t2.oid= t1.oiddbfMessageBox
join sysOTPs t3 on oidOTP=t0.idOTP
where (t3.oClass = 'TdbfMessage')
group by t2.Name order by count(*) desc

Oracle:

select t2.Name, count(*) from DBMASTER.TDBFMESSAGE t0 
join DBMASTER.TDBFMESSAGEBOXASSMESSAGE t1 on t1.oiddbfMessage = t0.oid
join DBMASTER.TDBFMESSAGEBOX t2 on t2.oid= t1.oiddbfMessageBox
join DBMASTER.sysOTPs t3 on oidOTP=t0.idOTP
where (t3.oClass = 'TdbfMessage')
group by t2.Name order by count(*) desc

Procédure stockée pour supprimer les messages d'une boite:

IF EXISTS(SELECT * FROM sys.objects WHERE name='deleteMessages' AND type='P')
drop procedure dbo.deleteMessages
GO

create procedure dbo.deleteMessages
@Name varchar(80),
@Date datetime
As

BEGIN TRANSACTION
insert into dbo.sysGlbTemp
select '000000000000000000020000811E0003',t0.oid from dbo.TDBFMESSAGE t0 WITH (NOLOCK)
join TDBFMESSAGEBOXASSMESSAGE t1 on t1.oiddbfMessage = t0.oid
join TDBFMESSAGEBOX t2 on t2.oid= t1.oiddbfMessageBox
join sysOTPs t3 on oidOTP=t0.idOTP
where (t3.oClass = 'TdbfMessage') and ((t2.Name = @Name) and (t0.DateCreate < @Date))

insert into dbo.sysGlbTemp
select '000000000000000000020000811E0003',t0.oid from dbo.TDBFMESSAGE t0 WITH (NOLOCK)
join sysOTPs t1 on oidOTP=t0.idOTP
where (t1.oClass = 'TdbfMessage') and ((t0.ReceivedFrom = @Name) and (t0.DateCreate < @Date))
and not t0.oid in  (select oid from dbo.sysGlbTemp where idOpe='000000000000000000020000811E0003')

insert into dbo.sysGlbTemp
select '000000000000000000870000811E0004',oid from dbo.TDMFDOCUMENT where oiddbfMessage in (select oid from dbo.sysGlbTemp where idOpe='000000000000000000020000811E0003')

insert into dbo.sysGlbTemp
select '000000000000000000870000811E0006',oidObjectBinary from dbo.TDMFDOCUMENT 
where oid in (select oid from dbo.sysGlbTemp where idOpe='000000000000000000020000811E0004')

delete from dbo.TDMFDOCUMENT
where oid in (select oid from dbo.sysGlbTemp where idOpe='000000000000000000870000811E0004')

delete from dbo.TDMFOBJECTBINARY
where oid in (select oid from dbo.sysGlbTemp where idOpe='000000000000000000870000811E0006')

update dbo.TDBFMESSAGE set oidAnswerTo=NULL
where oidAnswerTo in (select oid from dbo.sysGlbTemp where idOpe='000000000000000000020000811E0003')

insert into dbo.sysGlbTemp
select '000000000000000000040000811E0005',oid from dbo.TDBFMESSAGEBOXASSMESSAGE where oiddbfMessage in (select oid from dbo.sysGlbTemp where idOpe='000000000000000000020000811E0003')

delete from dbo.TDBFMESSAGEBOXASSMESSAGE
where oid in (select oid from dbo.sysGlbTemp where idOpe='000000000000000000040000811E0005')

delete from dbo.TDBFMESSAGE
where oid in (select oid from dbo.sysGlbTemp where idOpe='000000000000000000020000811E0003')

delete from dbo.sysGlbTemp where idOpe='000000000000000000870000811E0004'
delete from dbo.sysGlbTemp where idOpe='000000000000000000040000811E0005'
delete from dbo.sysGlbTemp where idOpe='000000000000000000020000811E0003'
delete from dbo.sysGlbTemp where idOpe='000000000000000000020000811E0006'

COMMIT TRANSACTION
GO

Exemple :

  exec dbo.deleteMessages '660_SF_MONO', '01/01/2014'