• Replies 5 replies
  • Subscribers 342 subscribers
  • Views 360 views
  • Users 0 members are here
Options
Related

Plan (& CSI) reminders that send themselves

Heath Wilder
$organization

So I did a thing and if you are interested in feeding back on the code that'd be neat.

For a long time I’ve been wanting to make plans and CSI reminders a little more helpful.  I have a problem with people engaging with Tessitura (don't we all) and using Plans and CSIs proactively. There’s also that thing where quite a lot of the reminders are for Exec, or staff that don’t use Tessitura or do so intermittently.  Also back at TLCC 2017 I was chatting to a friend  who had a problem of having about a hundred plans and difficulty keeping track of them all. 

So for the last while I’ve had a project on the back burner where I wanted to have plan steps and CSIs that would email themselves to the person on the day that the reminder was set up for.  

After researching sp_send_dbmail and AP_SEND_SMTP_MAIL, and not getting far with it because of RAMP permissions, I was talking to a good pal in support and got access to a little program that does the job called LP_SEND_DBMAIL that is a wrapper for sp_send_dbmail.

Next I wrote a little script that looks for the reminders for the day for each individual and creates an html table and bundles them in one by one and sends them off to the people they are for (using their email). I also stole some code from dba.stackexchange here which has become a tradition.

Finally I scheduled that as a nightly SSAgent job

It seems to work great. I’ve attached the stored procedure in case you are interested and want to check my work.  I think it helps with the engagement thing and is useful for those of us that need to get people to do stuff and don’t want them to forget to check their portfolio every day (or in my case check my plans ever).  Also if you have 100 reminders you’ll only get one email with them all stacked in there and not 100 emails.

Interested to hear feedback and/or improvements.

Cheers,
H

Fullscreen LP_SDC_REMINDER_DB_EMAIL.sql Download 
USE [impresario]
GO

/****** Object:  StoredProcedure [dbo].[LP_SDC_REMINDER_DB_EMAIL]    Script Date: 10/07/2022 1:37:44 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		Heath Wilder
-- Create date: 22-06-2022
-- Description:	DB Email for reminders
-- For Plan reminders (and CSIs) to email the Worker
-- to let them know they have something to complete
-- rather than relying on daily reports.
-- Hoping to drive Tessitura engagment by being 
-- essential and relevant.
-- =============================================

CREATE PROCEDURE [dbo].[LP_SDC_REMINDER_DB_EMAIL]
 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;


CREATE TABLE #TEMP_TABLE ( WorkerId int, to_address varchar(55), worker_nm varchar(55), body varchar(1000) );

INSERT INTO #TEMP_TABLE 


select 
	a.worker_customer_no,
	b.address, 
	c.display_name, 
	(select concat(  
	'<p><br><b>Owner:</b> ', e.display_name,
	' ', e.customer_no,
	'<br><b>Campaign:</b> ', f.description,
	'</p><p><b>Action:</b> ',  a.description, 
	'<br><b>Notes:</b> ',  a.notes,
	'<br><b>Due date:</b> ', FORMAT(a.due_dt, 'dd-MM-yyyy'), 
	'</p><br>' )) as body 
from VS_STEP a 
Join VS_PLAN d on a.plan_no = d.plan_no
Join FT_GET_PRIMARY_EADDRESS() b on a.worker_customer_no = b.customer_no 
Join FT_CONSTITUENT_DISPLAY_NAME() c on c.customer_no = a.worker_customer_no 
Join FT_CONSTITUENT_DISPLAY_NAME() e on e.customer_no = d.customer_no 
Join VS_CAMPAIGN f on d.campaign_no = f.campaign_no

where 	 
DateDiff (day, getdate(),due_dt - COALESCE(warning_days,0) ) = 0 --between 0 and 100 -- will be 0 for email 
and completed_on_dt is null -- not complete 
and due_dt > getdate() -- future 

--CSIs and Reminders

INSERT INTO #TEMP_TABLE

select 
	e.worker_customer_no,
	e.email_address, 
	f.display_name, 
	(select concat(  
	'<p><br><b>Owner:</b> ', e.fname ,' ',e.lname ,
	' ', e.worker_customer_no,
	'<br><b>CSI:</b> ', a.category_desc, ' > ', a.activity_type_desc,  ' > ', a.contact_type_desc,
	'</p><p><b>Action:</b> ',  d.description, 
	'<br><b>Notes:</b> ',  c.notes,
	'<br><b>Due date:</b> ', FORMAT(c.complete_by_dt, 'dd-MM-yyyy'), 
	'</p><br>' )) as body 
from VS_ELEMENTS_CSI a
left Join T_ISSUE_ACTION b
	on a.activity_no = b.activity_no
Join TX_SOL_TASK c
	on a.activity_no = c.cust_service_no
Join TR_TASK_TYPE d
	on c.task_type = d.id
Join T_METUSER e 
	on c.solicitor = e.userid 
Join FT_CONSTITUENT_DISPLAY_NAME() f on a.customer_no = f.customer_no 

where 	 
DateDiff (day, getdate(),c.complete_by_dt - COALESCE(c.warning_days,0) ) = 0 --between 0 and 100 -- will be 0 for email 
and c.completed_on_dt is null -- not complete 
and c.complete_by_dt > getdate() -- future 
-- Start procedure

DECLARE	
	@return_value int,

	@to_address varchar(55),
	@Worker_nm varchar(55),
	@worker varchar(55), 
	@body varchar(1000),
	@HTML nvarchar(max);


-- Create cursor with Workers
DECLARE curWorker CURSOR FAST_FORWARD
FOR
    SELECT DISTINCT WorkerId, to_address, worker_nm FROM #TEMP_TABLE;

-- Open cursor and run over them one by one
OPEN curWorker;
FETCH NEXT FROM curWorker INTO @worker, @to_address, @Worker_nm;
WHILE @@FETCH_STATUS = 0 BEGIN

    -- Create html email header
    SELECT @HTML = N'<!DOCTYPE html>
                        <html>
                        <head lang="en">
                            <title></title>
                            <style type="text/css">
                                html {
                                color: #222;
                                font-size: 1em;
                                -webkit-font-smoothing: antialiased;
                                }

                                body {
                                padding: 0;
                                margin: 0;
                                width: 100%;
                                }

                                #main {
                                font-family: "Segoe UI", sans-serif;
                                font-size: 12px;
                                margin: 0 auto 100px auto;
                                position: relative;
                                font-weight: 300;
                                }

                                h1 {
                                font-family: "Segoe UI", sans-serif;
                                font-weight: 500;
                                font-size: 18px;
                                margin-bottom: 12px;
                                }

                                h2 {
                                font-family: "Segoe UI", sans-serif;
                                font-weight: 500;
                                font-size: 16px;
                                }

                                table {
                                border-collapse: collapse;
                                border-spacing: 0;
                                padding: 0;
                                margin: 0;
                                font-family: "Segoe UI", sans-serif;
                                font-size: 12px;
                                }

                                caption { text-align: left; padding: 2px;}
                                thead tr th { background-color:#ddd; }

                                th {
                                background-color: #e2e9fc;
                                border: 1px solid #ccc;
                                color: #555;
                                text-align: center;
                                font-weight: 700;
                                padding: 1px 4px;
                                font-family: "Segoe UI", sans-serif;
                                font-size: 12px;
                                }

                                td {
                                border-bottom: 1px solid #ccc;
                                vertical-align: middle;
                                padding: 1px 4px;
                                font-family: "Segoe UI", sans-serif;
                                font-size: 12px;
                                }

                                th.fix60 { width: 60px; }
                                th.fix90 { width: 90px; }
                                th.fix120 { width: 120px; }
                                th.fix140 { width: 140px; }
                                th.fix180 { width: 180px }

                                .container table {
                                border: 1px solid #e7e7e7;
                                margin: 0 -1px 24px 0;
                                text-align: left;
                                width: 100%;
                                }
                                .container tr { background-color:#fff; }
                            </style>
                        </head>
                        <body>
                            <div id="main">';
    SELECT @HTML = @HTML + N'<p style="font-weight:bold">Dear ' + @Worker_nm + '.</p><p> The following Tessitura reminders are due soon</p>';

    -- Create table header
    SELECT @HTML = @HTML + N'<table><thead><th class="fix140">Reminder</th></thead><tbody>';


	-- Create cursor with tickets of the Worker we are working on
    DECLARE curRemind CURSOR FAST_FORWARD
    FOR
        SELECT DISTINCT body FROM #TEMP_TABLE WHERE WorkerId = @worker;

    -- Open the cursor and run over the tickets one by one and add them to a table row
    OPEN curRemind;
    FETCH NEXT FROM curRemind INTO @body;
    WHILE @@FETCH_STATUS = 0 BEGIN

    -- Add row for every ticket
    SELECT @HTML = @HTML + N'<tr><td>' + @body + N'</td></tr>';

    FETCH NEXT FROM curRemind INTO @body;
    END;
    CLOSE curRemind;
    DEALLOCATE curRemind;


	-- Close table
    SELECT @HTML = @HTML + N'</tbody></table>' + '<p>Sent by your friendly neighbourhood Tessitura database c/- HeathW</p><br> 
		<img src="https://www.sydneydancecompany.com/tnew_template_2019/logo.b37d81ec.svg" alt="SDC logo" height="16" width = "325" >';

    -- Close html
    SELECT @HTML = @HTML + N'</body></html>';

    -- Print html
    SELECT @HTML;

	


EXEC	[dbo].[LP_SEND_DBMAIL]
		@from = NULL,
		@reply_to = NULL,
		@To = @to_address,
		@cc = NULL,
		@bcc = NULL,
		@Subject = 'Your Tessitura Reminders',
		@Body = @HTML,
		@bodyformat = 'HTML',
		@Attachment = NULL,
		@db_mail_profile = 'XXXXXX' -- put your db_profile name in here


FETCH NEXT FROM curWorker INTO @Worker, @to_address, @Worker_nm;
END;
CLOSE curWorker;
DEALLOCATE curWorker;

SELECT	'Return Value' = @return_value

DROP TABLE #TEMP_TABLE;


END

/*BE SURE TO SET PERMISSIONS 

grant execute on dbo.[LP_SDC_REMINDER_DB_EMAIL] TO IMPUSERS
grant execute on dbo.[LP_SDC_REMINDER_DB_EMAIL] TO TESSITURA_APP
*/
GO

Parents Reply Children
No Data