Vider une boîte de messages

De Wiki1000
(Différences entre les versions)
(→Procédure stockée pour supprimer les messages d'une boite)
 
(10 révisions intermédiaires par un utilisateur sont masquées)
Ligne 1 : Ligne 1 :
−
Ce code SQL permet de vider le contenu d'une boîte à message :
+
===Statistique par boîte à message===
  
−
Replacer chfou par le nom de la boite et '2008-01-01 00:00:00' par la date désirée
+
'''SQL Server:'''
 +
<pre>
 +
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
 +
</pre>
  
 +
'''Oracle:'''
 
<pre>
 
<pre>
 +
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
 +
</pre>
 +
 +
===Procédure stockée pour supprimer les messages d'une boite===
 +
'''SQL Server :'''
 +
 +
<pre>
 +
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
 
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
 
insert into dbo.sysGlbTemp
 
select '000000000000000000020000811E0003',t0.oid from dbo.TDBFMESSAGE t0 WITH (NOLOCK)
 
select '000000000000000000020000811E0003',t0.oid from dbo.TDBFMESSAGE t0 WITH (NOLOCK)
Ligne 15 : Ligne 40 :
 
join TDBFMESSAGEBOX t2 on t2.oid= t1.oiddbfMessageBox
 
join TDBFMESSAGEBOX t2 on t2.oid= t1.oiddbfMessageBox
 
join sysOTPs t3 on oidOTP=t0.idOTP
 
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)))
+
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
 
insert into dbo.sysGlbTemp
 
select '000000000000000000870000811E0004',oid from dbo.TDMFDOCUMENT where oiddbfMessage in (select oid from dbo.sysGlbTemp where idOpe='000000000000000000020000811E0003')
 
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
 
delete from dbo.TDMFDOCUMENT
 
where oid in (select oid from dbo.sysGlbTemp where idOpe='000000000000000000870000811E0004')
 
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
 
update dbo.TDBFMESSAGE set oidAnswerTo=NULL
Ligne 36 : Ligne 74 :
  
 
delete from dbo.sysGlbTemp where idOpe='000000000000000000870000811E0004'
 
delete from dbo.sysGlbTemp where idOpe='000000000000000000870000811E0004'
−
 
 
delete from dbo.sysGlbTemp where idOpe='000000000000000000040000811E0005'
 
delete from dbo.sysGlbTemp where idOpe='000000000000000000040000811E0005'
−
 
 
delete from dbo.sysGlbTemp where idOpe='000000000000000000020000811E0003'
 
delete from dbo.sysGlbTemp where idOpe='000000000000000000020000811E0003'
 +
delete from dbo.sysGlbTemp where idOpe='000000000000000000020000811E0006'
  
 
COMMIT TRANSACTION
 
COMMIT TRANSACTION
 +
GO
 +
</pre>
 +
 +
'''Exemple :'''
 +
 +
<pre>
 +
  exec dbo.deleteMessages '660_SF_MONO', '01/01/2014'
 +
</pre>
 +
 +
'''Oracle :'''
 +
<pre>
 +
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;
 +
</pre>
 +
 +
'''Exemple :'''
 +
 +
<pre>
 +
  execute DBMASTER.deleteMessages('admin', DATE '2019-01-01')
 
</pre>
 
</pre>
  
 
[[Category:Boîtes à messages]]
 
[[Category:Boîtes à 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')
Récupérée de « https://frp.sage.fr/wiki1000/index.php?title=Vider_une_bo%C3%AEte_de_messages&oldid=12583 »
Outils personnels