Guest Events

SELECT DISTINCT ACA.int_actor_type_id
			FROM tbl_access_control_rules ACR
			INNER JOIN tbl_access_control_allow ACA ON ACR.int_access_rule_id = ACA.int_access_rule_id
			WHERE
				
					str_fuse_action LIKE ? 
				AND ISNULL(ACA.int_actor_type_id,'')<>''
				AND ACA.bit_allow = 1

SET NOCOUNT ON

	DECLARE @UsesRefundRequest CHAR(1)

	SET @UsesRefundRequest = 0
	SET @UsesRefundRequest = (
			SELECT 1
			FROM RefundPaymentMethods
			WHERE RefundPaymentMethodID = 7
				AND STATUS = 'A'
			)

	SELECT TOP 1 rg.RegionID
		, rg.NAME
		, rg.Address1
		, rg.Address2
		, rtrim(rg.City) + ', ' + rg.STATE + '  ' + rg.Zip AS CSZ
		, rg.Phone1
		, rg.SiteTitle
		, rg.Footer
		/**/
		, rg.prefooter
		, rg.brandingfooter
		/**/
		, rg.MetaText
		, rg.EmailAux4
		, IsNull((
				SELECT Fee
				FROM Products
				WHERE Code = 'REGFEE'
					AND STATUS = 'A'
					AND RegionID = rg.RegionID
				), 0.00) AS RegistrationFee
		, IsNull((
				SELECT Fee
				FROM Products
				WHERE Code = 'DEFERRED_PAYMENT_FEE'
					AND STATUS = 'A'
					AND RegionID = rg.RegionID
				), 0.00) AS deferred_payment_fee
		, fc.*
		, @UsesRefundRequest AS UsesRefundRequest
		, rg.privacy_data_mask_id
		, rg.str_google_verify
		, rg.str_yahoo_verify
		, rg.str_hubspot_portal_id
		, rg.str_hubspot_salog_version
		, rg.str_hubspot_ppa
		, rg.str_genoo_api_key
		, rg.str_genoo_tracking_code
		, rg.TimeZoneHourAdjustment
		/**/
		/**/
		/**/
		, e.str_erp_name
		/**/
		/**/
		, CAST(DecryptByKeyAutoCert(cert_id('LumensCert'), NULL, ec.varbin_erp_password) AS NVARCHAR(500)) AS str_erp_password_decrypted
		/**/
		/**/
		, rg.theURL
		, rg.URLText
		, rg.str_analytics_linked_domains
		, rg.GTMContainerID
		, rg.str_social_media_sharing
		, ec.*
		, (
			SELECT TOP 1 Description
			FROM PaymentMethods
			WHERE PaymentMethodID >= 200
				AND STATUS = 'A'
		) AS str_internal_payment_method_name
	FROM FeatureConfiguration fc
	INNER JOIN Regions rg ON fc.RegionID = rg.RegionID
	LEFT JOIN tbl_erp e ON fc.int_erp_id = e.int_erp_id
	LEFT JOIN tbl_erp_config ec ON ec.str_erp_url <> ''
	WHERE rg.STATUS = 'A'

SELECT regionId,orgID
				FROM featureConfigurationQry
				WHERE baseurl LIKE '%testingapi.augusoft.net%'

SET NOCOUNT ON

			SELECT OrgID
				, RegionID
				, MenuItemID
				, MenuItemType
				, MainLevel
				, SubLevel
				
				,MainLevelSortOrder
				, SubLevelSortOrder
				, 
					SiteURL
				
				, AccessLevel
				, IsNull(DisplayCriteria_1, '') AS DisplayCriteria_1
				
				, IsNull(DisplayValue_1, '') AS DisplayValue_1
				, IsNull(DisplayCriteria_2, '') AS DisplayCriteria_2
				, IsNull(DisplayValue_2, '') AS DisplayValue_2
				, Condition
				, LTRIM(RTRIM(IsNull(NewWindowName, ''))) AS NewWindowName
				, m.Label AS UniqueLable
				
				, CASE
					WHEN str_user_nav_label IS NULL
						THEN CASE
								WHEN MenuItemType != 'Lnk'
									AND SiteURL = 'index.cfm?fuseaction=9440'
									THEN Replace(Replace(Replace(Replace(m.Label, '\', '\\'), '''', '\'''), '"', '\"'), 'Membership', 'Membership')
								WHEN MenuItemType != 'Lnk'
									AND SiteURL = 'index.cfm?fuseaction=9620'
									THEN Replace(Replace(Replace(Replace(m.Label, '\', '\\'), '''', '\'''), '"', '\"'), 'Member', 'Member')
										/*BEGIN NEO 00106  20 FEB 2015 Student Donations UC-02 */
								WHEN m.Label LIKE 'Donate'
									THEN Replace(m.Label, 'Donate', 'Donate')
										/*END NEO 00106  20 FEB 2015 Student Donations UC-02 */
								WHEN m.Label LIKE 'MERCHANDISE'
									THEN Replace(m.Label, 'MERCHANDISE', 'Merchandise')
								WHEN m.UniqueLabel = 'registration/scholarships' OR m.UniqueLabel = 'myscholarships'
									THEN Replace(m.Label, 'Scholarships', 'Scholarship')
								ELSE Replace(Replace(Replace(m.Label, '\', '\\'), '''', '\'''), '"', '\"')
								END
					ELSE
						/*BEGIN NEO 00106  20 FEB 2015 Student Donations UC-02 */
						CASE
							WHEN str_menu_label LIKE 'Donate'
								THEN (str_user_nav_label)
							ELSE str_user_nav_label
							END
						/*END NEO 00106  20 FEB 2015 Student Donations UC-02 */
					END AS Label
				
				, CASE
					WHEN str_user_nav_label IS NULL
						THEN CASE
								WHEN MenuItemType != 'Lnk'
									AND SiteURL = 'index.cfm?fuseaction=9440'
									THEN Replace(m.Label, 'Membership', 'Membership')
								WHEN MenuItemType != 'Lnk'
									AND SiteURL = 'index.cfm?fuseaction=9620'
									THEN Replace(m.Label, 'Member', 'Member')
									/*BEGIN NEO 00106  20 FEB 2015 Student Donations UC-02 */
								WHEN m.Label LIKE 'Donate'
									THEN Replace(m.Label, 'Donate', 'Donate')
									/*END NEO 00106  20 FEB 2015 Student Donations UC-02 */
								WHEN m.Label LIKE 'MERCHANDISE'
									THEN Replace(m.Label, 'MERCHANDISE', 'Merchandise')
								WHEN m.UniqueLabel = 'registration/scholarships' OR m.UniqueLabel = 'myscholarships'
									THEN Replace(m.Label, 'Scholarships', 'Scholarship')
								ELSE m.Label
								END
					ELSE
						/*BEGIN NEO 00106  20 FEB 2015 Student Donations UC-02 */
						CASE
							WHEN str_menu_label LIKE 'Donate'
								THEN dbo.fun_strip_html((str_user_nav_label))
							ELSE dbo.fun_strip_html(str_user_nav_label)
							END
						/*END NEO 00106  20 FEB 2015 Student Donations UC-02 */
					END AS LabelNoHTML 
				
				, ISNULL(int_parent_id,0) as int_parent_id
				, int_sort_order
				, m.UniqueLabel
				, m.bit_default_behavior
			FROM MenuItems m
			LEFT JOIN tbl_user_nav_menu_label u ON m.MenuItemID = u.int_menu_item_id
			WHERE STATUS = 'A'
				AND SubCategoryID IS NULL
				AND (
					
						int_actor_type_id = ?
						OR
						(
							int_actor_type_id IS NULL
							AND AccessLevel = ?
						)
					
				)
				
				AND ISNULL(SiteURL, '') NOT LIKE 'index.cfm?fuseaction=1010' /*29 MAY 2012 NEO 00043 TRACKER 29863*/
				
			ORDER BY int_sort_order

SET ANSI_NULLS ON

		SELECT IsNull((
					CASE
						WHEN str_dashboard_name = ''
							THEN NULL
						ELSE str_dashboard_name
						END
					), CASE
					WHEN ActorTypeID = 55
						THEN 'Dashboard'
					ELSE 'my dashboard'
					END) AS str_dashboard_name /* 02 APRIL 2014 NEO 0088 TRACKER 40005 */
		FROM ActorTypes
		WHERE ActorTypeID = ?

SELECT pfo.int_order
				, pf.str_field_display_name
				, pf.str_field_name
				, pfo.int_profile_field_tab_id
				, pf.str_actor_types
				, pf.chr_field_type
			FROM tbl_profile_field_order pfo
			INNER JOIN tbl_profile_field pf ON pfo.int_profile_field_id = pf.int_profile_field_id
			WHERE pf.int_region_id = ?
				AND bit_enabled = ?
				AND
				(
					(
						pf.chr_field_type not in ('C','O')
					)
					
						OR
						(
							pf.chr_field_type in ('C','O') AND
							(
								pf.str_actor_types LIKE ? 
							)
						)
					
				)
			ORDER BY pfo.int_profile_field_tab_id
				, pfo.int_order ASC

select * from tbl_social_media_details WHERE int_region_id = ?
			order by int_order asc

select * from UIVariables where RegionID = ?

SELECT menitms.SiteURL AS nav_site_url
			, menitms.label AS nav_label
			, menitms.rowNum
			, COUNT(menitms.rowNum) OVER () AS Total_Rows
			, menitms.DisplayCriteria_1
			, menitms.DisplayValue_1
			, menitms.DisplayCriteria_2
			, menitms.DisplayValue_2
			, menitms.Condition
			, menitms.NewWindowName
		FROM (
			SELECT SiteURL
				, label
				, DisplayCriteria_1
				, DisplayValue_1
				, DisplayCriteria_2
				, DisplayValue_2
				, Condition
				, Row_Number() OVER (
					ORDER BY MenuitemID ASC
					) AS rowNum
				, NewWindowName
			FROM MenuItems
			WHERE AccessLevel 
			
					= ? 
			
				AND RegionID = ?
			AND bit_display_in_nav=1
				AND STATUS = ?
				
	) menitms

SELECT menitms.SiteURL
			, menitms.label
			, menitms.AccessLevel
			, menitms.rowNum
			, ((rowNum - 1) / 4) + 1 AS block
			, menitms.DisplayCriteria_1
			, menitms.DisplayValue_1
			, menitms.DisplayCriteria_2
			, menitms.DisplayValue_2
			, menitms.Condition
			, menitms.NewWindowName
		FROM (
			SELECT SiteURL
				, label
				, AccessLevel
				, DisplayCriteria_1
				, DisplayValue_1
				, DisplayCriteria_2
				, DisplayValue_2
				, Condition
				, Row_Number() OVER (
					ORDER BY MenuitemID ASC
					) AS rowNum
				, NewWindowName
			FROM MenuItems
			WHERE AccessLevel
			
			
					= ? 
			
				AND RegionID = ?
			AND bit_display_in_footer=1
				AND STATUS = ?
				
	) menitms

DECLARE @currentTime DATETIME2 = ?
				UPDATE tbl_cart_class_detail
				SET EnteredDate = @currentTime
				WHERE SessionID = ?;
				UPDATE tbl_cart_merchandise_detail
				SET EnteredDate = @currentTime
				WHERE SessionID = ?;

DECLARE @expireRequestsAfter DATETIME2 = ?;
						DELETE FROM tbl_cart_class_detail WHERE EnteredDate <= @expireRequestsAfter;
						DELETE FROM tbl_cart_merchandise_detail WHERE EnteredDate <= @expireRequestsAfter;

SELECT str_header_script
				, str_footer_script
			FROM tbl_style_settings WITH (NOLOCK)
			-- WHERE int_region_id = Commented as long as multiple regions are not there

select * from UIVariables where RegionID = ?

select * from qTempMenu where MenuItemID NOT IN  (397,478)

select * from qSocialMedia order by int_order desc

SELECT isNull(str_user_nav_label,'') AS str_menu_label
		FROM tbl_user_nav_menu_label
		WHERE int_actor_type_id = ?
		AND str_menu_label LIKE 'DONATE'
		AND str_display_condition='application.appvars[ri].bit_allow_student_donation EQ 1 AND application.appvars[ri].bit_display_donate_in_navmenu EQ 1';

select * from qMenu where int_parent_id = 536

select * from qMenu where int_parent_id = 543

select * from qMenu where int_parent_id = 619

select * from qMenu where int_parent_id = 2

select * from qMenu where int_parent_id = 5

select * from qMenu where int_parent_id = 6

select * from qMenu where int_parent_id = 7

select * from qMenu where int_parent_id = 403

select * from qMenu where int_parent_id = 439

select * from qMenu where int_parent_id = 468

SET NOCOUNT ON

		SELECT rg.Phone1
			,rg.Phone1Ext
			,rg.email
		FROM Regions rg
		WHERE rg.RegionID = ?
			AND rg.STATUS = 'A'

SELECT TOP 1 Label  FROM base_field_customized WITH (NOLOCK)
		WHERE base_field = 'class_section'
		AND base_table_id  = 2 AND customized_base_field_id IS NOT NULL

SELECT TOP 1 fc.str_text_when_class_waiting_list_not_allowed
		FROM FeatureConfiguration fc
		INNER JOIN Regions rg ON fc.RegionID = rg.RegionID
		LEFT JOIN tbl_erp e ON fc.int_erp_id = e.int_erp_id
		LEFT JOIN tbl_erp_config ec ON ec.str_erp_url <> ''
		WHERE rg.STATUS = 'A'

SELECT sc.CategoryId
				, sc.SubCategoryID
			FROM tbl_class_listing tcl WITH (NOLOCK)
			INNER JOIN Subcategories sc WITH (NOLOCK) ON sc.SubCategoryID = tcl.SubCategoryID
			WHERE tcl.classid = ?;

select  contents from contents WITH (NOLOCK) where
	path= ?

DECLARE @TimeZoneShift as float;
		
		Set @TimeZoneShift = IsNull((select TimeZoneHourAdjustment from Regions where RegionID = 1),0)

        SELECT top 3 
        cl.ClassID, 
        co.CourseID, co.Name as CourseName,
        cl.Name as ClassName
        FROM Courses co WITH (NOLOCK)
        INNER JOIN Subcategories sc WITH (NOLOCK)
            INNER JOIN Categories c WITH (NOLOCK) ON sc.categoryid = c.categoryid
        ON co.SubcategoryID = sc.SubcategoryID
        INNER JOIN Classes cl WITH (NOLOCK)
            INNER JOIN LessonReleaseTimes lrt WITH (NOLOCK) ON cl.LessonReleaseID=lrt.LessonReleaseID
            
            
            
        ON co.CourseID = cl.CourseID
        LEFT JOIN CourseSeries cs WITH (NOLOCK)
            ON cs.CourseSeriesID = co.CourseSeriesID
        WHERE 
        co.regionid = 1 
        AND 
        cl.Status IN ('A', 'C') 
 
        AND 
        DateAdd(hh, @TimeZoneShift, dbo.fun_GetDateCT()) BETWEEN cl.HotPixStartDate AND DateAdd(ms, -10, IsNull(cl.HotPixEndDate,0))

        ORDER BY co.Name, co.CourseNumber, cl.StartDate

select str_class_listing_order from tbl_class_listing_order

SELECT COUNT(catalogid) AS IsAllCatalogMeansAllClass
		FROM CATALOGS
		WHERE CatalogID = 1
			AND NAME = 'All Catalogs'

SET NOCOUNT ON
		SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
		DECLARE @MembershipID AS INT
			,@AllcatalogId AS INT/*TRACKER 37132 : SEP 24 2013 NEO 0126 */
			,@SearchKey VARCHAR(500)
			,@SubCategoryID INT
			,@StrSubCategoryID VARCHAR(20)
			,@CategoryID INT
			,@classtype INT
			,@CourseName VARCHAR(255)
			,@StartDate DATETIME
			,@EndDate DATETIME
			,@StartTimeHour INT
			,@StartTimeMinute INT
			,@EndTimeHour INT
			,@EndTimeMinute INT
			,@City VARCHAR(255)
			,@ActorID INT
			,@LearnerID INT
			,@ClassID INT
			,@InstructorID INT
			,@CourseNumber VARCHAR(30)
			,@LocationID INT
			,@CourseSeriesID INT
			,@containsearchKey VARCHAR(510)
			,@bit_use_guest_checkout BIT
			, @currentTimeZoneOffsetDate DATETIME2 = ?;

		
			DECLARE @bit_group_class_listing_online BIT
				, @bit_group_class_listing_non_online BIT;

			SELECT @bit_group_class_listing_online = bit_group_class_listing_online
				, @bit_group_class_listing_non_online = bit_group_class_listing_non_online
			FROM FeatureConfiguration
		

		SET @LearnerID = ?

		SET @MembershipID = IsNull((
					SELECT MembershipID
					FROM learners
					WHERE learnerid = @LearnerID
					), 0)

		SET @AllcatalogId = (
				SELECT catalogid
				FROM catalogs
				WHERE NAME = 'All Catalogs'
				) /*TRACKER 37132 : SEP 24 2013 NEO 0126 */
		
		SET @SearchKey = ?;
		SET @containsearchKey = '"'+replace(@SearchKey,'"','')+'*"';

		

		SET @bit_use_guest_checkout = ?;


		/*new advanced search fields */
		DECLARE @durationTypeID INT
			, @ceus decimal(5,2)
			, @ContactHours decimal(10,2)
			, @InstructionalMethodId INT
			, @ClassRoom VARCHAR(30)
			, @LMSID INT
			, @BadgeID INT
			, @BadgeTemplateID INT
			, @PublicRegistrationStarts DATETIME
			, @PublicRegistrationEnds DATETIME
			, @MinAge INT
			, @MaxAge INT
			, @LimitSelfRegistrationMembershipTypeID INT
			, @QtyRegistration BIT
			, @CountTowardsBenefitsMembershipTypeID BIT
			, @AllowMultipleRegistrations BIT
			, @RequiresRegFee BIT
			, @UsesLottery BIT
			, @LotteryRunDate DATE
			, @LotteryCloseDate DATE
			, @LotteryTypeID INT
			, @ClassUnlockKey VARCHAR(100)
			, @MinPrice DECIMAL(10,2)
			, @MaxPrice DECIMAL(10,2);

		
		/*new advanced search fields */

		/* Table variables to populate the list of ID's to build the query from.  This change improved performance by 40%+.  These have the majority of the conditions AND are joined to each of the union sections. */
		DECLARE @ClassIDTable1 TABLE (
			ClassListingID INT NOT NULL
			, Rank INT
			, Rank2 INT
			);/* for first part of Union  */
		DECLARE @ClassIDTable2 TABLE (
			ClassListingID INT NOT NULL
			, Rank INT
			, Rank2 INT
			);/* for second part of Union  */
		DECLARE @ClassIDTable3 TABLE (
			ClassListingID INT NOT NULL
			, Rank INT
			, Rank2 INT
			);/* for third part of Union  */
		DECLARE @ClassIDTable4 TABLE (
			ClassListingID INT NOT NULL
			, Rank INT
			, Rank2 INT
			);/* for fourth part of Union  */
		DECLARE @ClassIDTable5 TABLE (
			ClassListingID INT NOT NULL
			, Rank INT
			, Rank2 INT
			);/*  */

		/**************************************************************************/
		/******* Begin Populate ID's for first part of union for classes **********/
		/**************************************************************************/
			INSERT INTO @ClassIDTable1
			SELECT DISTINCT ClassListingID
				
					,0 AS Rank
					,0 AS Rank2
				
			FROM tbl_class_listing cl
				
				INNER JOIN Courses co ON co.CourseID = cl.CourseID
				
			WHERE 1 = 1
				/* 7/7/12/2012 tracker 20200 */
				
				/* end tracker 20200*/
				
					AND @currentTimeZoneOffsetDate BETWEEN cl.AppearanceStartDate
							AND CL.AppearanceEndDate
				
				/* BEGIN : 09 SEP 2014 NEO 00159 TRACKER 42510 */
				
				/* END : 09 SEP 2014 NEO 00159 TRACKER 42510 */
				
					/* Tracker 44088 Dec 09 2014 Neo 0126 */
					AND cl.classID NOT IN (SELECT ClassID FROM programsectionclasses)
				
					AND (CL.bit_display_to_public = 1)
				
				/* Begin Dec 07 2012 AIMembership Enhancement UC 10 Neo 00110  */
				
						AND (
							CL.str_limit_self_reg_mbrship_type_ids IS NULL
							OR CL.str_limit_self_reg_mbrship_type_ids = '0'
							OR CL.str_limit_self_reg_mbrship_type_ids = ''
							OR EXISTS (
										SELECT 1
										FROM STRING_SPLIT(CL.str_limit_self_reg_mbrship_type_ids, ',')
										WHERE value IN (
												SELECT MembershipTypeID
												FROM MembershipTypes
												WHERE bit_display_classes_members_only = 0
								)
							)
							

					)
					
				AND ISNULL(cl.bit_use_guest_checkout,0) = @bit_use_guest_checkout
				/**/
				AND ( @durationTypeID IS NULL OR cl.int_duration_type_id = @durationTypeID )
				AND ( @ClassRoom IS NULL OR cl.ClassRoom LIKE '%'+@ClassRoom+'%' )
				AND ( @PublicRegistrationStarts IS NULL OR cl.dat_public_registration_begin >= @PublicRegistrationStarts )
				AND ( @PublicRegistrationEnds IS NULL OR cl.dat_public_registration_closes <= @PublicRegistrationEnds )
				AND ( @MinAge IS NULL OR cl.MinAge >= @MinAge )
				AND ( @MaxAge IS NULL OR cl.MaxAge <= @MaxAge )
				AND ( @LimitSelfRegistrationMembershipTypeID IS NULL
					OR EXISTS (
						SELECT 1 FROM
						STRING_SPLIT(cl.str_limit_self_reg_mbrship_type_ids, ',')
						WHERE value = @LimitSelfRegistrationMembershipTypeID
					)
				)
				AND ( @CountTowardsBenefitsMembershipTypeID IS NULL
					OR EXISTS (
						SELECT 1 FROM
						STRING_SPLIT(cl.str_count_towards_membership_benefits, ',')
						WHERE value = @CountTowardsBenefitsMembershipTypeID
					)
				)
				AND ( @MinPrice IS NULL OR cl.Cost >= @MinPrice )
				AND ( @MaxPrice IS NULL OR cl.Cost <= @MaxPrice )
				AND ( @UsesLottery IS NULL OR cl.bit_uses_class_lottery = @UsesLottery )
				AND ( @LotteryCloseDate IS NULL OR cl.dat_lottery_close_date = @LotteryCloseDate )
				AND ( @LotteryRunDate IS NULL OR cl.dat_lottery_run_date = @LotteryRunDate )
				AND ( @LotteryTypeID IS NULL OR cl.int_lottery_type_id = @LotteryTypeID )
				AND ( @BadgeID IS NULL OR cl.int_badge_integration_id = @BadgeID )
				AND ( @BadgeTemplateID IS NULL OR cl.int_badge_integration_template_id = @BadgeTemplateID )
				AND ( @ceus IS NULL OR cl.CEUs = @ceus )
				AND ( @ContactHours IS NULL OR cl.ContactHrs = @ContactHours )
				AND ( @LMSID IS NULL OR cl.int_lms_integration_id = @LMSID )
				AND ( @QtyRegistration IS NULL OR cl.AllowQtyPurchase = @QtyRegistration )
				AND ( @AllowMultipleRegistrations IS NULL OR cl.bit_allow_multiple_registration = @AllowMultipleRegistrations )
				AND ( @RequiresRegFee IS NULL OR cl.regFeeRequired = @RequiresRegFee )
				/* these require joins to other tables and can result in peformance degradation */
				AND (
						@InstructionalMethodID IS NULL
						OR EXISTS (  SELECT 1 FROM tbl_ClassSchedules WHERE ClassID = cl.ClassID AND int_instructional_method_id = @InstructionalMethodID )
				)
				AND (
						@ClassUnlockKey IS NULL
						OR EXISTS (
							SELECT TOP 1 1
							FROM tbl_restriction_code_detail rcd
							INNER JOIN tbl_table t ON rcd.int_table_id = t.int_table_id
							INNER JOIN tbl_restriction_code rc ON rc.int_restriction_code_id = rcd.int_restriction_code_id
							WHERE t.str_original_table_name = 'Classes'
							    AND rcd.int_primary_key_id = cl.ClassID
							    AND rc.str_restriction_code = @ClassUnlockKey
						)
				)
				
				/**/

				  /* don't delete if Class ID is the keyword because rank will be null  */
					IF (
							SELECT Count(CourseID)
							FROM Courses
							WHERE CourseNumber = @SearchKey
							) = 0 /* Delete null rank records if the keyword is NOT a course number */
					BEGIN
						DELETE
						FROM @ClassIDTable1
						WHERE (
								rank IS NULL
								AND rank2 IS NULL  /* PAV 14 Jul 2017 - changed from OR to AND to resolve issue where tenses of words were not returning correct records */
								)
							AND (
								classlistingid NOT IN (
									SELECT classlistingid
									FROM tbl_class_listing
									WHERE (ClassName LIKE '%'+@SearchKey+'%')
										OR (DescriptionNoHTML LIKE '%'+@SearchKey+'%')
									)
								) /* Tracker 42483,42624,42606 Sep 10 2014 Neo 0126 */
					END
				
		/**************************************************************************/
		/***** End Populate ID's for first part of union for classes **************/
		/**************************************************************************/


		/**************************************************************************/
		/***** BEGIN Populate ID's for second part of union for course series *****/
		/**************************************************************************/
		
		/**************************************************************************/
		/***** END Populate ID's for second part of union for course series *******/
		/**************************************************************************/

		/**************************************************************************/
		/***** BEGIN Populate ID's for third part of union for certificates   *****/
		/**************************************************************************/
		
		/**************************************************************************/
		/***** END Populate ID's for third part of union for certificates   *******/
		/**************************************************************************/

		/*****************************************************************************************/
		/***** Begin Populate ID's for fourth part of union for certificate program sections *****/
		/*****************************************************************************************/
		
		/******************************************************************************************/
		/***** END Populate ID's for fourth part of union for certificates program sections *******/
		/******************************************************************************************/

		/********************************************************************************/
		/***** BEGIN Populate ID's for fifth part of union for certificate groups   *****/
		/********************************************************************************/
		
		/********************************************************************************/
		/***** END Populate ID's for fifth part of union for certificate groups   *****/
		/********************************************************************************/

		/**************************************************************************************************/
		/******************** Begin select from first part of union for classes ***************************/
		/**************************************************************************************************/
			SELECT DISTINCT
				
				0 AS Rank
				,0 AS Rank2
				
				/* MODIFY NEO 00106  Flexible Schedules 06 SEP 2013 */
				
				,str_class_schedule_display_student_login AS str_class_schedule_display
				
				,showLocation AS showLocation
				,showCity AS showCity
				,showRoom AS showRoom
				,showCityLocation AS showCityLocation
				
				,CASE
					WHEN cl.bit_disp_instr_method_to_public = 1
						THEN instructionMethod
					ELSE ''
					END
				
				AS InstructionalMethod
				,DaysOfWeek AS DaysOfWeek
				,Instructors AS Instructor
				,CASE
					WHEN co.ExternalCourseProviderID = 1
						THEN ''
					ELSE CL.URLText
					END AS CourseURLText
				,CASE
					WHEN co.ExternalCourseProviderID = 1
						THEN ''
					ELSE CL.ClassURL
					END AS ClassURL
				,CASE
					WHEN co.ExternalCourseProviderID = 1
						THEN ''
					ELSE CL.ClassURlText
					END AS ClassURlText
				/* MODIFY NEO 00106  Flexible Schedules 06 SEP 2013 */
				,c.AllowQtyPurchase AS AllowQtyPurchase
				/* NEO 00083 Membership enhancement UC 10 SS2 */
				,c.RequiresReleaseForm AS RequiresReleaseForm
				,'' AS int_age_limit
				/*Added Jan 17 2013 AI Membership Enhancement UC 10 Neo 00110*/
				,CASE
					WHEN csj.int_series_group_id IS NULL
						THEN 0
					ELSE 1
					END AS InCourseSeries
				/* JS 10/29/2009 tracker 16972 */
				/*- 2/8/2012 tracker 28153 -*/
				,IsNull((
						SELECT count(registrationid)
						FROM registrations
						WHERE classid = cl.classid
							AND iscancelled = 0
							AND int_series_group_id > 0
						), 0) AS SeriesRegs
				,IsNull((
						SELECT count(registrationID)
						FROM registrations
						WHERE classID = cl.classID
							AND Iscancelled = 0
						), 0) AS TotalRegs
				,IsNull((
						SELECT sum(int_reserved_seats)
						FROM tbl_class_course_series_group
						WHERE int_class_ID = cl.classID
						), 0) AS ReservedSeats
				/* End tracker 28153 */
				/*Author :00097 date: 26 Aug 2011 Task: Certificate Issue Begin*/
				,CL.DescriptionNoHTML AS description
				,CL.description AS descriptionFull
				/*Author :00097 date: 26 Aug 2011 Task: Certificate Issue End*/
				,(
					CL.TotalSeats - (
						SELECT isnull(SUM(ccsg1.int_reserved_seats), 0)
						FROM tbl_class_course_series_group ccsg1
						WHERE ccsg1.int_class_id = CL.ClassID
						)
					) AS ClassTotalSeats
				/*00097 01/27/2012 Tracker:27948*/
				,CL.ClassListingID
				,CL.ClassID
				,CL.CourseID
				,CL.SubCategoryID
				,CL.CourseNumber
				,CL.int_delivery_type_id /* Added Neo 00049 22nd Jan 2013 Flexible schedules II F-05 */
				,CL.CourseTitle
				,CL.NAME
				, CASE
					WHEN co.ExternalCourseProviderID = 1
						THEN ''
					ELSE CL.CourseURL
					END AS CourseURL
				, CASE
					WHEN co.ExternalCourseProviderID = 1
						THEN ''
					ELSE CL.URLText
					END AS URLText
				,CL.IsOnlineCourse
				,CL.Keywords
				,CL.ClassName
				,CL.CatalogID
				,CL.SectionNumber
				,CL.classStartDate
				,CL.classEndDate
				,CL.StartDate
				,CL.StartDateOrder
				,CL.EndDate
				,CL.EndDateOrder
				,CL.AppearanceStartDate
				,CL.AppearanceEndDate
				,CL.HotPixStartDate
				,CL.HotPixEndDate
				,CL.dat_public_registration_begin
				,CL.dat_public_registration_closes
				,CL.dat_staff_registration_begin
				,CL.str_alt_subcategory_ids
				,CL.IsFeaturedSpeaker
				,CL.ClassRating
				,CL.ClassRatingCount
				,CL.NumberSessions
				,CL.NumberWeeks
				,CL.DaysOfWeek
				,CL.Cost
				,CL.CostNoMaterialsFee
				,CL.MaterialsCost
				,CL.LocationID
				,CASE
					WHEN co.ExternalCourseProviderID = 1
						THEN ''
					ELSE CL.Location
					END AS Location
				,CL.City
				,CL.ClassRoom
				,CL.SpecialNotice
				,CL.TotalSeats
				,CL.InternalComments
				,CL.Comments
				,CL.PublicRegistration
				,CL.contactPhone
				/*,CL.InCourseSeries*/
				,CL.LessonReleaseTimes
				,CL.NumberofSessions
				,CL.NumberofWeeks
				,CL.StartDate1
				,CL.EndDate1
				,CL.CourseLogoPath
				,CL.AltCourseLogoPath
				,CL.bit_display_course_logo
				,CL.TimeZoneHourAdjustment
				,CL.MinAge
				,CL.MaxAge
				,CL.str_class_time_display
				,CL.int_certificate_id
				,CL.str_class_icon
				
				,co.coursegraphic
				,sc.str_subCat_graphic
				,ca.str_Cat_graphic
				
				
				,c.str_class_graphic_filename
				
				,CL.str_class_icon_alt_descr
				,csj.int_series_group_id int_course_series_group_id
				,CL.int_course_series_id
				,CL.str_class_type
				,CL.chr_status
				,CL.int_duration_type_id
				,CL.int_quantity
				,CL.bit_display_to_public
				,CL.dat_publish_date
				
				,CL.GoNumber
				 AS  GoNumber
				
				,CL.int_program_section_id
				,CL.str_count_towards_membership_benefits
				,CL.StaffDisplayStartDate
				,CL.StaffDisplayEndDate
				,convert(DATETIME, convert(VARCHAR, cl.classStartDate, 101) + ' ' + IsNull(cl.starttime1, '0:00'), 121) AS ClStartDate
				/*30 MAR 2012 NEO 00043 TRACKER 28840*/
				,convert(DATETIME, convert(VARCHAR, cl.classEndDate, 101) + ' ' + cl.endtime1, 121) AS ClEndDate
				/*30 MAR 2012 NEO 00043 TRACKER 28840*/
				,cl.CourseTitle AS NAME
				,c.dat_staff_registration_begin AS dat_staff_registration_begin
				,co.CourseNumber AS CourseNo
				,co.int_schedule_type_id AS ScheduleType
				,CL.int_duration_type_id AS DurationType
				,CL.int_quantity
				,CL.int_time_period_id
				,(
					SELECT str_description
					FROM tbl_time_periods
					WHERE int_time_period_id = CL.int_time_period_id
					) AS TimeDescription
				,(SELECT count(R.RegistrationID)
					FROM registrations R
					WHERE R.classID = CL.classID
					AND R.IsCancelled = 0) AS registrationCount
				,c.TotalSeats - (SELECT count(registrationid) from registrations where classid = c.classid and iscancelled = 0) as remainingseats
				,(
					SELECT count(R.RegistrationID)
					FROM registrations R
					WHERE R.classID = CL.classID
						AND R.IsCancelled = 0
						AND (
							R.int_series_group_id = 0
							OR R.int_series_group_id IS NULL
							)
					) AS FilledSeats
				/*00097 01/27/2012 Tracker:27948*/
				
				,IsNull(wl.WaitingListCount, 0) AS waitingList
				,CASE
					WHEN IsNull(wl.WaitingListCount, 0) > 0
						THEN 1
					ELSE 0
					END AS bit_class_on_waiting_list
				,CASE
					WHEN (
							c.remainingseats <= 0
							OR IsNull(wl.WaitingListCount, 0) > 0
							)
						THEN 1
					ELSE 0
					END AS isfull
				
				,CASE
					/* 10 July 2012 Poornima Added a check to see if the registration is a quantity registration */
					WHEN EXISTS (
							SELECT RegistrationID
							FROM Registrations R
							WHERE R.classid = cl.classid
								AND R.LearnerID = @LearnerID
								AND R.IsCancelled = 0
								AND IsNull(R.bit_qty_reg_additional, 0) = 0
							)
						THEN 1
					ELSE 0
					END AS bit_enrolled
				/*Added 06 AUG 2012 NEO 00049 Rochester Community Schools Register for a Class Multiple Times */
				,c.bit_allow_multiple_registration
				,CASE
					WHEN EXISTS (
							SELECT w.WaitListID
							FROM WaitingLists w
							INNER JOIN WaitingListEntries we ON w.waitlistid = we.waitlistid
							WHERE w.classid = cl.classid
								AND we.LearnerID = @LearnerID
							)
						THEN 1
					ELSE 0
					END AS bit_on_waiting_list
				/*
					Author      : Neo 00007
					Date        : 13 Oct 2010
					Function    : For Tracker 20377 - Collect whether the class is in cart for that learner.
				*/
				/* Aswin 20 Feb 2015 updated the incart logic*/
				,(
					SELECT CASE
							WHEN (
									SELECT COUNT(DISTINCT int_learner_id)
									FROM tbl_cart_class_detail tcd
									INNER JOIN classes ON classes.classid = tcd.int_class_id
									WHERE tcd.int_class_id = Cl.ClassID
										AND classes.bit_allow_multiple_registration = 0
										/* some customers use household as group having large number of members */
										
											AND int_learner_id IN (?)
										
									) = ?
								THEN 1
							ELSE 0
							END
					) AS learnerAlreadyInCart
				/* 00097 NLUAT-3256 14 Aug 2012 */
				/* Aswin 20 Feb 2015 updated the incart logic*/
				,'' AS typeName
				,'' AS Email
				,'' AS FName
				,'' AS LName
				,'' AS ClassesToComplete
				,'' AS classCount
				,0 AS sortorder
				,'' AS bitDisplay
				,'' AS bitRegistration
				/* END  For Tracker 20377 - Collect whether the class is in cart for that learner.-*/
				,'' AS int_certificate_id
				/* Added July 11 2012 Tracker 30411 Neo 00110*/
				,'' AS bit_multiple_elective_groups /* Added July 11 2012 Tracker 30411 Neo 00110*/
				/* Begin Author 00097 : Neo AI Course Series change 19 July 2011*/
				, (
					SELECT str_course_series_type_name FROM tbl_course_series_type
					WHERE int_course_series_type_id = csj.int_course_series_type_id
					) AS CourseSeriesType
				,0 AS SeriesGroupMatFeeAdv
				/* End Author 00097 : Neo AI Course Series Use Case :19 July 2011*/
				/* Neo D 00045 AI Certificates Added Features 2011-10-14 Feature Change 1 Date:Nov 17,2011*/
				,(
					SELECT sum(ISNULL(int_reserved_seats, 0))
					FROM tbl_certificates_classes
					WHERE int_class_id = c.ClassID
					) AS int_certificate_reserved_seats
				,(
					SELECT sum(ISNULL(int_remaining_seats, 0))
					FROM tbl_certificates_classes
					WHERE int_class_id = c.ClassID
					) AS int_certificate_remaining_seats
				,(
					SELECT sum(ISNULL(int_reserved_seats, 0))
					FROM tbl_class_course_series_group tccsg1
					INNER JOIN courseseries cs1 ON tccsg1.int_course_series_id = cs1.courseseriesid
					WHERE int_class_id = c.ClassID
						AND STATUS <> 'I'
					) AS int_courseseries_reserved_seats /*Tracker 38702 jan 14 2014 Neo 0126 */
				,(
					SELECT sum(ISNULL(int_remaining_seats, 0))
					FROM tbl_class_course_series_group tccsg1
					INNER JOIN courseseries cs1 ON tccsg1.int_course_series_id = cs1.courseseriesid
					WHERE int_class_id = c.ClassID
						AND STATUS <> 'I'
					) AS int_courseseries_remaining_seats /*Tracker 38702 jan 14 2014 Neo 0126 */
				/* End AI Certificates Added Features 2011-10-14 Feature Change 1 */
				,IsNull((
						SELECT count(registrationid)
						FROM registrations
						WHERE classid = c.classid
							AND iscancelled = 0
							AND int_certificate_id > 0
						), 0) AS int_certificate_regs /* Added Feb 20 2012 Fun: Tracker 28377,28043 Neo s 00045 */
				/* BEGIN PAV 2 May 2012 Class listing grouping  */
				,co.ExternalCourseProviderID
				/* END PAV 2 May 2012 Class listing grouping  */
				,CASE
					WHEN (
							LEN(cl.minage) > 0
							AND LEN(cl.maxage) > 0
							)
						THEN convert(VARCHAR, cl.minage) + ' - ' + convert(VARCHAR, cl.maxage)
					ELSE ''
					END AS ClassAges
				/* BEGIN: NEO 00091 Date:Sep 20,2012 AI V7.3 Full-Time Certificate Use Cases 2012-08-03 UC-13 */
				,'' AS UseProgramSection
				,'' AS ProgramSectionStartDate
				,'' AS ProgramSectionEndDate
				,'' AS isAllProgramSectionClassesAvailable
				,'' AS bit_use_prerequisite
				,'' AS isProgramSectionSeatsAvailable
				/* END: NEO 00091 Date:Sep 20,2012 AI V7.3 Full-Time Certificate Use Cases 2012-08-03 UC-13 */
				/* Begin Dec 07 2012 AIMembership Enhancement UC 10 Neo 00110 */
				,cl.str_limit_self_reg_mbrship_type_ids
				,(
					SELECT SUM(CONVERT(INT, bit_display_classes_members_only)) AS bit_display_classes_members_only
					FROM MembershipTypes mt
					LEFT JOIN Classes c ON ',' + c.str_limit_self_reg_mbrship_type_ids + ',' LIKE '%,' + CAST(mt.MembershipTypeID AS VARCHAR(255)) + '%'
					WHERE c.ClassID IN (CL.classid)
					) AS bit_display_classes_members_only
				,(
					SELECT SUM(CONVERT(INT, bit_allow_student_purchase_mbrship)) AS bit_allow_student_purchase_mbrship
					FROM MembershipTypes mt
					LEFT JOIN Classes c ON ',' + c.str_limit_self_reg_mbrship_type_ids + ',' LIKE '%,' + CAST(mt.MembershipTypeID AS VARCHAR(255)) + '%'
					WHERE c.ClassID IN (CL.classid)
					) AS bit_allow_student_purchase_mbrship
				/* End Dec 07 2012 AIMembership Enhancement UC 10 Neo 00110 */
				,CASE
					WHEN c.contactPhone IS NULL
						THEN co.contactPhone
					ELSE c.contactPhone
					END AS contactphonenumber
				,c.ContactHrs
				,c.CEUs
				,c.flt_PLUs /*Modify NEO 00187 03 JUN 2015 PLU&Registration Transfer UC-04*/
				/* Begin NEO 00049 Frederick Discount Price Display 12th Feb 2013 */
				,stuff((
						SELECT ',' + CAST(cf.NAME AS VARCHAR(100)) + '||' + CAST(CAST(cl.CostNoMaterialsFee - clf.DiscountAmount_1 AS money) AS VARCHAR(100))
						FROM ClassFees clf
						LEFT JOIN CourseFees cf ON clf.CourseFeeID = cf.CourseFeeID
						WHERE ClassID = cl.classID
							AND clf.STATUS = 'A'
							AND cf.NAME > ''
						FOR XML path('')
						), 1, 1, '') optionalAttributeDiscount
				/* End NEO 00049 Frederick Discount Price Display 12th Feb 2013 */
				,tcdt.str_delivery_type
				/* Begin NEO 00088 AI VCCS Modifications Group 3  09 April 2014 */
				,ISNULL(c.QualityScore, 0) AS QualityScore
				,ISNULL((
						SELECT int_quality_score_upper_limit
						FROM featureconfiguration
						WHERE regionid = 1
						), 1) AS QualityScoreUpperLimit
				,ISNULL((
						SELECT SUM(QualityScore)
						FROM Classes
						WHERE courseid = c.CourseID
						), 1) AS QualityScoreSum
				,(
					SELECT COUNT(ClassID)
					FROM Classes
					WHERE courseid = c.CourseID
						AND ISNULL(QualityScore, 0) > 0
					) AS ClassesWithQualityScore
				/* End NEO 00088 AI VCCS Modifications Group 3  09 April 2014 */
				,1 AS bit_disp_cs_to_public /* 30 JUL 2014 NEO 00159 TRACKER 39672 */
				/* Begin NEO 00164 1st DEC 2014 OLLI Lottery UC-08 */
				
				,CASE
					WHEN cl.bit_uses_class_lottery IS NOT NULL
						THEN cl.bit_uses_class_lottery
					ELSE 0
					END AS bit_uses_class_lottery
				,CASE
					WHEN cl.bit_uses_class_lottery IS NOT NULL
						AND cl.dat_lottery_close_date IS NOT NULL
						THEN cl.dat_lottery_close_date
					ELSE ''
					END AS dat_lottery_close_date
				,CASE
					WHEN cl.bit_uses_class_lottery IS NOT NULL
						AND cl.dat_lottery_run_date IS NOT NULL
						THEN cl.dat_lottery_run_date
					ELSE ''
					END AS dat_lottery_run_date
				,CASE
					WHEN cl.bit_uses_class_lottery IS NOT NULL
						THEN cl.int_lottery_status_id
					ELSE 0
					END AS int_lottery_status_id
				,CASE
					WHEN cl.bit_uses_class_lottery IS NOT NULL
						THEN cl.int_lottery_type_id
					ELSE 0
					END AS int_lottery_type_id
				,isnull((
						SELECT count(lr.int_lottery_request_id)
						FROM tbl_lottery_requests lr
						WHERE lr.int_class_id = cl.classid
							AND ISNULL(lr.int_removed_by_id, 0) = 0
						), 0) AS lotteryRequestCount
				
				/* End NEO 00164 1st DEC 2014 OLLI Lottery UC-08 */
				,(
					SELECT TOP 1 LTrim(FName) + ',' + LName AS InstructorName
					FROM tbl_class_instructor
					INNER JOIN instructors ON instructors.instructorid = tbl_class_instructor.int_instructor_id
					WHERE tbl_class_instructor.int_class_id = c.classid
					ORDER BY InstructorName
					) AS str_instructorname
				/*extra cols for QofQ sorting*/
				, lower(cl.ClassName) as ClassNameSort
				, lower(CL.Location) as LocationSort
				, lower(CL.CourseNumber) as CourseNumberSort
				, lower((
						SELECT TOP 1 CASE
								WHEN LEN(LTrim(RTrim(CatalogName))) > 0
									THEN CatalogName
								ELSE LTrim(FName) + ',' + LName
								END AS InstructorName
						FROM tbl_class_instructor
						INNER JOIN instructors ON instructors.instructorid = tbl_class_instructor.int_instructor_id
						WHERE tbl_class_instructor.int_class_id = c.classid
						ORDER BY InstructorName
						)) AS InstructorNameSort
				/*extra cols for QofQ sorting*/
				, cl.dat_public_registration_begin AS TermPublicRegistrationBegin
				, c.TermID
				, NULL str_series_icon
				, NULL str_series_icon_alt_descr
				
					, (
						SELECT COUNT(int_item_class_id)
						FROM tbl_merchandise_item_class mic
						INNER JOIN tbl_merchandise_item mi ON mi.int_item_id = mic.int_item_id
						WHERE mi.bit_available_with_class_or_course = 1
							
								AND mi.dat_student_display_start <= @currentTimeZoneOffsetDate
								AND mi.bit_merchandise_item_available_to_students=1
							
							AND 1 = CASE
									WHEN bit_item_expire = 1
										THEN CASE
											WHEN @currentTimeZoneOffsetDate < mi.dat_item_expire
												THEN 1
											ELSE 0
											END
									ELSE 1
									END
							AND mic.int_class_id = c.ClassID
					) AS AvailableItemCount
				
				, cl.bit_allow_wcg_registration
				, CL.bit_allow_waiting_lists
				, CL.str_default_text_for_call_message_class
				
				,CL.int_certificate_group_id
				
					
					, CASE
						WHEN cl.bit_use_class_prerequisites = 1 THEN
							CASE
								WHEN (SELECT COUNT(tpc.int_prerequisite_class_id)
										FROM tbl_prerequisite_class tpc
											INNER JOIN tbl_prerequisites tp ON tp.int_prerequisites_id = tpc.int_prerequisites_id
											INNER JOIN tbl_prerequisite_status PS ON PS.int_prerequisite_status_id = tp.int_prerequisite_status_id
										WHERE tpc.int_class_id = CL.ClassID
											AND (tp.str_available_for IN ('Class','All'))
											AND tpc.str_status = 'A'
											AND PS.chr_prerequisite_status = 'A') > 0
									THEN 1
								ELSE 0
							END
						ELSE 0
					  END AS bit_use_class_prerequisites
				
					, CASE
						WHEN @bit_group_class_listing_online = 1
							AND @bit_group_class_listing_non_online = 1
							THEN DENSE_RANK() OVER ( ORDER BY ClassName, ExternalCourseProviderID )
						WHEN @bit_group_class_listing_online = 1
							AND ExternalCourseProviderID > 0
							THEN DENSE_RANK() OVER ( ORDER BY ClassName, ExternalCourseProviderID )
						WHEN @bit_group_class_listing_non_online = 1
							AND ISNULL(ExternalCourseProviderID, 0) = 0
							THEN DENSE_RANK() OVER ( ORDER BY ClassName)
						ELSE 0
					END AS int_group_number
				
				, '' AS CertificateSlug
				, '' AS str_special_notice
			FROM @ClassIDTable1 CT1
				INNER JOIN tbl_class_listing CL ON CT1.ClassListingID = CL.ClassListingID
				/* PAV Tracker 25230 7/26/2011 added for full-text change  */
				/* PAV Tracker 26142 9/15/2011 altered to LEFT to accomodate using keyword field for ClassID - plus added use of FeeTextTable in AND criteria below */
				INNER JOIN classes c ON cl.classid = c.classid
				INNER JOIN Courses co ON co.CourseID = c.CourseID
				
				LEFT JOIN (SELECT ClassID, count(classid) as WaitingListCount FROM waitingLists group by ClassID) wl on cl.classid = wl.classid
				LEFT JOIN (select int_class_id, count(int_class_id) as ReservationCount from tbl_cart_class_detail group by int_class_id) ccd on ccd.int_class_id = cl.classid
				/* LEFT JOIN(SELECT int_time_period_id as TimePeriod from tbl_time_periods) tp on cd.int_time_period_id= tp.TimePeriod */
				LEFT JOIN (
					SELECT s.int_class_id
						, min(s.int_series_group_id) int_series_group_id
						, MIN(ISNULL(cs.int_course_series_type_id, 3)) int_course_series_type_id /*default is series?*/
					FROM tbl_class_course_series_group s
					INNER JOIN courseseries cs ON s.int_course_series_id = cs.courseseriesid
					INNER JOIN courseseriesgroups csg ON s.int_Series_group_id = csg.SeriesGroupID
					WHERE cs.STATUS = 'A'
						AND csg.STATUS = 'A'
						AND (
							SELECT COUNT(ClassID)
							FROM Classes
							WHERE classid IN (
									SELECT int_class_id
									FROM tbl_class_course_series_group
									WHERE int_Series_group_id = s.int_Series_group_id
									)
								AND Classes.STATUS = 'X'
							) = 0
					GROUP BY s.int_class_id
				) csj ON csj.int_class_id = cl.classid /* JS 10/29/2009 tracker 16372 Tracker 42084 Jul 24 2014 neo 00126 */
				/* PAV 6 Feb 2012 added next 2 joins so a URL can retrieve an entire category - Tracker 28161 */
				LEFT JOIN SubCategories sc ON sc.SubCategoryID = cl.SubCategoryID
				LEFT JOIN Categories ca ON ca.CategoryID = sc.CategoryID
				LEFT JOIN tbl_course_delivery_types tcdt ON tcdt.int_delivery_type_id = cl.int_delivery_type_id /* 01 JULY 2014 NEO 00088 TRACKER 41851 */
			WHERE 1 = 1
			/* PAV Tracker 26142 9/15/2011 moved to here to accomodate using keyword field for ClassID - when a join doesn't work */
			
			AND c.status IN ('A','C')/* JS 11/15/2009 Tracker 16558 */
			/*
				Below condition for checking whether user can register on the basis of
				comparison with display start on AND display end on date for open-ended schedule type
			*/
			AND
				
					@currentTimeZoneOffsetDate BETWEEN cl.AppearanceStartDate
							AND CL.AppearanceEndDate
				
			/*
				Below condition for checking whether students can register
				on basis of comparison with public registration begins AND public registration closes
			*/
			
			AND c.IsContractTraining = 0/* TRACKER 36774 AUG 20 2013 NEO 0126*/
			/* Begin:Tracker 39813 Apr 14 2014 Neo 0126 */
			
			/* End:Tracker 39813 Apr 14 2014 Neo 0126 */
			/* End Dec 07 2012 AIMembership Enhancement UC 10 Neo 00110  */
		/**************************************************************************************************/
		/******************** End select from first part of union for classes *****************************/
		/**************************************************************************************************/

		/**************************************************************************************************/
		/******************** Begin select from second part of union for course series ********************/
		/**************************************************************************************************/
		
		/**************************************************************************************************/
		/******************** End select from second part of union for course series **********************/
		/**************************************************************************************************/

		/**************************************************************************************************/
		/******************** Begin select from third part of union for Certificates **********************/
		/**************************************************************************************************/
		
		/*End Author: Neo 00098 Description : Certificates UC-16 , Date : 18 JUL 2011*/
		/**************************************************************************************************/
		/******************** End select from third  of union for Certificates ****************************/
		/**************************************************************************************************/

		/**************************************************************************************************/
		/******************** Begin select from fourth part of union for Program Sections *****************/
		/**************************************************************************************************/
		
		/**************************************************************************************************/
		/******************** End select from fourth part of union for Program Sections *******************/
		/**************************************************************************************************/

		/********************************************************************************************************/
		/******************** Begin select from fifth part of union for Certificate Groups **********************/
		/********************************************************************************************************/
		
		/*End Author: Neo 00098 Description : Certificates UC-16 , Date : 18 JUL 2011*/
		/********************************************************************************************************/
		/******************** Begin select from fifth part of union for Certificate Groups **********************/
		/********************************************************************************************************/

		/*Author: 00097 Date: 27 Aug 2011 Task: Certificate Issue Begin*/
		
			ORDER BY
			
					CL.str_class_type Desc ,
				ClassName,CLSTARTDATE,CourseNumber 
		/*Author: 00097 Date: 27 Aug 2011 Task: Certificate Issue End*/
		SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT 0 AS total_rec_count
			,*
		FROM qGetClassListing
		WHERE str_class_type = 'cf'

		UNION

		SELECT 0 AS total_rec_count
			,*
		FROM qGetClassListing
		WHERE str_class_type = 'ps'
			AND isProgramSectionSeatsAvailable = 1
		

			UNION

			SELECT 0 AS total_rec_count
				,*
			FROM qGetClassListing
			WHERE str_class_type = 'cs'
				AND bit_disp_cs_to_public = 1
		

		UNION

		SELECT 0 AS total_rec_count
			,*
		FROM qGetClassListing
		WHERE str_class_type NOT IN (
				'cf'
				,'ps'
				,'cs'
				)

SELECT *
		FROM qGetClassListingPagination
		
			ORDER BY
				
						str_class_type DESC
						,
					ClassNameSort,ClStartDate,CourseNumberSort

SELECT label FROM MenuItems WITH(NOLOCK) WHERE UniqueLabel = 'guestevents'

q_customer_by_domain (Datasource=LumensCustomers, Time=0ms) in E:\Lumens\app_setup.cfc @ 05:31:29.029

CGI Variables:
AUTH_PASSWORD=
AUTH_TYPE=
AUTH_USER=
CERT_COOKIE=
CERT_FLAGS=
CERT_ISSUER=
CERT_KEYSIZE=
CERT_SECRETKEYSIZE=
CERT_SERIALNUMBER=
CERT_SERVER_ISSUER=
CERT_SERVER_SUBJECT=
CERT_SUBJECT=
CF_TEMPLATE_PATH=E:\Lumens\index.cfm
CONTENT_LENGTH=0
CONTENT_TYPE=
CONTEXT_PATH=
GATEWAY_INTERFACE=CGI/1.1
HTTPS=off
HTTPS_KEYSIZE=
HTTPS_SECRETKEYSIZE=
HTTPS_SERVER_ISSUER=
HTTPS_SERVER_SUBJECT=
HTTP_ACCEPT=*/*
HTTP_ACCEPT_ENCODING=gzip, br, zstd, deflate
HTTP_ACCEPT_LANGUAGE=
HTTP_CONNECTION=
HTTP_COOKIE=
HTTP_HOST=testingapi.augusoft.net
HTTP_REFERER=
HTTP_URL=/index.cfm?method=ClassListing.GuestClassListingDisplay
HTTP_USER_AGENT=Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)
LOCAL_ADDR=172.31.100.171
PATH_INFO=
PATH_TRANSLATED=E:\Lumens\index.cfm
QUERY_STRING=method=ClassListing.GuestClassListingDisplay
REMOTE_ADDR=172.31.103.9
REMOTE_HOST=172.31.103.9
REMOTE_USER=
REQUEST_METHOD=GET
SCRIPT_NAME=/index.cfm
SERVER_NAME=testingapi.augusoft.net
SERVER_PORT=80
SERVER_PORT_SECURE=0
SERVER_PROTOCOL=HTTP/1.1
SERVER_SOFTWARE=Microsoft-IIS/10.0
WEB_SERVER_API=

Cookie Variables:
LUMPERSIST=8E3C700977C3FA43FE2141A9E863C7D3

Session Variables:
actortypeid=90
addtoken=CFID=15100749&CFTOKEN=4b7ad44558aac593-127A2A68-0B68-150E-A186902005B0FC27&jsessionid=3F1AE4DD473EB88AB74977200B2F4551.cfusion
allowunmaskeddata=0
appname=TestingAPI
arrtrackflowregister=Array (3)
basecfcs=versions.v81QA
basecode=/versions/v81QA
bit_canvas_failed_process=false
bit_member=0
customcct=
customcfcs=Customers.TestingAPI
customerid=581
custompath=/Customers/TestingAPI/custom
debug=0
debugoutput=1
docxdir=DocxHTML
domain=testingapi.augusoft.net
ds=TestingAPI
enabledlivechat=false
enabledsupportwidget=false
enteredsiteusingipaddress=1
foundeo_firewall_ip=216.73.216.49
foundeo_firewall_ua=Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)
loggedin=No
loggeduseremail=
loggeduserfname=Guest
loggeduserlname=
membershiptypeid=0
menuid=1
orgid=1
privacypolicyack=
regionid=1
sessionid=3F1AE4DD473EB88AB74977200B2F4551.cfusion
sitepath=/Customers/TestingAPI
sslreturnurl=https://testingapi.augusoft.net/
stylesheetpath=/Customers/TestingAPI/StyleSheets/style.css
timeoutminutes=60
urltoken=CFID=15100749&CFTOKEN=4b7ad44558aac593-127A2A68-0B68-150E-A186902005B0FC27&jsessionid=3F1AE4DD473EB88AB74977200B2F4551.cfusion
version=v81QA

URL Parameters:
method=ClassListing.GuestClassListingDisplay