--
-- $Id$
--
-- Script containing customer specific configuration sql statements for Norah (NORA)

DEFINE thisfile = 'NORA.SQL'
DEFINE dbuser   = '^NORA'

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 PROCEDURE nora_processemail (pfrom        IN VARCHAR2,
                                               pto          IN VARCHAR2,
                                               psubject     IN VARCHAR2,
                                               pbody        IN VARCHAR2,
                                               psessionid   IN VARCHAR2,
                                               pemailkey    IN NUMBER)
AS
    v_sender         prs_perslid.prs_perslid_key%TYPE;
    v_kostenplaats   prs_afdeling.prs_kostenplaats_key%TYPE;
    v_mldstat        mld_melding.mld_melding_status%TYPE;
    v_stdmelding     mld_stdmelding.mld_stdmelding_key%TYPE;
    v_stduitvoer     mld_stdmelding.mld_stdmelding_t_uitvoertijd%TYPE;
    v_errormsg       fac_result.fac_result_waarde%TYPE;
    v_from           VARCHAR2 (4000);
    v_fromaddr       VARCHAR2 (4000);
    v_body           VARCHAR2 (4000);
    m_body           VARCHAR2 (4000);
    v_mldkey         mld_melding.mld_melding_key%TYPE;
    v_mailorigin     mld_kenmerk.mld_kenmerk_key%TYPE;
    v_mailattach     mld_kenmerk.mld_kenmerk_key%TYPE;
    v_locatiekey     alg_locatie.alg_locatie_key%TYPE;
BEGIN
    v_sender := 3;                            --Standaard de persoon Facilitor
    v_stdmelding := fac.safe_to_number (fac.getsetting ('defaultstdmelding'));
    v_mailorigin := 1;                      --Kenmerk_key oorspronkelijke mail
    v_mailattach := 2;                                 --Kenmerk_key bijlagen

    -- Verwijder de Bounce Address Tag Validation

    v_errormsg := '(0x101)';

    IF pfrom LIKE 'prvs=%'
    THEN
        v_from := SUBSTR (pfrom, INSTR (pfrom, '=', -1) + 1);
    ELSE
        v_from := pfrom;
    END IF;

    v_errormsg := '(0x102)';

    -- Indien een mail is doorgestuurd, dan moet er teruggevallen worden op de v_fromaddr.
		-- Maar niet altijd is v_fromaddr gevuld, vandaar de IF
    IF v_fromaddr IS NOT NULL
		THEN
    		SELECT fac_result_waarde
      		INTO v_fromaddr
      		FROM fac_result
     		WHERE fac_result_sessionid = psessionid AND fac_result_naam = 'fromaddr';
    END IF;

    v_fromaddr := REPLACE (REPLACE (v_fromaddr, '>', ''), '<', '');

    v_body := pbody;
    m_body := pbody;

    -- Valideer de sender in pfrom: kennen we deze?
    v_errormsg := '(0x111)';

    SELECT MIN (p.prs_perslid_key), MIN (d.prs_kostenplaats_key)
      INTO v_sender, v_kostenplaats
      FROM prs_perslid p, prs_afdeling d
     WHERE     p.prs_afdeling_key = d.prs_afdeling_key
           AND p.prs_perslid_verwijder IS NULL
           AND UPPER (prs_perslid_email) = UPPER (v_from);

    CASE
        WHEN UPPER (pto) LIKE 'STORINGEN@%'
        THEN
            BEGIN
                v_errormsg :=
                    SUBSTR (('(0x201)' || psessionid || pfrom || m_body),
                            0,
                            4000);

                -- Valideer de sender in pfromaddr: kennen we deze?
                -- Dan mag de melding aan die persoon gekoppeld worden
                SELECT MIN (p.prs_perslid_key), MIN (d.prs_kostenplaats_key)
                  INTO v_sender, v_kostenplaats
                  FROM prs_perslid p, prs_afdeling d
                 WHERE     p.prs_afdeling_key = d.prs_afdeling_key
                       AND p.prs_perslid_verwijder IS NULL
                       AND UPPER (prs_perslid_email) = UPPER (v_fromaddr);
            EXCEPTION
                WHEN OTHERS
                THEN
                    fac.writelog (
                        'PROCESSEMAIL',
                        'W',
                           'Mail kon niet geformateerd worden afzender:'
                        || pfrom
                        || '['
                        || v_errormsg
                        || ']',
                           'OTHERS (error '
                        || SQLCODE
                        || '/'
                        || SUBSTR (SQLERRM, 1, 100)
                        || ')');
            END;

            -- Als v_sender gevuld is (winkel?), dan proberen we de locatie te bepalen.
            -- Die kunnen we dan ook vast vullen in de melding. Niet erg als dat niet lukt.
            IF v_sender IS NOT NULL
            THEN
                BEGIN
                    SELECT l.alg_locatie_key
                      INTO v_locatiekey
                      FROM alg_v_allonroerendgoed l, prs_perslid p
                     WHERE     l.alg_type = p.alg_onroerendgoed_type
                           AND l.alg_onroerendgoed_keys =
                               p.alg_onroerendgoed_keys
                           AND p.prs_perslid_key = v_sender;
                EXCEPTION
                    WHEN OTHERS
                    THEN
                        v_locatiekey := NULL;
                END;
            END IF;

            --Hieronder de acties die gedaan moeten worden.
            BEGIN
                v_errormsg := '(0x211)' || pfrom || psubject;
                v_mldstat := 2;           -- Standaard de status in Nieuw (2).
                v_stduitvoer :=
                    mld.getstduitvoer (v_stdmelding,
                                       NULL,
                                       NULL,
                                       NULL);
                v_errormsg := '(0x212)' || pfrom || v_mldkey || psubject;

                INSERT INTO mld_melding (mld_melding_module,
                                         mld_meldbron_key,
                                         mld_melding_datum,
                                         mld_melding_onderwerp,
                                         mld_melding_omschrijving,
                                         mld_melding_status,
                                         mld_melding_t_uitvoertijd,
                                         mld_stdmelding_key,
                                         mld_alg_locatie_key,
                                         mld_melding_externnr,
                                         prs_perslid_key,
                                         prs_perslid_key_voor,
                                         prs_kostenplaats_key,
                                         mld_melding_spoed)
                     VALUES (
                                'MLD',
                                4,                                    -- email
                                SYSDATE,
                                SUBSTR (psubject, 1, 80),
                                SUBSTR (
                                    REPLACE (
                                        SUBSTR (v_body, 1, 4000),
                                           CHR (13)
                                        || CHR (10)
                                        || CHR (13)
                                        || CHR (10),
                                        CHR (13) || CHR (10)),
                                    1,
                                    4000),     -- verwijder onnodige witregels
                                v_mldstat,
                                NULL,                          -- v_stduitvoer
                                v_stdmelding,
                                v_locatiekey, -- Locatie bepaald via standaardlocatie v_sender (indien ingesteld)
                                NULL,
                                v_sender,
                                v_sender,
                                v_kostenplaats,
                                3)
                  RETURNING mld_melding_key
                       INTO v_mldkey;

                v_errormsg := '(0x221)';
                mld.setmeldingstatus (v_mldkey, v_mldstat, v_sender);

                INSERT INTO fac_result (fac_result_sessionid,
                                        fac_result_naam,
                                        fac_result_waarde)
                         VALUES (psessionid,
                                 'maillog',
                                 'Geregistreerd onder melding ' || v_mldkey);
            END;

            IF v_mldkey IS NOT NULL
            THEN
                BEGIN
                    -- Originele email wordt opgeslagen
                    v_errormsg := '(0x231)' || v_mldkey || psubject;

                    INSERT INTO fac_result (fac_result_sessionid,
                                            fac_result_naam,
                                            fac_result_waarde)
                             VALUES (
                                        psessionid,
                                        'kenmerkorgmailpath',
                                           'MLD\M'
                                        || TO_CHAR (TRUNC (v_mldkey / 1000),
                                                    'FM0000')
                                        || '___\M'
                                        || v_mldkey
                                        || '\'
                                        || v_mailorigin
                                        || '\');
                END;
            END IF;


            v_errormsg := 'Fout toevoegen bijlage(n)';

            IF v_mailattach IS NOT NULL
            THEN
                BEGIN
                    -- Toevoegen bijlage(n)
                    v_errormsg := '(0x232)' || v_mldkey || psubject;

                    INSERT INTO fac_result (fac_result_sessionid,
                                            fac_result_naam,
                                            fac_result_waarde)
                             VALUES (
                                        psessionid,
                                        'kenmerkpath',
                                           'MLD\M'
                                        || TO_CHAR (TRUNC (v_mldkey / 1000),
                                                    'FM0000')
                                        || '___\M'
                                        || v_mldkey
                                        || '\'
                                        || v_mailattach
                                        || '\');
                END;
            END IF;
        ELSE
            v_errormsg :=
                '(0x900)' || v_mldkey || psubject || ' - geen pto herkend';

            INSERT INTO fac_result (fac_result_sessionid,
                                    fac_result_naam,
                                    fac_result_waarde)
                 VALUES (psessionid, 'maillog', v_errormsg);
    END CASE;
EXCEPTION
    WHEN OTHERS
    THEN
        INSERT INTO fac_result (fac_result_sessionid,
                                fac_result_naam,
                                fac_result_waarde)
             VALUES (psessionid, 'maillog', v_errormsg);

        fac.writelog (
            'PROCESSEMAIL',
            'E',
               'Mail kon niet ingelezen worden afzender:'
            || pfrom
            || '['
            || v_errormsg
            || v_mldkey
            || psubject
            || ']',
               'OTHERS (error '
            || SQLCODE
            || '/'
            || SUBSTR (SQLERRM, 1, 100)
            || ')');
END;
/

CREATE OR REPLACE VIEW nora_rap_planning_td
AS
    SELECT m.mld_melding_behandelaar_key                            xuser_key,
           pf.prs_perslid_naam_full                                 behandelaar,
           m.mld_melding_key || ' ' || mld_melding_onderwerp        title,
           mld_melding_actiedatum                                   tot,
           mld_melding_actiedatum                                   van,
           m.mld_melding_key                                        item_key,
           '#FF0000'                                                color,
           '#FFFFFF'                                                textcolor,
           '?u=melding' || CHR (38) || 'k=' || m.mld_melding_key    url
      FROM mld_melding m, prs_v_perslid_fullnames_all pf
     WHERE     mld_melding_actiedatum IS NOT NULL
           AND m.mld_melding_status in (0,2,3,4,7)
           AND m.mld_melding_behandelaar_key = pf.prs_perslid_key
    UNION ALL
    SELECT NULL                                                     xuser_key,
           '<geen behandelaar>'                                     behandelaar,
           m.mld_melding_key || ' ' || mld_melding_onderwerp        title,
           mld_melding_actiedatum                                   tot,
           mld_melding_actiedatum                                   van,
           m.mld_melding_key                                        item_key,
           '#FF0000'                                                color,
           '#FFFFFF'                                                textcolor,
           '?u=melding' || CHR (38) || 'k=' || m.mld_melding_key    url
      FROM mld_melding m
     WHERE     mld_melding_actiedatum IS NOT NULL
           AND m.mld_melding_status in (0,2,3,4,7)
           AND m.mld_melding_behandelaar_key IS NULL;

CREATE OR REPLACE VIEW NORA_V_LOCATIESTATUS
AS
      SELECT x.datum,
             x.module,
             x.locatie,
             x.referentie,
             x.TYPE,
             x.discipline,
             x.categorie,
             x.onderwerp,
             x.omschrijving,
             x.datum_afhandeling,
             x.opmerking_afhandeling,
             x.informatie
        FROM (SELECT m.mld_melding_datum
                         AS datum,
                     'melding'
                         AS module,
                     l.alg_locatie_omschrijving
                         AS locatie,
                     'Melding ' || TO_CHAR (m.mld_melding_key)
                         AS referentie,
                     sd.ins_srtdiscipline_omschrijving
                         AS TYPE,
                     md.ins_discipline_omschrijving
                         AS discipline,
                     s.mld_stdmelding_omschrijving
                         AS categorie,
                     m.mld_melding_onderwerp
                         AS onderwerp,
                     TO_CHAR(SUBSTR(m.mld_melding_omschrijving,1,4000))
                         AS omschrijving,
                     fac.gettrackingdate ('MLDAFM', m.mld_melding_key)
                         AS datum_afhandeling,
                     m.mld_melding_opmerking
                         AS opmerking_afhandeling,
                        m.mld_melding_omschrijving
                     || CHR (10)
                     || (CASE
                             WHEN fac.gettrackingdate ('MLDAFM',
                                                       m.mld_melding_key)
                                      IS NOT NULL
                             THEN
                                 (   'Afgemeld op '
                                  || TO_CHAR (
                                         TO_DATE (
                                             fac.gettrackingdate (
                                                 'MLDAFM',
                                                 m.mld_melding_key)),
                                         'dd-mm-yyyy')
                                  || ': '
                                  || m.mld_melding_opmerking)
                             ELSE
                                 NULL
                         END)
                         AS informatie
                FROM mld_melding      m,
                     mld_stdmelding   s,
                     alg_locatie      l,
                     --                  alg_district d,
                     --                  alg_regio r,
                     --                  alg_v_allonrgoed_gegevens og,
                     mld_discipline   md,
                     ins_srtdiscipline sd
               WHERE     m.mld_alg_locatie_key = l.alg_locatie_key
                     AND m.mld_stdmelding_key = s.mld_stdmelding_key
                     AND s.mld_ins_discipline_key = md.ins_discipline_key
                     AND md.ins_srtdiscipline_key = sd.ins_srtdiscipline_key
              UNION ALL
              SELECT COALESCE (t.inspectiedatum, t.volgendedatum)
                         AS datum,
                     'periodieke taak'
                         AS module,
                     t.locatie
                         AS locatie,
                     t.object
                         AS referentie,
                     t.taaksoort
                         AS TYPE,
                     t.discipline || ' - ' || t.groep
                         AS discipline,
                     t.taak
                         AS categorie,
                     t.soort
                         AS onderwerp,
                     t.opmerking
                         AS omschrijving,
                     t.inspectiedatum
                         AS datum_afhandeling,
                     t.inspectie_opmerking
                         AS opmerking_afhandeling,
                     NULL
                         AS informatie
                FROM CTR_V_MONTHLY_RECURRING_TASKS t) x
       WHERE TRUNC (x.datum) <= TRUNC (SYSDATE)
    ORDER BY x.datum DESC;

------ 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