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 [code]
			, [key]
			, [value]
			, [serialized]
		FROM tbl_setting
		WHERE code IN ('userconfig', 'cryptoconfig', 'featureconfig')
			AND [encrypted] = 0

SELECT max(CASE is_enabled
					WHEN 1
						THEN 1
					ELSE 0
					END) [value]
			, 'bit_enabled_' + lower(base_field) + '_' + replace(lower(bft.description), ' ', '_') [key]
		FROM base_field_customized bfc
		INNER JOIN base_field_table bft ON bfc.base_table_id = bft.base_table_id
		WHERE base_field IN ('CourseProgramManager','programmanager','int_pronoun_id','int_suffix_id','str_alt_fname','class_section','customer_learner_id','birthdate','str_program_code')
		/* if the list ever grows to include more than 50% of optional fields remove the WHERE condition*/
		GROUP BY base_field
			, bft.description

Select nvs.str_name, nvp.str_erp_code from tbl_name_value_source nvs INNER JOIN tbl_name_value_pair nvp on nvs.int_name_value_source_id = nvp.int_name_value_source_id
		WHERE IsNull(nvp.str_erp_code,'') > ''

Select nvs.str_name, nvp.str_value from tbl_name_value_source nvs INNER JOIN tbl_name_value_pair nvp on nvs.int_name_value_source_id = nvp.int_name_value_source_id
		WHERE IsNull(nvp.str_value,'') > ''

SELECT ProductId
			, Code AS ProductCode
			, Name AS ProductName
			, str_erp_code AS ProductERPCode
		FROM Products
		WHERE code IN ('CLASS','MATLS')
			OR code LIKE '%LABFEE'
			OR code LIKE '%INSFEE'
			OR code LIKE '%BUILDINGFEE'
			OR code LIKE '%BUILDFEE'
			OR code LIKE '%BOOKFEE'
			OR code LIKE '%TUITIONFEE'
			OR code LIKE '%MATFEE'
			OR code LIKE '%PROGRAMFEE'

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

SELECT Label
		,STATUS
		, dbo.fun_strip_html(Label) as LabelNoHTML
	FROM MenuItems WITH (NOLOCK)
	WHERE SiteURL = 'index.cfm?fuseaction=1010'

SELECT str_user_nav_label
			, dbo.fun_strip_html(str_user_nav_label) as str_user_nav_label_noHTML
		FROM tbl_user_nav_menu_label WITH (NOLOCK)
		WHERE int_menu_item_id = 1
			AND int_actor_type_id  = ?

SET NOCOUNT ON
			SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
			DECLARE @TimeZoneShift AS FLOAT
				, @str_alt AS VARCHAR(max)
				, @intNumber_wAltSubCat AS INT
				, @RegionID BIGINT
				, @MemberShipTypeID INT;

			SET @RegionID = ?;
			SET @MemberShipTypeID = ?

			SET @TimeZoneShift = ISNULL((
						SELECT TimeZoneHourAdjustment
						FROM Regions
						WHERE RegionID = @RegionID
						), 0);
			SET @intNumber_wAltSubCat = (
					SELECT count(*) AS Number_wAltSubCat
					FROM tbl_class_listing tcl
					WHERE ISNULL(str_alt_subcategory_ids, '') > ''
						AND
						/**/
						
								DateAdd(hh, @TimeZoneShift, dbo.fun_GetDateCT()) BETWEEN tcl.AppearanceStartDate
									AND tcl.AppearanceEndDate
						
						/**/
						AND 0 < (
							CASE 
								WHEN tcl.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 = tcl.int_course_series_group_id
												AND Cl.STATUS = 'A'
											)
								ELSE 1
								END
							) 
						/**/
						
							AND tcl.bit_display_to_public = 1 /**/
						
						
					)

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

			/* 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
					convert(INT, Split.a.value('.', 'VARCHAR(100)')   ) AS [value] 
				into #temp
				FROM  (SELECT   
							CAST ('<M>' + REPLACE(COALESCE(NULLIF(str_alt_subcategory_ids, ''),'0'), ',', '</M><M>') + '</M>' AS XML) AS String  
						FROM tbl_class_listing tcl
						WHERE str_alt_subcategory_ids IS NOT NULL
						AND
						/**/
						
							DateAdd(hh, @TimeZoneShift, dbo.fun_GetDateCT()) BETWEEN tcl.AppearanceStartDate
								AND tcl.AppearanceEndDate
																							
						/**/
						
							AND tcl.bit_display_to_public = 1 /**/
						)AS A CROSS APPLY String.nodes ('/M') AS Split(a);
						

				SELECT c.OrgID
					,c.RegionID
					,c.CategoryID
					,c.NAME AS CategoryName
					,dbo.fun_strip_html(c.NAME) as CategoryNameNoHTML
					,sc.SubcategoryID
					,(
						SELECT ExternalCourseProviderID
						FROM ExternalCourseProvider
						WHERE DefaultSubcategoryID = sc.SubCategoryID
						) AS ExternalCourseProviderID
					,sc.NAME AS SubcategoryName
					,dbo.fun_strip_html(sc.NAME) as SubcategoryNameNoHTML
					,c.SortOrder AS categsortoder
					,sc.SortOrder AS subcategsortorder
				FROM Categories c
				LEFT JOIN SubCategories sc ON c.CategoryID = sc.CategoryID
				WHERE c.STATUS = 'A'
					AND sc.STATUS = 'A'
					AND c.RegionID = @RegionID
					AND (

						EXISTS (
							SELECT Subcategories.subcategoryid
							FROM Classes
							LEFT JOIN Catalogs ON Classes.CatalogID = Catalogs.CatalogID
							INNER JOIN Courses ON Classes.CourseID = Courses.CourseID
							INNER JOIN tbl_class_detail tcd ON tcd.int_class_id = Classes.ClassID 
								/**/
									AND (
										Classes.STATUS = 'A'
										OR Classes.STATUS = 'C'
										)
								/**/
								
									AND (tcd.bit_display_to_public = 1)
									/**/
									AND Classes.classid IN (
										SELECT classid
										FROM tbl_class_listing cl1
										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 = @MemberShipTypeID
											OR bit_display_classes_members_only = 0 
											/**/
										
										UNION
										
										SELECT classid
										FROM tbl_class_listing cl1
										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 DateAdd(hh, @TimeZoneShift, dbo.fun_GetDateCT()) BETWEEN Classes.AppearanceStartDate
										AND DateAdd(ms, - 10, Classes.AppearanceEndDate)
								
								/**/
								AND Classes.ClassID NOT IN (
									SELECT cx.ClassID
									FROM ProgramSectionClasses cx
									GROUP BY cx.ClassID
									)
								
								
								
							INNER JOIN Subcategories ON Subcategories.SubcategoryID = Courses.SubcategoryID
								AND Subcategories.SubcategoryID = sc.SubCategoryID
								
						)
						OR sc.subcategoryid IN (
								SELECT [value]
								FROM #temp
								)
						OR sc.subcategoryid IN (
								/**/
								/*Select SubCategoryID from tbl_class_listing where int_course_series_group_id > 0*/
								SELECT DISTINCT CL.SubCategoryID
								FROM tbl_class_listing CL
								INNER JOIN CourseSeriesGroups CSG ON CSG.SeriesGroupID = CL.int_course_series_group_id
								INNER JOIN tbl_course_series_type cst ON cst.int_course_series_type_id = CSG.int_course_series_type_id
								INNER JOIN tbl_class_course_series_group ccsg ON ccsg.int_Series_group_id = CL.int_course_series_group_id
								INNER JOIN Classes ON classes.ClassID = ccsg.int_class_id
								INNER JOIN tbl_class_detail tcd ON tcd.int_class_id = Classes.ClassID 
									/* */
									
										AND (csg.bit_disp_cs_to_public = 1)
										/**/
										AND Classes.classid IN (
											SELECT classid
											FROM tbl_class_listing cl1
											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 = @MemberShipTypeID
												OR bit_display_classes_members_only = 0 /**/
											
											UNION
											
											SELECT classid
											FROM tbl_class_listing cl1
											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')
											)
										/**/
									
									/* */
								WHERE 
									
										DateAdd(hh, @TimeZoneShift, dbo.fun_GetDateCT()) BETWEEN Classes.AppearanceStartDate
											AND DateAdd(ms, - 10, Classes.AppearanceEndDate) 
									
									/**/
									AND 0 = (
										SELECT Count(cl3.classid)
										FROM classes cl3
										WHERE classID = ccsg.int_class_id
											AND STATUS IN ('X','D','I','R')
										)
									/**/
								)
						/**/
						OR sc.subcategoryid IN (
								SELECT SubCategoryID
								FROM tbl_class_listing tcl
								INNER JOIN tbl_certificates_classes tcc ON tcc.int_class_id = tcl.ClassID
								INNER JOIN Classes c ON c.ClassID = tcl.ClassID
								INNER JOIN tbl_class_detail tcd ON tcd.int_class_id = c.ClassID 
									
										AND (tcl.bit_display_to_public = 1)
										/**/
										AND c.ClassID IN (
											SELECT classid
											FROM tbl_class_listing cl1
											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 = @MemberShipTypeID
												OR bit_display_classes_members_only = 0 /**/
											
											UNION
											
											SELECT classid
											FROM tbl_class_listing cl1
											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')
											)
										/**/
									
								)
						/* */
						OR sc.subcategoryid IN (
								SELECT DISTINCT cl.SubCategoryID
								FROM tbl_class_listing CL
								INNER JOIN tbl_certificates tc ON CL.int_certificate_id = tc.int_certificate_id
									AND tc.chr_status = 'P'
								WHERE CL.str_class_type = 'cf'
									AND DateAdd(hh, @TimeZoneShift, dbo.fun_GetDateCT()) BETWEEN CL.AppearanceStartDate
										AND DateAdd(ms, - 10, CAST(CL.AppearanceEndDate AS DATETIME2)) 
										
											AND tc.bit_display_to_public = 1 
										
									
								)
						/**/
					)
				ORDER BY c.SortOrder
					,c.NAME
					,sc.SortOrder
					,sc.NAME

				DROP TABLE #temp
			END
			ELSE
			BEGIN
				SELECT c.OrgID
					,c.RegionID
					,c.CategoryID
					,c.NAME AS CategoryName
					,dbo.fun_strip_html(c.NAME) as CategoryNameNoHTML
					,sc.SubcategoryID
					,(
						SELECT ExternalCourseProviderID
						FROM ExternalCourseProvider
						WHERE DefaultSubcategoryID = sc.SubCategoryID
						) AS ExternalCourseProviderID
					,sc.NAME AS SubcategoryName
					,dbo.fun_strip_html(sc.NAME) as SubcategoryNameNoHTML
					,c.SortOrder AS categsortoder
					,sc.SortOrder AS subcategsortorder
				FROM Categories c
				LEFT JOIN SubCategories sc ON c.CategoryID = sc.CategoryID
				WHERE c.STATUS = 'A'
					AND sc.STATUS = 'A'
					AND c.RegionID = 1
					AND (
						EXISTS (
							SELECT Subcategories.subcategoryid
							FROM Classes
							LEFT JOIN Catalogs ON Classes.CatalogID = Catalogs.CatalogID
							INNER JOIN Courses ON Classes.CourseID = Courses.CourseID
							INNER JOIN tbl_class_detail tcd ON tcd.int_class_id = Classes.ClassID /**/
								AND (
									Classes.STATUS = 'A'
									OR Classes.STATUS = 'C'
									)
								/**/
								
									AND (tcd.bit_display_to_public = 1)
									/**/
									AND Classes.classid IN (
										SELECT classid
										FROM tbl_class_listing cl1
										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 = @MemberShipTypeID
											OR bit_display_classes_members_only = 0 /**/
										
										UNION
										
										SELECT classid
										FROM tbl_class_listing cl1
										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 DateAdd(hh, @TimeZoneShift, dbo.fun_GetDateCT()) BETWEEN Classes.AppearanceStartDate
										AND DateAdd(ms, - 10, Classes.AppearanceEndDate)
								
								/**/
								AND Classes.ClassID NOT IN (
									SELECT cx.ClassID
									FROM ProgramSectionClasses cx
									GROUP BY cx.ClassID
									)
								
								
								
							INNER JOIN Subcategories ON Subcategories.SubcategoryID = Courses.SubcategoryID
								AND Subcategories.SubcategoryID = sc.SubCategoryID
								
							)
						)
				ORDER BY c.SortOrder
					,c.NAME
					,sc.SortOrder
					,sc.NAME
			END
			SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT DISTINCT categoryid
				,categoryname
				,CategoryNameNoHTML
				, LOWER(CategoryNameNoHTML) categorynamesort
			, categsortoder
			FROM CatSubCatQry
			ORDER BY categsortoder
				, categorynamesort

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	DECLARE @TimeZoneShift AS FLOAT;
	/**/
	SET @TimeZoneShift = IsNull((
				SELECT TimeZoneHourAdjustment
				FROM Regions
				WHERE RegionID = ?
				), 0)

	DROP TABLE IF EXISTS #altCatalogs

	SELECT COUNT(tbl_class_detail.int_class_id) as cnt,altCatalogs.value
	INTO #altCatalogs
	FROM tbl_class_detail
	CROSS APPLY STRING_SPLIT(tbl_class_detail.str_alt_catalog_ids, ',') altCatalogs
	WHERE NULLIF(TRIM(tbl_class_detail.str_alt_catalog_ids),'') IS NOT NULL
	GROUP BY altCatalogs.value
	
	SELECT Name
		, STATUS
		, CatalogID
		, SortOrder
		, CampusCode
		, county_id
		, dbo.fun_base_custom_field_stored_value(county_id, 'county_id', RegionID) AS county_id_description
		, AvailableForRequest
		, CASE 
			WHEN EXISTS (
					SELECT 'x'
					FROM Classes
					INNER JOIN tbl_class_listing tcl ON classes.classid = tcl.classid
					WHERE (
							Classes.STATUS = 'A'
							OR Classes.STATUS = 'C'
							) 
						
						AND DateAdd(hh, @TimeZoneShift, dbo.fun_GetDateCT()) BETWEEN Classes.AppearanceStartDate
							AND DateAdd(ms, - 10, 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 (
					SELECT COUNT(ClassID)
					FROM Classes
					WHERE Classes.CatalogID = Catalogs.CatalogID
				) > 0
				THEN 1
			WHEN (
				SELECT cnt
				FROM #altCatalogs
				WHERE [value] = Catalogs.CatalogID
			) > 0
				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
	WHERE 1 = 1
		
	ORDER BY SortOrder
	SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT * FROM qCatalog WHERE ClassActive = 1 AND status = 'A' AND  hide_from_menu = 0 AND SortOrder <> 0

SELECT COUNT(int_certificate_id) AS ct_count
			FROM tbl_certificates WITH (NOLOCK)
			WHERE chr_status = 'P'
				
					AND bit_display_to_public = 1

DECLARE @TimeZoneShift AS FLOAT;

			SET @TimeZoneShift = ISNULL((
						SELECT TimeZoneHourAdjustment
						FROM Regions
						WHERE RegionID = 1
						), 0)

			SELECT COUNT(ClassListingID) AS cs_count
			FROM tbl_class_listing tbl WITH (NOLOCK)
			INNER JOIN tbl_class_course_series_group ccsg WITH (NOLOCK) ON ccsg.int_Series_group_id = tbl.int_course_series_group_id
			INNER JOIN Classes WITH (NOLOCK) ON classes.ClassID = ccsg.int_class_id
			WHERE tbl.chr_status = 'A'
				AND tbl.str_class_type = 'CS'
				AND DateAdd(hh, @TimeZoneShift, dbo.fun_GetDateCT()) BETWEEN tbl.AppearanceStartDate
					AND DateAdd(ms, - 10, tbl.AppearanceEndDate)
				AND 0 = (
					SELECT Count(cl3.classid)
					FROM classes cl3 WITH (NOLOCK)
					WHERE classID = ccsg.int_class_id
						AND STATUS IN (
							'X'
							,'D'
							,'I'
							,'R'
							)
					)
				
					AND tbl.bit_display_to_public = 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', '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 = ?

SET NOCOUNT ON
SELECT rg.Name, 
rg.RegionID, 
ui.TableWidth, 
ui.CatGraphicName, 
ui.CatImageWidth, 
ui.CatImageHeight, 
ui.CatImageAltTag, 
ui.CatOnGraphicUsed, 
ui.CatOnGraphicName, 
ui.CatLink, 
ui.BannerGraphicName, 
ui.BannerImageWidth, 
ui.BannerImageHeight, 
ui.BannerAltTag, 
ui.BannerLink1_AltTag, 
ui.BannerLink1_X1, 
ui.BannerLink1_X2, 
ui.BannerLink1_Y1, 
ui.BannerLink1_Y2, 
ui.BannerLink1_URL, 
ui.BannerLink1_IsPopup, 
ui.BannerLink2_AltTag, 
ui.BannerLink2_X1, 
ui.BannerLink2_X2, 
ui.BannerLink2_Y1, 
ui.BannerLink2_Y2, 
ui.BannerLink2_URL, 
ui.BannerLink2_IsPopup, 
ui.BannerLink3_AltTag, 
ui.BannerLink3_X1, 
ui.BannerLink3_X2, 
ui.BannerLink3_Y1, 
ui.BannerLink3_Y2, 
ui.BannerLink3_URL, 
ui.BannerLink3_IsPopup, 
ui.BannerLink4_AltTag, 
ui.BannerLink4_X1, 
ui.BannerLink4_X2, 
ui.BannerLink4_Y1, 
ui.BannerLink4_Y2, 
ui.BannerLink4_URL, 
ui.BannerLink4_IsPopup, 
ui.BannerLink5_AltTag, 
ui.BannerLink5_X1, 
ui.BannerLink5_X2, 
ui.BannerLink5_Y1, 
ui.BannerLink5_Y2, 
ui.BannerLink5_URL, 
ui.BannerLink5_IsPopup,
ui.bit_logo_enable,

ui.BannerLink6_AltTag, 
IsNULL(ui.BannerLink6_X1,0) AS BannerLink6_X1, 
ISNULL(ui.BannerLink6_X2,0) AS BannerLink6_X2, 
IsNULL(ui.BannerLink6_Y1,0) AS BannerLink6_Y1, 
IsNULL(ui.BannerLink6_Y2,0) AS BannerLink6_Y2, 
ui.BannerLink6_URL, 
ui.BannerLink6_IsPopup,
 
ui.BannerLink7_AltTag, 
IsNULL(ui.BannerLink7_X1,0) AS BannerLink7_X1, 
ISNULL(ui.BannerLink7_X2,0) AS BannerLink7_X2, 
IsNULL(ui.BannerLink7_Y1,0) AS BannerLink7_Y1, 
IsNULL(ui.BannerLink7_Y2,0) AS BannerLink7_Y2, 
ui.BannerLink7_URL, 
ui.BannerLink7_IsPopup,

ui.BannerLink8_AltTag, 
IsNULL(ui.BannerLink8_X1,0) AS BannerLink8_X1, 
ISNULL(ui.BannerLink8_X2,0) AS BannerLink8_X2, 
IsNULL(ui.BannerLink8_Y1,0) AS BannerLink8_Y1, 
IsNULL(ui.BannerLink8_Y2,0) AS BannerLink8_Y2, 
ui.BannerLink8_URL, 
ui.BannerLink8_IsPopup,

ui.BannerLink9_AltTag, 
IsNULL(ui.BannerLink9_X1,0) AS BannerLink9_X1, 
ISNULL(ui.BannerLink9_X2,0) AS BannerLink9_X2, 
IsNULL(ui.BannerLink9_Y1,0) AS BannerLink9_Y1, 
IsNULL(ui.BannerLink9_Y2,0) AS BannerLink9_Y2, 
ui.BannerLink9_URL, 
ui.BannerLink9_IsPopup,

ui.BannerLink10_AltTag, 
IsNULL(ui.BannerLink10_X1,0) AS BannerLink10_X1, 
ISNULL(ui.BannerLink10_X2,0) AS BannerLink10_X2, 
IsNULL(ui.BannerLink10_Y1,0) AS BannerLink10_Y1, 
IsNULL(ui.BannerLink10_Y2,0) AS BannerLink10_Y2, 
ui.BannerLink10_URL, 
ui.BannerLink10_IsPopup,

ui.LLMSImageUsed, 
ui.MenuWaitLength, 
ui.Menulevel1Height, 
ui.MenuLevel2Height, 
ui.MenuLevel2Width, 
ui.featuredClassGraphicName, 
ui.FeaturedClassImageWidth, 
ui.FeaturedClassImageHeight, 
ui.FeaturedClassAltTag, 
ui.FeaturedClassImageTop, 
ui.AltGraphicUsed, 
ui.AltGraphicName, 
ui.AltImageWidth, 
ui.AltImageHeight, 
ui.AltImageAltTag, 
ui.AltImageURL,    

/*Begin NEO 00164 06 dec 2015 SU2 F-7 */
ui.bit_banner_type,
ui.str_desktop_banner_logo,
ui.str_desktop_banner_logo_text,
ui.str_egifts_card_icon,
ui.str_egifts_card_icon_text,
ui.str_login_icon,
ui.str_login_icon_text,
ui.str_logout_icon,
ui.str_logout_icon_text,
ui.str_view_cart_icon,
ui.str_view_cart_icon_text,
ui.str_checkout_icon,
ui.str_checkout_icon_text,
ui.str_footer_logo,
ui.str_footer_logo_text,
ui.str_search_icon,
ui.str_search_icon_text,
ui.str_mobile_search_icon_text,
ui.str_mobile_search_icon,
ui.str_close_mobile_icon,
ui.str_close_mobile_icon_text,
ui.str_browse_icon,
ui.str_browse_icon_text,
ui.str_browse_secondary_icon,
ui.str_browse_secondary_icon_text,
ui.str_back_to_top_icon,
ui.str_back_to_top_icon_text,
ui.str_course_info_icon,
ui.str_course_info_icon_text,
ui.str_close_icon,
ui.str_close_icon_text,
ui.str_tablet_banner_logo,
ui.str_tablet_banner_logo_text,
ui.BgAccountRepeat,
ui.BgFooterRepeat,
ui.BgHeadergradientRepeat,
ui.BgHeaderRepeat,
ui.BgPrefooterRepeat
/* End NEO 00164 06 dec 2015 SU2 F-7 */
FROM UIVariables ui INNER JOIN Regions rg ON ui.RegionID = rg.RegionID
WHERE rg.Status = 'A'

SELECT
			str_phone_type,
			str_erp_url
			,str_erp_login
			,bit_update_erp_allowed
			,CASE WHEN Convert(integer,lefT(convert(varchar(20),SERVERPROPERTY('productversion')),PatIndex('%.%',Convert(varchar(20),(select SERVERPROPERTY('productversion'))))-1)) < 12 THEN CAST(DecryptByKeyAutoCert(cert_id('LumensCert'), NULL, varbin_erp_password) AS Varchar(500))  ELSE CAST(DecryptByKeyAutoCert(cert_id('LumensCert'), NULL, varbin_erp_password) AS NVarchar(500))  END as str_erp_password_decrypted
			,str_erp_site_prefix
			,bit_erp_learner_sync
			,bit_erp_company_sync
			,bit_erp_course_sync
			,bit_erp_class_sync
			,bit_erp_location_sync
			,bit_erp_room_sync
			,bit_erp_registration_sync
			,bit_erp_financial_sync
			,bit_erp_instructor_sync
			,bit_erp_contract_sync
			,bit_erp_grades_sync
			,str_erp_admin_user_id
			,bit_erp_learner_realtime
			,bit_erp_company_realtime
			,bit_erp_course_realtime
			,bit_erp_class_realtime
			,bit_erp_location_realtime
			,bit_erp_room_realtime
			,bit_erp_registration_realtime
			,bit_erp_financial_realtime
			,bit_erp_instructor_realtime
			,bit_erp_contract_realtime
			,bit_erp_grades_realtime
			,str_ws_learner
			,str_ws_course
			,str_ws_company
			,str_ws_instructor
			,str_ws_class
			,str_ws_location
			,str_ws_room
			,str_ws_registration
			,str_ws_transaction
			,str_ws_contract
			,str_ws_grades,
			
			bit_erp_getlearner_sync,
			bit_erp_getcompany_sync,
			bit_erp_getcourse_sync,
			bit_erp_getclass_sync,
			bit_erp_getlocation_sync,
			bit_erp_getroom_sync,
			bit_erp_getinstructor_sync,
			bit_erp_getlearner_realtime,
			bit_erp_getcompany_realtime,
			bit_erp_getcourse_realtime,
			bit_erp_getclass_realtime,
			bit_erp_getlocation_realtime,
			bit_erp_getroom_realtime,
			bit_erp_getinstructor_realtime,
			str_ws_getlearner,
			str_ws_getinstructor,
			str_ws_getcompany,
			str_ws_getcourse,
			str_ws_getclass,
			str_ws_getlocation,
			str_ws_getroom
			
			,str_erp_site_code_1
			,banner_type
			,bit_names_read_only_non_staff
			,int_update_linked_profiles
			
			
			
			,bit_value_list_sync  
		FROM tbl_erp_config

SELECT
				str_app_id
				,str_app_key
				,str_call_back_url
				,str_scope
			FROM tbl_facebook_config

SET NOCOUNT ON
	SELECT (
			CASE bit_use_predefined_template
				WHEN 1
					THEN int_site_template_id
				WHEN 0
					THEN int_customized_template_id
				END
			) AS int_template_id
		, 'int_template_id' AS str_column_name 
	FROM tbl_style_settings

	UNION 

	SELECT t.int_template_id,tt.str_column_name
	FROM tbl_site_template_setting ts
	INNER JOIN tbl_site_template_type tt ON ts.int_template_type_id = tt.int_template_type_id
	INNER JOIN tbl_site_template t ON t.int_template_id = ts.int_template_id

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

UPDATE tbl_cart_class_detail
			SET EnteredDate = dbo.fun_GetDateCT()
			WHERE SessionID = ?;
			DELETE
			FROM tbl_cart_class_detail
			WHERE DATEDIFF(minute,EnteredDate,dbo.fun_GetDateCT()) >= 61;
			UPDATE tbl_cart_merchandise_detail
			SET EnteredDate = dbo.fun_GetDateCT()
			WHERE SessionID = ?;
			DELETE FROM tbl_cart_merchandise_detail
			WHERE DATEDIFF(minute,EnteredDate,dbo.fun_GetDateCT()) >= 61;

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  (562,2,633)

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

select * from qMenu where int_parent_id = 532

select * from qMenu where int_parent_id = 538

select * from qMenu where int_parent_id = 539

select * from qMenu where int_parent_id = 540

select * from qMenu where int_parent_id = 5

select * from qMenu where int_parent_id = 6

select * from qMenu where int_parent_id = 7

select * from qMenu where int_parent_id = 458

select * from qMenu where int_parent_id = 500

select * from qMenu where int_parent_id = 532

select * from qMenu where int_parent_id = 538

select * from qMenu where int_parent_id = 539

select * from qMenu where int_parent_id = 540

select * from qMenu where int_parent_id = 5

select * from qMenu where int_parent_id = 6

select * from qMenu where int_parent_id = 7

select * from qMenu where int_parent_id = 458

select * from qMenu where int_parent_id = 500

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 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 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 qSocialMedia order by int_order desc

q_customer_by_domain (Datasource=LumensCustomers, Time=0ms) in E:\Lumens\app_setup.cfc @ 10:58:00.000

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=
HTTP_ACCEPT_LANGUAGE=
HTTP_CONNECTION=
HTTP_COOKIE=
HTTP_HOST=v8template2.augusoft.net
HTTP_REFERER=
HTTP_URL=/index.cfm
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=
REMOTE_ADDR=172.31.103.52
REMOTE_HOST=172.31.103.52
REMOTE_USER=
REQUEST_METHOD=GET
SCRIPT_NAME=/index.cfm
SERVER_NAME=v8template2.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=51414C193DDB0DE028F5DDCF747F1AD8

Session Variables:
actortypeid=90
addtoken=CFID=8003655&CFTOKEN=e91b98f1236aa3eb-0F470134-FA85-F4DF-51E51D3F3E8373B4&jsessionid=B7A916AEE4FB0D93F2836489009169AF.cfusion
allowunmaskeddata=0
appname=v8Template2
basecfcs=versions.v81QA
basecode=/versions/v81QA
bit_canvas_failed_process=false
bit_member=0
customcct=
customcfcs=Customers.v8Template2
customerid=487
custompath=/Customers/v8Template2/custom
debug=0
debugoutput=1
docxdir=DocxHTML
domain=v8template2.augusoft.net
ds=v8Template2
enabledlivechat=false
enabledsupportwidget=false
enteredsiteusingipaddress=1
foundeo_firewall_ip=3.137.170.183
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=B7A916AEE4FB0D93F2836489009169AF.cfusion
sitepath=/Customers/v8Template2
sslreturnurl=https://v8template2.augusoft.net/
stylesheetpath=/Customers/v8Template2/StyleSheets/style.css
timeoutminutes=60
urltoken=CFID=8003655&CFTOKEN=e91b98f1236aa3eb-0F470134-FA85-F4DF-51E51D3F3E8373B4&jsessionid=B7A916AEE4FB0D93F2836489009169AF.cfusion
version=v81QA