Add to calendar link for email confirmations?

Hi all -  Thanks to all of the upvoting on the Idea Board and also for the existing feedback we have received, we are considering adding this functionality to v16 in an upcoming release. We will keep you posted!

I have created sql code which does this for icalendar and google calendar, which behave differently, if anyone is interested.

BWG

That sounds amazing. Please send the script to neil.cole@state.mn.us.

Also, thank you for the tip regarding TR_REFERENCE_COLUMN that you mentioned at this year's TLCC. It made my custom system table much more usable. I also found directions on how to do a custom system table AFTER I figured out how to do it on my own. That'll teach me to use the Help system.

Neil

I would be curious to see that as well.  I am not sure I have seen anyone create one of those in SQL before.

jmoskal@thecenterpresents.org

I would love to see it as well.  blainem@kcrep.org

I would be curious to see the script as well. alroark@ucdavis.edu

I would be curious to see it, too!

Laura

lhouse@kauffmancenter.org

Below is sql from my email confirmation script, which you can update as needed.  Basically, the template called by tnew executes this sql script which generates the html code.  It can also be sent to the Tessitura Processing Service via a tessitura report.

The google calendar is just a link, but the ical is actually a file which is generated, and placed on our server, then accessed by a link.

Email me if you would like the full email contribution code.

BWG

select @theatre_address = ISNULL(a.street,'') + ', ' + ISNULL(a.city,'') + ' ' + ISNULL(a.state,'') + ' ' + ISNULL(a.postal_code,'')
FROM tr_theater A WHERE id = @th_no

SELECT @perf_duration_minutes = ISNULL(a.duration,150)
FROM t_perf a WHERE perf_no = @perf_no

SET @calendar_start =
CAST(DATENAME(yyyy,@perf_dt) AS VARCHAR(4)) +
--CAST(DATEPART(mm,@perf_dt) AS VARCHAR(2)) +
SUBSTRING(CONVERT(VARCHAR(10),@perf_dt,102),6,2) +
--CAST(DATEPART(dd,@perf_dt) AS VARCHAR(2)) +
SUBSTRING(CONVERT(VARCHAR(10),@perf_dt,102),9,2) +
'T' +
CAST(DATEPART(hh,@perf_dt) AS VARCHAR(2)) +
CAST(DATEPART(mi,@perf_dt) AS VARCHAR(2)) +
CAST(DATEPART(ss,@perf_dt) AS VARCHAR(2)) + '00'
SET @calendar_end =
CAST(DATENAME(yyyy,dateadd(mi,@perf_duration_minutes,@perf_dt)) AS VARCHAR(4)) +
--CAST(DATEPART(mm,dateadd(mi,120,@perf_dt)) AS VARCHAR(2)) +
SUBSTRING(CONVERT(VARCHAR(10),dateadd(mi,@perf_duration_minutes,@perf_dt),102),6,2) +
--CAST(DATEPART(dd,dateadd(mi,120,@perf_dt)) AS VARCHAR(2)) +
SUBSTRING(CONVERT(VARCHAR(10),dateadd(mi,@perf_duration_minutes,@perf_dt),102),9,2) +
'T' +
CAST(DATEPART(hh,dateadd(mi,@perf_duration_minutes,@perf_dt)) AS VARCHAR(2)) +
CAST(DATEPART(mi,dateadd(mi,@perf_duration_minutes,@perf_dt)) AS VARCHAR(2)) +
CAST(DATEPART(ss,dateadd(mi,@perf_duration_minutes,@perf_dt)) AS VARCHAR(2)) + '00'

-----------------------------------------------------
SET @google_calendar_link =
'www.google.com/.../render +
'&text=' +
CASE WHEN ISNULL(@performance_title_override,'') <> ''
THEN @performance_title_override ELSE ISNULL(@performance,'')
END +
'&dates=' + @calendar_start + '/' + @calendar_end +
'&ctz=America%2FNew_York' +
'&location=' +
CASE when @theater_display = 'Y'
THEN isnull(@theater,'') + ', ' + @theatre_address
ELSE '' END +

'&details=' +
CASE WHEN @csi_notes_display = 'Y'
THEN isnull(@csi_notes,'') + CASE WHEN ISNULL(@csi_notes,'') <> '' THEN ' - ' ELSE '' END
ELSE '' END +
CASE when isnull(@zone_section,'') <> ''
THEN @zone_section +
'<br><br>Ticket QR codes can be displayed online at tickets.shakespearetheatre.org/.../login
ELSE ''
END
SET @google_calendar_link = REPLACE(@google_calendar_link,' ','%20')
SET @google_calendar_link = '<br><a href = "' + @google_calendar_link + '">add to google calendar</a><br>'

SELECT @iCalendar_name =
CAST(ISNULL(@li_seq_no,0) AS VARCHAR(12)) + '-' +
SUBSTRING(REPLACE(ISNULL(a.lname,''),' ',''),1,12) +
'.ics'
FROM dbo.T_CUSTOMER a WHERE customer_no = @customer_no

SET @iCalendar_link = 'cms.shakespearetheatre.org/.../' + @iCalendar_name

--DECLARE @icalendar_link NVARCHAR(max)
SELECT @icalendar_text =
'BEGIN:VCALENDAR
VERSION:1.0
BEGIN:VEVENT
CATEGORIES:MEETING
STATUS:TENTATIVE
DTSTART:' + @calendar_start + '
DTEND:' + @calendar_end + '
LOCATION:' + CASE when @theater_display = 'Y'
THEN isnull(@theater,'') + ', ' + @theatre_address
ELSE '' END + '
SUMMARY:' + CASE WHEN ISNULL(@performance_title_override,'') <> ''
THEN @performance_title_override ELSE ISNULL(@performance,'')
END + '
DESCRIPTION:' + CASE WHEN @csi_notes_display = 'Y'
THEN isnull(@csi_notes,'') + CASE WHEN ISNULL(@csi_notes,'') <> '' THEN ' - ' ELSE '' END
ELSE '' END +
CASE when isnull(@zone_section,'') <> ''
THEN REPLACE(@zone_section,'<br>','\r') +
'\r\rTicket QR codes can be displayed online at tickets.shakespearetheatre.org/.../login
ELSE ''
END + '
CLASS:PRIVATE
END:VEVENT
END:VCALENDAR'

SET @iCalendar_file_name = 'C:\tessitura\icalendar\' + @iCalendar_name

exec LP_FILE_WRITE_TEXT
@file_name = @iCalendar_file_name,
@text = @icalendar_text,
@append = 0

EXEC dbo.FTP_PutFile @FTPServer = 'xxx.xxx.xxx.xxx', -- varchar(128)
@FTPUser = 'ftps.xxxx.xxx|FTPSuser', -- varchar(128)
@FTPPWD = 'xxxxxxxxx, -- varchar(128)
@FTPPath = 'iCalendar/', -- varchar(128)
@FTPFileName = @iCalendar_name, -- varchar(128)
@SourcePath = 'c:\tessitura\icalendar\', -- varchar(128)
@SourceFile = @iCalendar_name, -- varchar(128)
@workdir = '', -- varchar(128)
@ShowOutput = 'N' -- bit

--------------------

USE [impresario]
GO

/****** Object: StoredProcedure [dbo].[FTP_PutFile] Script Date: 1/17/2023 4:21:16 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create procedure [dbo].[FTP_PutFile]
@FTPServer varchar(128) ,
@FTPUser varchar(128) ,
@FTPPWD varchar(128) ,
@FTPPath varchar(128) ,
@FTPFileName varchar(128) ,
@SourcePath varchar(128) ,
@SourceFile varchar(128) ,
@workdir varchar(128),
@ShowOutput char(1) = 'N'
with execute as 'dbo' as
Set NoCount On
/* ---------------------------------------

EXEC dbo.FTP_PutFile @FTPServer = 'xxx.xxx.xxx.xxx', -- varchar(128)
@FTPUser = 'ftps.xxxxx|FTPSuser', -- varchar(128)
@FTPPWD = 'xxxxxxxx, -- varchar(128)
@FTPPath = 'iCalendar/', -- varchar(128)
@FTPFileName = 'test.ics', -- varchar(128)
@SourcePath = 'c:\tessitura\icalendar\', -- varchar(128)
@SourceFile = 'ical.txt', -- varchar(128)
@workdir = '', -- varchar(128)
@ShowOutput = 'Y'

----------------------------------------*/

DECLARE @cmd VARCHAR(1000)
DECLARE @workfilename VARCHAR(128)

select @workfilename = 'ftpcmd.txt'

-- deal with special characters for echo commands
select @FTPServer = replace(replace(replace(@FTPServer, '|', '^|'),'<','^<'),'>','^>')
select @FTPUser = replace(replace(replace(@FTPUser, '|', '^|'),'<','^<'),'>','^>')
select @FTPPWD = replace(replace(replace(@FTPPWD, '|', '^|'),'<','^<'),'>','^>')
select @FTPPath = replace(replace(replace(@FTPPath, '|', '^|'),'<','^<'),'>','^>')

select @cmd = 'echo ' + 'open ' + @FTPServer
+ ' > ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd, NO_OUTPUT
select @cmd = 'echo ' + @FTPUser
+ '>> ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd, NO_OUTPUT
select @cmd = 'echo ' + @FTPPWD
+ '>> ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd, NO_OUTPUT
select @cmd = 'echo ' + 'put ' + @SourcePath + @SourceFile + ' ' + @FTPPath + @FTPFileName
+ ' >> ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd, NO_OUTPUT
select @cmd = 'echo ' + 'quit'
+ ' >> ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd, NO_OUTPUT

select @cmd = 'ftp -s:' + @workdir + @workfilename

create table #a (id int identity(1,1), s varchar(1000))
if @ShowOutput = 'Y'
begin
insert #a
exec master..xp_cmdshell @cmd
select id, ouputtmp = s from #a
end
else
exec master..xp_cmdshell @cmd, NO_OUTPUT

alter authorization on [dbo].[FTP_PutFile] to schema owner

grant execute on [dbo].[FTP_PutFile] to [ImpUsers] as [dbo]
go

alter authorization on [dbo].[FTP_PutFile] to schema owner
go

grant execute on [dbo].[FTP_PutFile] to [ImpUsers] as [dbo]
go

grant execute on [dbo].[FTP_PutFile] to [tessitura_app] as [dbo]
go

Below is sql from my email confirmation script, which you can update as needed.  Basically, the template called by tnew executes this sql script which generates the html code.  It can also be sent to the Tessitura Processing Service via a tessitura report.

The google calendar is just a link, but the ical is actually a file which is generated, and placed on our server, then accessed by a link.

Email me if you would like the full email contribution code.

BWG

select @theatre_address = ISNULL(a.street,'') + ', ' + ISNULL(a.city,'') + ' ' + ISNULL(a.state,'') + ' ' + ISNULL(a.postal_code,'')
FROM tr_theater A WHERE id = @th_no

SELECT @perf_duration_minutes = ISNULL(a.duration,150)
FROM t_perf a WHERE perf_no = @perf_no

SET @calendar_start =
CAST(DATENAME(yyyy,@perf_dt) AS VARCHAR(4)) +
--CAST(DATEPART(mm,@perf_dt) AS VARCHAR(2)) +
SUBSTRING(CONVERT(VARCHAR(10),@perf_dt,102),6,2) +
--CAST(DATEPART(dd,@perf_dt) AS VARCHAR(2)) +
SUBSTRING(CONVERT(VARCHAR(10),@perf_dt,102),9,2) +
'T' +
CAST(DATEPART(hh,@perf_dt) AS VARCHAR(2)) +
CAST(DATEPART(mi,@perf_dt) AS VARCHAR(2)) +
CAST(DATEPART(ss,@perf_dt) AS VARCHAR(2)) + '00'
SET @calendar_end =
CAST(DATENAME(yyyy,dateadd(mi,@perf_duration_minutes,@perf_dt)) AS VARCHAR(4)) +
--CAST(DATEPART(mm,dateadd(mi,120,@perf_dt)) AS VARCHAR(2)) +
SUBSTRING(CONVERT(VARCHAR(10),dateadd(mi,@perf_duration_minutes,@perf_dt),102),6,2) +
--CAST(DATEPART(dd,dateadd(mi,120,@perf_dt)) AS VARCHAR(2)) +
SUBSTRING(CONVERT(VARCHAR(10),dateadd(mi,@perf_duration_minutes,@perf_dt),102),9,2) +
'T' +
CAST(DATEPART(hh,dateadd(mi,@perf_duration_minutes,@perf_dt)) AS VARCHAR(2)) +
CAST(DATEPART(mi,dateadd(mi,@perf_duration_minutes,@perf_dt)) AS VARCHAR(2)) +
CAST(DATEPART(ss,dateadd(mi,@perf_duration_minutes,@perf_dt)) AS VARCHAR(2)) + '00'

-----------------------------------------------------
SET @google_calendar_link =
'www.google.com/.../render +
'&text=' +
CASE WHEN ISNULL(@performance_title_override,'') <> ''
THEN @performance_title_override ELSE ISNULL(@performance,'')
END +
'&dates=' + @calendar_start + '/' + @calendar_end +
'&ctz=America%2FNew_York' +
'&location=' +
CASE when @theater_display = 'Y'
THEN isnull(@theater,'') + ', ' + @theatre_address
ELSE '' END +

'&details=' +
CASE WHEN @csi_notes_display = 'Y'
THEN isnull(@csi_notes,'') + CASE WHEN ISNULL(@csi_notes,'') <> '' THEN ' - ' ELSE '' END
ELSE '' END +
CASE when isnull(@zone_section,'') <> ''
THEN @zone_section +
'<br><br>Ticket QR codes can be displayed online at tickets.shakespearetheatre.org/.../login
ELSE ''
END
SET @google_calendar_link = REPLACE(@google_calendar_link,' ','%20')
SET @google_calendar_link = '<br><a href = "' + @google_calendar_link + '">add to google calendar</a><br>'

SELECT @iCalendar_name =
CAST(ISNULL(@li_seq_no,0) AS VARCHAR(12)) + '-' +
SUBSTRING(REPLACE(ISNULL(a.lname,''),' ',''),1,12) +
'.ics'
FROM dbo.T_CUSTOMER a WHERE customer_no = @customer_no

SET @iCalendar_link = 'cms.shakespearetheatre.org/.../' + @iCalendar_name

--DECLARE @icalendar_link NVARCHAR(max)
SELECT @icalendar_text =
'BEGIN:VCALENDAR
VERSION:1.0
BEGIN:VEVENT
CATEGORIES:MEETING
STATUS:TENTATIVE
DTSTART:' + @calendar_start + '
DTEND:' + @calendar_end + '
LOCATION:' + CASE when @theater_display = 'Y'
THEN isnull(@theater,'') + ', ' + @theatre_address
ELSE '' END + '
SUMMARY:' + CASE WHEN ISNULL(@performance_title_override,'') <> ''
THEN @performance_title_override ELSE ISNULL(@performance,'')
END + '
DESCRIPTION:' + CASE WHEN @csi_notes_display = 'Y'
THEN isnull(@csi_notes,'') + CASE WHEN ISNULL(@csi_notes,'') <> '' THEN ' - ' ELSE '' END
ELSE '' END +
CASE when isnull(@zone_section,'') <> ''
THEN REPLACE(@zone_section,'<br>','\r') +
'\r\rTicket QR codes can be displayed online at tickets.shakespearetheatre.org/.../login
ELSE ''
END + '
CLASS:PRIVATE
END:VEVENT
END:VCALENDAR'

SET @iCalendar_file_name = 'C:\tessitura\icalendar\' + @iCalendar_name

exec LP_FILE_WRITE_TEXT
@file_name = @iCalendar_file_name,
@text = @icalendar_text,
@append = 0

EXEC dbo.FTP_PutFile @FTPServer = 'xxx.xxx.xxx.xxx', -- varchar(128)
@FTPUser = 'ftps.xxxx.xxx|FTPSuser', -- varchar(128)
@FTPPWD = 'xxxxxxxxx, -- varchar(128)
@FTPPath = 'iCalendar/', -- varchar(128)
@FTPFileName = @iCalendar_name, -- varchar(128)
@SourcePath = 'c:\tessitura\icalendar\', -- varchar(128)
@SourceFile = @iCalendar_name, -- varchar(128)
@workdir = '', -- varchar(128)
@ShowOutput = 'N' -- bit

--------------------

USE [impresario]
GO

/****** Object: StoredProcedure [dbo].[FTP_PutFile] Script Date: 1/17/2023 4:21:16 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create procedure [dbo].[FTP_PutFile]
@FTPServer varchar(128) ,
@FTPUser varchar(128) ,
@FTPPWD varchar(128) ,
@FTPPath varchar(128) ,
@FTPFileName varchar(128) ,
@SourcePath varchar(128) ,
@SourceFile varchar(128) ,
@workdir varchar(128),
@ShowOutput char(1) = 'N'
with execute as 'dbo' as
Set NoCount On
/* ---------------------------------------

EXEC dbo.FTP_PutFile @FTPServer = 'xxx.xxx.xxx.xxx', -- varchar(128)
@FTPUser = 'ftps.xxxxx|FTPSuser', -- varchar(128)
@FTPPWD = 'xxxxxxxx, -- varchar(128)
@FTPPath = 'iCalendar/', -- varchar(128)
@FTPFileName = 'test.ics', -- varchar(128)
@SourcePath = 'c:\tessitura\icalendar\', -- varchar(128)
@SourceFile = 'ical.txt', -- varchar(128)
@workdir = '', -- varchar(128)
@ShowOutput = 'Y'

----------------------------------------*/

DECLARE @cmd VARCHAR(1000)
DECLARE @workfilename VARCHAR(128)

select @workfilename = 'ftpcmd.txt'

-- deal with special characters for echo commands
select @FTPServer = replace(replace(replace(@FTPServer, '|', '^|'),'<','^<'),'>','^>')
select @FTPUser = replace(replace(replace(@FTPUser, '|', '^|'),'<','^<'),'>','^>')
select @FTPPWD = replace(replace(replace(@FTPPWD, '|', '^|'),'<','^<'),'>','^>')
select @FTPPath = replace(replace(replace(@FTPPath, '|', '^|'),'<','^<'),'>','^>')

select @cmd = 'echo ' + 'open ' + @FTPServer
+ ' > ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd, NO_OUTPUT
select @cmd = 'echo ' + @FTPUser
+ '>> ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd, NO_OUTPUT
select @cmd = 'echo ' + @FTPPWD
+ '>> ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd, NO_OUTPUT
select @cmd = 'echo ' + 'put ' + @SourcePath + @SourceFile + ' ' + @FTPPath + @FTPFileName
+ ' >> ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd, NO_OUTPUT
select @cmd = 'echo ' + 'quit'
+ ' >> ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd, NO_OUTPUT

select @cmd = 'ftp -s:' + @workdir + @workfilename

create table #a (id int identity(1,1), s varchar(1000))
if @ShowOutput = 'Y'
begin
insert #a
exec master..xp_cmdshell @cmd
select id, ouputtmp = s from #a
end
else
exec master..xp_cmdshell @cmd, NO_OUTPUT

alter authorization on [dbo].[FTP_PutFile] to schema owner

grant execute on [dbo].[FTP_PutFile] to [ImpUsers] as [dbo]
go

alter authorization on [dbo].[FTP_PutFile] to schema owner
go

grant execute on [dbo].[FTP_PutFile] to [ImpUsers] as [dbo]
go

grant execute on [dbo].[FTP_PutFile] to [tessitura_app] as [dbo]
go