--
-- $Id$
--
-- Het anonimiseerscript voor de bezoekers en afspraken wordt aangepast. Dat wordt wat eenduidiger en er wordt wat meer anoniem gemaakt dan voorheen.
-- Dat anonimiseerscript is een geschedulede taak, die enkel kijkt naar de periode van afgelopen jaar (tot 31 dgn geleden). 
-- 
-- Met dit once script wordt hetzelfde gedaan als het anonimiseerscript ook doet, maar dan voor de periode langer geleden (de hele oude historie dus).
--
-- Voor dbuser invullen: - indien script voor 1 klant is: 'AADS' (de klantcode, zoals vermeld in fac_version_cust)
--                       - script is voor meerdere klanten: 'AAXX' (de groepcode, zoals vermeld in fac_version_group)
--                       - script is voor meerdere klanten met naam volgens een bepaald patroon: '^AA|^ASMS|^GULU|^NMMS|^RABO|^ZKHM'
--
DEFINE thisfile = 'UWVA#59571.sql'
DEFINE dbuser   = 'UWVA'

SET ECHO ON
SET DEFINE ON
COLUMN fcltlogfile NEW_VALUE fcltlogfile NOPRINT;
COLUMN fcltcusterr NEW_VALUE fcltcusterr NOPRINT;
WHENEVER SQLERROR CONTINUE;
SELECT adm.getscriptspoolfile('&thisfile') AS fcltlogfile FROM DUAL;
SPOOL &fcltlogfile
WHENEVER SQLERROR EXIT;
SELECT adm.checkscriptcust('&dbuser') AS fcltcusterr FROM DUAL;
WHENEVER SQLERROR CONTINUE;
PROMPT &fcltcusterr
SET DEFINE OFF

------ payload begin ------

DECLARE
   CURSOR c_afspraak
   IS
      SELECT DISTINCT (a.bez_afspraak_key)
        FROM bez_afspraak a, bez_bezoekers b
       WHERE a.bez_afspraak_key = b.bez_afspraak_key
             AND a.bez_afspraak_datum < SYSDATE - 31;
BEGIN
   -- Eerst de tracking van de bezoekers verwijderen
   DELETE FROM fac_tracking
         WHERE fac_tracking_key IN
                     (SELECT t.fac_tracking_key
                        FROM fac_srtnotificatie sn,
                             fac_tracking t,
                             bez_bezoekers b,
                             bez_afspraak a
                       WHERE t.fac_srtnotificatie_key =
                                sn.fac_srtnotificatie_key
                             AND sn.fac_srtnotificatie_xmlnode =
                                   'bezoeker'
                             AND t.fac_tracking_refkey = b.bez_bezoekers_key
                             AND b.bez_afspraak_key = a.bez_afspraak_key
                             AND a.bez_afspraak_datum <
                                   SYSDATE - 31);

   COMMIT;

   -- De tracking van de afspraken moet in een paar delen om een ora-30036 te voorkomen.
   DELETE FROM fac_tracking
         WHERE fac_tracking_key IN
                     (SELECT t.fac_tracking_key
                        FROM fac_srtnotificatie sn,
                             fac_tracking t,
                             bez_afspraak a
                       WHERE t.fac_srtnotificatie_key =
                                sn.fac_srtnotificatie_key
                             AND sn.fac_srtnotificatie_xmlnode =
                                   'afspraak'
                             AND t.fac_tracking_refkey = a.bez_afspraak_key
                             AND a.bez_afspraak_datum <
                                   SYSDATE - 2000);

   COMMIT;

   DELETE FROM fac_tracking
         WHERE fac_tracking_key IN
                     (SELECT t.fac_tracking_key
                        FROM fac_srtnotificatie sn,
                             fac_tracking t,
                             bez_afspraak a
                       WHERE t.fac_srtnotificatie_key =
                                sn.fac_srtnotificatie_key
                             AND sn.fac_srtnotificatie_xmlnode =
                                   'afspraak'
                             AND t.fac_tracking_refkey = a.bez_afspraak_key
                             AND a.bez_afspraak_datum <
                                   SYSDATE - 1000);

   COMMIT;

   DELETE FROM fac_tracking
         WHERE fac_tracking_key IN
                     (SELECT t.fac_tracking_key
                        FROM fac_srtnotificatie sn,
                             fac_tracking t,
                             bez_afspraak a
                       WHERE t.fac_srtnotificatie_key =
                                sn.fac_srtnotificatie_key
                             AND sn.fac_srtnotificatie_xmlnode =
                                   'afspraak'
                             AND t.fac_tracking_refkey = a.bez_afspraak_key
                             AND a.bez_afspraak_datum <
                                   SYSDATE - 31);

   COMMIT;


   -- Anonimiseer gegevens per bezoeker van afspraken >31 dagen oud (bij bezoeker of reservering)
   -- Met UWVA#59571 moeten bepaalde bezoekersregistraties (KZ) naar 'Anoniem', anderen naar 'Bezoeker'
   FOR rc IN c_afspraak
   LOOP
      BEGIN
	  
	     DELETE FROM bez_kenmerkwaarde kw
         WHERE EXISTS
                  (SELECT 1
                     FROM bez_bezoekers
                    WHERE bez_bezoekers_key = kw.bez_bezoekers_key
                          AND bez_afspraak_key = rc.bez_afspraak_key);
						  
         UPDATE bez_bezoekers
            SET bez_afspraak_naam = 'Anoniem', bez_afspraak_bedrijf = NULL
          WHERE bez_afspraak_key = rc.bez_afspraak_key
                AND bez_afspraak_key IN
                         (SELECT a2.bez_afspraak_key
                            FROM fac_usrdata ud,
                                 bez_afspraak a2,
                                 res_rsv_ruimte r
                           WHERE r.res_activiteit_key =
                                    fac.safe_to_number (ud.fac_usrdata_code)
                                 AND a2.res_rsv_ruimte_key =
                                       r.res_rsv_ruimte_key
                                 AND ud.fac_usrdata_verwijder IS NULL
                                 AND ud.fac_usrtab_key = 1663); -- Eigen tabel Klantzone

         UPDATE bez_bezoekers
            SET bez_afspraak_naam = 'Bezoeker', bez_afspraak_bedrijf = NULL
          WHERE bez_afspraak_key = rc.bez_afspraak_key
                AND bez_afspraak_key NOT IN
                         (SELECT a2.bez_afspraak_key
                            FROM fac_usrdata ud,
                                 bez_afspraak a2,
                                 res_rsv_ruimte r
                           WHERE r.res_activiteit_key =
                                    fac.safe_to_number (ud.fac_usrdata_code)
                                 AND a2.res_rsv_ruimte_key =
                                       r.res_rsv_ruimte_key
                                 AND ud.fac_usrdata_verwijder IS NULL
                                 AND ud.fac_usrtab_key = 1663); -- Eigen tabel Klantzone

         -- Verwijder opmerking van afspraken >31 dagen oud (bij bezoeker of reservering)

         UPDATE bez_afspraak
            SET bez_afspraak_opmerking = NULL
          WHERE bez_afspraak_key = rc.bez_afspraak_key;

         -- Met UWVA#59571 moeten de reserveringsopmerkingen ook leeg worden gemaakt voor de kz-reserveringen.

         UPDATE res_rsv_ruimte
            SET res_rsv_ruimte_opmerking = NULL
          WHERE res_status_bo_key <> 6 --Reserveringen met status Verwerkt mogen niet aangepast worden. Het betreft maar 2 reserveringen met die status
          	AND res_rsv_ruimte_key IN
                      (SELECT r.res_rsv_ruimte_key
                         FROM fac_usrdata ud,
                              bez_afspraak a2,
                              res_rsv_ruimte r
                        WHERE r.res_activiteit_key =
                                 fac.safe_to_number (ud.fac_usrdata_code)
                              AND a2.res_rsv_ruimte_key =
                                    r.res_rsv_ruimte_key
                              AND a2.bez_afspraak_key = rc.bez_afspraak_key
                              AND ud.fac_usrdata_verwijder IS NULL
                              AND ud.fac_usrtab_key = 1663);

         ----In het anoniseerscript word thier de tracking verwijderd, maar dat is in deze once al eerder gedaan.
         --   DELETE FROM fac_tracking
         --         WHERE fac_tracking_key IN
         --                     (SELECT t.fac_tracking_key
         --                        FROM fac_srtnotificatie sn,
         --                             fac_tracking t,
         --                             bez_bezoekers b,
         --                             bez_afspraak a
         --                       WHERE t.fac_srtnotificatie_key =
         --                                sn.fac_srtnotificatie_key
         --                             AND UPPER (sn.fac_srtnotificatie_xmlnode) =
         --                                   'BEZOEKER'
         --                             AND t.fac_tracking_refkey = b.bez_bezoekers_key
         --                             AND b.bez_afspraak_key = a.bez_afspraak_key
         --                             AND t.fac_tracking_refkey = rc.bez_afspraak_key);
         --
         --   DELETE FROM fac_tracking
         --         WHERE fac_tracking_key IN
         --                     (SELECT t.fac_tracking_key
         --                        FROM fac_srtnotificatie sn,
         --                             fac_tracking t,
         --                             bez_afspraak a
         --                       WHERE t.fac_srtnotificatie_key =
         --                                sn.fac_srtnotificatie_key
         --                             AND UPPER (sn.fac_srtnotificatie_xmlnode) =
         --                                   'AFSPRAAK'
         --                             AND t.fac_tracking_refkey = a.bez_afspraak_key
         --                             AND t.fac_tracking_refkey = rc.bez_afspraak_key);


         COMMIT;
      END;
   END LOOP;

   COMMIT;
END;
/

------ payload end   ------

SET DEFINE OFF
BEGIN adm.systrackscriptId ('$Id$', 1); END;
/

COMMIT;
SET ECHO OFF
SPOOL OFF
SET DEFINE ON
PROMPT Logfile of this upgrade is: &fcltlogfile