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

			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', 'Support OLLI-UHM')
										/*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', 'Support OLLI-UHM')
									/*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 = ?;

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  (510)

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 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 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 qCourseMenu where int_parent_id = ? 
					and MenuItemType = ?
					and Label <> ?
					and int_parent_id<>0

select * from qCourseMenu where int_parent_id = ? 
					and MenuItemType = ?
					and Label <> ?
					and int_parent_id<>0

select * from qCourseMenu where int_parent_id = ? 
					and MenuItemType = ?
					and Label <> ?
					and int_parent_id<>0

select * from qMenu where int_parent_id = 597

select * from qMenu where int_parent_id = 525

select * from qMenu where int_parent_id = 598

select * from qMenu where int_parent_id = 595

select * from qMenu where int_parent_id = 596

select * from qMenu where int_parent_id = 503

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

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= ?

/*
				What should happen : Refer Tracker 43106
				Best Match for the 'Recommended Classes' box for a Student 
				Lumens will compare the courses for all the classes in which 
					the student has been registered (but not those for which the registrations have been cancelled or transferred) 
					to the courses that other students, who also registered in the same classes as the student, have been registered in (but not those for which the registrations have been cancelled or transferred). 
				Lumens will determine the 20 courses which are  
				1) the parents of the classes most frequently registered for and  
				2) are currently available for the student to register in. 
				  
				Lumens will display the Class Names of the three (3) classes that are children of the most frequently registered  
				courses from that list in the 'Recommended Courses' box. 
				  
				If the student has not registered for any classes, or if the user is a guest, then Lumens will determine the 20 classes that are most frequently registered for in the Category being viewed that are available for registration for this student. 
				Once determined, Lumens will display the Class Names of the three (3) courses from that list. 

				Aswin 01/02/2018 : If the student has not registered for any classes - I dont think this is handled.
						Also - arguments.recommended_type is not asked for by the spec.
			*/
			DECLARE @LearnerID AS INT
				, @SubcategoryID AS INT;

			SET @LearnerID = 0;

			

			SET @SubcategoryID = ?;

			SELECT TOP 20 co.NAME
				, co.courseid
				, co.subcategoryid
				, c.ClassID
				, c.NAME AS class_name
				, count(co.courseid) AS RegCount
			FROM registrations r WITH (NOLOCK)
			INNER JOIN classes c WITH (NOLOCK) ON r.classid = c.classid
			INNER JOIN tbl_class_detail tcd WITH (NOLOCK) ON tcd.int_class_id = c.classid
			INNER JOIN courses co WITH (NOLOCK) ON c.courseid = co.courseid
			
			WHERE 1 = 1 
				 
					AND c.dat_public_registration_closes > dbo.fun_GetDateCT()
					AND c.STATUS IN (
						'A'
						, 'C'
						) 
				
				AND co.status in ('A')
				
					AND tcd.bit_display_to_public = 1 
				
			GROUP BY co.courseid
				, co.NAME
				, co.subcategoryid
				, c.ClassID
				, c.NAME
			HAVING SUM(c.RemainingSeats) > 1
			ORDER BY RegCount DESC

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 */
				
					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 */
				
				/* 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
			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

DECLARE @currentTimeZoneOffsetDate AS DATETIME2 = ?;
		SELECT TermID
			, Code
			, NAME
			, STATUS
		FROM InstructionCalendar
		WHERE 1 = 1
			AND Status IN ('A','F')
			
				AND CatalogStartDate < @currentTimeZoneOffsetDate AND CatalogEndDate > @currentTimeZoneOffsetDate
			
			ORDER BY CatalogStartDate

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

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=AWSALBAPP-2=_remove_; AWSALBAPP-3=_remove_; JSESSIONID=53E07086F3AFB4DED00F56FB5CEA7110.cfusion; LUMPERSIST=B211E42008CC88014D6D629714766E32; AWSALBAPP-1=_remove_; AWSALBAPP-0=AAAAAAAAAABa5MMbQqbjk3v6Z48sDzDvkihnM9GbRFmIeyUpAoiC7moVs3pMurPMw3eBU8QM6f1VPrZBTDMWG3cBqH9c3kIcu4ioo95v7ehuPE6WI4N+1TLgUA3uw7xu/jYdHKJspxnz9g==
HTTP_HOST=v8testing7.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.102.152
REMOTE_HOST=172.31.102.152
REMOTE_USER=
REQUEST_METHOD=GET
SCRIPT_NAME=/index.cfm
SERVER_NAME=v8testing7.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:
AWSALBAPP-0=AAAAAAAAAABa5MMbQqbjk3v6Z48sDzDvkihnM9GbRFmIeyUpAoiC7moVs3pMurPMw3eBU8QM6f1VPrZBTDMWG3cBqH9c3kIcu4ioo95v7ehuPE6WI4N 1TLgUA3uw7xu/jYdHKJspxnz9g
AWSALBAPP-1=_remove_
AWSALBAPP-2=_remove_
AWSALBAPP-3=_remove_
JSESSIONID=53E07086F3AFB4DED00F56FB5CEA7110.cfusion
LUMPERSIST=B211E42008CC88014D6D629714766E32

Session Variables:
actortypeid=90
addtoken=CFID=14064736&CFTOKEN=118b4936f302380d-0C802131-FBC8-1F83-A302257FF2A294CD&jsessionid=53E07086F3AFB4DED00F56FB5CEA7110.cfusion
allowunmaskeddata=0
appname=v8Testing7
arrtrackflowregister=Array (6)
basecfcs=versions.v81QA
basecode=/versions/v81QA
bit_canvas_failed_process=false
bit_member=0
customcct=
customcfcs=Customers.v8Testing7
customerid=495
custompath=/Customers/v8Testing7/custom
debug=0
debugoutput=1
docxdir=DocxHTML
domain=v8testing7.augusoft.net
ds=v8Testing7
enabledlivechat=false
enabledsupportwidget=false
enteredsiteusingipaddress=1
foundeo_firewall_ip=18.117.85.73
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=53E07086F3AFB4DED00F56FB5CEA7110.cfusion
sitepath=/Customers/v8Testing7
sslreturnurl=https://v8testing7.augusoft.net/
stylesheetpath=/Customers/v8Testing7/StyleSheets/style.css
timeoutminutes=60
urltoken=CFID=14064740&CFTOKEN=7dae454b3a646571-0C803722-ABA4-622C-00B6B979060269DB&jsessionid=53E07086F3AFB4DED00F56FB5CEA7110.cfusion
version=v81QA

URL Parameters:
method=ClassListing.GuestClassListingDisplay