Garbage Collection Calendar SQL Code

This page contains the code that I used to create the iCalendar files for the Ottawa Garbage Collection Calendar project. I’m posting it here in hopes that it can be re-used for the London garbage project.

I did the entire thing in MS SQL. Definitely not the only way (or even the most logical way) of doing it, but I had SQL installed on my computer, and knew I could write the stored procedure quickly.

Table Create Statements

CREATE TABLE [dbo].[GCC_CALENDAR](

 [ZoneID] [char](1) NOT NULL,

 [CollectionTypeCode] [char](3) NOT NULL,

 [StartDate] [datetime] NOT NULL,

 [EndDate] [datetime] NOT NULL,

 [WeekInterval] [tinyint] NOT NULL

)

CREATE TABLE [dbo].[GCC_COLLECTIONTYPE](
 [CollectionTypeCode] [char](3) NOT NULL,
 [CollectionTypeNameENG] [varchar](100) NOT NULL,
 [CollectionTypeNameFRA] [varchar](100) NOT NULL,

 PRIMARY KEY (CollectionTypeCode)
)

The Data

Collection Date Ranges – This file contains the date ranges for each type of garbage/recycling collection along with the interval in weeks when this collection occurs. For example, during some times of the year green bins are collected every week, others only every two weeks.
Collection Type Codes – This file contains the mapping of Collection Types found in the date range file to the label used on the calendar (ex: Blue Box, Green Bin, etc.)

SQL Code to Generate iCal Files

NOTE: The code is wider than the template I’m using for this blog. When cut-and-paste from here, you’ll see the entire procedure.

CREATE FUNCTION [dbo].[fnGetDatesforAday]

-- Add the parameters for the function here

@DtFrom DATETIME,

@DtTo DATETIME,

@DayName VARCHAR(12)

RETURNS @DateList TABLE ([Day] varchar(20),Dt datetime)

AS

BEGIN

IF NOT (@DayName = 'Monday' OR @DayName = 'Sunday' OR  @DayName = 'Tuesday' OR @DayName = 'Wednesday' OR @DayName = 'Thursday'  OR @DayName = 'Friday' OR @DayName = 'Saturday')

BEGIN

--Error Insert the error message and return

INSERT INTO @DateList

SELECT 'Invalid Day',NULL AS DAT

RETURN

END

DECLARE @TotDays INT

DECLARE @CNT INT

SET @TotDays =  DATEDIFF(DD,@DTFROM,@DTTO)-- [NO OF DAYS between two dates]

SET @CNT = 0

WHILE @TotDays >= @CNT          -- repeat for all days

BEGIN

-- Pick each single day and check for the day needed

IF DATENAME(DW, (@DTTO - @CNT)) = @DAYNAME

BEGIN

INSERT INTO @DateList

SELECT @DAYNAME,(@DTTO - @CNT) AS DAT

END

SET @CNT = @CNT + 1

END

RETURN

END

GO


CREATE FUNCTION [dbo].[WeeklyDays]
(
@STARTDATE datetime, --the start date of the appointment
@CHECKDATE datetime, --the date we’re checking
@INTERVAL int --the interval
)
RETURNS bit
AS
BEGIN
DECLARE @WDIFF int,
@RESULT bit
SET @WDIFF = DATEDIFF(ww,@STARTDATE,@CHECKDATE)
SET @RESULT = 0
IF @WDIFF%@INTERVAL = 0
SET @RESULT = 1
RETURN @RESULT
END

GO

CREATE PROCEDURE usp_gcc_ical @zone CHAR(1), @dayofweek CHAR(2), @language CHAR(2)

AS

BEGIN

SET NOCOUNT ON

DECLARE @DayName VARCHAR(12)

DECLARE @DayNameFR VARCHAR(12)

DECLARE @StartDate DATETIME

DECLARE @EndDate DATETIME

DECLARE @WeekInterval TINYINT

DECLARE @CollectionTypeCode CHAR(3)

IF @DayOfWeek = 1 SET @DayName = 'Monday'

IF @DayOfWeek = 2 SET @DayName = 'Tuesday'

IF @DayOfWeek = 3 SET @DayName = 'Wednesday'

IF @DayOfWeek = 4 SET @DayName = 'Thursday'

IF @DayOfWeek = 5 SET @DayName = 'Friday'

IF @DayOfWeek = 6 SET @DayName = 'Saturday'

IF @DayOfWeek = 7 SET @DayName = 'Sunday'

IF @DayOfWeek = 1 SET @DayNameFR = 'Lundi'

IF @DayOfWeek = 2 SET @DayNameFR = 'Mardi'

IF @DayOfWeek = 3 SET @DayNameFR = 'Mercredi'

IF @DayOfWeek = 4 SET @DayNameFR = 'Jeudi'

IF @DayOfWeek = 5 SET @DayNameFR = 'Vendredi'

IF @DayOfWeek = 6 SET @DayNameFR = 'Samdi'

IF @DayOfWeek = 7 SET @DayNameFR = 'Dimanche'

CREATE TABLE #COLLECTION_DAYS (

 CollectionTypeCode CHAR(3),

 CollectionDate DATETIME,

 Delayed BIT

/* Weekly Pickups */

DECLARE cDateRanges CURSOR FOR

SELECT StartDate, EndDate, WeekInterval, CollectionTypeCode FROM dbo.GCC_CALENDAR WHERE ZoneID = @Zone AND CollectionTypeCode IN('BLU', 'BLA', 'GRB', 'ELW', 'LYW', 'CHR')

OPEN cDateRanges

FETCH NEXT FROM cDateRanges INTO @StartDate, @EndDate, @WeekInterval, @CollectionTypeCode

WHILE @@FETCH_STATUS = 0

BEGIN

 INSERT INTO #COLLECTION_DAYS( CollectionTypeCode ,  CollectionDate , Delayed)

 SELECT @CollectionTypeCode, dt, 0

 FROM dbo.fnGetDatesforAday(@StartDate, @EndDate, @DayName)

 WHERE dbo.WeeklyDays(@StartDate, Dt, @WeekInterval) = 1

 FETCH NEXT FROM cDateRanges INTO @StartDate, @EndDate, @WeekInterval, @CollectionTypeCode

END

CLOSE cDateRanges

DEALLOCATE cDateRanges

/* Insert Delays */

DECLARE cDateRanges CURSOR FOR

SELECT StartDate FROM dbo.GCC_CALENDAR WHERE ZoneID = @Zone AND CollectionTypeCode = 'NGC'

OPEN cDateRanges

FETCH NEXT FROM cDateRanges INTO @StartDate

WHILE @@FETCH_STATUS = 0

BEGIN

 IF DATENAME(dw, @StartDate) = 'Monday'

 BEGIN

 INSERT INTO #COLLECTION_DAYS ( CollectionTypeCode , CollectionDate , Delayed)

 SELECT 'NGC', CollectionDate, 0

 FROM #COLLECTION_DAYS AS cd

 WHERE DATEDIFF(dd, @StartDate, CollectionDate) BETWEEN 0 AND 4

 UPDATE #COLLECTION_DAYS

 SET CollectionDate=DATEADD(dd, 1, CollectionDate),

 Delayed = 1

 WHERE CollectionTypeCode <> 'NGC' AND DATEDIFF(dd, @StartDate, CollectionDate) BETWEEN 0 AND 4

END

 IF DATENAME(dw, @StartDate) = 'Tuesday'

 BEGIN

 INSERT INTO #COLLECTION_DAYS ( CollectionTypeCode , CollectionDate , Delayed)

 SELECT 'NGC', CollectionDate, 0

 FROM #COLLECTION_DAYS AS cd

 WHERE DATEDIFF(dd, @StartDate, CollectionDate) BETWEEN 0 AND 3

 UPDATE #COLLECTION_DAYS

 SET CollectionDate=DATEADD(dd, 1, CollectionDate),

 Delayed = 1

 WHERE CollectionTypeCode <> 'NGC' AND DATEDIFF(dd, @StartDate, CollectionDate) BETWEEN 0 AND 3

END

 IF DATENAME(dw, @StartDate) = 'Wednesday'

 BEGIN

 INSERT INTO #COLLECTION_DAYS ( CollectionTypeCode , CollectionDate , Delayed)

 SELECT 'NGC', CollectionDate, 0

 FROM #COLLECTION_DAYS AS cd

 WHERE DATEDIFF(dd, @StartDate, CollectionDate) BETWEEN 0 AND 2

 UPDATE #COLLECTION_DAYS

 SET CollectionDate=DATEADD(dd, 1, CollectionDate),

 Delayed = 1

 WHERE CollectionTypeCode <> 'NGC' AND DATEDIFF(dd, @StartDate, CollectionDate) BETWEEN 0 AND 2

END

 IF DATENAME(dw, @StartDate) = 'Thursday'

 BEGIN

 INSERT INTO #COLLECTION_DAYS ( CollectionTypeCode , CollectionDate , Delayed)

 SELECT 'NGC', CollectionDate, 0

 FROM #COLLECTION_DAYS AS cd

 WHERE DATEDIFF(dd, @StartDate, CollectionDate) BETWEEN 0 AND 1

 UPDATE #COLLECTION_DAYS

 SET CollectionDate=DATEADD(dd, 1, CollectionDate),

 Delayed = 1

 WHERE CollectionTypeCode <> 'NGC' AND DATEDIFF(dd, @StartDate, CollectionDate) BETWEEN 0 AND 1

END

 IF DATENAME(dw, @StartDate) = 'Friday'

 BEGIN

 INSERT INTO #COLLECTION_DAYS ( CollectionTypeCode , CollectionDate , Delayed)

 SELECT 'NGC', CollectionDate, 0

 FROM #COLLECTION_DAYS AS cd

 WHERE DATEDIFF(dd, @StartDate, CollectionDate) = 0

 UPDATE #COLLECTION_DAYS

 SET CollectionDate=DATEADD(dd, 1, CollectionDate),

 Delayed = 1

 WHERE CollectionTypeCode <> 'NGC' AND DATEDIFF(dd, @StartDate, CollectionDate) = 0

END

 FETCH NEXT FROM cDateRanges INTO @StartDate

END

CLOSE cDateRanges

DEALLOCATE cDateRanges

/* Special Dates */

DECLARE cDateRanges CURSOR FOR

SELECT StartDate, EndDate, WeekInterval, CollectionTypeCode FROM dbo.GCC_CALENDAR WHERE ZoneID = @Zone AND CollectionTypeCode IN('GAW', 'HHW')

OPEN cDateRanges

FETCH NEXT FROM cDateRanges INTO @StartDate, @EndDate, @WeekInterval, @CollectionTypeCode

WHILE @@FETCH_STATUS = 0

BEGIN

 INSERT INTO #COLLECTION_DAYS( CollectionTypeCode,  CollectionDate, Delayed)

 SELECT @CollectionTypeCode, @StartDate, 0

 IF @EndDate <> @StartDate

 BEGIN

 INSERT INTO #COLLECTION_DAYS( CollectionTypeCode,  CollectionDate, Delayed)

 SELECT @CollectionTypeCode, @EndDate, 0

END

 FETCH NEXT FROM cDateRanges INTO @StartDate, @EndDate, @WeekInterval, @CollectionTypeCode

END

CLOSE cDateRanges

DEALLOCATE cDateRanges

DECLARE cOutput CURSOR FOR

SELECT DISTINCT CASE @Language WHEN 'FR' THEN gc.CollectionTypeNameFRA ELSE gc.CollectionTypeNameENG END AS CollectionTypeName, CollectionDate, Delayed

FROM #COLLECTION_DAYS AS cd

INNER JOIN dbo.GCC_COLLECTIONTYPE AS gc ON cd.CollectionTypeCode = gc.CollectionTypeCode

ORDER BY CollectionDate

OPEN cOutput

DECLARE @CollectionTypeName VARCHAR(100)

DECLARE @DelayedBit TINYINT

PRINT 'BEGIN:VCALENDAR'

IF @Language = 'FR'

BEGIN

 PRINT 'PRODID:-//Shawn M. Hooper (mail@shawnhooper.ca)//Calendrier de la collecte de recyclage et déchets du Ville d''Ottawa - Zone ' + @Zone + ' - ' + @DayName + '//' + @Language

END

ELSE

BEGIN

 PRINT 'PRODID:-//Shawn M. Hooper (mail@shawnhooper.ca)//Recycling and Garbage Collection Calendar for the City of Ottawa - Zone ' + @Zone + ' - ' + @DayName + '//' + @Language

END

PRINT 'VERSION:2.0'

PRINT 'CALSCALE:GREGORIAN'

FETCH NEXT FROM cOutput INTO @CollectionTypeName, @StartDate, @DelayedBit

WHILE @@FETCH_STATUS = 0

BEGIN

 PRINT 'BEGIN:VEVENT'

 PRINT 'DTSTART;VALUE=DATE:' + CAST(DATEPART(YEAR, @StartDate) AS VARCHAR) + REPLICATE('0', 2- LEN(CAST(DATEPART(MONTH, @StartDate) AS VARCHAR)))+CAST(DATEPART(MONTH, @StartDate) AS VARCHAR) + REPLICATE('0', 2- LEN(CAST(DATEPART(DAY, @StartDate) AS VARCHAR)))+CAST(DATEPART(DAY, @StartDate) AS VARCHAR)

 PRINT 'DTEND;VALUE=DATE:' + CAST(DATEPART(YEAR, @StartDate) AS VARCHAR) + REPLICATE('0', 2- LEN(CAST(DATEPART(MONTH, @StartDate) AS VARCHAR)))+CAST(DATEPART(MONTH, @StartDate) AS VARCHAR) + REPLICATE('0', 2- LEN(CAST(DATEPART(DAY, @StartDate) AS VARCHAR)))+CAST(DATEPART(DAY, @StartDate) AS VARCHAR)

 IF @DelayedBit = 1

 PRINT 'SUMMARY:' + @CollectionTypeName + CASE @Language WHEN 'FR' THEN ' (retardée)' ELSE ' (delayed)' END

 ELSE

 PRINT 'SUMMARY:' + @CollectionTypeName

 PRINT 'END:VEVENT'

 FETCH NEXT FROM cOutput INTO @CollectionTypeName, @StartDate, @DelayedBit

END

PRINT 'END:VCALENDAR'

CLOSE cOutput

DEALLOCATE cOutput

DROP TABLE #COLLECTION_DAYS

END

Creating the iCal File

To create the Zone A calendar for Monday pickups, execute the stored procedure:

EXEC usp_gcc_ical @zone=’A', @language=’EN’, @dayofweek=1

Character Encoding

The first time I ran these files and tried importing them into Google Calendar, I ended up with a file of weird characters in place of the accents in the French files. My solution was to use the Stringconverter tool to on the outputted .ics files. These are the versions that are posted for download.

http://www.beernut.ca/jim/ Jim Sellers

I had a similar problem with accented chars. Make sure for the ics file your web server specifies the encoding. e.g. your response should have the http header:
Content-Type: text/calendar; charset=utf-8

Yours does not.
$ curl –head http://www.shawnhooper.ca/projects/ottawa-garbage-ical/gcc_a_tuesday.ics
HTTP/1.1 200 OK
…
Content-Type: text/calendar

Just add it to your .htaccess file and you should be okay.
http://www.beernut.ca/jim/archives/005326.html
http://www.shawnhooper.ca/ Shawn

Thanks Jim, I’ll take a look at that. I’m more of a .NET/Windows guy, so sometimes the Apache options elude me!
http://www.shawnhooper.ca/ Shawn

Thanks Jim, I’ll take a look at that. I’m more of a .NET/Windows guy, so sometimes the Apache options elude me!

Brain Dump

A blog of local news, geeky findings, and random things that interest me

Garbage Collection Calendar SQL Code

Table Create Statements

The Data

SQL Code to Generate iCal Files

Creating the iCal File

Character Encoding

Contact Me

Projects

My Recent Tweets