Schoolbase to Firefly calendar import config file. This query will not include any events with the word "Prov" (for provisional) in the description and will also deal with events that have no end time, by creating an end time that is the start time plus 1 minute. It will also sanitise event description etc by replacing certain web unfriendly cha…

sqlcalendar.config

<databases>
  <!-- Schoolbase Calendar -->  
  <database name="Schoolbase Calendar" friendlyName="Schoolbase Calendar">
    <connectionString>Server=YOUR_SQL_SERVER;Database=YOUR_SCHOOLBASE_DATABASE;User ID=YOUR_DB_USERID;Password=YOUR_DB_PASSWORD;Trusted_Connection=False;</connectionString>
    
    <!-- get categories list.  Inputs NONE.  Outputs: name -->
    <getCategoriesQuery>
      <![CDATA[  	   
			  SELECT distinct(eventtype) AS [name] from Events Ev inner join EventType Et on (ev.evtypeident = et.evtypeident)
		  ]]>
  	</getCategoriesQuery>
  
    <!-- get calendar event list.  Inputs @start, @end, @categories, @username, @isguest.  Outputs: subject, eventStart, eventEnd, location, description, url, keywords -->
    <getEventsQuery localDateTime="yes">
      <![CDATA[	
  		  SELECT TOP (100) PERCENT
  			et.eventtype,
  			REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(EvName, ',', ' '), '&', 'and'), '/', '-'), '', ''), '', ' '), '', ''), '"', ''''), '  ',
  			' ') AS subject,
  			CONVERT(datetime, LEFT(CONVERT(varchar, CONVERT(date, CalDate)), 10) + ' ' + LEFT(CONVERT(varchar, CONVERT(time, ISNULL(EvTime, CalDate))), 8)) AS [eventStart],
  			CONVERT(datetime, LEFT(CONVERT(varchar, CONVERT(date, CalDateEnd)), 10) + ' ' + LEFT(CONVERT(varchar, CONVERT(time, isnull(ISNULL(EvTimeEnd, DATEADD(minute, 1, EvTime)),CalDate))), 8)) AS [eventEnd],
  			REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(isnull(EvDetails, '') + ' (last updated: ' + CONVERT(varchar(10), GETDATE(), 103)
  			+ ' at ' + CONVERT(varchar(5), GETDATE(), 108) + ')', ',', ' '), '&', 'and'), '/', '-'), '', ''), '', ' '), '', ''), '"', ''''), '  ', ' ') AS [description],
  			REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(isnull(EvVenue, ''), ',', ' '), '&', 'and'), '/', '-'), '', ''), '', ' '), '', ''), '"', ''''), '  ', ' ') AS [location],
  			et.eventtype AS keywords
  			FROM dbo.Events Ev
  			INNER JOIN EventType Et
  				ON (ev.evtypeident = et.evtypeident)
  			WHERE CalDate >= @start
  			AND
  			CalDateEnd <= @end
  			AND
  			(@all_categories = 1 OR et.eventtype IN (@categories))
  			AND (LOWER(isnull(EvDetails,'')) NOT LIKE '%prov%')
  			ORDER BY caldate
      ]]>      
    </getEventsQuery>
    
  </database>
</databases>