eyE51gQJ

1
2DROP PROCEDURE IF EXISTS DocumentCleanupJob;;
3CREATE PROCEDURE DocumentCleanupJob (ticket_expiration_period INT, ticket_cool_period INT, document_qa_period INT, document_cool_ticket_period INT)
4BEGIN
5SET SESSION tx_isolation='READ-COMMITTED';
6DROP TABLE IF EXISTS DocumentCleanup;
7CREATE TABLE DocumentCleanup (Chng_ID int(10), T_Number int(10),
8  D_Name varchar(66) ,
9  T_Created_TS timestamp null DEFAULT CURRENT_TIMESTAMP,
10  INDEX (`D_Name`)) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_irish_accent_ci;
11INSERT INTO DocumentCleanup
12SELECT MAX(THX.Chng_ID) as Chng_ID, THX.T_Number, THX.D_Name,
13THX.T_Created_TS FROM TicketHist THX JOIN
14(SELECT MAX(THP.Chng_ID) AS max_id, THP.T_Number FROM TicketHist THP GROUP BY THP.T_Number) THY
15ON THY.T_Number = THX.T_Number AND THY.max_id = THX.Chng_ID
16LEFT JOIN
17(SELECT DomainHist.D_Name, DomainHist.D_Holder, MAX(SMBRH.Chng_TS) max_Chng_TS FROM SecondaryMarketBuyRequestHist SMBRH
18     LEFT JOIN DomainHist on Domain_Chng_ID = DomainHist.Chng_ID
19     WHERE SMBRH.Status = 'Sold'
20     GROUP BY DomainHist.D_Name, DomainHist.D_Holder) SMBRHD ON THX.D_Name = SMBRHD.D_Name AND THX.D_Holder = SMBRHD.D_Holder
21     JOIN Domain D on D.D_Name = THX.D_Name
22WHERE (  (THX.Overall_Status IN ('Cancelled','Expired') AND datediff(NOW(),THX.O_Status_TS) > document_cool_ticket_period)
23      OR (THX.Overall_Status = 'Passed'
24      AND datediff(NOW(),IF(max_Chng_TS IS NOT NULL AND SMBRHD.max_Chng_TS > THX.O_Status_TS ,SMBRHD.max_Chng_TS, THX.O_Status_TS) ) > document_qa_period)
25        )
26        AND THX.T_Type IN ('Registration', 'Modification')
27GROUP BY THX.T_Number, THX.T_Created_TS;
28-- additional insert orphans
29INSERT INTO DocumentCleanup
30SELECT 0, 0, DOMAIN_NAME, DomainHist.D_Reg_TS FROM INCOMING_DOC_DOMAINS, DomainHist
31    LEFT JOIN TicketHist ON (TicketHist.D_Name = DomainHist.D_Name)
32WHERE TicketHist.D_Name is null AND datediff(CURDATE(),DomainHist.D_Reg_TS)> document_qa_period AND
33  INCOMING_DOC_DOMAINS.DOMAIN_NAME = DomainHist.D_Name;
34INSERT INTO DocumentCleanup
35SELECT 0,0, DOMAIN_NAME, now()  FROM INCOMING_DOC_DOMAINS WHERE INCOMING_DOC_DOMAINS.DOMAIN_NAME NOT IN (
36 SELECT DISTINCT A.D_Name FROM (
37  SELECT DISTINCT TicketHist.D_Name FROM TicketHist
38     UNION ALL
39     SELECT DISTINCT DomainHist.D_Name FROM DomainHist) AS A
40);
41-- pre delete
42DELETE DocumentCleanup FROM  DocumentCleanup LEFT JOIN INCOMING_DOC_DOMAINS ON DocumentCleanup.D_Name = INCOMING_DOC_DOMAINS.DOMAIN_NAME WHERE INCOMING_DOC_DOMAINS.DOMAIN_NAME IS NULL;
43-- real delete
44DELETE INCOMING_DOC_DOMAINS  FROM DocumentCleanup, INCOMING_DOC_DOMAINS
45    WHERE DocumentCleanup.D_Name = INCOMING_DOC_DOMAINS.DOMAIN_NAME ;
46-- flush outdated DOCS
47DELETE IDD FROM INCOMING_DOC_DOMAINS IDD
48JOIN INCOMING_DOC ID ON IDD.DOC_ID = ID.DOC_ID
49WHERE datediff(NOW(),Create_TS) > (ticket_expiration_period + document_qa_period);
50-- remove docs for gdpr2
51DELETE IDD FROM INCOMING_DOC_DOMAINS IDD
52JOIN INCOMING_DOC ID ON IDD.DOC_ID = ID.DOC_ID
53JOIN SecondaryMarketBuyRequestHist SMBRH ON ID.BuyRequestId = SMBRH.id
54WHERE (SMBRH.Status IN ('Cancelled','Expired') AND datediff(NOW(),SMBRH.Chng_TS) > document_cool_ticket_period)
55    OR (SMBRH.Status IN ('Sold','Passed') AND datediff(NOW(),SMBRH.Chng_TS) > document_qa_period);
56-- log
57SELECT concat( T_Number,' - ',D_Name) AS Ticket_Domain from DocumentCleanup;
58SET SESSION tx_isolation='REPEATABLE-READ';
59END;;