# MAIL_PKG

## Package Specification

```sql
PACKAGE MAIL_PKG AS
    --==================================================================================
    -- SPECIFICA DEL PACKAGE
    -- Aggiunta delle nuove procedure per il reminder della seconda caparra.
    -- Ultima modifica: 26/07/2024
    --==================================================================================

    -- Procedura generica per inviare email
    procedure send_custom_mail(p_recipients varchar2, p_subject varchar2, p_body varchar2);

    -- Procedure esistenti
    PROCEDURE send_richiesta_riscontro_preventivo (
        p_style_id  NUMBER DEFAULT 1,
        p_evento_id NUMBER
    );
    PROCEDURE send_richiesta_riscontro_preventivo_job;

    PROCEDURE send_richiesta_riscontro_post_degustazione (
        p_style_id  NUMBER DEFAULT 1,
        p_evento_id NUMBER
    );
    PROCEDURE send_richiesta_riscontro_post_degustazione_job;

    -- ===============================================================================
    -- NUOVE PROCEDURE PER IL REMINDER DELLA SECONDA CAPARRA
    -- Data creazione: 26/07/2024
    -- ===============================================================================

    /**
     * @descr Costruisce e invia la mail di sollecito per la seconda caparra per un dato evento.
     * @param p_style_id ID dello stile HTML da applicare alla mail.
     * @param p_evento_id ID dell'evento per cui inviare la notifica.
     */
    PROCEDURE send_reminder_seconda_caparra (
        p_style_id  NUMBER DEFAULT 1,
        p_evento_id NUMBER
    );

    /**
     * @descr Job che seleziona gli eventi per cui inviare il sollecito della seconda caparra.
     * Il primo invio avviene 65 giorni prima dell'evento.
     * Gli invii successivi avvengono ogni 5 giorni fino alla data dell'evento,
     * se la seconda caparra non risulta ancora pagata.
     */
    PROCEDURE send_reminder_seconda_caparra_job;

END MAIL_PKG;
```

## Package Body

```sql
PACKAGE BODY MAIL_PKG AS
    --==================================================================================
    -- BODY DEL PACKAGE
    -- Implementazione delle nuove procedure e mantenimento di quelle esistenti.
    -- Ultima modifica: 25/07/2024
    --==================================================================================

    /**
     * @descr Procedura di utility generica per inviare una mail tramite APEX_MAIL.
     * @param p_recipients Lista di destinatari separati da virgola.
     * @param p_subject Oggetto della mail.
     * @param p_body Corpo della mail (può contenere HTML).
     */
    procedure send_custom_mail(p_recipients varchar2, p_subject varchar2, p_body varchar2) AS
    BEGIN
        -- Se non ci sono destinatari, interrompe l'esecuzione.
        if trim(p_recipients) is null
        then
            return;
        end if;

        -- Utilizza il package APEX_MAIL per comporre e inviare l'email.
        APEX_MAIL.SEND(
            p_to        => p_recipients,
            p_from      => 'noreply@apollinarecatering.it',
            p_bcc       => 'monia@apollinarecatering.it, matrimonio@apollinarecatering.it',
            --p_bcc       => 'monia@apollinarecatering.it, maria@apollinarecatering.it', -- Copia conoscenza nascosta
            p_subj      => p_subject,
            p_body      => p_body,
            p_body_html => p_body -- Il corpo viene inviato sia come testo che come HTML.
        );

        -- Forza l'invio immediato delle mail presenti nella coda di APEX.
        APEX_MAIL.PUSH_QUEUE;
    END send_custom_mail;

    /**
     * @descr Invia una mail di sollecito per avere un riscontro su un preventivo inviato per eventi in stato preventivo (100) o scheda confermata (200).
     * @param p_style_id ID dello stile HTML da usare per il corpo della mail.
     * @param p_evento_id ID dell'evento a cui la mail fa riferimento.
     */
    PROCEDURE send_richiesta_riscontro_preventivo (
        p_style_id  NUMBER DEFAULT 1,
        p_evento_id NUMBER
    ) AS
        v_evento   eventi%ROWTYPE;
        v_location location%ROWTYPE;
    BEGIN
        -- Imposta la lingua e il formato della data della sessione per garantire la corretta formattazione.
        EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_LANGUAGE="ITALIAN"';
        EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT="DD-MON-YYYY"';

        -- Recupera i dettagli dell'evento e della location.
        BEGIN
            -- Seleziona i dati dell'evento solo se le mail sono abilitate (mail_enabled > 0).
            SELECT e.* INTO v_evento
            FROM   eventi e
            WHERE  e.id = p_evento_id
              AND  e.stato in (100, 200)
              AND  e.mail_enabled > 0;

            -- Seleziona i dati della location associata all'evento.
            SELECT l.* INTO v_location
            FROM   location l
            WHERE  l.id = v_evento.id_location;
        EXCEPTION
            -- Se l'evento non viene trovato (o ha le mail disabilitate), la procedura termina.
            WHEN NO_DATA_FOUND THEN RETURN;
        END;

        -- Inizializza il corpo della mail e imposta lo stile HTML.
        CMN_MAIL_HTMLUTILS.mailbody := '';
        CMN_MAIL_HTMLUTILS.set_style(p_style_id);

        -- Costruisce il corpo della mail paragrafo per paragrafo utilizzando il package di utility.
        CMN_MAIL_HTMLUTILS.create_paragraph('Gentilissimi,');
        CMN_MAIL_HTMLUTILS.create_paragraph(
            'in seguito all''invio della nostra proposta per il vostro evento, '||
            'desideriamo sapere se avete avuto modo di valutarla e se siete interessati ai nostri servizi.'
        );
        CMN_MAIL_HTMLUTILS.create_paragraph(
            'Saremmo lieti di incontrarvi per una degustazione, così da potervi presentare al meglio le nostre offerte. '||
            'Potete prenotare una delle date disponibili direttamente tramite il nostro sito: '||
            '<a href="https://www.apollinarecatering.it/degustazioni/">Degustazioni</a>.'
        );
        CMN_MAIL_HTMLUTILS.create_paragraph(
            'Per qualsiasi ulteriore informazione o chiarimento potete scriverci a '||
            '<a href="mailto:info@apollinarecatering.it">info@apollinarecatering.it</a>.'
        );
        CMN_MAIL_HTMLUTILS.create_paragraph('<b>Si prega di non rispondere direttamente a questa email.</b>');
        CMN_MAIL_HTMLUTILS.create_paragraph('Cordiali saluti,');
        CMN_MAIL_HTMLUTILS.create_paragraph('<b>Ufficio Commerciale</b>');
        CMN_MAIL_HTMLUTILS.create_paragraph('📞 +39 0743 45 449');
        CMN_MAIL_HTMLUTILS.create_paragraph('<b>Apollinare Catering</b>');

        -- Invia la mail usando APEX_MAIL, componendo un oggetto dinamico.
        APEX_MAIL.SEND(
            p_to        => v_evento.cliente_email,
            p_from      => 'noreply@apollinarecatering.it',
            p_bcc       => 'monia@apollinarecatering.it, matrimonio@apollinarecatering.it',
            --p_bcc       => 'monia@apollinarecatering.it, maria@apollinarecatering.it',
            p_subj      => 'Apollinare – richiesta riscontro per evento del '||
                           TO_CHAR(v_evento.data, 'DD/MM/YYYY')||' presso '||v_location.location,
            p_body      => CMN_MAIL_HTMLUTILS.mailbody,
            p_body_html => CMN_MAIL_HTMLUTILS.mailbody
        );

        -- Forza l'invio immediato dalla coda di APEX.
        APEX_MAIL.PUSH_QUEUE;
    END send_richiesta_riscontro_preventivo;

    /**
     * @descr Invia una mail di sollecito dopo che il cliente ha partecipato a una degustazione.
     * @param p_style_id ID dello stile HTML da usare per il corpo della mail.
     * @param p_evento_id ID dell'evento a cui la mail fa riferimento.
     */
    PROCEDURE send_richiesta_riscontro_post_degustazione (
        p_style_id  NUMBER DEFAULT 1,
        p_evento_id NUMBER
    ) AS
        v_evento   eventi%ROWTYPE;
        v_location location%ROWTYPE;
    BEGIN
        -- Imposta la lingua e il formato della data della sessione.
        EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_LANGUAGE="ITALIAN"';
        EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT="DD-MON-YYYY"';

        -- Recupera i dettagli dell'evento e della location.
        BEGIN
            -- Seleziona i dati dell'evento solo se le mail sono abilitate.
            SELECT e.* INTO v_evento
            FROM   eventi e
            WHERE  e.id = p_evento_id
              AND  e.stato in (200)
              AND  e.mail_enabled > 0;

            -- Seleziona i dati della location.
            SELECT l.* INTO v_location
            FROM   location l
            WHERE  l.id = v_evento.id_location;
        EXCEPTION
            -- Se non trova l'evento, esce dalla procedura.
            WHEN NO_DATA_FOUND THEN RETURN;
        END;

        -- Inizializza il corpo della mail e imposta lo stile HTML.
        CMN_MAIL_HTMLUTILS.mailbody := '';
        CMN_MAIL_HTMLUTILS.set_style(p_style_id);

        -- Costruisce il corpo della mail.
        CMN_MAIL_HTMLUTILS.create_paragraph('Gentilissimi,');
        CMN_MAIL_HTMLUTILS.create_paragraph(
            'in seguito alla degustazione effettuata per il vostro evento, '||
            'desideriamo sapere se la nostra proposta risponde alle vostre aspettative '||
            'e se intendete procedere con la conferma dei servizi.'
        );
        CMN_MAIL_HTMLUTILS.create_paragraph(
            'Per ogni ulteriore chiarimento siamo a vostra completa disposizione: '||
            '<a href="mailto:info@apollinarecatering.it">info@apollinarecatering.it</a>.'
        );
        CMN_MAIL_HTMLUTILS.create_paragraph('<b>Si prega di non rispondere direttamente a questa email.</b>');
        CMN_MAIL_HTMLUTILS.create_paragraph('Cordiali saluti,');
        CMN_MAIL_HTMLUTILS.create_paragraph('<b>Ufficio Commerciale</b>');
        CMN_MAIL_HTMLUTILS.create_paragraph('📞 +39 0743 45 449');
        CMN_MAIL_HTMLUTILS.create_paragraph('<b>Apollinare Catering</b>');

        -- Invia la mail, componendo un oggetto dinamico con data e location.
        APEX_MAIL.SEND(
            p_to        => v_evento.cliente_email,
            p_from      => 'noreply@apollinarecatering.it',
            p_bcc       => 'monia@apollinarecatering.it, matrimonio@apollinarecatering.it',
            --p_bcc       => 'monia@apollinarecatering.it, maria@apollinarecatering.it',
            p_subj      => 'Apollinare – riscontro post-degustazione evento del '||
                           TO_CHAR(v_evento.data, 'DD/MM/YYYY')||' presso '||v_location.location,
            p_body      => CMN_MAIL_HTMLUTILS.mailbody,
            p_body_html => CMN_MAIL_HTMLUTILS.mailbody
        );

        -- Forza l'invio immediato.
        APEX_MAIL.PUSH_QUEUE;
    END send_richiesta_riscontro_post_degustazione;

    /**
     * @descr Procedura schedulabile (job) che invia solleciti per i preventivi non confermati.
     */
    PROCEDURE send_richiesta_riscontro_preventivo_job AS
    BEGIN
        -- Itera su tutti gli eventi che soddisfano i criteri per il sollecito.
        FOR evt IN (
            SELECT e.*
            FROM   eventi e
            WHERE  e.stato        in (100)            -- Stato: Preventivo non confermato
              AND  e.mail_enabled = 1                 -- Mail abilitate
              AND  TRUNC(e.data_doc) = TRUNC(SYSDATE) - 10 -- Sono passati 10 giorni dalla data del documento.
        )
        LOOP
            -- Per ogni evento trovato, chiama la procedura di invio mail.
            send_richiesta_riscontro_preventivo(
                p_style_id  => 1,
                p_evento_id => evt.id
            );
        END LOOP;
    END send_richiesta_riscontro_preventivo_job;

    /**
     * @descr Procedura schedulabile (job) che invia solleciti dopo una degustazione.
     */
    PROCEDURE send_richiesta_riscontro_post_degustazione_job AS
    BEGIN
        -- Itera su tutti gli eventi che soddisfano i criteri per il sollecito post-degustazione.
        FOR evt IN (
            SELECT e.*
            FROM   eventi e
            JOIN   ( -- Sottoquery per trovare la data della prima degustazione per ogni evento.
                     SELECT id_evento,
                            MIN(TRUNC(data)) AS min_data
                     FROM   eventi_det_degust
                     GROUP  BY id_evento ) dm
                   ON dm.id_evento = e.id
            WHERE  e.stato        = 200               -- Stato: Scheda evento in preparazione
              AND  e.mail_enabled = 1                 -- Mail abilitate
              AND  dm.min_data    = TRUNC(SYSDATE) - 15 -- Sono passati 15 giorni dalla prima degustazione.
        )
        LOOP
            -- Per ogni evento trovato, chiama la procedura di invio mail.
            send_richiesta_riscontro_post_degustazione(
                p_style_id  => 1,
                p_evento_id => evt.id
            );
        END LOOP;
    END send_richiesta_riscontro_post_degustazione_job;


    -- ===============================================================================
    -- IMPLEMENTAZIONE NUOVE PROCEDURE
    -- Data creazione: 26/07/2024
    -- ===============================================================================

    PROCEDURE send_reminder_seconda_caparra (
        p_style_id  NUMBER DEFAULT 1,
        p_evento_id NUMBER
    ) AS
        v_evento   eventi%ROWTYPE;
        v_location location%ROWTYPE;
    BEGIN
        EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_LANGUAGE="ITALIAN"';
        EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT="DD-MON-YYYY"';

        -- Recupero i dati dell'evento e della location
        BEGIN
            SELECT e.* INTO v_evento
            FROM   eventi e
            WHERE  e.id = p_evento_id
              AND  e.mail_enabled > 0;

            SELECT l.* INTO v_location
            FROM   location l
            WHERE  l.id = v_evento.id_location;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                -- Se l'evento non esiste o le mail sono disabilitate, esco.
                RETURN;
        END;

        -- Costruzione del corpo della mail
        CMN_MAIL_HTMLUTILS.mailbody := '';
        CMN_MAIL_HTMLUTILS.set_style(p_style_id);

        CMN_MAIL_HTMLUTILS.create_paragraph('Gentilissimi,');
        CMN_MAIL_HTMLUTILS.create_paragraph(
            'sperando che tutto proceda per il meglio, desideriamo cortesemente ricordarvi la scadenza relativa alla seconda tranche di pagamento per il vostro evento.'
        );
        CMN_MAIL_HTMLUTILS.create_paragraph(
            'Qualora non fosse ancora stato effettuato, vi invitiamo a procedere con il versamento della seconda caparra, previsto 60 giorni prima della data dell’evento, come da accordi e indicato nel preventivo.'
        );
        CMN_MAIL_HTMLUTILS.create_paragraph(
            'Rimaniamo a disposizione per qualsiasi chiarimento e vi ringraziamo sin da ora per la preziosa collaborazione.'
        );
        CMN_MAIL_HTMLUTILS.create_paragraph('Cordiali saluti,');
        CMN_MAIL_HTMLUTILS.create_paragraph('<b>Ufficio Commerciale</b>');
        CMN_MAIL_HTMLUTILS.create_paragraph('📞 +39 0743 45 449');
        CMN_MAIL_HTMLUTILS.create_paragraph('<b>Apollinare Catering</b>');

        -- Invio della mail
        APEX_MAIL.SEND(
            --p_to        => v_evento.cliente_email,
            p_to        => 'amministrazione@apollinarecatering.it',
            p_from      => 'noreply@apollinarecatering.it',
            p_bcc       => 'monia@apollinarecatering.it, matrimonio@apollinarecatering.it',
            --p_bcc       => 'monia@apollinarecatering.it, maria@apollinarecatering.it',
            p_subj      => 'Apollinare – Promemoria pagamento per evento del '||
                           TO_CHAR(v_evento.data, 'DD/MM/YYYY'),
            p_body      => CMN_MAIL_HTMLUTILS.mailbody,
            p_body_html => CMN_MAIL_HTMLUTILS.mailbody
        );

        -- Invio immediato dalla coda
        APEX_MAIL.PUSH_QUEUE;

    END send_reminder_seconda_caparra;


    PROCEDURE send_reminder_seconda_caparra_job AS
    BEGIN
        -- Scorro tutti gli eventi che necessitano del reminder.
        -- La logica ora utilizza la vista GET_EVENTI_DA_PAGARE_ENTRO_65GG
        -- per identificare gli eventi che non hanno saldato la caparra.
        FOR evt IN (
            SELECT
                v.id,
                v.data -- Seleziono la data per il calcolo del MOD
            FROM
                GET_EVENTI_DA_PAGARE_ENTRO_65GG v
            JOIN
                eventi e ON v.id = e.id -- Join per recuperare il flag mail_enabled
            WHERE
                -- La vista già filtra per stato, importi e finestra di 65 giorni.
                -- Aggiungo solo le condizioni specifiche del job.

                -- Le mail automatiche devono essere abilitate.
                e.mail_enabled = 1

                -- La logica MOD assicura l'invio periodico ogni 5 giorni.
                AND MOD(65 - (TRUNC(v.data) - TRUNC(SYSDATE)), 5) = 0
        )
        LOOP
            -- Per ogni evento trovato, chiamo la procedura di invio mail.
            send_reminder_seconda_caparra(
                p_style_id  => 1,
                p_evento_id => evt.id
            );
        END LOOP;
    END send_reminder_seconda_caparra_job;

END MAIL_PKG;```