17 KiB
17 KiB
MAIL_PKG
Package Specification
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
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;```