Vider une boîte de messages

Version actuelle en date du 18 avril 2019 à 08:03

Statistique par boîte à 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

SQL Server :

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'

Oracle :

create or replace procedure DBMASTER.deleteMessages (pName IN varchar2, pDate IN DATE)
IS

BEGIN
insert into DBMASTER.sysGlbTemp
select '000000000000000000020000811E0003',t0.oid from DBMASTER.TDBFMESSAGE t0
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 = pName) and (t0.DateCreate < pDate));

insert into DBMASTER.sysGlbTemp
select '000000000000000000020000811E0003',t0.oid from DBMASTER.TDBFMESSAGE t0
join sysOTPs t1 on oidOTP=t0.idOTP
where (t1.oClass = 'TdbfMessage') and ((t0.ReceivedFrom = pName) and (t0.DateCreate < pDate))
and not t0.oid in  (select oid from DBMASTER.sysGlbTemp where idOpe='000000000000000000020000811E0003');

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

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

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

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

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

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

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

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

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

COMMIT;

END;

Exemple :

  execute DBMASTER.deleteMessages('admin', DATE '2019-01-01')