Test Certificate One (3331) Information

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 int_certificate_id
		FROM tbl_certificates
		WHERE chr_status = 'P'
			AND str_url_slug = ?

SELECT tc.[int_certificate_id]
		,tc.[str_certificate_name]
		,tc.[int_certificate_type_id]
		,tc.[str_description]
		,tc.[str_description_no_html]
		,tc.[str_graphic_name]
		,tc.str_certificate_no
		,tc.[int_courses_offered]
		,tc.[str_acct_code]
		,tc.[int_refund_policy_id]
		,tc.[int_staff_contact_id]
		,tc.[str_phone]
		,tc.[str_phone_ext]
		,tc.[int_phone_type_id]
		,tc.[str_contact_email]
		,tc.[int_courses_required_completion]
		,tc.[bit_grades_required]
		,tc.[bit_attendance_required]
		,tc.[bit_test_score]
		,tc.[int_internal_passing_score]
		,tc.[int_external_passing_score]
		,tc.[int_allow_elective_option_id]
		,tc.[bit_enforce_time_limit]
		,tc.[int_months_after_class_start_date]
		,tc.[date_retroactive_credit_for_classes_after]
		,tc.[int_category_id]
		,tc.[int_subcategory_id]
		,tc.[str_alt_subcategory_ids]
		,tc.[bit_display_to_public]
		,tc.[bit_allow_public_registration]
		,tc.[bit_ordered]
		,tc.[bit_online_courses_only]
		,tc.[bit_multiple_elective_groups]
		,tc.[bit_all_req_reg_in_one_transaction]
		,tc.[bit_allow_retroactive_credit]
		,tc.[int_months_prior_to_publish_date]
		,tc.[chr_status]
		,tc.[str_template_ids]
		
		,tc.bit_uses_open_ended_courses
		
		,tc.bit_allow_ct_info /*Added June 27 2012 Certificate Change:To dispay certificate information in student login Neo 00110 */
		,(SELECT COUNT(bit_elective) from tbl_certificates_courses  where int_certificate_id=?
				and bit_elective=0) as mandatorycount
		,(tc.[int_courses_offered] - (SELECT COUNT(bit_elective) from tbl_certificates_courses  where int_certificate_id=? and bit_elective=0)) as electivecount
		/* BEGIN: NEO 00091 Date:05 Aug 2012 AI V7.3 Full-Time Certificate Use Cases 2012-08-03 UC-08 */
		,[bit_use_program_sections]
		,[bit_use_payment_periods]
		,[int_no_of_payment_periods]
		,[bit_use_prerequisite]
		,[bit_requires_release_form]
		,[int_release_form_name]
		/* END: NEO 00091 Date:05 Aug 2012 AI V7.3 Full-Time Certificate Use Cases 2012-08-03 UC-08 */
		
		,tc.bit_info_request
		,tc.str_info_request_email
		
		
		,tc.str_certificate_icon
		,tc.str_certificate_icon_alt_descr
		
		
		,tc.bit_auto_increment_reserved_seats
		,tc.int_auto_increment_reserved_seats
		
		
		
			,tc.int_grade_scale_id
			,tc.int_grade_detail_template_id
		
		
		,(SELECT COUNT(tcc.int_course_id) FROM tbl_certificates_courses tcc WHERE tcc.int_certificate_id = tc.int_certificate_id) AS Course_no
		, tc.bit_has_domicile_discount
		, tc.int_certificate_merchandise
		, tc.str_default_text_for_call_message_class
		
			, 1 AS bit_allow_wcg_registration
		
		, tc.bit_allow_external_credits
		, tc.bit_use_badges
		, tc.int_badge_integration_id
		, tc.int_badge_integration_template_id
		, tc.str_badge_template_label
		, tc.int_program_version as anthology_int_program_version
		, tbit.str_template_name
		, tc.bit_use_certificate_groups
		, tc.bit_display_pricing_on_receipt
		, tc.int_minimum_age
		, tc.int_maximum_age
		, tc.bit_use_class_pricing
		, tct.str_certificate_type_name
	FROM tbl_certificates tc
	LEFT JOIN tbl_badge_integration_templates tbit ON tc.int_badge_integration_template_id = tbit.int_badge_integration_template_id
	LEFT JOIN tbl_certificate_type tct ON tct.int_certificate_type_id = tc.int_certificate_type_id
  	WHERE tc.int_certificate_id = ?

SET NOCOUNT ON
    	SELECT 	 rg.Phone1, rg.Phone1Ext
    	FROM Regions rg
    	WHERE rg.RegionID = 1 AND rg.Status = 'A'

SELECT str_certificate_name FROM FeatureConfiguration

SELECT tcc.int_course_id,tcc.bit_elective,co.Name,co.Description,co.CourseNumber FROM tbl_certificates_courses tcc
		INNER JOIN Courses co ON tcc.int_course_id = co.CourseID
		WHERE int_certificate_id=? 
		ORDER BY co.Name

SELECT
             tcl.dat_public_registration_begin
            ,tcl.dat_public_registration_closes
			
			,tcl.dat_staff_registration_begin as dat_staff_registration_begin
			
            , tc.bit_display_to_public as bitDisplay
            
            	, tc.bit_allow_public_registration as bitRegistration
            
            ,tc.int_certificate_id
        FROM tbl_class_listing tcl
            INNER JOIN tbl_certificates tc ON tcl.int_certificate_id = tc.int_certificate_id
            AND tc.chr_status='P'
            
        WHERE
        	tcl.int_certificate_id=?
        AND (
            SELECT count(tcc1.int_remaining_seats) from tbl_certificates_classes tcc1
            INNER JOIN classes cl ON cl.classID= tcc1.int_class_id
            WHERE tcc1.int_certificate_id = ?
            AND tcc1.int_certificate_group_id = ?
            AND tcc1.int_remaining_seats > 0
            ) > 0

SELECT
			max(Case when len(tc.str_phone) > 0 THEN tc.str_phone else cl.contactphone end) as ContactNumber
			,s.FName+' '+s.LName AS StaffName
			, s.Email
		FROM tbl_certificates tc
        INNER JOIN tbl_certificates_classes tcc1 ON tcc1.int_certificate_id = tc.int_certificate_id
        INNER JOIN Classes cl ON cl.ClassID = tcc1.int_class_id
		INNER JOIN Staff s ON s.StaffID = tc.int_staff_contact_id
        WHERE tc.int_certificate_id = ?
		GROUP BY
			s.FName
			, s.LName
			, s.Email

SELECT int_certificates_elective_group_id,int_req_no_classes_in_group
			FROM tbl_certificates_elective_groups
			WHERE int_certificate_id=?

SELECT (isnull((SELECT SUM(flt_class_price)
								FROM tbl_certificates_courses
								WHERE int_certificate_id =?
									AND bit_elective = 0),0)
						) as CertificatePrice

DECLARE @ContentCondition BIT

				SET @ContentCondition = 1

				SELECT contents.name
					, contents.contents
					, contents.description
					, contents.str_instructor_detail_list
					, isNull(emailType.allowHTML, 1) AS allowHTML
					/**/
					, contents.emailTemplate
					/**/
					, contents.path
					, contents.str_page_title
					, contents.str_meta_description
					, contents.str_keywords
					, contents.section
					, contents.URL
					/**/
					, contents.EmailSubject
					, contents.bit_email_confirmation
					, contents.ContentsNoHTML
					/* Custom Reminder and Confirmation Email Templates Dangerfield 202105 */
					, contents.bit_custom_email_template
					, (SELECT name
						FROM Contents C2
						WHERE C2.ContentID = contents.int_parent_content_id) AS ParentName
					, contents.int_parent_content_id
					/* Custom Reminder and Confirmation Email Templates Dangerfield 202105 */
					, contents.CreateDate
					, contents.ModifyDate
					, IsNull(S1.FName + ' ' + S1.LName,'System') as created_by
					, IsNull(S2.FName + ' ' + S2.LName,'System') as modified_by
					, contents.str_desc_learner_erp_email
					, contents.str_desc_learner_erp_email_nohtml
					, contents.str_desc_learner_no_erp_email
					, contents.str_desc_learner_no_erp_email_nohtml
					, CASE
						WHEN Contents.Path IN ('ForgotPasswordNotice', 'StaffProfile')
							THEN 1
						ELSE 0
					END AS isEmailSubjectEditable
					, CASE
						WHEN 0 = 0 THEN 0
						WHEN (
								Contents.bit_email_confirmation = 1
								OR Contents.bit_learner_class_reminder = 1
								/*OR Contents.bit_instructor_class_reminder = 1*/
							)
							THEN 1
						WHEN Contents.Path LIKE 'ClassEndReminder%'
							OR Contents.Path LIKE 'LearnerTransferClassMerchandiseConfirmation%'
							OR Contents.Path LIKE 'LearnerTransferConfirm%'
							OR Contents.Path LIKE 'LearnerMerchandiseConfirmation%'
							THEN 1
						ELSE 0
						END AS IsConditonalEmailTemplate
					, Contents.str_desc_learner_erp_email
					, Contents.str_desc_learner_no_erp_email
					, Contents.str_test_email_subject
					, Contents.str_test_from_email
					, Contents.str_test_from_name
				FROM Contents
				LEFT JOIN EmailTypeContent ON contents.contentid = EmailTypeContent.contentid
				LEFT JOIN emailType ON EmailTypeContent.emailTypeid = emailType.emailtypeid
				LEFT JOIN Users U1 ON contents.CreateUserID = U1.UserID
				LEFT JOIN Staff S1 ON U1.ActorID = S1.StaffID
				LEFT JOIN Users U2 ON contents.ModifyUserID = U2.UserID
				LEFT JOIN Staff S2 ON U2.ActorID = S2.StaffID
				WHERE @ContentCondition = @ContentCondition
					
						AND Contents.path = ?

SELECT AvailableFieldID,ContentID,VariableName,VariableDisplay
				FROM AvailableField
				WHERE 
					/* Custom Reminder and Confirmation Email Templates Dangerfield 202105 */
					ContentID = (
									SELECT 
										CASE
											WHEN int_parent_content_id <> 0 THEN int_parent_content_id
											ELSE ContentID
										END AS ContentID
									FROM Contents
									WHERE path = ?
									/* BEGIN: NEO 00091 AI Company Qty Reg Use Cases 2013-02-21 - F04 Date:04 July 2013 */
									AND (ISNULL(int_slim_profile_attribute_id,0) = 0 OR int_slim_profile_attribute_id IN (SELECT int_slim_profile_attribute_id FROM tbl_slim_profile_attributes WHERE bit_required = 1))
									/* END: NEO 00091 AI Company Qty Reg Use Cases 2013-02-21 - F04 Date:04 July 2013 */
								)
					/* Custom Reminder and Confirmation Email Templates Dangerfield 202105 */

DECLARE @ContentCondition BIT

				SET @ContentCondition = 1

				SELECT contents.name
					, contents.contents
					, contents.description
					, contents.str_instructor_detail_list
					, isNull(emailType.allowHTML, 1) AS allowHTML
					/**/
					, contents.emailTemplate
					/**/
					, contents.path
					, contents.str_page_title
					, contents.str_meta_description
					, contents.str_keywords
					, contents.section
					, contents.URL
					/**/
					, contents.EmailSubject
					, contents.bit_email_confirmation
					, contents.ContentsNoHTML
					/* Custom Reminder and Confirmation Email Templates Dangerfield 202105 */
					, contents.bit_custom_email_template
					, (SELECT name
						FROM Contents C2
						WHERE C2.ContentID = contents.int_parent_content_id) AS ParentName
					, contents.int_parent_content_id
					/* Custom Reminder and Confirmation Email Templates Dangerfield 202105 */
					, contents.CreateDate
					, contents.ModifyDate
					, IsNull(S1.FName + ' ' + S1.LName,'System') as created_by
					, IsNull(S2.FName + ' ' + S2.LName,'System') as modified_by
					, contents.str_desc_learner_erp_email
					, contents.str_desc_learner_erp_email_nohtml
					, contents.str_desc_learner_no_erp_email
					, contents.str_desc_learner_no_erp_email_nohtml
					, CASE
						WHEN Contents.Path IN ('ForgotPasswordNotice', 'StaffProfile')
							THEN 1
						ELSE 0
					END AS isEmailSubjectEditable
					, CASE
						WHEN 0 = 0 THEN 0
						WHEN (
								Contents.bit_email_confirmation = 1
								OR Contents.bit_learner_class_reminder = 1
								/*OR Contents.bit_instructor_class_reminder = 1*/
							)
							THEN 1
						WHEN Contents.Path LIKE 'ClassEndReminder%'
							OR Contents.Path LIKE 'LearnerTransferClassMerchandiseConfirmation%'
							OR Contents.Path LIKE 'LearnerTransferConfirm%'
							OR Contents.Path LIKE 'LearnerMerchandiseConfirmation%'
							THEN 1
						ELSE 0
						END AS IsConditonalEmailTemplate
					, Contents.str_desc_learner_erp_email
					, Contents.str_desc_learner_no_erp_email
					, Contents.str_test_email_subject
					, Contents.str_test_from_email
					, Contents.str_test_from_name
				FROM Contents
				LEFT JOIN EmailTypeContent ON contents.contentid = EmailTypeContent.contentid
				LEFT JOIN emailType ON EmailTypeContent.emailTypeid = emailType.emailtypeid
				LEFT JOIN Users U1 ON contents.CreateUserID = U1.UserID
				LEFT JOIN Staff S1 ON U1.ActorID = S1.StaffID
				LEFT JOIN Users U2 ON contents.ModifyUserID = U2.UserID
				LEFT JOIN Staff S2 ON U2.ActorID = S2.StaffID
				WHERE @ContentCondition = @ContentCondition
					
						AND Contents.path = ?

SELECT AvailableFieldID,ContentID,VariableName,VariableDisplay
				FROM AvailableField
				WHERE 
					/* Custom Reminder and Confirmation Email Templates Dangerfield 202105 */
					ContentID = (
									SELECT 
										CASE
											WHEN int_parent_content_id <> 0 THEN int_parent_content_id
											ELSE ContentID
										END AS ContentID
									FROM Contents
									WHERE path = ?
									/* BEGIN: NEO 00091 AI Company Qty Reg Use Cases 2013-02-21 - F04 Date:04 July 2013 */
									AND (ISNULL(int_slim_profile_attribute_id,0) = 0 OR int_slim_profile_attribute_id IN (SELECT int_slim_profile_attribute_id FROM tbl_slim_profile_attributes WHERE bit_required = 1))
									/* END: NEO 00091 AI Company Qty Reg Use Cases 2013-02-21 - F04 Date:04 July 2013 */
								)
					/* Custom Reminder and Confirmation Email Templates Dangerfield 202105 */

SELECT DISTINCT TPC.int_prerequisites_id AS int_prerequisites_id
			, P.str_prerequisites_name AS PrerequisiteName
			, P.str_prerequisites_name AS PrerequisiteNameee
			, P.int_prerequisites_type_id AS int_prerequisites_type_id
			, PT.str_prerequisites_type AS PrerequisiteType
			, PT.str_prerequisites_type_desc AS str_prerequisites_type_desc
			, P.int_prerequisite_status_id AS StatusID
			, P.int_course_id AS int_course_id
			, PS.chr_prerequisite_status AS STATUS
			, PS.str_prerequisite_status AS StatusName
			, PCT.chr_completion_type AS chr_completion_type
			, PCT.str_completion_type AS CompletionType
			, P.int_completion_type_id AS int_completion_type_id
			, P.str_description AS Description
			, P.form_field_type_id AS form_field_type_id
			, P.str_hint_text AS QuestionHint
			, P.flt_passing_grade AS PassingGrade
			, P.bit_work_keys_test AS bit_work_keys_test
			, P.int_work_keys_type_id AS int_work_keys_type_id
			, PWKT.str_work_keys_type AS WorkKeysType
			, TPC.int_certificate_id AS int_certificate_id
			, TPC.int_sort_order AS SortOrder
			, TPC.str_status AS CertificateStatus
			, TPC.int_prerequisite_certificate_id AS int_prerequisite_certificate_id
		FROM tbl_prerequisites P
		INNER JOIN tbl_prerequisites_type PT ON PT.int_prerequisites_type_id = P.int_prerequisites_type_id
		INNER JOIN tbl_prerequisite_certificate TPC ON TPC.int_prerequisites_id = P.int_prerequisites_id
		INNER JOIN tbl_certificates TC ON TC.int_certificate_id = TPC.int_certificate_id
		LEFT JOIN tbl_prerequisite_work_keys_type PWKT ON PWKT.int_work_keys_type_id = P.int_work_keys_type_id
		LEFT JOIN tbl_prerequisite_status PS ON PS.int_prerequisite_status_id = P.int_prerequisite_status_id
		LEFT JOIN tbl_prerequisite_completion_type PCT ON PCT.int_completion_type_id = P.int_completion_type_id
		WHERE TPC.int_certificate_id = ?
			AND (P.str_available_for IN ('Certificate','All')) /* Class Prerequisites Dangerfield 202104 */
			AND TPC.int_prerequisite_certificate_id IN (
				SELECT int_prerequisite_certificate_id
				FROM tbl_prerequisite_certificate
				WHERE int_certificate_id = ?
					AND str_status <> 'D'
				)
			AND TPC.str_status <> 'D'
			
					AND TPC.str_status='A'

select * from qSocialMedia order by int_order desc

q_customer_by_domain (Datasource=LumensCustomers, Time=0ms) in E:\Lumens\app_setup.cfc @ 02:17:50.050

CGI Variables:
AUTH_PASSWORD=
AUTH_TYPE=
AUTH_USER=
CERT_COOKIE=
CERT_FLAGS=
CERT_ISSUER=
CERT_KEYSIZE=
CERT_SECRETKEYSIZE=
CERT_SERIALNUMBER=
CERT_SERVER_ISSUER=
CERT_SERVER_SUBJECT=
CERT_SUBJECT=
CF_TEMPLATE_PATH=E:\Lumens\index.cfm
CONTENT_LENGTH=0
CONTENT_TYPE=
CONTEXT_PATH=
GATEWAY_INTERFACE=CGI/1.1
HTTPS=off
HTTPS_KEYSIZE=
HTTPS_SECRETKEYSIZE=
HTTPS_SERVER_ISSUER=
HTTPS_SERVER_SUBJECT=
HTTP_ACCEPT=*/*
HTTP_ACCEPT_ENCODING=gzip, br, zstd, deflate
HTTP_ACCEPT_LANGUAGE=
HTTP_CONNECTION=
HTTP_COOKIE=AWSALBAPP-2=_remove_; AWSALBAPP-3=_remove_; AWSALBAPP-1=_remove_; AWSALBAPP-0=_remove_
HTTP_HOST=lumenstesting4.augusoft.net
HTTP_REFERER=
HTTP_URL=/index.cfm//info/certificate-information/3331/?cl=1&classID=9740&int_certificate_group_id=0?cl=1&classID=9740&int_certificate_group_id=0
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=/info/certificate-information/3331/
PATH_TRANSLATED=E:\Lumens\index.cfm
QUERY_STRING=cl=1&classID=9740&int_certificate_group_id=0
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:
AWSALBAPP-0=_remove_
AWSALBAPP-1=_remove_
AWSALBAPP-2=_remove_
AWSALBAPP-3=_remove_
LUMPERSIST=CE4909BA17F3D54B8E83A385C0FA507D

Session Variables:
actortypeid=90
addtoken=CFID=15999389&CFTOKEN=2dec052f5432d39b-391453A9-94FF-4C93-FA13B9F1EC915C68&jsessionid=3A38569ED1E674D3FBFBF9FAC7FE35FB.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.139.83.202
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=3A38569ED1E674D3FBFBF9FAC7FE35FB.cfusion
sitepath=/Customers/Lumenstesting4
sslreturnurl=https://lumenstesting4.augusoft.net/
stylesheetpath=/Customers/Lumenstesting4/StyleSheets/style.css
timeoutminutes=60
urltoken=CFID=15999389&CFTOKEN=2dec052f5432d39b-391453A9-94FF-4C93-FA13B9F1EC915C68&jsessionid=3A38569ED1E674D3FBFBF9FAC7FE35FB.cfusion
version=v81

URL Parameters:
cl=1
classID=9740
int_certificate_group_id=0