Courses

	    Element ROOMNAME is undefined in REQUESTROOMRSVNQRY.
	    

	    Element CLASSTIMEDISPLAY is undefined in VARIABLES.
	    

	    Element LOCATIONNAME is undefined in REQUESTROOMRSVNQRY.
	    

	    Element CLASSNAME is undefined in REQUESTROOMRSVNQRY.
	    

	    Element CLASSSETUP.EVENTNAME is undefined in SESSION.
	    

	    Variable CLASSSCHEDULEDISP is undefined.
	    

	    Element ROOMNAME is undefined in GETROOMDATAFOREMAILQRY.
	    

	    Element ROOMCONTACTNAME is undefined in GETROOMDATAFOREMAILQRY.
	    

	    Element LOCATIONNAME is undefined in GETROOMDATAFOREMAILQRY.
	    

	    Element BIRTHDATE is undefined in GETLEARNERAGEQRY.
	    

	    Element GRADELEVEL is undefined in GETLEARNERAGEQRY.
	    

	    Element COMPANYNAME is undefined in MASTERROSTERQRY.
	    

	    Element COMPANYUSER is undefined in MASTERROSTERQRY.
	    

	    Element BASECLASSFEE is undefined in MASTERROSTERQRY.
	    

	    Element COURSENUMBER is undefined in MASTERROSTERQRY.
	    

	    Element CLASSDESCRIPTION is undefined in MASTERROSTERQRY.
	    

	    Element CLASSSCHEDULEDISPLAY is undefined in VARIABLES.
	    

	    Element INSTRNAME is undefined in MASTERROSTERQRY.
	    

	    Element ROOMNAME is undefined in REQUESTROOMRSVNQRY.
	    

	    Element CLASSTIMEDISPLAY is undefined in VARIABLES.
	    

	    Element LOCATIONNAME is undefined in REQUESTROOMRSVNQRY.
	    

	    Element CLASSNAME is undefined in REQUESTROOMRSVNQRY.
	    

	    Element CLASSSETUP.EVENTNAME is undefined in SESSION.
	    

	    Variable CLASSSCHEDULEDISP is undefined.
	    

	    Element ROOMNAME is undefined in GETROOMDATAFOREMAILQRY.
	    

	    Element ROOMCONTACTNAME is undefined in GETROOMDATAFOREMAILQRY.
	    

	    Element LOCATIONNAME is undefined in GETROOMDATAFOREMAILQRY.
	    

	    Element BIRTHDATE is undefined in GETLEARNERAGEQRY.
	    

	    Element GRADELEVEL is undefined in GETLEARNERAGEQRY.
	    

	    Element COMPANYNAME is undefined in MASTERROSTERQRY.
	    

	    Element COMPANYUSER is undefined in MASTERROSTERQRY.
	    

	    Element BASECLASSFEE is undefined in MASTERROSTERQRY.
	    

	    Element COURSENUMBER is undefined in MASTERROSTERQRY.
	    

	    Element CLASSDESCRIPTION is undefined in MASTERROSTERQRY.
	    

	    Element CLASSSCHEDULEDISPLAY is undefined in VARIABLES.
	    

	    Element INSTRNAME is undefined in MASTERROSTERQRY.
	    

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 '%lumenstesting4.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 qTempMenu where MenuItemID NOT IN  (486,453)

select * from qSocialMedia order by int_order desc

select * from qMenu where int_parent_id = 609

select * from qMenu where int_parent_id = 603

select * from qMenu where int_parent_id = 593

select * from qMenu where int_parent_id = 520

select * from qMenu where int_parent_id = 2

select * from qMenu where int_parent_id = 499

select * from qMenu where int_parent_id = 506

select * from qMenu where int_parent_id = 5

select * from qMenu where int_parent_id = 574

select * from qMenu where int_parent_id = 6

SELECT OrgID, RegionID, CustomizationID, rtrim(ltrim(FuseAction)) as fuseAction, Status, 
	CASE Status When 'A' Then 'Active' When 'I' Then 'Inactive' END AS StatusDesc,
	CreateDate, CustomizationName, CustomizationDesc
	FROM Customizations c
	WHERE Status = 'A'
	AND RegionID = 1
	Order By Status, Fuseaction

SELECT	Catalogs.CatalogID,
			Catalogs.Status,
			Catalogs.SystemCatalog,
			CASE WHEN ExternalCourseProvider.DefaultCatalogID IS NULL THEN 0
				 ELSE 1
			END AS IsExternalCourseProviderDefaultCatalog
	FROM	Catalogs
			LEFT JOIN ExternalCourseProvider
				ON ExternalCourseProvider.DefaultCatalogID = Catalogs.CatalogID
	
		WHERE	Catalogs.CatalogID IN (?)

SELECT * FROM qCatalogIsExternal WHERE IsExternalCourseProviderDefaultCatalog = 1 AND SystemCatalog = 1

SELECT * FROM qCatalogIsExternal WHERE IsExternalCourseProviderDefaultCatalog = 0

SET NOCOUNT ON

SELECT rg.ApplName
    , rg.NAME
    , rg.EMail
    , rg.EMailAux1
    , rg.EMailAux2
    , rg.EMailAux3
    , rg.EMailAux4
    , rg.EMailAux5
    , rg.EMailAux6
    , rg.theUrl
    , rg.BadWeatherText
    , rg.Phone1
    , rg.Phone1Ext
    , rg.Address1
    , rg.Address2
    , rtrim(rg.City) + ', ' + rg.STATE + '  ' + rg.Zip AS CSZ
    , rg.City
    , rg.STATE
    , rg.Zip
    , rg.ZipPlus4
    , rg.Footer
    , rg.SiteTitle
    , rtrim(rg.City) + ', ' + rg.STATE + ', ' + rg.Zip AS OrganizationCityStateZip
FROM Regions rg
WHERE rg.RegionID = ?
    AND rg.Status = 'A'

SET NOCOUNT ON

    SELECT rg.ApplName
        , rg.NAME AS OrganizationName
        , rg.EMail
        , rg.EMailAux1
        , rg.EMailAux2
        , rg.EMailAux3
        , rg.EMailAux4 AS Mailbox4
        , rg.EMailAux4 AS Mailbox5
        , rg.theUrl
        , rg.BadWeatherText
        , rg.Phone1 AS OrganizationPhone
        , rg.Phone1Ext
        , rg.Address1 AS OrganizationAddress
        , rg.Address2 AS OrganizationAddress2
        , rtrim(rg.City) + ', ' + rg.STATE + ', ' + rg.Zip AS OrganizationCityStateZip
        , rg.City
        , rg.State
        , rg.Zip
        , rg.ZipPlus4
        , rg.Footer
        , rg.SiteTitle
        -- Issue TT59300 OrganizationCity,OrganizationState,OrganizationZip
        , rg.City AS OrganizationCity
        , rg.State AS OrganizationState
        , rg.Zip AS OrganizationZip
    FROM Regions rg
    WHERE rg.RegionID = ?
        AND rg.Status = 'A'

SELECT TOP 1 CatalogID FROM Catalogs 
	WHERE SystemCatalog = 1
	AND Name = 'All Catalogs'
	ORDER BY SortOrder

SET NOCOUNT ON

		DECLARE @str_alt AS VARCHAR(max)
			, @intNumber_wAltSubCat AS INT
			, @AllcatalogId AS INT
			, @CatalogID AS INT
			, @AltCatalogID AS VARCHAR(20)
			, @currentTimeZoneOffsetDate DATETIME2 = ?
			, @MemberShipTypeID INT;

		SET @AllcatalogId = ( SELECT catalogid FROM catalogs WHERE NAME = 'All Catalogs' );
		SET @CatalogID = ?;
		SET @AltCatalogID = ?;
		SET @MemberShipTypeID = ?;
		/**/

		SET @intNumber_wAltSubCat = (
			SELECT count(ClassListingID) AS Number_wAltSubCat
			FROM tbl_class_listing tcl WITH (NOLOCK)
			
			WHERE ( tcl.str_alt_subcategory_ids IS NULL OR tcl.str_alt_subcategory_ids > '' )
				AND
				
					@currentTimeZoneOffsetDate BETWEEN tcl.AppearanceStartDate
						AND tcl.AppearanceEndDate
				
					AND tcl.bit_display_to_public = 1
				
						AND NOT EXISTS (
							SELECT 1
							FROM tbl_restriction_code_detail
							WHERE int_table_id = 2 /*classes*/
								AND int_primary_key_id = tcl.ClassID
						)
					
		)


		IF object_id('tempdb..#temp') IS NOT NULL
		BEGIN
			DROP TABLE #temp
		END

		IF object_id('tempdb..#temp1') IS NOT NULL
		BEGIN
			DROP TABLE #temp1
		END

		IF object_id('tempdb..#temp2') IS NOT NULL
		BEGIN
			DROP TABLE #temp2
		END

		IF object_id('tempdb..#temp3') IS NOT NULL
		BEGIN
			DROP TABLE #temp3
		END

		SELECT Subcategories.subcategoryid
		INTO #temp1
		FROM tbl_class_listing Classes WITH (NOLOCK)
		LEFT JOIN Catalogs WITH (NOLOCK) ON Classes.CatalogID = Catalogs.CatalogID
		INNER JOIN Courses WITH (NOLOCK) ON Classes.CourseID = Courses.CourseID
		
		INNER JOIN tbl_class_detail tcd WITH (NOLOCK) ON tcd.int_class_id = classes.classid
			AND (
				Classes.chr_Status = 'A'
				OR Classes.chr_Status = 'C'
			)
			
				AND (tcd.bit_display_to_public = 1)
				AND Classes.classid IN (
					SELECT classid
					FROM tbl_class_listing cl1 WITH (NOLOCK)
					LEFT JOIN MembershipTypes mt1 
						ON mt1.MembershipTypeID IN (
							SELECT value 
							FROM STRING_SPLIT(cl1.str_limit_self_reg_mbrship_type_ids, ',')
						)
					WHERE (
						mt1.MemberShipTypeID = @MemberShipTypeID 
						OR mt1.bit_display_classes_members_only = 0
					)

					UNION

					SELECT classid
					FROM tbl_class_listing cl1 WITH (NOLOCK)
					WHERE (cl1.str_limit_self_reg_mbrship_type_ids = '')
						OR (cl1.str_limit_self_reg_mbrship_type_ids IS NULL)
						OR (cl1.str_limit_self_reg_mbrship_type_ids = '0')
					)
			
			AND
			
				@currentTimeZoneOffsetDate BETWEEN Classes.AppearanceStartDate
					AND Classes.AppearanceEndDate
			
			AND Classes.ClassID NOT IN (
				SELECT cx.ClassID
				FROM ProgramSectionClasses cx
				GROUP BY cx.ClassID
				)
			
					AND NOT EXISTS (
						SELECT 1
						FROM tbl_restriction_code_detail
						WHERE int_table_id = 2 /*classes*/
							AND int_primary_key_id = Classes.ClassID
					)
				
		INNER JOIN Subcategories WITH (NOLOCK) ON Subcategories.SubcategoryID = Courses.SubcategoryID

		SELECT sbc.subcategoryid
			, cl1.AppearanceEndDate
			, Cl1.classid
			, cl1.str_limit_self_reg_mbrship_type_ids
			, AppearanceStartDate
			, Cl1.CatalogID
			, cl1.str_alt_catalog_ids
		INTO #temp2
		FROM tbl_class_listing cl1 WITH (NOLOCK)
		LEFT JOIN Catalogs WITH (NOLOCK) ON cl1.CatalogID = Catalogs.CatalogID
		INNER JOIN Courses WITH (NOLOCK) ON cl1.CourseID = Courses.CourseID
		INNER JOIN tbl_class_detail tcd WITH (NOLOCK) ON tcd.int_class_id = cl1.classid
		
		INNER JOIN tbl_class_course_series_group tcs WITH (NOLOCK) ON tcs.int_class_id = tcd.int_class_id
			AND (
				cl1.chr_Status = 'A'
				OR cl1.chr_Status = 'C'
				)
			AND (tcd.bit_display_to_public = 1)
			AND cl1.ClassID NOT IN (
				SELECT cx.ClassID
				FROM ProgramSectionClasses cx WITH (NOLOCK)
				GROUP BY cx.ClassID
				)
			
			
					AND NOT EXISTS (
						SELECT 1
						FROM tbl_restriction_code_detail
						WHERE int_table_id = 2 /*classes*/
							AND int_primary_key_id = cl1.ClassID
					)
				
		INNER JOIN Subcategories sbc WITH (NOLOCK) ON sbc.SubcategoryID = Courses.SubcategoryID

		/*Multiple subcategory display PAV 26 Aug 2009 This section gets a list of subcategory ids which have been assigned to classes as alternate subcategory ids*/
		/* sample code obtained from http://rafudb.blogspot.com/2009/04/sysconnectbypath-in-sql-server_24.html */
		/* This flattens the comma delimited values from several rows into one field and places them into a temp table */
		IF @intNumber_wAltSubCat > 0
		BEGIN
			
			SELECT DISTINCT t.[value]
			INTO #temp
			FROM tbl_class_listing col1 WITH (NOLOCK)
			
			CROSS APPLY STRING_SPLIT(str_alt_subcategory_ids,',') t
			WHERE str_alt_subcategory_ids IS NOT NULL
				
					AND @currentTimeZoneOffsetDate BETWEEN AppearanceStartDate
						AND AppearanceEndDate
				
				AND 0 < (
					CASE
						WHEN str_class_type = 'CS'
							THEN (
									SELECT COUNT(ClassID)
									FROM Classes Cl
									INNER JOIN tbl_class_course_series_group tcc ON tcc.int_class_id = CL.ClassID
									WHERE tcc.int_Series_group_id = col1.int_course_series_group_id
										AND Cl.STATUS = 'A'
									)
						ELSE 1
						END
					)
				
					AND bit_display_to_public = 1
				
				AND col1.ClassID NOT IN (
					SELECT cx.ClassID
					FROM ProgramSectionClasses cx
					GROUP BY cx.ClassID
				)
				
				
						AND NOT EXISTS (
							SELECT 1
							FROM tbl_restriction_code_detail
							WHERE int_table_id = 2 /*classes*/
								AND int_primary_key_id = col1.ClassID
						)
					

			SELECT classid
			INTO #temp3
			FROM tbl_class_listing cl1 WITH (NOLOCK)
			
			LEFT JOIN MembershipTypes mt1 
				ON mt1.MembershipTypeID IN (
					SELECT value 
					FROM STRING_SPLIT(cl1.str_limit_self_reg_mbrship_type_ids, ',')
				)
			WHERE 1 = 1
				AND ( 
					MemberShipTypeID = @MemberShipTypeID
					OR bit_display_classes_members_only = 0 
				)
				
					AND bit_display_to_public = 1
				
						AND NOT EXISTS (
							SELECT 1
							FROM tbl_restriction_code_detail
							WHERE int_table_id = 2 /*classes*/
								AND int_primary_key_id = cl1.ClassID
						)
					

			UNION

			SELECT classid
			FROM tbl_class_listing cl1 WITH (NOLOCK)
			
			WHERE ((cl1.str_limit_self_reg_mbrship_type_ids = '')
				OR (cl1.str_limit_self_reg_mbrship_type_ids IS NULL)
				OR (cl1.str_limit_self_reg_mbrship_type_ids = '0'))
				AND ClassID NOT IN (
					SELECT cx.ClassID
					FROM ProgramSectionClasses cx
					GROUP BY cx.ClassID
				)
				
					AND bit_display_to_public = 1
				
						AND NOT EXISTS (
							SELECT 1
							FROM tbl_restriction_code_detail
							WHERE int_table_id = 2 /*classes*/
								AND int_primary_key_id = cl1.ClassID
						)
					

			SELECT c.OrgID
				, c.RegionID
				, c.CategoryID
				, c.NAME AS CategoryName
				, sc.SubcategoryID
				, (
					SELECT ExternalCourseProviderID
					FROM ExternalCourseProvider WITH (NOLOCK)
					WHERE DefaultSubcategoryID = sc.SubCategoryID
					) AS ExternalCourseProviderID
				, sc.NAME AS SubcategoryName
				, sc.SubCategoryID AS SubCategoryID
				, sc.str_subCat_graphic AS SubCategoryImg
				, c.str_cat_graphic AS CategoryImg
				, c.SortOrder AS CategorySortOrder
				, sc.SortOrder AS SubCategorySortOrder
			FROM Categories c WITH (NOLOCK)
			LEFT JOIN SubCategories sc WITH (NOLOCK) ON c.CategoryID = sc.CategoryID
			WHERE c.STATUS = 'A'
				AND sc.STATUS = 'A'
				
				
				AND sc.int_displayed_in_category IN (
				SELECT int_displayed_in_category FROM tbl_displayed_in_category WHERE
				 (str_displayed_in_category LIKE 'Classes' OR str_displayed_in_category LIKE 'Both')
				 )
					
						AND sc.bit_available_to_students=1
					
				
				AND c.RegionID = 1 
						AND (
							EXISTS (
									SELECT subcategoryid
									FROM #temp1 t1
									WHERE t1.SubcategoryID = sc.SubCategoryID
						)
						OR sc.subcategoryid IN (
								SELECT [value]
								FROM #temp
								)
						OR sc.subcategoryid IN (
								SELECT DISTINCT CL.SubCategoryID
								FROM tbl_class_listing CL WITH (NOLOCK)
								INNER JOIN CourseSeriesGroups CSG WITH (NOLOCK) ON CSG.SeriesGroupID = CL.int_course_series_group_id
								INNER JOIN tbl_course_series_type cst WITH (NOLOCK) ON cst.int_course_series_type_id = CSG.int_course_series_type_id
								INNER JOIN tbl_class_course_series_group ccsg WITH (NOLOCK) ON ccsg.int_Series_group_id = CL.int_course_series_group_id
								LEFT JOIN tbl_class_detail tcd WITH (NOLOCK) ON tcd.int_class_id = ccsg.int_class_id
								
									AND (CSG.bit_disp_cs_to_public = 1)
								
								WHERE @currentTimeZoneOffsetDate BETWEEN CL.AppearanceStartDate AND CL.AppearanceEndDate
									
										AND tcd.int_class_id IN (
											SELECT classid FROM #temp3
											)
									
								)
						OR sc.subcategoryid IN (
								SELECT SubCategoryID
								FROM tbl_class_listing tcl WITH (NOLOCK)
								INNER JOIN tbl_certificates tc WITH (NOLOCK) ON tcl.int_certificate_id = tc.int_certificate_id
								INNER JOIN tbl_certificates_classes tcc WITH (NOLOCK) ON tcc.int_certificate_id = tc.int_certificate_id
								INNER JOIN tbl_class_detail tcd WITH (NOLOCK) ON tcd.int_class_id = tcc.int_class_id
									
										AND (tc.bit_display_to_public = 1)
									
									AND tcl.dat_publish_date IS NOT NULL
									AND tc.chr_status = 'P'
									AND (
										SELECT count(int_certificate_class_id)
										FROM tbl_certificates_classes cc WITH (NOLOCK)
										INNER JOIN tbl_class_listing cl WITH (NOLOCK) ON cc.int_class_id = cl.classid
											
										) > 0
								)
						OR sc.subcategoryid IN (
								SELECT DISTINCT cl.SubCategoryID
								FROM tbl_class_listing CL WITH (NOLOCK)
								INNER JOIN tbl_certificates tc WITH (NOLOCK) ON CL.int_certificate_id = tc.int_certificate_id
									AND tc.chr_status = 'P'
								WHERE CL.str_class_type = 'cf'
									AND @currentTimeZoneOffsetDate BETWEEN CL.AppearanceStartDate
										AND CL.AppearanceEndDate
										
											AND tc.bit_display_to_public = 1
										
											AND (
												(
													tc.bit_display_in = 1
													AND (
															cl.catalogid IN (
															@CatalogID
														, @AllcatalogId
														)
															OR ',' + str_alt_catalog_ids + ',' LIKE '%,'+@AltCatalogID+',%'
													)
													)
												OR (
													tc.bit_display_in = 0
													AND (
															cl.catalogid IN (
															@CatalogID
														, @AllcatalogId
														)
															OR ',' + str_alt_catalog_ids + ',' LIKE '%,'+@AltCatalogID+',%'
														)
													AND (
														cl.catalogid IN (
															SELECT catalogid
															FROM catalogs
															)
														OR cl.catalogid IS NULL
														)
													)
												)
										
									)
						
							OR EXISTS (
									SELECT subcategoryid
									FROM #temp2
									WHERE @currentTimeZoneOffsetDate BETWEEN AppearanceStartDate
									AND AppearanceEndDate
									AND SubcategoryID = sc.SubCategoryID
									AND classid IN (SELECT classid FROM #temp3)
										
										
										
									)
						
						)
					
			ORDER BY c.SortOrder
				, c.NAME
				, sc.SortOrder
				, sc.NAME
		END
		ELSE
		BEGIN
			SELECT c.OrgID
				, c.RegionID
				, c.CategoryID
				, c.NAME AS CategoryName
				, sc.SubcategoryID
				, (
					SELECT ExternalCourseProviderID
					FROM ExternalCourseProvider WITH (NOLOCK)
					WHERE DefaultSubcategoryID = sc.SubCategoryID
					) AS ExternalCourseProviderID
				, sc.NAME AS SubcategoryName
				, sc.SubCategoryID AS SubCategoryID
				, sc.str_subCat_graphic AS SubCategoryImg
				, c.str_cat_graphic AS CategoryImg
				, c.SortOrder AS CategorySortOrder
				, sc.SortOrder AS SubCategorySortOrder
			FROM Categories c WITH (NOLOCK)
			LEFT JOIN SubCategories sc WITH (NOLOCK) ON c.CategoryID = sc.CategoryID
			WHERE c.STATUS = 'A'
				AND sc.STATUS = 'A'
				AND c.RegionID = 1
				/* Filter by a list of catalog ID's */
				
				
				AND sc.int_displayed_in_category IN (
				SELECT int_displayed_in_category FROM tbl_displayed_in_category WHERE
				 (str_displayed_in_category LIKE 'Classes' OR str_displayed_in_category LIKE 'Both')
				 )
					
						AND sc.bit_available_to_students=1
					

					AND (
							/*
								First part of ShowOnlyCatSubCatWithActiveClass
								to get subcategories of classes that are active or confirmed
							*/
							EXISTS (
									SELECT subcategoryid
									FROM #temp1 t1
									WHERE t1.SubcategoryID = sc.SubCategoryID
								)
							/*
								Second part of ShowOnlyCatSubCatWithActiveClass to get subcategories of course series groups that are active
								Same conditions of membership, public display and display dates applies here as well.
								Condition is written only for public and not staff like above - why?? -
								AP: ***Fill in if you know the reason***
								AP: Probably because, if a course series group is active the associated class is active?
							*/
							
								OR 	EXISTS (
										SELECT subcategoryid
										FROM #temp2
										WHERE @currentTimeZoneOffsetDate BETWEEN AppearanceStartDate
										AND AppearanceEndDate
										AND SubcategoryID = sc.SubCategoryID
										
												AND #temp2.classid IN (
														SELECT classid
														FROM tbl_class_listing cl1 WITH (NOLOCK)
														LEFT JOIN MembershipTypes mt1 
															ON mt1.MembershipTypeID IN (
																SELECT value 
																FROM STRING_SPLIT(cl1.str_limit_self_reg_mbrship_type_ids, ',')
															)
														WHERE 1 = 1
															AND (
																MemberShipTypeID = @MemberShipTypeID 
																OR bit_display_classes_members_only = 0
															)
															

														UNION

														SELECT classid
														FROM tbl_class_listing cl1 WITH (NOLOCK)
														WHERE (cl1.str_limit_self_reg_mbrship_type_ids = '')
															OR (cl1.str_limit_self_reg_mbrship_type_ids IS NULL)
															OR (cl1.str_limit_self_reg_mbrship_type_ids = '0')
															
														)
											
										)
							
							/*
								Third part of ShowOnlyCatSubCatWithActiveClass to get subcategories for certificates that are active
								The dates are checeked agains appearance dates only - Probabaly needs to be written similar to first part.
							*/
							OR sc.subcategoryid IN (
								SELECT DISTINCT cl.SubCategoryID
								FROM tbl_class_listing CL WITH (NOLOCK)
								INNER JOIN tbl_certificates tc WITH (NOLOCK) ON CL.int_certificate_id = tc.int_certificate_id
									AND tc.chr_status = 'P'
								WHERE CL.str_class_type = 'cf'
									AND @currentTimeZoneOffsetDate BETWEEN CL.AppearanceStartDate AND CL.AppearanceEndDate
										
											AND tc.bit_display_to_public = 1
										
							)
						)
			
			ORDER BY c.SortOrder
				, c.NAME
				, sc.SortOrder
				, sc.NAME
		END

		IF object_id('tempdb..#temp') IS NOT NULL
		BEGIN
			DROP TABLE #temp
		END

		IF object_id('tempdb..#temp1') IS NOT NULL
		BEGIN
			DROP TABLE #temp1
		END

		IF object_id('tempdb..#temp2') IS NOT NULL
		BEGIN
			DROP TABLE #temp2
		END

		IF object_id('tempdb..#temp3') IS NOT NULL
		BEGIN
			DROP TABLE #temp3
		END

		SET NOCOUNT OFF

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	DECLARE @currentTimeZoneOffsetDate AS DATETIME2 = ?;

	SELECT Name
		, STATUS
		, Catalogs.CatalogID
		, SortOrder
		, CampusCode
		
		, AvailableForRequest
		, CASE 
			WHEN EXISTS (
					SELECT 'x'
					FROM Classes
					INNER JOIN tbl_class_listing tcl ON classes.classid = tcl.classid
					WHERE Classes.STATUS IN ('A','C')
						
						AND @currentTimeZoneOffsetDate BETWEEN Classes.AppearanceStartDate
							AND Classes.AppearanceEndDate
						AND tcl.bit_display_to_public = 1 
						
						AND (
							Classes.CatalogID = Catalogs.CatalogID
							OR (',' + RTRIM(tcl.str_alt_catalog_ids) + ',') LIKE '%,' + CAST(Catalogs.CatalogID AS VARCHAR) + ',%'
							)
					)
				THEN 1
			ELSE 0
			END AS ClassActive
		, CASE WHEN CatalogClsCnt.CatalogID IS NOT NULL OR AltCatalogClsCnt.CatalogID IS NOT NULL
				THEN 1
			ELSE 0
			END AS EverHadClass
		, str_catalog_display_name
		, bit_hide_from_menu AS hide_from_menu
		, dbo.fun_strip_html(Name) AS NameNoHTML
	FROM Catalogs 
	LEFT JOIN (
		SELECT COUNT(ClassID) AS Cnt
			, CatalogID
		FROM Classes
		GROUP BY CatalogId
		HAVING COUNT(ClassID) > 0
	) CatalogClsCnt ON CatalogClsCnt.CatalogID = Catalogs.CatalogID
	LEFT JOIN (
		SELECT COUNT(tcd.int_class_id) AS cnt
			, altCatalogs.value AS CatalogID
		FROM tbl_class_detail tcd
		CROSS APPLY STRING_SPLIT(tcd.str_alt_catalog_ids, ',') altCatalogs
		WHERE tcd.str_alt_catalog_ids IS NOT NULL AND TRIM(tcd.str_alt_catalog_ids) != ''
		GROUP BY altCatalogs.value
		HAVING COUNT(tcd.int_class_id) > 0
	) AltCatalogClsCnt ON AltCatalogClsCnt.CatalogID = Catalogs.CatalogID
	WHERE 1 = 1
		
			AND Status IN (?)
		
	ORDER BY SortOrder
	SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT DISTINCT CategoryName FROM CatSubCatQry

SELECT	ExternalCourseProvider.ExternalCourseProviderID,
			ExternalCourseProvider.DefaultSubcategoryID,
			ExternalCourseProvider.DefaultCatalogID,
			ExternalCourseProvider.IsFeatured,
			ExternalCourseProvider.FeaturedImagePath,
			Categories.CategoryID AS DefaultCategoryID,
			Subcategories.Name AS DefaultSubcategoryName,
			Categories.Name AS DefaultCategoryName,
			Subcategories.BaseCourseNumber,
			ExternalCourseProvider.CourseImportTranslationURL,
			ExternalCourseProvider.Name,
			ExternalCourseProvider.Status,
			ExternalCourseProviderType.Description AS ExternalCourseProviderTypeDescription,
			ExternalCourseProvider.ExternalCourseProviderCustomerCode,
			ExternalCourseProvider.SendRegistrationURL,
			ExternalCourseProvider.SendRegistrationEmail,
			ExternalCourseProvider.username,
			ExternalCourseProvider.password,
			ExternalCourseProvider.unit
	FROM	ExternalCourseProvider
			INNER JOIN ExternalCourseProviderType
				ON ExternalCourseProviderType.ExternalCourseProviderTypeID = ExternalCourseProvider.ExternalCourseProviderTypeID
			LEFT JOIN Subcategories
				ON ExternalCourseProvider.DefaultSubcategoryID = Subcategories.SubcategoryID
			LEFT JOIN Categories
				ON Categories.CategoryID = Subcategories.CategoryID
	WHERE	1=1
	
		AND ExternalCourseProvider.IsFeatured = 1

SELECT ContentID
		, OrgID
		, RegionID
		, Section
		, Path
		, Contents
		, ContentsNoHTML
		, Description
		, Name
		, emailTemplate
		, str_default_condition
		, str_page_title
		, str_meta_description
		, str_keywords
	FROM Contents
	WHERE Path = ?
		AND	orgid = ?
		AND	regionid= ?

SELECT NAME
					FROM Catalogs
					WHERE CatalogID IN (1)
						AND CatalogID > 0
						AND NAME <> 'All Catalogs'
					ORDER BY NAME

SELECT * FROM qCatalog WHERE ClassActive = 1

Declare @ContentCondition bit
			SET @ContentCondition = 1
			SELECT contents
				
				,contents.path
                ,contents.str_page_title
				,contents.str_meta_description
				,contents.str_keywords
				,contents.section
	    		,contents.str_desc_learner_erp_email
	    		,contents.str_desc_learner_no_erp_email
				
			FROM Contents
			WHERE @ContentCondition = @ContentCondition
			
			AND Path = ?
			AND section = ? 
			AND RegionID = ?

/*Select SubCategoryIDs where CatalogID conditions are met*/
		SELECT DISTINCT SubCategoryID
		FROM tbl_class_listing
		WHERE 1 = 1
			
				AND (
					CatalogID IN (?)
					OR EXISTS (
						SELECT 1
						FROM STRING_SPLIT(str_alt_catalog_ids, ',') AS alt
						WHERE TRIM(alt.value) IN (?)
					)
				)
			

		UNION

		/*Select individual subcategory ids from str_alt_subcategory_ids*/
		SELECT DISTINCT TRIM(value) AS SubCategoryID
		FROM tbl_class_listing
		CROSS APPLY STRING_SPLIT(str_alt_subcategory_ids, ',')
		WHERE 1 = 1
			AND str_alt_subcategory_ids IS NOT NULL AND TRIM(str_alt_subcategory_ids) != ''
			
				AND (
					CatalogID IN (?)
					OR EXISTS (
						SELECT 1
						FROM STRING_SPLIT(str_alt_catalog_ids, ',') AS alt
						WHERE TRIM(alt.value) IN (?)
					)
				)

SELECT *
		FROM CatSubCatQry
			, CatalogCoursesQry
		WHERE CatSubCatQry.SubCategoryID = CatalogCoursesQry.SubCategoryID
		ORDER BY 
			
			 CategorySortOrder
			,CategoryName
			,SubCategorySortOrder
			,SubcategoryName

Declare @ContentCondition bit
			SET @ContentCondition = 1
			SELECT contents
				
				,contents.path
                ,contents.str_page_title
				,contents.str_meta_description
				,contents.str_keywords
				,contents.section
	    		,contents.str_desc_learner_erp_email
	    		,contents.str_desc_learner_no_erp_email
				
			FROM Contents
			WHERE @ContentCondition = @ContentCondition
			
			AND Path = ?
			AND section = ? 
			AND RegionID = ?

select * from qSocialMedia order by int_order desc

q_customer_by_domain (Datasource=LumensCustomers, Time=15ms) in E:\Lumens\app_setup.cfc @ 13:28:42.042

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=lumenstesting4.augusoft.net
HTTP_REFERER=
HTTP_URL=/index.cfm?catalogid=1&fuseaction=1010
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=catalogid=1&fuseaction=1010
REMOTE_ADDR=172.31.102.152
REMOTE_HOST=172.31.102.152
REMOTE_USER=
REQUEST_METHOD=GET
SCRIPT_NAME=/index.cfm
SERVER_NAME=lumenstesting4.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=3A21AC270552E352B4DDB75568884642

Session Variables:
actortypeid=90
addtoken=CFID=14139472&CFTOKEN=71484035a4f01d75-511214D4-0719-333B-3695BBB41E498B8A&jsessionid=B0E38F25ACBDABB878F6D0D775F616A8.cfusion
allowunmaskeddata=0
appname=Lumenstesting4
basecfcs=versions.v81
basecode=/versions/v81
bit_canvas_failed_process=false
bit_member=0
customcct=
customcfcs=Customers.Lumenstesting4
customerid=576
custompath=/Customers/Lumenstesting4/custom
debug=0
debugoutput=1
docxdir=DocxHTML
domain=lumenstesting4.augusoft.net
ds=Lumenstesting4
enabledlivechat=false
enabledsupportwidget=false
enteredsiteusingipaddress=1
foundeo_firewall_ip=3.135.185.96
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=B0E38F25ACBDABB878F6D0D775F616A8.cfusion
sitepath=/Customers/Lumenstesting4
sslreturnurl=https://lumenstesting4.augusoft.net/
stylesheetpath=/Customers/Lumenstesting4/StyleSheets/style.css
timeoutminutes=60
urltoken=CFID=14139472&CFTOKEN=71484035a4f01d75-511214D4-0719-333B-3695BBB41E498B8A&jsessionid=B0E38F25ACBDABB878F6D0D775F616A8.cfusion
version=v81

URL Parameters:
catalogid=1
fuseaction=1010