--
-- $Id$
--
-- Script containing customer specific sql statements for the FACILITOR database
-- 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'
-- Ook als het script gedraaid wordt voor de verkeerde cust wordt er een logfile gemaakt.
-- (dit in tegenstelling tot sample_xxxx.sql)

DEFINE thisfile = 'LBRN.SQL'
DEFINE dbuser   = 'LBRN'

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

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

CREATE OR REPLACE PACKAGE LBRN 
AS
    PROCEDURE anonimiseer_bezoeker;

END;
/

CREATE OR REPLACE PACKAGE BODY LBRN
AS
    PROCEDURE anonimiseer_bezoeker
    AS
        anonymize_period      NUMBER := 42;
        bez_generic_visitor   VARCHAR2 (2001)
                                  := lcl.l ('lcl_bez_generic_visitor');
        v_count               NUMBER;

        CURSOR c_afspraak IS
              SELECT a.bez_afspraak_key
                FROM bez_afspraak a,
                     (SELECT datum_van
                        FROM (SELECT TRUNC (MIN (bez_afspraak_datum))    datum_van
                                FROM bez_afspraak a
                               WHERE     bez_afspraak_datum <
                                         (SYSDATE - anonymize_period)
                                     AND NOT EXISTS
                                             (SELECT ft.fac_tracking_key
                                                FROM fac_tracking ft
                                               WHERE     ft.fac_tracking_refkey =
                                                         bez_afspraak_key
                                                     AND ft.fac_srtnotificatie_key =
                                                         (SELECT fac_srtnotificatie_key
                                                            FROM fac_srtnotificatie
                                                           WHERE fac_srtnotificatie_code =
                                                                 'BEZANO'))))
                     van,
                     (SELECT TRUNC (SYSDATE - anonymize_period) --- 6 weken dus 6*7
                                                                    datum_tot
                        FROM DUAL) tot
               WHERE     a.bez_afspraak_datum BETWEEN van.datum_van
                                                  AND tot.datum_tot
                     AND NOT EXISTS
                             (SELECT ft.fac_tracking_key
                                FROM fac_tracking ft
                               WHERE     ft.fac_tracking_refkey =
                                         a.bez_afspraak_key
                                     AND ft.fac_srtnotificatie_key =
                                         (SELECT fac_srtnotificatie_key
                                            FROM fac_srtnotificatie
                                           WHERE fac_srtnotificatie_code =
                                                 'BEZANO'))
            ORDER BY a.bez_afspraak_key;
    BEGIN
        FOR rec IN c_afspraak
        LOOP
            -- Verwijder alle kenmerkwaarden van bezoekers van de afspraak waarvan het ampersand 8 (BITAND) bit van het bez_kenmerk.bez_kenmerk_systeem veld is gezet.
            DELETE FROM bez_kenmerkwaarde bkw
                  WHERE     bkw.bez_bezoekers_key IN
                                (SELECT b.bez_bezoekers_key
                                   FROM bez_bezoekers b
                                  WHERE b.bez_afspraak_key =
                                        rec.bez_afspraak_key)
                        AND bkw.bez_kenmerk_key IN
                                (SELECT bk.bez_kenmerk_key
                                   FROM bez_kenmerk bk
                                  WHERE     bk.bez_kenmerk_key =
                                            bkw.bez_kenmerk_key
                                        AND BITAND (bk.bez_kenmerk_systeem,
                                                    8) =
                                            8);

            -- Anonimiseer alle bezoekers.
            UPDATE bez_bezoekers
               SET bez_afspraak_naam = bez_generic_visitor,
                   bez_afspraak_bedrijf = NULL,
                   bez_afspraak_opmerking = NULL,
                   bez_bezoekers_telefoon = NULL,
                   bez_bezoekers_email = NULL,
                   bez_bezoekers_kenteken = NULL
             WHERE bez_afspraak_key = rec.bez_afspraak_key;

            -- Anonimiseer alle afspraken.
            -- Door de trigger in de update wordt automatische de naam van de host in de gastheer geplakt.
            -- Daardoor wordt de lcl "Ganonimiseerd" in vele gevallen niet blijvend in de gasheer gezet.
            -- De persoon (prs record) moet daarom eerst aangepast worden.
            -- Tijdens het anonimiseren van de persoon wordt daarom de bez_afspraak_gastheer ook geanonimiseerd.
            -- In de gevallen dat er geen host wordt gebruikt en alleen tekst voor de persoon (gastheer) wordt ingevuld wordt het veld wel blijvend gezet.
            UPDATE bez_afspraak
               SET bez_afspraak_gastheer = bez_generic_visitor,
                   bez_afspraak_telefoonnr = NULL,
                   bez_afspraak_opmerking = NULL
             WHERE bez_afspraak_key = rec.bez_afspraak_key;

            -- Alle BEZ tracking anonimiseren m.b.t. de afspraak.
            UPDATE fac_tracking
               SET fac_tracking_oms = NULL
             WHERE     fac_srtnotificatie_key IN
                           (SELECT fac_srtnotificatie_key
                              FROM fac_srtnotificatie
                             WHERE     fac_srtnotificatie_xmlnode =
                                       'afspraak'
                                   AND fac_srtnotificatie_code != 'BEZANO')
                   AND fac_tracking_refkey = rec.bez_afspraak_key
                   AND fac_tracking_oms IS NOT NULL;

            -- Alle BEZ tracking anonimiseren m.b.t. de bezoekers van de afspraak.
            UPDATE fac_tracking
               SET fac_tracking_oms = NULL
             WHERE     fac_srtnotificatie_key IN
                           (SELECT fac_srtnotificatie_key
                              FROM fac_srtnotificatie
                             WHERE fac_srtnotificatie_xmlnode = 'bezoeker')
                   AND fac_tracking_refkey IN
                           (SELECT bb.bez_bezoekers_key
                              FROM bez_bezoekers bb
                             WHERE bb.bez_afspraak_key = rec.bez_afspraak_key)
                   AND fac_tracking_oms IS NOT NULL;

            -- Anonimiseren van de afspraak tracken.
            fac.trackaction ('BEZANO',
                             rec.bez_afspraak_key,
                             NULL,
                             NULL,
                             NULL);

            IF v_count >= 1000
            THEN
                COMMIT;
                v_count := 0;
            ELSE
                v_count := v_count + 1;
            END IF;
        END LOOP;
    END;


END;
/


CREATE OR REPLACE PROCEDURE lbrn_daily
AS
BEGIN
    -- inactiveren personen die 12 maanden niet hebben ingelogd
    -- lbrn.inactivate_prs ();

    -- anonimiseer bezoekers
    lbrn.anonimiseer_bezoeker ();

        
END;
/




CREATE OR REPLACE PROCEDURE lbrn_import_contactpersoon(p_import_key IN NUMBER)
AS
   oracle_err_num            NUMBER;
   oracle_err_mes            VARCHAR2 (200);
   v_errormsg                VARCHAR2 (400);
   v_errorhint               VARCHAR2 (400);
   v_count                   NUMBER;
BEGIN
  -- de sequence array staat beschreven in FAC_PACF.SRC bij de fac_import_perslid_upd_body proc
  -- ode sequence 'Afdelingscode;PersoonAchternaam;Tussenvoegsel;Voorletters;Voornaam;Titel/Aanhef;Telefoonnummer;Mobiel;Email;Functie%'
   prs.import_perslid (p_import_key,
                       '0;0;0;0;0;0;4;1;0;2;' ||
                       '0;0;0;0;0;0;6;3;0;0;' ||
                       '5;3;0;0;0;0;4;0;0;0;'||
                       '0;0;0;0;0;0;0;0;0;0;'||
                       '0;0;0;0;0;0',
                       '"Surname";"GivenName";"mail";"Department";"Title"%'                
                       );
  
EXCEPTION
   WHEN OTHERS
   THEN
      oracle_err_num := SQLCODE;
      oracle_err_mes := SUBSTR (SQLERRM, 1, 100);
      v_errormsg := 'Error ' || oracle_err_num || '/' || oracle_err_mes;
      fac.imp_writelog (p_import_key, 'E', v_errormsg, v_errorhint);
END lbrn_import_contactpersoon;
/


CREATE OR REPLACE PROCEDURE lbrn_update_contactpersoon( p_import_key IN NUMBER)
IS
   CURSOR c_del IS
      SELECT p.prs_perslid_key, p.prs_perslid_email, pf.prs_perslid_naam_full
         FROM fac_imp_perslid i, prs_perslid p, prs_v_perslid_fullnames_all pf
         WHERE i.prs_perslid_email(+) = p.prs_perslid_email 
               AND i.prs_perslid_email IS NULL
               AND pf.prs_perslid_key = p.prs_perslid_key
               AND p.prs_perslid_email IS NOT NULL
               AND SUBSTR(COALESCE(p.prs_perslid_oslogin,'LEEG'),1,1) <> '_'
               AND p.prs_perslid_verwijder IS NULL;
               
               
   CURSOR c_sel IS
    SELECT i.prs_perslid_email email, i.prs_kenmerk1 afdeling
       FROM fac_imp_perslid i
       WHERE i.fac_import_key = p_import_key;
     
   v_count          NUMBER;
   v_perslid_key    NUMBER;
   v_bedrijf_key    NUMBER;
   v_groep_key      NUMBER;
   v_afdelingCode   prs_afdeling.prs_afdeling_naam%TYPE;
   oracle_err_num   NUMBER;
   oracle_err_mes   VARCHAR2 (200);
   v_errormsg       VARCHAR2 (400);
   v_errorhint      VARCHAR2 (400);
BEGIN
   -- generic update hierbij is email adres unieke sleutel , NULL geen werkplek koppeling
   -- Als waardes aangevuld worden in facilitor deze eerst uit lezen en dan de imp_perslid object updaten
   FOR rec in c_del
   LOOP
        BEGIN
             v_errorhint := 'Persoon verwijderen: ' || rec.prs_perslid_naam_full || '-' || rec.prs_perslid_email;
             prs.delete_perslid (p_import_key, rec.prs_perslid_key);
        END;
   END LOOP;
   
   FOR rec1 IN c_sel
   LOOP
      BEGIN
          v_afdelingCode := '';
          v_errorhint :='Fout bij ophalen afdelingscode';
          SELECT count(*), max(p.prs_afdeling_naam)
            INTO v_count,v_afdelingCode
            FROM prs_afdeling p
            WHERE UPPER(p.prs_afdeling_omschrijving) like UPPER(rec1.afdeling)||'%' 
              AND p.prs_afdeling_verwijder IS NULL;
          
          IF v_count=0
          THEN
            v_afdelingCode :='Onbekend';
          END IF;
          v_errorhint :='Fout bij update van fac_imp_perslid';
          UPDATE fac_imp_perslid 
             SET prs_afdeling_naam = v_afdelingCode, prs_kenmerk1=''
           WHERE prs_perslid_email = rec1.email 
             AND fac_import_key = p_import_key;
          
      END;
   END LOOP;
   COMMIT;
   prs.update_perslid (p_import_key, 'EMAIL', NULL);
   
   -- Evt aanpassingen die gedaan moeten worden na de updat van de personen
   -- BV : Voeg toe aan autorisatie groep
   
EXCEPTION
   WHEN OTHERS
   THEN
      oracle_err_num := SQLCODE;
      oracle_err_mes := SUBSTR (SQLERRM, 1, 100);
      v_errormsg := 'Error ' || oracle_err_num || '/' || oracle_err_mes;
      fac.imp_writelog (p_import_key,
                        'E',
                        v_errormsg,
                        v_errorhint);
END lbrn_update_contactpersoon;
/


CREATE OR REPLACE VIEW lbrn_v_noti_bez_aanmaak
AS
    SELECT NULL                   sender,
           NULL                   receiver,
              'U bent als bezoeker aan Laborijn aangemeld op'
           || TO_CHAR (bez_afspraak_datum, 'DD-MM-YYYY')
           || ' , '
           || TO_CHAR (bez_afspraak_datum, 'HH24:MI') text,
           'CUST01'               code,
           a.bez_afspraak_key     key,
           bez_bezoekers_key      xkey,
           bez_bezoekers_email    xemail,
           NULL                   xmobile,
           a.bez_afspraak_datum
      FROM bez_afspraak a, bez_bezoekers b
     WHERE     a.bez_afspraak_key = b.bez_afspraak_key
           AND TRUNC(a.bez_afspraak_aanmaak) = TRUNC(SYSDATE)
           AND NOT EXISTS
                   (SELECT 1
                      FROM fac_tracking t
                     WHERE     t.fac_srtnotificatie_key = 30
                           AND t.fac_tracking_refkey = a.bez_afspraak_key
                           AND t.fac_tracking_oms LIKE '%CUST01%');
                           
                           
                           
CREATE OR REPLACE VIEW lbrn_v_noti_bezoeker
AS
    SELECT NULL                   sender,
           NULL                   receiver,
              'Uw bezoek aan Laborijn op '
           || TO_CHAR (bez_afspraak_datum, 'DD-MM-YYYY')
           || ' , '
           || TO_CHAR (bez_afspraak_datum, 'HH24:MI') text,
           'CUST02'               code,
           a.bez_afspraak_key     key,
           bez_bezoekers_key      xkey,
           bez_bezoekers_email    xemail,
           NULL                   xmobile
      FROM bez_afspraak a, bez_bezoekers b
     WHERE     a.bez_afspraak_key = b.bez_afspraak_key
           AND TRUNC(a.bez_afspraak_datum) = TRUNC(SYSDATE)
           AND NOT EXISTS
                   (SELECT 1
                      FROM fac_tracking t
                     WHERE     t.fac_srtnotificatie_key = 405
                           AND t.fac_tracking_refkey = a.bez_afspraak_key
                           AND t.fac_tracking_oms LIKE '%CUST02%');
                           
                           
                           
CREATE OR REPLACE VIEW lbrn_v_noti_res
AS
    SELECT NULL                   sender,
           NULL                   receiver,
              'Herinnering : Uw reservering op '
           || TO_CHAR (rrr.res_rsv_ruimte_van, 'DD-MM-YYYY')
           || ' om '
           || TO_CHAR (rrr.res_rsv_ruimte_van, 'HH24:MI')
           || ' voor ruimte '||rr.res_ruimte_nr
           || ' in '|| al.alg_locatie_omschrijving text,
           'CUST03'               code,
           rrr.res_reservering_key    key,
           null                       xkey,
           p.prs_perslid_email        xemail,
           NULL                       xmobile
      FROM res_rsv_ruimte rrr, 
           res_ruimte_opstelling rro, 
           res_ruimte rr,
           res_alg_ruimte rar, 
           Alg_V_Allonrgoed_Gegevens alg,
           alg_locatie al,
           prs_perslid p
     WHERE     TRUNC(rrr.res_rsv_ruimte_van) = TRUNC(SYSDATE+1)
           AND p.Prs_Perslid_Key = rrr.res_rsv_ruimte_host_key
           AND p.prs_perslid_email IS NOT NULL
           AND rro.res_ruimte_opstel_key = rrr.res_ruimte_opstel_key
           AND rr.res_ruimte_key = rro.res_ruimte_key
           AND rar.res_ruimte_key = rr.res_ruimte_key
           AND alg.alg_ruimte_key = rar.alg_ruimte_key
           AND al.alg_locatie_key = alg.alg_locatie_key
           AND NOT EXISTS
                   (SELECT 1
                      FROM fac_tracking t
                     WHERE     t.fac_srtnotificatie_key = 421
                           AND t.fac_tracking_refkey = rrr.res_reservering_key
                           AND t.fac_tracking_oms LIKE '%CUST03%');            
 
 
CREATE OR REPLACE VIEW lbrn_v_noti_res_host
(
	code,
	sender,
	receiver,
	text,
	key,
	xemail,
	xmobile,
	attachments,
	xsender
)
AS                           
				SELECT 'CUST05' code
				     , NULL sender  
				     , rsvr.res_rsv_ruimte_host_key receiver
				     , 'Reservering '
				         ||   res.res_reservering_key
				         ||   ': U heeft op '
				         ||   TO_CHAR (rsvr.res_rsv_ruimte_van, 'DD-MM')
				         ||   ' van '
				         ||   TO_CHAR (rsvr.res_rsv_ruimte_van, 'HH24:MI')
				         ||   ' tot '
				         ||   TO_CHAR (rsvr.res_rsv_ruimte_tot, 'HH24:MI')
				         ||   ' ruimte '
				         ||   r.res_ruimte_nr
				         ||   ' gereserveerd.' text
				     , res.res_reservering_key key
				     , NULL xemail
				     , NULL xmobile     
				     , NULL attachments
				     , NULL xsender     
				  FROM res_v_aanwezigreservering res
				     , res_v_aanwezigrsv_ruimte rsvr
				     , res_ruimte_opstelling opst
				     , res_disc_params rdp
				     , res_ruimte r
				 WHERE r.res_discipline_key = rdp.res_ins_discipline_key
				   AND rdp.res_disc_params_noti_dagen IS NOT NULL
				   AND res.res_reservering_key = rsvr.res_reservering_key
				   AND opst.res_ruimte_opstel_key = rsvr.res_ruimte_opstel_key
				   AND opst.res_ruimte_key = r.res_ruimte_key
				   AND rsvr.res_status_fo_key <= 5
				   AND TRUNC(rsvr.res_rsv_ruimte_van) >TRUNC(fac.datumtijdplusuitvoertijd (SYSDATE, 0, 'DAGEN'))
				    AND  TRUNC(res_rsv_ruimte_van) <= TRUNC(fac.datumtijdplusuitvoertijd (SYSDATE, res_disc_params_noti_dagen, 'DAGEN'))
				   AND fac.getweekdaynum(sysdate) NOT IN (1,7)
				   AND rsvr.res_rsv_ruimte_van >= SYSDATE;                           
				                           
                           
CREATE OR REPLACE VIEW  lbrn_v_cnt_verleng_rap
AS
   SELECT NULL sender,
          p.prs_perslid_key receiver,
          'Rappel verlengingstermijn van contract '
           || cnt_contract_key
           || ' is verlopen. Verlengdatum is: '|| To_CHAR(verleng_datum,'DD-MM-YYYY') text,
          'CUST04' code,
          cnt_contract_key key, 
          NULL xemail, 
          NULL xmobile,
          NULL xkey 
    FROM (
          SELECT x.* ,
                 cnt.cnt_getTermijndatum(contract_looptijd_tot,verleng_termijn_key,-1) verleng_datum, 
                 cnt.cnt_getTermijndatum(cnt.cnt_getTermijndatum(contract_looptijd_tot,verleng_termijn_key,-1),rappel_termijn_key,-1) rappel_datum 
            FROM (SELECT c.cnt_contract_key,
                         c.prs_perslid_key_beh,
                         c.cnt_contract_looptijd_tot contract_looptijd_tot, 
                         FAC.SAFE_TO_NUMBER(ck.cnt_kenmerkcontract_waarde) verleng_termijn_key, 
                         (SELECT cnt_kenmerkcontract_waarde 
                            FROM cnt_kenmerkcontract ck,
                                 cnt_kenmerk ckk1 
                           WHERE ck.cnt_contract_key=c.cnt_contract_key 
                             AND ck.cnt_kenmerk_key=ckk1.CNT_KENMERK_KEY
                             AND UPPER(ckk1.CNT_KENMERK_OMSCHRIJVING)='RAPPEL VERLENGINGSTERMIJN') rappel_termijn_key 
                   FROM cnt_contract c, 
                        cnt_kenmerkcontract ck,
                        cnt_kenmerk ckk 
                  WHERE ck.cnt_contract_key=c.cnt_contract_key 
                    AND ck.cnt_kenmerk_key=ckk.CNT_KENMERK_KEY
                    AND UPPER(ckk.CNT_KENMERK_OMSCHRIJVING)='UITERLIJKE VERLENGINGSTERMIJN'
                  ) x
                  ),
          prs_perslid p 
    WHERE TRUNC(rappel_datum) = TRUNC(SYSDATE)   
      AND p.prs_perslid_key = prs_perslid_key_beh;
      --AND NOT EXISTS (SELECT 1
      --                  FROM fac_tracking t
      --                 WHERE t.fac_tracking_refkey = cnt_contract_key
      --                   AND t.fac_srtnotificatie_key = 196  --CNTTRK
      --                   AND t.fac_tracking_oms LIKE '%CUST04%'
      --                   AND SYSDATE > t.fac_tracking_datum);                                                


CREATE OR REPLACE VIEW lbrn_v_spreekuurbezetting
(  
    USER_KEY,
    TITLE,
    VAN,
    TOT,
    ITEM_KEY,
    COLOR,
    TEXTCOLOR,
    URL
)
AS
      SELECT x.res_rsv_ruimte_host_key user_key,      
             (SELECT f.fac_usrdata_omschr 
                FROM fac_usrdata f 
               WHERE f.fac_usrdata_key=x.fud_key)||
               ' ('||(SELECT p.prs_perslid_naam_friendly 
                        FROM prs_v_perslid_fullnames p 
              WHERE p.prs_perslid_key=x.res_rsv_ruimte_host_key)||')' title,                           
             x.res_rsv_ruimte_van van, 
             x.res_rsv_ruimte_tot tot,
             x.res_rsv_ruimte_key item_key,
              CASE x.bezoekers 
                   WHEN 0 THEN (SELECT fac_usrdata_omschr2 
                                  FROM fac_usrdata 
                                 WHERE fac_usrdata_key=x.fud_key) 
                   ELSE '#ff0000' 
                   END color, 
                   '#ffffff' textcolor,
             '?u=reservering' || CHR (38) || 'k=' || x.res_rsv_ruimte_key    url
      FROM (
              SELECT rrr.*,
                     (SELECT COUNT(*) 
                        FROM bez_bezoekers b 
                       where b.bez_afspraak_key=ba.bez_afspraak_key) bezoekers,
                     fac.safe_to_number(rkw.res_kenmerkreservering_waarde) fud_key  
                FROM res_rsv_ruimte rrr,
                     res_activiteit ra, 
                     bez_afspraak ba,
                     res_kenmerkwaarde rkw 
               WHERE ra.RES_ACTIVITEIT_UPPER='SPREEKUUR'
                 AND rrr.res_activiteit_key= ra.res_activiteit_key
                 AND ba.res_rsv_ruimte_key(+) = rrr.res_rsv_ruimte_key
                 AND rkw.res_rsv_ruimte_key(+) = rrr.res_rsv_ruimte_key
                 AND rkw.res_kenmerk_key(+)=1                 
                 AND rrr.res_rsv_ruimte_verwijder IS NULL
      ORDER BY 2) x;

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

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

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