Vider une boîte de messages

Version du 15 avril 2015 à 07:52

Statistique par boite à message :

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(*) des

Ce code SQL permet de vider le contenu d'une boîte à message :

Replacer chfou par le nom de la boite et '2008-01-01 00:00:00' par la date désirée

BEGIN TRANSACTION
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 = 'chfou') and (t0.DateCreate < Convert(DateTime,'2008-01-01 00:00:00',120)))

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 = 'chfou') and (t0.DateCreate < Convert(DateTime,'2008-01-01 00:00:00',120)))

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

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

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'

COMMIT TRANSACTION