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 '%v8template1.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  (582,655)

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

select * from qMenu where int_parent_id = 502

select * from qMenu where int_parent_id = 7

select * from qMenu where int_parent_id = 555

select * from qMenu where int_parent_id = 551

select * from qMenu where int_parent_id = 515

select * from qMenu where int_parent_id = 5

select * from qMenu where int_parent_id = 491

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

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

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

		SET @intNumber_wAltSubCat = (
			SELECT count(ClassListingID) AS Number_wAltSubCat
			FROM tbl_class_listing tcl WITH (NOLOCK)
			WHERE isnull(tcl.str_alt_subcategory_ids, '') > ''
				AND
				
					@currentTimeZoneOffsetDate BETWEEN tcl.AppearanceStartDate
						AND tcl.AppearanceEndDate
				
					AND tcl.bit_display_to_public = 1
				
				
		)


		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 ',' + cl1.str_limit_self_reg_mbrship_type_ids + ',' LIKE '%,' + CAST(mt1.MembershipTypeID AS VARCHAR(255)) + '%'
					WHERE 1 = 1
						AND MemberShipTypeID = 0
						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')
					)
			
			AND
			
				@currentTimeZoneOffsetDate BETWEEN Classes.AppearanceStartDate
					AND Classes.AppearanceEndDate
			
			AND Classes.ClassID NOT IN (
				SELECT cx.ClassID
				FROM ProgramSectionClasses cx
				GROUP BY cx.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
				)
			
			
			
		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 NULLIF(TRIM(col1.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
				)
				
				
				

			SELECT classid
			INTO #temp3
			FROM tbl_class_listing cl1 WITH (NOLOCK)
			LEFT JOIN MembershipTypes mt1 ON ',' + cl1.str_limit_self_reg_mbrship_type_ids + ',' LIKE '%,' + CAST(mt1.MembershipTypeID AS VARCHAR(255)) + '%'
			WHERE 1 = 1
				AND ( MemberShipTypeID = 0
				OR bit_display_classes_members_only = 0 )
				
					AND bit_display_to_public = 1
				
				

			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
				
				

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

		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) != ''

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

q_customer_by_domain (Datasource=LumensCustomers, Time=0ms) in E:\Lumens\app_setup.cfc @ 12:20:16.016

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=v8template1.augusoft.net
HTTP_REFERER=
HTTP_URL=/index.cfm?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.167
PATH_INFO=
PATH_TRANSLATED=E:\Lumens\index.cfm
QUERY_STRING=fuseaction=1010&
REMOTE_ADDR=172.31.102.122
REMOTE_HOST=172.31.102.122
REMOTE_USER=
REQUEST_METHOD=GET
SCRIPT_NAME=/index.cfm
SERVER_NAME=v8template1.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=1584A30F0A6BFB48CD6F7FB01E0851A9

Session Variables:
actortypeid=90
addtoken=CFID=10157112&CFTOKEN=d1d60b8ffc6f277-E62DDA62-E3A1-7A3E-303EEFBB3AB07445&jsessionid=2A549B4C758B5131906EB14A0109DA60.cfusion
allowunmaskeddata=0
appname=v8Template1
basecfcs=versions.v81QA
basecode=/versions/v81QA
bit_canvas_failed_process=false
bit_member=0
customcct=
customcfcs=Customers.v8Template1
customerid=486
custompath=/Customers/v8Template1/custom
debug=0
debugoutput=1
docxdir=DocxHTML
domain=v8template1.augusoft.net
ds=v8Template1
enabledlivechat=false
enabledsupportwidget=false
enteredsiteusingipaddress=1
foundeo_firewall_ip=3.18.104.220
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=2A549B4C758B5131906EB14A0109DA60.cfusion
sitepath=/Customers/v8Template1
sslreturnurl=https://v8template1.augusoft.net/
stylesheetpath=/Customers/v8Template1/StyleSheets/style.css
timeoutminutes=60
urltoken=CFID=10157112&CFTOKEN=d1d60b8ffc6f277-E62DDA62-E3A1-7A3E-303EEFBB3AB07445&jsessionid=2A549B4C758B5131906EB14A0109DA60.cfusion
version=v81QA

URL Parameters:
fuseaction=1010