CREATE OR REPLACE VIEW qry_FilmReviewRating_AvgYear AS 
	SELECT
		1          		  AS intReviewYear_M,
		intFestYear           AS intReviewYear,
		AVG(bytRatingPercent) AS sngReviewYearAvg_C,
		COUNT(intReviewID)    AS intReviewYearCnt
	FROM
		tblRating INNER JOIN (
			tblReview INNER JOIN (
				tblFilm
			) ON intReviewFilmKey = intFilmID
		) ON intReviewRatingKey = intRatingID
	WHERE
		(bytRatingPercent > 0)
	GROUP BY
		intReviewYear

CREATE OR REPLACE VIEW qry_FilmPick_AvgYear AS 
	SELECT
		1         		AS intPickYear_M,
		intFestYear         AS intPickYear,
		AVG(bytPickPercent) AS sngPickYearAvg_C,
		COUNT(intPickID)    AS intPickYearCnt
	FROM
		tblFilm INNER JOIN (
			tblUserPick
		) ON intPickFilmKey = intFilmID
	WHERE
		(bytPickPercent > 0)
	GROUP BY
		intPickYear

CREATE OR REPLACE VIEW qry_FilmReviewRating_AvgFilm AS 
	SELECT
		intReviewFilmKey,
		COUNT(intReviewID)                      AS intReviewFilmCnt_V,
		AVG(bytRatingPercent)                   AS sngReviewFilmAvg_R,
		CEILING( AVG(bytRatingPercent)/10 )*10  AS intReviewFilmAvg
	FROM
		tblRating INNER JOIN (
			tblReview
		) ON intReviewRatingKey = intRatingID
	WHERE
		(bytRatingPercent > 0)
	GROUP BY
		intReviewFilmKey

CREATE OR REPLACE VIEW qry_FilmPick_AvgFilm AS 
	SELECT
		intPickFilmKey,
		COUNT(intPickID)                     AS intPickFilmCnt_V,
		AVG(bytPickPercent)                  AS sngPickFilmAvg_R,
		CEILING( AVG(bytPickPercent)/20 )*20 AS intPickFilmAvg
	FROM
		tblUserPick
	WHERE
		(bytPickPercent > 0)
	GROUP BY
		intPickFilmKey

CREATE OR REPLACE VIEW qry_FilmReviewRating_Bayesian AS 
	SELECT
		intFilmID        	AS intReviewFilmKey,
		intReviewFilmAvg	AS intReviewAvg,
		         ((sngReviewFilmAvg_R * intReviewFilmCnt_V) + (sngReviewYearAvg_C * intReviewYear_M)) / (intReviewFilmCnt_V + intReviewYear_M)          AS sngReviewBayesian,
		CEILING( ((sngReviewFilmAvg_R * intReviewFilmCnt_V) + (sngReviewYearAvg_C * intReviewYear_M)) / (intReviewFilmCnt_V + intReviewYear_M)/10 )*10  AS bytReviewBayesian
	FROM
		qry_FilmReviewRating_AvgYear INNER JOIN (
			tblFilm	INNER JOIN (
				qry_FilmReviewRating_AvgFilm
			) ON intReviewFilmKey = intFilmID
		) ON intReviewYear = intFestYear

CREATE OR REPLACE VIEW qry_FilmPick_Bayesian AS 
	SELECT
		intFilmID		AS intPickFilmKey,
		intPickFilmAvg	AS intPickAvg,
		         ((sngPickFilmAvg_R * intPickFilmCnt_V) + (sngPickYearAvg_C * intPickYear_M)) / (intPickFilmCnt_V + intPickYear_M)         AS sngPickBayesian,
		CEILING( ((sngPickFilmAvg_R * intPickFilmCnt_V) + (sngPickYearAvg_C * intPickYear_M)) / (intPickFilmCnt_V + intPickYear_M)/20 )*20 AS bytPickBayesian
	FROM
		qry_FilmPick_AvgYear INNER JOIN (
			tblFilm INNER JOIN (
				qry_FilmPick_AvgFilm
			) ON intPickFilmKey = intFilmID
		) ON intPickYear = intFestYear
	

CREATE OR REPLACE VIEW qry_FilmReviewLatest AS 
	SELECT	intReviewFilmKey, MAX(dtmReviewUpdated) AS dtmReviewUpdatedLast
	FROM		tblReview
	WHERE		YEAR(dtmReviewUpdated) = 2024
	GROUP BY	intReviewFilmKey
	

CREATE OR REPLACE VIEW qry_CountReviewFilm AS 
	SELECT	intReviewFilmKey, COUNT(intReviewID) AS intCountReviewFilm
	FROM	tblReview
	GROUP BY intReviewFilmKey
	

CREATE OR REPLACE VIEW qry_CountReviewStar AS 
	SELECT	intReviewFilmKey, COUNT(intReviewID) AS intCountReviewStar
	FROM	tblReview
	WHERE 	( (intReviewRatingKey <> 9410520) AND (intReviewRatingKey IS NOT NULL) )
	GROUP BY intReviewFilmKey
	

CREATE OR REPLACE VIEW qry_CountReviewVideo AS 
	SELECT	intReviewFilmKey, COUNT(intReviewID) AS intCountReviewVideo
	FROM	tblReview
	WHERE 	(ysnReviewVideo IS NOT NULL) AND (ysnReviewVideo<>0) -- OR (strReviewURL LIKE '%youtube.com%')
	GROUP BY intReviewFilmKey
	

CREATE OR REPLACE VIEW qry_CountFilmEmbed AS 
	SELECT	intEmbedFilmKey, COUNT(intEmbedID) AS intCountFilmEmbed
	FROM	tblFilmEmbed
	WHERE 	(TRIM(memEmbedHTM) IS NOT NULL)
	GROUP BY intEmbedFilmKey
	

CREATE OR REPLACE VIEW qry_ScoreFilm AS 
	SELECT
		IFNULL(intFestYear, 2024) AS intScoreFilmYear,
		intFilmID AS intScoreFilmKey,
		strFilename,
		strProgCode, strProgName,
		dtmUpdated,
		dtmReviewUpdatedLast,
		( IF(intMinutes > 0,             1, 0)
		+ IF(memDir 		IS NOT NULL, 1, 0)
		+ IF(strCountry 	IS NOT NULL, 1, 0)
		+ IF(strLang 		IS NOT NULL, 1, 0)
		+ IF(memCast 		IS NOT NULL, 1, 0)
		+ IF(memDesc 		IS NOT NULL, 1, 0)
		+ IF(strPresentedBy	IS NOT NULL, 1, 0)
		+ IF(ysnElgin		IS NOT NULL AND ysnElgin <> 0, 1, 0)
		+ IF(intTicketKey	IS NOT NULL AND intTicketKey <> 4027988, 1, 0)
		+ IF(intClassKey 	IS NOT NULL AND intTicketKey <> 1246296, 1, 0)
		+ IF(strHtmNewsURL	IS NOT NULL, 1, 0)
		+ IF(strHtmIMDbURL	IS NOT NULL, 1, 0)
		+ IF(strHtmFestURL	IS NOT NULL AND strImgFstOrig <> '!', 1, 0)
		+ IF(ysnNotFilm 	IS NOT NULL AND ysnNotFilm <> 0, 1, 0)
		+ IF(ysnDoc 		IS NOT NULL AND ysnDoc <> 0, 1, 0)
		+ IF(ysnAnim 		IS NOT NULL AND ysnAnim <> 0, 1, 0)
		+ IF(ysnShort 		IS NOT NULL AND ysnShort <> 0, 1, 0)
		+ IF(ysnBW 			IS NOT NULL AND ysnBW <> 0,	1, 0)
		+ IF(ysn3D 			IS NOT NULL AND ysn3D <> 0,	1, 0)
		+ IF(ysnNoDialog 	IS NOT NULL AND ysnNoDialog <> 0, 1, 0)
		+ IF(dtmRelease 	IS NOT NULL, 1, 0)
		+ IF(strImgFstOrig 	IS NOT NULL AND strImgFstOrig <> '!', 1, 0)
		+ IF(strImgAltOrig 	IS NOT NULL AND strImgAltOrig <> '!', 1, 0)
		+ IFNULL(intCountReviewStar, 0)
		+ IFNULL(intCountReviewVideo, 0)
		+ (IFNULL(intCountReviewFilm,0) - IFNULL(intCountReviewStar,0) - IFNULL(intCountReviewVideo,0))
		+ IFNULL(intCountFilmEmbed, 0)
		) AS intScoreFilm
	FROM	
	qry_FilmReviewLatest RIGHT JOIN (
		qry_CountReviewFilm RIGHT JOIN (
			qry_CountReviewStar RIGHT JOIN (
				qry_CountReviewVideo RIGHT JOIN (
					qry_CountFilmEmbed RIGHT JOIN (
						tblFilmImg RIGHT JOIN (
							tblProg RIGHT JOIN (
								tblFilm
							) ON intProgKey = intProgID
						) ON intImgFilmKey = intFilmID
					) ON qry_CountFilmEmbed.intEmbedFilmKey = intFilmID
				) ON qry_CountReviewVideo.intReviewFilmKey = intFilmID
			) ON qry_CountReviewStar.intReviewFilmKey = intFilmID
		) ON qry_CountReviewFilm.intReviewFilmKey = intFilmID
	) ON qry_FilmReviewLatest.intReviewFilmKey = intFilmID
	WHERE
		IFNULL(dtmAnnounced, 0) <= '2025-05-20'

CREATE OR REPLACE VIEW qry_ScoreYear AS 
	SELECT
		intScoreFilmYear AS intScoreMaxYear,
		MAX(intScoreFilm) AS intScoreYearMax,
		MIN(intScoreFilm) AS intScoreYearMin
	FROM
		qry_ScoreFilm
	GROUP BY
		intScoreMaxYear

CREATE OR REPLACE VIEW qry_Sitemap AS 
	SELECT
		intScoreFilmKey AS intFilmKey,
		intScoreFilmYear AS intFestYear,
		strFilename,
		strProgCode, strProgName,
		dtmUpdated,
		dtmReviewUpdatedLast,
		intScoreFilm AS intScore,
		intScoreYearMax,
		ROUND((intScoreFilm-intScoreYearMin)/(intScoreYearMax-intScoreYearMin)*100) AS sngScorePercent,
		ROUND((intScoreFilm-intScoreYearMin)/(intScoreYearMax-intScoreYearMin)*5)*20 AS intScorePercent,
		ROUND((intScoreFilm-intScoreYearMin)/(intScoreYearMax-intScoreYearMin)*5) AS intScoreBuzz,
		FORMAT( ROUND((intScoreFilm-intScoreYearMin)/(intScoreYearMax-intScoreYearMin)*5)/10 + IF(intScoreFilmYear = '2024', 0.5, 0),1) AS sngPriority
	FROM
		qry_ScoreYear RIGHT JOIN (
			qry_ScoreFilm
		) ON intScoreMaxYear = intScoreFilmYear
	ORDER BY intFestYear DESC, intScoreFilm DESC

CREATE OR REPLACE VIEW qryFilmList AS 
	SELECT
		intFilmID,
		strFilmB64,
		IF(LOCATE(LEFT(strTitle,1),'ABCDEFGHIJKLMNOPQRSTUVWXYZ'),UPPER(LEFT(strTitle,1)),'~') AS strTitleChar,
		strTitle,
		strTitleOrig,
		IFNULL(intFilmYear,2024) AS intFilmYear,
		IFNULL(intFestYear,2024) AS intFestYear,
		strFilename,
		IF(CHAR_LENGTH(strCountry)>18, CONCAT(LEFT(strCountry,20),'…'), strCountry)	AS strCountry,
		IF(CHAR_LENGTH(strLang)>18, CONCAT(LEFT(strLang,20),'…'), strLang)		AS strLang,
		IF(CHAR_LENGTH(memDir)>28, CONCAT(LEFT(memDir,28),'…'), memDir)			AS memDir,
		IF(CHAR_LENGTH(memCast)>44, CONCAT(LEFT(memCast,44),'…'), memCast)		AS memCast,
		IF(CHAR_LENGTH(memDesc)>144, CONCAT(LEFT(memDesc,144),'…'), memDesc)		AS memDesc,
		ysnElgin,
		bytTally,
		dtmAnnounced, -- datetime
		DATE_FORMAT(dtmAnnounced, '%d%b') AS strAnnounceDay,
		DATE_FORMAT(dtmAnnounced, '%M %D') AS strAnnounceDate,
		dtmUpdated,
		DATE_FORMAT(dtmUpdated,'%a.%d/%b') AS strUpdated,
		dtmRelease,
		DATE_FORMAT(dtmRelease,'%a.%d/%b') AS strRelease,
		--
		IFNULL(strHtmNewsFile,'!') AS strHtmNewsFile, IFNULL(strHtmFestFile,'!') AS strHtmFestFile,	IFNULL(strHtmIMDbURL,'!') AS strHtmIMDbURL,
		--
		IFNULL(tblFilmImg.strImgFstThmb,'!') AS strImgFstThmb,
		tblFilmImg.strImgFstURL, tblFilmImg.strImgAltThmb, tblFilmImg.strImgAltURL,
		--
		tblProg.bytProgRank, tblProg.strProgCode, tblProg.strProgName, tblProg.strProgImg, tblProg.strProgRGB,		
		--
		tblClass.bytClassRank, tblClass.strClassCode, tblClass.strClassDesc, tblClass.strClassImg,
		--
		tblTicket.bytTicketRank, tblTicket.strTicketType, tblTicket.strTicketSymbol, tblTicket.strTicketDesc, tblTicket.strTicketImg,
		--
		IFNULL(bytReviewBayesian,0) AS intReviewAvgPercent,
		tblRating.strRatingStars, tblRating.strRatingDesc, tblRating.strRatingStarImg,
		IF(ysnPulled, '!','OK') AS ysnPulled
	FROM
		tblRating RIGHT JOIN (
			tblTicket RIGHT JOIN (
				tblClass RIGHT JOIN (
					tblProg RIGHT JOIN (
						tblFilmImg RIGHT JOIN (
							qry_FilmReviewRating_Bayesian RIGHT JOIN (
								tblFilm
							) ON intReviewFilmKey = intFilmID
						) ON intImgFilmKey = intFilmID
					) ON intProgID = intProgKey
				) ON intClassID = IFNULL(intClassKey,1246296)
			) ON intTicketID = IFNULL(intTicketKey,4027988) 
		) ON bytRatingPercent = IFNULL(bytReviewBayesian,0)
	WHERE
		IFNULL(dtmAnnounced, 0) <= '2025-05-20'
	

CREATE OR REPLACE VIEW qryFilmDetail AS 
	SELECT
		intScorePercent AS intBuzzAvgPercent,
		-- IFNULL(intScore,0) AS intBuzzAvgPercent,
		-- IFNULL(bytPickBayesian,0) AS intBuzzAvgPercent,
		--
		IF(LOCATE(LEFT(strTitle,1),'ABCDEFGHIJKLMNOPQRSTUVWXYZ'),UPPER(LEFT(strTitle,1)),'~') AS strTitleChar,
		--
		tblFilm.*,
		IF(intMinutes>59,CONCAT(TIME_FORMAT(SEC_TO_TIME(intMinutes * 60), '%k:%i'), ' (',intMinutes, 'min)'),TIME_FORMAT(SEC_TO_TIME(intMinutes * 60), '%k:%i')) AS strRuntime,
		tblFilmImg.*, tblProg.*, tblClass.*, tblTicket.*, tblRating.*
	FROM
		tblRating RIGHT JOIN (
			tblTicket RIGHT JOIN (
				tblClass RIGHT JOIN (
					tblProg RIGHT JOIN (
						tblFilmImg RIGHT JOIN (
							qry_Sitemap RIGHT JOIN (
								-- qry_FilmPick_Bayesian RIGHT JOIN (
								tblFilm
								-- ) ON intPickFilmKey = intFilmID
							) ON intFilmKey = intFilmID
						) ON tblFilmImg.intImgFilmKey = intFilmID
					) ON tblProg.intProgID = intProgKey
				) ON tblClass.intClassID = IFNULL(intClassKey,1246296)
			) ON tblTicket.intTicketID = IFNULL(intTicketKey,4027988) 
		-- ) ON tblRating.bytRatingPercent = IFNULL(bytPickBayesian,0)
		) ON tblRating.bytRatingPercent = IFNULL(intScorePercent,0)
	

CREATE OR REPLACE VIEW qryReview AS 
	SELECT		tblReview.*,
			IF(ysnReviewOfficial IS NOT NULL AND ysnReviewOfficial<>0, TRUE, FALSE) AS ysnOfficial,
			IF(ysnReviewVideo    IS NOT NULL AND ysnReviewVideo<>0,    TRUE, FALSE) AS ysnVideo,
			IF(ysnReviewPhoto    IS NOT NULL AND ysnReviewPhoto<>0,    TRUE, FALSE) AS ysnPhoto,
			tblRating.*
	FROM		tblReview LEFT JOIN tblRating 
			ON intReviewRatingKey = intRatingID
	ORDER BY	bytRatingPercent DESC, ysnOfficial DESC, ysnVideo DESC, ysnPhoto DESC, strReviewURL
	

CREATE OR REPLACE VIEW qrySearch AS 
	SELECT		tblSearch.*, tblSearchPost.*
	FROM		tblSearch LEFT JOIN (
					tblSearchPost
				) ON intPostSearchKey = intSearchID
	WHERE		NOT ysnSearchDisable
	ORDER BY	bytSearchRank

CREATE OR REPLACE VIEW qry_ReviewURL AS 
	SELECT
		TRIM(
			REPLACE(
				REPLACE(
					SUBSTRING(
						SUBSTRING(strReviewURL FROM INSTR(strReviewURL,'://')+3)
					FROM
						1
					FOR
						INSTR(
							SUBSTRING(strReviewURL FROM INSTR(strReviewURL,'://')+3)
						,'/')
					)
				,'/','')
			,'www.','')
			)
	AS		strURL
	FROM	tblReview

CREATE OR REPLACE VIEW qry_ReviewURLcount AS 
	SELECT
		strURL,
		COUNT(strURL) AS intURL
	FROM qry_ReviewURL
	WHERE ((strURL NOT LIKE '%blogspot%') AND (strURL NOT LIKE '%wordpress%'))
	GROUP BY strURL
	ORDER BY intURL DESC
	LIMIT 47

CREATE OR REPLACE VIEW qry_ReviewURLlength AS 
	SELECT
		strURL,
		CHAR_LENGTH(strURL) AS intURL
	FROM qry_ReviewURL
	WHERE ((strURL NOT LIKE '%blogspot%') AND (strURL NOT LIKE '%wordpress%'))
	GROUP BY strURL
	ORDER BY intURL DESC
	LIMIT 47

CREATE OR REPLACE VIEW qryVenueScreen AS 
	SELECT
		tblScreen.*,
		tblVenue.*
	FROM
		tblScreen LEFT JOIN tblVenue ON intScreenVenueKey = intVenueID
	

CREATE OR REPLACE VIEW qryEvent AS 
	SELECT
			ADDDATE('2025-09-01 00:00:00', intEventDays) AS dtmEvent,
			DATEDIFF(ADDDATE('2025-09-01 00:00:00', intEventDays), CURDATE()) AS intDateDiff,
			DATE_FORMAT(ADDDATE('2025-09-01 00:00:00', intEventDays),'%W, %M %D') AS strEventDay,
			DATE_FORMAT(ADDDATE('2025-09-01 00:00:00', intEventDays),'%a.%d.%b') AS strEventDate,
			tblEvent.*
	FROM		tblEvent
	ORDER BY	intEventDays
	

CREATE OR REPLACE VIEW qry_RSS AS 
	SELECT	intFilmID, strFilmB64, intFestYear, strTitle, strFilename, memDesc,
			dtmAnnounced, dtmAdded, dtmUpdated, dtmReviewUpdatedLast,
			strImgFstThmb, strImgFstURL,
			strImgAltThmb, strImgAltURL
	FROM	
			qry_FilmReviewLatest RIGHT JOIN (
				tblFilm	INNER JOIN tblFilmImg ON intImgFilmKey = intFilmID
			) ON intReviewFilmKey = intFilmID
	WHERE
			(intFestYear = 2024) AND (IFNULL(dtmAnnounced, 0) <= '2025-05-20')
	ORDER BY
			strTitle
	

CREATE OR REPLACE VIEW qry_RSSupdate AS 
	SELECT	intFestYear,
			MAX(dtmAnnounced) AS dtmAnnounced,
			MAX(dtmAdded) AS dtmAdded,
			MAX(dtmUpdated) AS dtmUpdated,
			MAX(dtmReviewUpdatedLast) AS dtmReviewUpdatedLast
	FROM
			qry_FilmReviewLatest RIGHT JOIN (
				qry_CountReviewVideo RIGHT JOIN (
					qry_CountFilmEmbed RIGHT JOIN (
						tblFilmImg RIGHT JOIN (
							tblFilm
						) ON intImgFilmKey = intFilmID
					) ON qry_CountFilmEmbed.intEmbedFilmKey = intFilmID
				) ON qry_CountReviewVideo.intReviewFilmKey = intFilmID
			) ON qry_FilmReviewLatest.intReviewFilmKey = intFilmID
	WHERE
			(intFestYear = 2024)
				AND
			(IFNULL(dtmAnnounced, 0) <= '2025-05-20')
	ORDER BY
			strTitle
	

CREATE OR REPLACE VIEW qry_ODBC AS 
	SELECT
		(intFilmID) AS id,
		(strTitleOrig) AS title,
		CAST(
			LOWER(
				REPLACE(
					REPLACE(strHtmFestURL, CONCAT('http://tiff.net/filmsandschedules/festival/',intFestYear,'/'),''),
						CONCAT('http://www.tiff.net/filmsandschedules/festival/',intFestYear,'/'),'')
		) AS CHAR) AS tiff_id,
		LOWER(strHtmFestURL) AS tiff_url,
		LOWER(strHtmFestFile) AS tiff_htm,		
		CAST(
				CONCAT('http://TOfilmfest.ca/films/?by=Film&at=', strFilename, '&yr=', intFestYear, '#', strFilename)
		AS CHAR) AS tofilmfest_url,
		IF(strImgFstFile IS NOT NULL AND strImgFstFile <> '!', strImgFstFile, strImgAltFile) AS tofilmfest_img,
		(strHtmIMDbURL) AS imdb_url
	FROM
		tblFilmImg INNER JOIN tblFilm ON intImgFilmKey = intFilmID
	WHERE
		(intFestYear = 2024) AND (IFNULL(dtmAnnounced, 0) <= '2025-05-20')
	

Audience buzz

LanguageUnknown

CountryUnknown

Search

YouTube IMDb Wiki Variety ScrDaily HReporter Twitch TheStar IndieWire BeyondH Ion Rotten MrQE Cannes Twitter Ask Yahoo Google images.Google video.Google vimeo