Login/Create account
Donate
checkout
view cart (
0
)Login/Create account
Donate
checkoutview cart (
0
)
|
Debugging Information
Execution Time
SQL Queries checkAccessControlRules (Datasource=LumensCustomers, Time=15ms, Records=11) in E:\Lumens\Versions\v81\checkPermission.cfm @ 01:09:53.053SELECT 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 Query Parameter Value(s) -MenuQry (Datasource=v8Testing8, Time=0ms, Records=14) in E:\Lumens\Versions\v81\Administration\SQL\MenuQry.cfm @ 01:09:53.053SET 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 to PAC') /*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 to PAC') /*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 Query Parameter Value(s) -qGetDashboardName (Datasource=v8Testing8, Time=0ms, Records=1) in E:\Lumens\Versions\v81\Administration\SQL\MenuQry.cfm @ 01:09:53.053SET 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 = ? Query Parameter Value(s) -qLearnerfields (Datasource=v8Testing8, Time=0ms, Records=113) in E:\Lumens\Versions\v81\cfc\systemoptions\siteconfiguration\siteconfiguration.cfc @ 01:09:53.053SELECT 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
Query Parameter Value(s) -qsocialNetFieldDetails (Datasource=v8Testing8, Time=0ms, Records=2) in E:\Lumens\Versions\v81\cfc\systemoptions\siteconfiguration\siteconfiguration.cfc @ 01:09:53.053select * from tbl_social_media_details WHERE int_region_id = ? order by int_order asc Query Parameter Value(s) -qGraphics (Datasource=v8Testing8, Time=0ms, Records=1) in E:\Lumens\Versions\v81\cfc\systemoptions\siteconfiguration\siteconfiguration.cfc @ 01:09:53.053select * from UIVariables where RegionID = ? Query Parameter Value(s) -qUserNavItems (Datasource=v8Testing8, Time=0ms, Records=0) in E:\Lumens\Versions\v81\cfc\systemoptions\poweruser\poweruser.cfc @ 01:09:53.053SELECT 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 Query Parameter Value(s) -qFooterNavItems (Datasource=v8Testing8, Time=0ms, Records=0) in E:\Lumens\Versions\v81\cfc\systemoptions\poweruser\poweruser.cfc @ 01:09:53.053SELECT 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 Query Parameter Value(s) -qryResult (Datasource=v8Testing8, Time=0ms, Records=0) in E:\Lumens\Application.cfc @ 01:09:53.053DECLARE @currentTime DATETIME2 = ? UPDATE tbl_cart_class_detail SET EnteredDate = @currentTime WHERE SessionID = ?; UPDATE tbl_cart_merchandise_detail SET EnteredDate = @currentTime WHERE SessionID = ?; Query Parameter Value(s) -variables.custom_scripts (Datasource=v8Testing8, Time=0ms, Records=1) in E:\Lumens\Versions\v81\header.cfm @ 01:09:53.053SELECT 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 qGraphics (Datasource=v8Testing8, Time=0ms, Records=1) in E:\Lumens\Versions\v81\cfc\systemoptions\siteconfiguration\siteconfiguration.cfc @ 01:09:53.053select * from UIVariables where RegionID = ? Query Parameter Value(s) -qTempMenu (Datasource=, Time=0ms, Records=13) in E:\Lumens\Versions\v81\vendor\site_templates\shared\includes\menu_action.cfm @ 01:09:53.053select * from qTempMenu where MenuItemID NOT IN (510) qSocialMediaHeader (Datasource=, Time=0ms, Records=2) in E:\Lumens\Versions\v81\vendor\site_templates\shared\includes\nav-social-media.cfm @ 01:09:53.053select * from qSocialMedia order by int_order desc qGetDonateUserNavLabel (Datasource=v8Testing8, Time=0ms, Records=1) in E:\Lumens\Versions\v81\header.cfm @ 01:09:53.053SELECT 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'; Query Parameter Value(s) -qGetDonateUserNavLabel (Datasource=v8Testing8, Time=0ms, Records=1) in E:\Lumens\Versions\v81\header.cfm @ 01:09:53.053SELECT 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'; Query Parameter Value(s) -qGetDonateUserNavLabel (Datasource=v8Testing8, Time=0ms, Records=1) in E:\Lumens\Versions\v81\header.cfm @ 01:09:53.053SELECT 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'; Query Parameter Value(s) -qTempCourseMenu (Datasource=, Time=0ms, Records=3) in E:\Lumens\Versions\v81\vendor\site_templates\template1\includes\nav-main.cfm @ 01:09:53.053select * from qCourseMenu where int_parent_id = ? and MenuItemType = ? and Label <> ? and int_parent_id<>0 Query Parameter Value(s) -qTempCourseMenu (Datasource=, Time=0ms, Records=5) in E:\Lumens\Versions\v81\vendor\site_templates\template1\includes\nav-main.cfm @ 01:09:53.053select * from qCourseMenu where int_parent_id = ? and MenuItemType = ? and Label <> ? and int_parent_id<>0 Query Parameter Value(s) -qTempCourseMenu (Datasource=, Time=0ms, Records=1) in E:\Lumens\Versions\v81\vendor\site_templates\template1\includes\nav-main.cfm @ 01:09:53.053select * from qCourseMenu where int_parent_id = ? and MenuItemType = ? and Label <> ? and int_parent_id<>0 Query Parameter Value(s) -qTempCourseMenu (Datasource=, Time=0ms, Records=2) in E:\Lumens\Versions\v81\vendor\site_templates\template1\includes\nav-main.cfm @ 01:09:53.053select * from qCourseMenu where int_parent_id = ? and MenuItemType = ? and Label <> ? and int_parent_id<>0 Query Parameter Value(s) -qTempCourseMenu (Datasource=, Time=0ms, Records=3) in E:\Lumens\Versions\v81\vendor\site_templates\template1\includes\nav-main.cfm @ 01:09:53.053select * from qCourseMenu where int_parent_id = ? and MenuItemType = ? and Label <> ? and int_parent_id<>0 Query Parameter Value(s) -qTempCourseMenu (Datasource=, Time=16ms, Records=2) in E:\Lumens\Versions\v81\vendor\site_templates\template1\includes\nav-main.cfm @ 01:09:53.053select * from qCourseMenu where int_parent_id = ? and MenuItemType = ? and Label <> ? and int_parent_id<>0 Query Parameter Value(s) -qTempCourseMenu (Datasource=, Time=0ms, Records=4) in E:\Lumens\Versions\v81\vendor\site_templates\template1\includes\nav-main.cfm @ 01:09:53.053select * from qCourseMenu where int_parent_id = ? and MenuItemType = ? and Label <> ? and int_parent_id<>0 Query Parameter Value(s) -qTempCourseMenu (Datasource=, Time=0ms, Records=2) in E:\Lumens\Versions\v81\vendor\site_templates\template1\includes\nav-main.cfm @ 01:09:53.053select * from qCourseMenu where int_parent_id = ? and MenuItemType = ? and Label <> ? and int_parent_id<>0 Query Parameter Value(s) -qTempCourseMenu (Datasource=, Time=0ms, Records=3) in E:\Lumens\Versions\v81\vendor\site_templates\template1\includes\nav-main.cfm @ 01:09:53.053select * from qCourseMenu where int_parent_id = ? and MenuItemType = ? and Label <> ? and int_parent_id<>0 Query Parameter Value(s) -qTempCourseMenu (Datasource=, Time=0ms, Records=1) in E:\Lumens\Versions\v81\vendor\site_templates\template1\includes\nav-main.cfm @ 01:09:53.053select * from qCourseMenu where int_parent_id = ? and MenuItemType = ? and Label <> ? and int_parent_id<>0 Query Parameter Value(s) -qTempCourseMenu (Datasource=, Time=0ms, Records=1) in E:\Lumens\Versions\v81\vendor\site_templates\template1\includes\nav-main.cfm @ 01:09:53.053select * from qCourseMenu where int_parent_id = ? and MenuItemType = ? and Label <> ? and int_parent_id<>0 Query Parameter Value(s) -qTempCourseMenu (Datasource=, Time=0ms, Records=4) in E:\Lumens\Versions\v81\vendor\site_templates\template1\includes\nav-main.cfm @ 01:09:53.053select * from qCourseMenu where int_parent_id = ? and MenuItemType = ? and Label <> ? and int_parent_id<>0 Query Parameter Value(s) -qTempMenu (Datasource=, Time=0ms, Records=0) in E:\Lumens\Versions\v81\vendor\site_templates\template1\includes\nav-main.cfm @ 01:09:53.053select * from qMenu where int_parent_id = 551 qTempMenu (Datasource=, Time=0ms, Records=0) in E:\Lumens\Versions\v81\vendor\site_templates\template1\includes\nav-main.cfm @ 01:09:53.053select * from qMenu where int_parent_id = 609 qTempMenu (Datasource=, Time=0ms, Records=0) in E:\Lumens\Versions\v81\vendor\site_templates\template1\includes\nav-main.cfm @ 01:09:53.053select * from qMenu where int_parent_id = 503 qTempMenu (Datasource=, Time=0ms, Records=0) in E:\Lumens\Versions\v81\vendor\site_templates\template1\includes\nav-main.cfm @ 01:09:53.053select * from qMenu where int_parent_id = 525 qTempMenu (Datasource=, Time=0ms, Records=0) in E:\Lumens\Versions\v81\vendor\site_templates\template1\includes\nav-main.cfm @ 01:09:53.053select * from qMenu where int_parent_id = 5 qTempMenu (Datasource=, Time=0ms, Records=0) in E:\Lumens\Versions\v81\vendor\site_templates\template1\includes\nav-main.cfm @ 01:09:53.053select * from qMenu where int_parent_id = 7 qTempMenu (Datasource=, Time=0ms, Records=1) in E:\Lumens\Versions\v81\vendor\site_templates\template1\includes\nav-main.cfm @ 01:09:53.053select * from qMenu where int_parent_id = 2 qGetOrganisationPhoneQry (Datasource=v8Testing8, Time=0ms, Records=1) in E:\Lumens\Versions\v81\model\course\classinformation\act_class_information.cfm @ 01:09:53.053SET NOCOUNT ON SELECT rg.Phone1 , rg.Phone1Ext , rg.email FROM Regions rg WITH (NOLOCK) WHERE rg.RegionID = ? AND rg.Status = 'A' Query Parameter Value(s) -qRegions (Datasource=v8Testing8, Time=0ms, Records=1) in E:\Lumens\Versions\v81\model\course\classinformation\act_class_information.cfm @ 01:09:53.053SELECT rg.str_google_conversion_id ,rg.str_google_conversion_language ,rg.str_google_conversion_format ,rg.str_google_conversion_color ,rg.str_google_conversion_label FROM Regions rg WITH (NOLOCK) WHERE rg.RegionID = 1 AND rg.Status = 'A' FeatureConfigurationText (Datasource=v8Testing8, Time=0ms, Records=1) in E:\Lumens\Versions\v81\model\course\classinformation\act_class_information.cfm @ 01:09:53.053SELECT TOP 1 fc.str_text_when_class_waiting_list_not_allowed FROM FeatureConfiguration fc INNER JOIN Regions rg ON fc.RegionID = rg.RegionID LEFT JOIN tbl_erp e ON fc.int_erp_id = e.int_erp_id LEFT JOIN tbl_erp_config ec ON ec.str_erp_url <> '' WHERE rg.STATUS = 'A' qRecommendedClass (Datasource=v8Testing8, Time=0ms, Records=20) in E:\Lumens\versions\v81\cfc\class\recommended.cfc @ 01:09:53.053/*
What should happen : Refer Tracker 43106
Best Match for the 'Recommended Classes' box for a Student
Lumens will compare the courses for all the classes in which
the student has been registered (but not those for which the registrations have been cancelled or transferred)
to the courses that other students, who also registered in the same classes as the student, have been registered in (but not those for which the registrations have been cancelled or transferred).
Lumens will determine the 20 courses which are
1) the parents of the classes most frequently registered for and
2) are currently available for the student to register in.
Lumens will display the Class Names of the three (3) classes that are children of the most frequently registered
courses from that list in the 'Recommended Courses' box.
If the student has not registered for any classes, or if the user is a guest, then Lumens will determine the 20 classes that are most frequently registered for in the Category being viewed that are available for registration for this student.
Once determined, Lumens will display the Class Names of the three (3) courses from that list.
Aswin 01/02/2018 : If the student has not registered for any classes - I dont think this is handled.
Also - arguments.recommended_type is not asked for by the spec.
*/
DECLARE @LearnerID AS INT
, @SubcategoryID AS INT;
SET @LearnerID = 0;
SET @SubcategoryID = ?;
SELECT TOP 20 co.NAME
, co.courseid
, co.subcategoryid
, c.ClassID
, c.NAME AS class_name
, count(co.courseid) AS RegCount
FROM registrations r WITH (NOLOCK)
INNER JOIN classes c WITH (NOLOCK) ON r.classid = c.classid
INNER JOIN tbl_class_detail tcd WITH (NOLOCK) ON tcd.int_class_id = c.classid
INNER JOIN courses co WITH (NOLOCK) ON c.courseid = co.courseid
WHERE 1 = 1
AND c.dat_public_registration_closes > dbo.fun_GetDateCT()
AND c.STATUS IN (
'A'
, 'C'
)
AND co.status in ('A')
AND tcd.bit_display_to_public = 1
AND NOT EXISTS (
SELECT 1
FROM tbl_restriction_code_detail
WHERE int_table_id = 2 /*classes*/
AND int_primary_key_id = c.ClassID
)
GROUP BY co.courseid
, co.NAME
, co.subcategoryid
, c.ClassID
, c.NAME
HAVING SUM(c.RemainingSeats) > 1
ORDER BY RegCount DESC
Query Parameter Value(s) -qMerchandiseClassList (Datasource=v8Testing8, Time=32ms, Records=0) in E:\Lumens\versions\v81\cfc\merchandise\merchandise.cfc @ 01:09:53.053SET NOCOUNT ON DECLARE @TimeZoneShift AS FLOAT , @str_alt AS VARCHAR(max) , @intNumber_wAltSubCat AS INT; SET @TimeZoneShift = ISNULL(( SELECT TimeZoneHourAdjustment FROM Regions WITH (NOLOCK) WHERE RegionID = 1 ), 0); SELECT tmi.int_item_id ,t1.int_item_class_id ,t1.int_item_id ,t1.int_class_id ,t1.bit_set_zero_price ,t1.bit_required_with_reg ,tmi.str_item_name ,tmi.str_item_sku ,tmi.str_item_desc ,tmi.chr_status ,tmi.int_category_id ,tmi.int_sub_category_id ,tmi.str_alt_sub_category_id ,tmi.dat_student_display_start ,tmi.dat_student_purchase_start ,tmi.dat_staff_display_start ,tmi.dat_staff_purchase_start ,tmi.dat_item_expire ,tmi.bit_item_expire ,tmi.str_acct_code ,tmi.str_graphic ,tmi.str_listing_icon ,tmi.str_listing_icon_alt_desc ,tmi.bit_merchandise_item_available_to_students ,tmi.bit_available_with_class_or_course ,tmi.str_delivery_type_ids ,tmi.str_pickup_location_ids ,tmi.int_return_policy_id ,tmi.bit_use_attributes ,tmi.str_attribute_ids ,tmi.bit_use_attribute_pricing ,tmi.bit_taxable_gst ,tmi.bit_taxable_hst ,tmi.bit_taxable_pst , tmi.bit_taxable_sales ,tmi.flt_price ,tmi.flt_cost ,tmi.int_total_quantity ,tmi.int_quantity_onhand , ( CASE WHEN tmi.bit_use_attributes = 1 THEN ( SELECT COUNT(*) FROM tbl_merchandise_attribute_combination t1 WHERE t1.int_item_id = tmi.int_item_id AND ( ISNULL(( SELECT (T1.int_quantity_available - isnull(SUM(int_quantity_count), 0)) FROM tbl_cart_merchandise_detail WHERE int_item_id= tmi.int_item_id AND t1.int_attribute_combination_id = tbl_cart_merchandise_detail.int_attribute_combination_id ), T1.int_quantity_available) ) > 0 ) ELSE ( SELECT (tmi.int_quantity_available - isnull(SUM(int_quantity_count), 0)) FROM tbl_cart_merchandise_detail WHERE int_item_id = tmi.int_item_id ) END ) AS int_quantity_available ,tmi.dat_created_date ,tmi.int_created_by_id ,tmi.int_modified_by_id ,tmi.dat_modified_date , c.OrgID , c.RegionID , c.CategoryID , c.NAME AS CategoryName , sc.int_displayed_in_category , sc.SubcategoryID , sc.Description AS subDescription , c.Description AS catDescription , ( SELECT ExternalCourseProviderID FROM ExternalCourseProvider WITH (NOLOCK) WHERE DefaultSubcategoryID = sc.SubCategoryID ) AS ExternalCourseProviderID , sc.NAME AS SubcategoryName ,isNUll(tmi.flt_item_length,0) AS flt_item_length ,isNUll(tmi.flt_item_width,0) AS flt_item_width ,isNUll(tmi.flt_item_height,0) AS flt_item_height ,isNUll(tmi.flt_item_weight,0) AS flt_item_weight ,isNUll(t1.int_minimum_quantity,0) AS int_minimum_quantity ,isNUll(t1.int_maximum_quantity,0) AS int_maximum_quantity , 0 as int_item_certificate_id FROM tbl_merchandise_item_class t1 WITH (NOLOCK) INNER JOIN tbl_merchandise_item tmi ON t1.int_item_id=tmi.int_item_id INNER JOIN Categories c ON c.CategoryID=tmi.int_category_id INNER JOIN SubCategories sc ON c.CategoryID = sc.CategoryID AND sc.SubcategoryID = tmi.int_sub_category_id WHERE 1 = 1 AND c.STATUS = 'A' AND sc.STATUS = 'A' AND tmi.chr_status = 'A' AND c.RegionID = 1 AND c.bit_merchandise_category =1 AND sc.int_displayed_in_category IN ( SELECT int_displayed_in_category FROM tbl_displayed_in_category WHERE ( str_displayed_in_category LIKE 'Merchandise' OR str_displayed_in_category LIKE 'Both' ) ) AND tmi.bit_merchandise_item_available_to_students =1 AND ( CASE WHEN tmi.bit_item_expire = 1 AND ( DateAdd(hh, @TimeZoneShift, dbo.fun_GetDateCT()) BETWEEN tmi.dat_student_display_start AND DateAdd(ms, - 10, tmi.dat_item_expire) ) THEN 1 WHEN tmi.bit_item_expire = 0 AND (DateAdd(hh, @TimeZoneShift, dbo.fun_GetDateCT()) >= tmi.dat_student_display_start) THEN 1 ELSE 0 END ) = 1 AND t1.int_class_id IN (?) AND tmi.int_item_id = CASE WHEN tmi.bit_use_attributes = 1 THEN ( SELECT DISTINCT t1.int_item_id FROM tbl_merchandise_attribute_combination t1 INNER JOIN tbl_merchandise_attribute_combination_options t2 ON t1.int_attribute_combination_id = t2.int_attribute_combination_id WHERE t1.int_item_id=tmi.int_item_id ) ELSE tmi.int_item_id END ORDER BY tmi.str_item_name ASC; SET NOCOUNT OFF; Query Parameter Value(s) -qClassDisplayValues (Datasource=v8Testing8, Time=0ms, Records=1) in E:\Lumens\versions\v81\cfc\courses\classinformation\classinformation.cfc @ 01:09:53.053select str_class_listing_order from tbl_class_listing_order qGetClassInfo (Datasource=v8Testing8, Time=31ms, Records=1) in E:\Lumens\versions\v81\cfc\courses\classinformation\classinformation.cfc @ 01:09:53.053DECLARE @TimeZoneShift AS FLOAT
, @locationBit INT
, @RoomBit INT
, @MembershipID AS INT
, @LearnerID INT;
SET @TimeZoneShift = IsNull((
SELECT TimeZoneHourAdjustment
FROM Regions WITH (NOLOCK)
WHERE RegionID = ?
), 0);
SET @LearnerID = 0;
SELECT @locationBit = COUNT(*)
FROM tbl_class_listing_order
WHERE SUBSTRING(str_class_listing_order, (PATINDEX('%:CityLocation%', str_class_listing_order) - 3), 1) IN (1, 2, 3)
SELECT @locationBit = @locationBit + COUNT(*)
FROM tbl_class_listing_order
WHERE SUBSTRING(str_class_listing_order, (PATINDEX('%:Location%', str_class_listing_order) - 3), 1) IN (1, 2, 3)
SELECT @RoomBit = COUNT(*)
FROM tbl_class_listing_order
WHERE SUBSTRING(str_class_listing_order, (PATINDEX('%:Classroom%', str_class_listing_order) - 3), 1) IN (1, 2, 3)
SET @MembershipID = IsNull((
SELECT MembershipID
FROM learners WITH (NOLOCK)
WHERE learnerid = @LearnerID
), 0)
SELECT DISTINCT cl.ClassID
, cl.NAME AS ClassName
, co.CourseID
, co.NAME AS CourseName
,CASE
WHEN co.ExternalCourseProviderID = 1
THEN ''
ELSE co.theURL
END AS CourseURL
,CASE
WHEN co.ExternalCourseProviderID = 1
THEN ''
ELSE co.URLText
END AS URLText
, co.int_schedule_type_id AS ScheduleType
, cl.dat_staff_registration_begin AS staffRegistration
, IsNull((
SELECT count(classid)
FROM minMaxAge WITH (NOLOCK)
WHERE classid = CL.classid
AND minage IS NOT NULL
AND maxage IS NOT NULL
), 0) AS classMinMaxAge
, co.IsOnlineCourse
, co.CourseGraphic
, co.CourseSeriesID
, CASE
WHEN (
SELECT min(s.int_series_group_id)
FROM tbl_class_course_series_group s WITH (NOLOCK)
INNER JOIN courseseries cs WITH (NOLOCK) ON s.int_course_series_id = cs.courseseriesid
WHERE cs.STATUS = 'A'
AND s.int_class_id = cl.classid
) IS NULL
THEN 0
ELSE 1
END AS InCourseSeries
, lrt.Description AS LessonReleaseTimes
, c.CategoryID
, c.NAME AS CatName
, sc.SubcategoryID
, sc.NAME AS SubCatName
, CASE
WHEN @locationBit = 1
AND @RoomBit = 1
THEN cdr.str_class_schedule_display_student_login_classInfo
WHEN @locationBit = 1
AND @RoomBit = 0
THEN cdr.str_class_schedule_display_student_login_classInfo_WL
WHEN @locationBit = 0
AND @RoomBit = 1
THEN cdr.str_class_schedule_display_student_login_classInfo_WR
ELSE cdr.str_class_schedule_display_student_login_classInfo_WORL
END AS str_class_schedule_display
, (
SELECT COUNT(int_learner_id)
FROM tbl_cart_class_detail WITH (NOLOCK)
WHERE int_class_id = Cl.ClassID
AND int_learner_id IN (?)
) AS learnerAlreadyInCart
, round(isnull(CL.Cost, 0) + isnull(CL.LabFeesCost, 0) + isnull(CL.InsuranceFeesCost, 0) + isnull(CL.BuildingFeesCost, 0) + isnull(CL.BookFeesCost, 0) + isnull(CL.ProgramFeesCost, 0), 2) AS Cost
, CASE @MembershipID
WHEN 0
THEN
/* count distinct discounts */
(
SELECT count(*)
FROM (
SELECT DISTINCT clf.Cost_1
, clf.DiscountAmount_1
FROM ClassFees clf WITH (NOLOCK)
INNER JOIN CourseFees cf WITH (NOLOCK) ON cf.CourseFeeID = clf.CourseFeeID
WHERE cf.FeeTypeID != 7
AND clf.ClassID = cl.ClassID
AND clf.STATUS = 'A'
AND (
cf.FeeTypeID NOT IN (4, 5)
OR dbo.fun_GetDateCT() BETWEEN clf.StartDate
AND clf.Enddate
)
) tblTemp
)
ELSE (
SELECT count(ClassFeeID)
FROM learners l WITH (NOLOCK)
INNER JOIN memberships m WITH (NOLOCK) ON l.membershipid = m.membershipid
INNER JOIN coursefees cf WITH (NOLOCK) ON m.membershiptypeid = cf.membershiptypeid
INNER JOIN classfees clf WITH (NOLOCK) ON cf.coursefeeid = clf.coursefeeid
WHERE l.learnerid = @LearnerID
AND cf.FeeTypeID != 7
AND clf.ClassID = cl.ClassID
AND clf.STATUS = 'A'
AND (
cf.FeeTypeID NOT IN (4, 5)
OR dbo.fun_GetDateCT() BETWEEN clf.StartDate
AND clf.Enddate
)
)
END AS DiscountFeeCount
, CASE
WHEN @MembershipID > 0
THEN (
SELECT clf.DiscountAmount_1
FROM learners l WITH (NOLOCK)
INNER JOIN memberships m WITH (NOLOCK) ON l.membershipid = m.membershipid
INNER JOIN coursefees cf WITH (NOLOCK) ON m.membershiptypeid = cf.membershiptypeid
INNER JOIN classfees clf WITH (NOLOCK) ON cf.coursefeeid = clf.coursefeeid
WHERE l.learnerid = @LearnerID
AND cf.FeeTypeID != 7
AND clf.ClassID = cl.ClassID
AND clf.STATUS = 'A'
AND (
cf.FeeTypeID NOT IN (4, 5)
OR dbo.fun_GetDateCT() BETWEEN clf.StartDate
AND clf.Enddate
)
)
WHEN
/* see if there is one distinct price for members, and if so, reflect that */
(
SELECT count(*)
FROM (
SELECT DISTINCT clf.Cost_1
, clf.DiscountAmount_1
FROM ClassFees clf WITH (NOLOCK)
INNER JOIN CourseFees cf WITH (NOLOCK) ON cf.CourseFeeID = clf.CourseFeeID
WHERE cf.FeeTypeID != 7
AND clf.ClassID = cl.ClassID
AND clf.STATUS = 'A'
AND (
cf.FeeTypeID NOT IN (4, 5)
OR dbo.fun_GetDateCT() BETWEEN clf.StartDate
AND clf.Enddate
)
) tblTemp
) = 1
THEN (
SELECT DISTINCT clf.DiscountAmount_1
FROM ClassFees clf WITH (NOLOCK)
INNER JOIN CourseFees cf WITH (NOLOCK) ON cf.CourseFeeID = clf.CourseFeeID
WHERE cf.FeeTypeID != 7
AND clf.ClassID = cl.ClassID
AND clf.STATUS = 'A'
AND (
cf.FeeTypeID NOT IN (4, 5)
OR dbo.fun_GetDateCT() BETWEEN clf.StartDate
AND clf.Enddate
)
)
ELSE 0
END AS DiscountAmount
, CASE
WHEN fc.UsesMembership <> 'NM'
THEN CASE
WHEN (CL.IsAdvanceCollect = 1)
THEN round(isnull(CL.Cost, 0) + isnull(CL.MaterialsCost, 0) + isnull(CL.LabFeesCost, 0) + isnull(CL.InsuranceFeesCost, 0) + isnull(CL.BuildingFeesCost, 0) + isnull(CL.BookFeesCost, 0) + isnull(CL.ProgramFeesCost, 0) - isnull(M.DiscountAmount, 0), 2)
ELSE round(isnull(CL.Cost, 0) + isnull(CL.LabFeesCost, 0) + isnull(CL.InsuranceFeesCost, 0) + isnull(CL.BuildingFeesCost, 0) + isnull(CL.BookFeesCost, 0) + isnull(CL.ProgramFeesCost, 0) - isnull(M.DiscountAmount, 0), 2)
END
ELSE CASE
WHEN (
CL.IsAdvanceCollect = 1
AND fc.USESMATLFEES = 0
)
THEN round(isnull(CL.Cost, 0) + isnull(CL.MaterialsCost, 0) + isnull(CL.LabFeesCost, 0) + isnull(CL.InsuranceFeesCost, 0) + isnull(CL.BuildingFeesCost, 0) + isnull(CL.BookFeesCost, 0) + isnull(CL.ProgramFeesCost, 0), 2)
ELSE round(isnull(CL.Cost, 0) + isnull(CL.LabFeesCost, 0) + isnull(CL.InsuranceFeesCost, 0) + isnull(CL.BuildingFeesCost, 0) + isnull(CL.BookFeesCost, 0) + isnull(CL.ProgramFeesCost, 0), 2)
END
END AS MemberCost
, CASE
WHEN @MembershipID > 0
THEN ISNull((
SELECT clf.DiscountAmount_1
FROM learners l WITH (NOLOCK)
INNER JOIN memberships m WITH (NOLOCK) ON l.membershipid = m.membershipid
INNER JOIN coursefees cf WITH (NOLOCK) ON m.membershiptypeid = cf.membershiptypeid
INNER JOIN classfees clf WITH (NOLOCK) ON cf.coursefeeid = clf.coursefeeid
WHERE l.learnerid = @LearnerID
AND cf.FeeTypeID != 7
AND clf.ClassID = cl.ClassID
AND clf.STATUS = 'A'
AND cf.FeeTypeID IN (2)
), 0)
WHEN
/* see if there is one distinct price for members, and if so, reflect that */
(
SELECT count(*)
FROM (
SELECT DISTINCT clf.Cost_1
, clf.DiscountAmount_1
FROM ClassFees clf WITH (NOLOCK)
INNER JOIN CourseFees cf WITH (NOLOCK) ON cf.CourseFeeID = clf.CourseFeeID
WHERE cf.FeeTypeID != 7
AND clf.ClassID = cl.classid
AND clf.STATUS = 'A'
AND cf.FeeTypeID IN (2)
) tblTemp
) = 1
THEN (
SELECT MIN(clf.DiscountAmount_1)
FROM ClassFees clf WITH (NOLOCK)
INNER JOIN CourseFees cf WITH (NOLOCK) ON cf.CourseFeeID = clf.CourseFeeID
WHERE cf.FeeTypeID != 7
AND clf.ClassID = cl.ClassID
AND clf.STATUS = 'A'
AND cf.FeeTypeID IN (2)
)
ELSE 0
END AS MinMemberDiscountAmount
, convert(varchar(10),round(CL.MaterialsCost,2)) as MaterialsCost
, IsNull(cl.Description, co.Description) AS Description
, IsNull(cl.str_description_no_html, co.str_description_no_html) AS DescriptionNoHTML
, cl.Notice
, cl.EndDate
, cl.NumberSessions
, cl.NumberWeeks
, IsNull(cl.Requirements, co.Requirements) AS Requirements
, CL.PublicRegistration
, cl.remainingseats
, CL.dat_public_registration_begin
, CL.dat_public_registration_closes
, CL.dat_staff_registration_begin
, CL.contactPhone
, (
SELECT round(isnull(avg(int_rating_scale_id), 0), 0)
FROM tbl_class_rating WITH (NOLOCK)
WHERE int_class_id IN (
SELECT ClassID
FROM Classes WITH (NOLOCK)
WHERE CourseID = CL.CourseID
)
) AS ClassRating
, (
SELECT round(isnull(count(int_rating_scale_id), 0), 0)
FROM tbl_class_rating WITH (NOLOCK)
WHERE int_class_id IN (
SELECT ClassID
FROM Classes WITH (NOLOCK)
WHERE CourseID = CL.CourseID
)
) AS ClassRatingCount
, (
isnull((
SELECT count(R.classid)
FROM Registrations R WITH (NOLOCK)
WHERE R.classid = cl.classid
AND R.IsCancelled = 0
), 0) + isnull((
SELECT count(R.int_class_id)
FROM tbl_cart_class_detail R WITH (NOLOCK)
WHERE R.int_class_id = cl.classid
), 0)
) AS registrationCount
, CASE
WHEN (
(cl.remainingseats <= 0)
OR (
SELECT count(classid)
FROM waitingLists WITH (NOLOCK)
WHERE classid = cl.classid
) > 0
)
THEN 1
ELSE 0
END AS isfull
, CL.TotalSeats
, CL.AppearanceEndDate
, CL.RequiresReleaseForm
, CL.AllowQtyPurchase
, cl.StartDate AS StartDate
, '0' as bit_enrolled
, '0' as bit_on_waiting_list
, (
SELECT Count(*)
FROM tbl_class_listing_order WITH (NOLOCK)
WHERE str_class_listing_order LIKE '%1:3:Location%'
OR str_class_listing_order LIKE '%1:4:Location%'
OR str_class_listing_order LIKE '%1:5:Location%'
OR str_class_listing_order LIKE '%1:6:Location%'
OR str_class_listing_order LIKE '%2:1:Location%'
OR str_class_listing_order LIKE '%2:2:Location%'
OR str_class_listing_order LIKE '%2:3:Location%'
OR str_class_listing_order LIKE '%2:4:Location%'
OR str_class_listing_order LIKE '%2:5:Location%'
OR str_class_listing_order LIKE '%2:6:Location%'
OR str_class_listing_order LIKE '%1:3:CityLocation%'
OR str_class_listing_order LIKE '%1:4:CityLocation%'
OR str_class_listing_order LIKE '%1:5:CityLocation%'
OR str_class_listing_order LIKE '%1:6:CityLocation%'
OR str_class_listing_order LIKE '%2:1:CityLocation%'
OR str_class_listing_order LIKE '%2:2:CityLocation%'
OR str_class_listing_order LIKE '%2:3:CityLocation%'
OR str_class_listing_order LIKE '%2:4:CityLocation%'
OR str_class_listing_order LIKE '%2:5:CityLocation%'
OR str_class_listing_order LIKE '%2:6:CityLocation%'
) AS bitLocationOnStudentView
, co.PublicRegCloses
, co.ExternalCourseProviderID
, co.CourseNumber AS CourseNumber
, co.ExternalCourseProviderCourseCode
, co.keywords
, co.bit_academic_course
, @TimeZoneShift AS TimeZoneHourAdjustment
, cl.CEUs
, cl.flt_PLUs
, cl.class_Section
/*BEGIN 19 APR 2012 NEO 00043 TRACKER 29256 */
, CASE
WHEN IsNull(wl.WaitingListCount, 0) > 0
THEN 1
ELSE 0
END AS bit_class_on_waiting_list
/*END 19 APR 2012 NEO 00043 TRACKER 29256 */
/*Begin Dec 10 2012 AIMembership Enhancement UC 10 Neo 00110 */
, cl.str_limit_self_reg_mbrship_type_ids
, (
SELECT SUM(CONVERT(INT, bit_display_classes_members_only)) AS bit_display_classes_members_only
FROM MembershipTypes mt WITH (NOLOCK)
LEFT JOIN Classes c WITH (NOLOCK) ON ',' + c.str_limit_self_reg_mbrship_type_ids + ',' LIKE '%,' + CAST(mt.MembershipTypeID AS VARCHAR(255)) + '%'
WHERE c.ClassID IN (CL.classid)
) AS bit_display_classes_members_only
, (
SELECT SUM(CONVERT(INT, bit_allow_student_purchase_mbrship)) AS bit_allow_student_purchase_mbrship
FROM MembershipTypes mt WITH (NOLOCK)
LEFT JOIN Classes c WITH (NOLOCK) ON ',' + c.str_limit_self_reg_mbrship_type_ids + ',' LIKE '%,' + CAST(mt.MembershipTypeID AS VARCHAR(255)) + '%'
WHERE c.ClassID IN (CL.classid)
) AS bit_allow_student_purchase_mbrship
/*End Dec 10 2012 AIMembership Enhancement UC 10 Neo 00110 */
, co.bit_info_request
, co.str_info_request_email
, CASE
WHEN cl.contactPhone IS NULL
THEN co.contactPhone
ELSE cl.contactPhone
END AS contactphonenumber
,str_class_schedule_display_student_login_normal AS ClassScheduleDisplay
, ISNULL(cl.QualityScore, 0) AS QualityScore
, ISNULL(fc.int_quality_score_upper_limit, 1) AS QualityScoreUpperLimit
, ISNULL((
SELECT SUM(QualityScore)
FROM Classes WITH (NOLOCK)
WHERE courseid = co.CourseID
), 1) AS QualityScoreSum
, (
SELECT COUNT(ClassID)
FROM Classes WITH (NOLOCK)
WHERE courseid = co.CourseID
AND ISNULL(QualityScore, 0) > 0
) AS ClassesWithQualityScore
, tcd.int_duration_type_id AS DurationType
, (
SELECT str_description
FROM tbl_time_periods WITH (NOLOCK)
WHERE int_time_period_id = tcd.int_time_period_id
) AS DurationTime
, tcd.int_quantity AS DurationQuantity
, tcd.bit_display_rating_graphic
, tcd.bit_uses_class_lottery
, tcd.dat_lottery_close_date
, tcd.dat_lottery_run_date
, tcd.int_lottery_status_id
, tcd.str_default_text_for_call_message_class
, isnull((
SELECT count(lr.int_lottery_request_id)
FROM tbl_lottery_requests lr WITH (NOLOCK)
WHERE lr.int_class_id = cl.classid
AND isNULL(lr.bit_removed, 0) = 0
AND lr.chr_status = 'A'
), 0) AS lotteryRequestCount
, ISNULL(tcd.str_class_icon, 0) AS str_class_icon /*added neo 00164 SU2*/
/*Begin Dec 29 2015 Tracker 48379 Neo 00110 */
, fc.str_display_on_class_info
, co.CourseNumber
/*End Dec 29 2015 Tracker 48379 Neo 00110 */
, cl.str_class_graphic_filename /* Tracker 50361 Mar 02 2016 Neo 00198 */
, cl.CreateDate
, cl.ModifyDate
, sc.str_subCat_graphic
, c.str_Cat_graphic
, (
SELECT COUNT(int_item_class_id)
FROM tbl_merchandise_item_class mic WITH (NOLOCK)
INNER JOIN tbl_merchandise_item mi WITH (NOLOCK) ON mi.int_item_id = mic.int_item_id
WHERE mi.bit_available_with_class_or_course = 1
AND mi.dat_student_display_start <= DateAdd(hh, @TimeZoneShift, dbo.fun_GetDateCT())
AND mi.bit_merchandise_item_available_to_students=1
AND 1 = CASE
WHEN bit_item_expire = 1
THEN CASE
WHEN DateAdd(hh, @TimeZoneShift, dbo.fun_GetDateCT()) < mi.dat_item_expire
THEN 1
ELSE 0
END
ELSE 1
END
AND mic.int_class_id = cl.ClassID
) AS AvailableItemCount
, 1 AS bit_allow_wcg_registration
, tcd.bit_disp_instr_method_to_public
, cl.bit_allow_waiting_lists
,stuff((
SELECT ',' + CAST(cf.NAME AS VARCHAR(100)) + '||' + CAST(CAST(TCL.CostNoMaterialsFee - clf.DiscountAmount_1 AS money) AS VARCHAR(100))
FROM ClassFees clf
LEFT JOIN tbl_class_listing TCL ON TCL.ClassID = clf.ClassID
LEFT JOIN CourseFees cf ON clf.CourseFeeID = cf.CourseFeeID
WHERE clf.ClassID = cl.classID
AND clf.STATUS = 'A'
AND cf.NAME > ''
FOR XML path('')
), 1, 1, '') optionalAttributeDiscount
, ic.Code as TermCode
,tcd.bit_use_class_prerequisites
, CASE
WHEN tcd.bit_use_class_prerequisites = 1 THEN
CASE
WHEN (SELECT COUNT(tpc.int_prerequisite_class_id)
FROM tbl_prerequisite_class tpc
INNER JOIN tbl_prerequisites tp ON tp.int_prerequisites_id = tpc.int_prerequisites_id
WHERE tpc.int_class_id = CL.ClassID
AND (tp.str_available_for IN ('Class','All'))) > 0
THEN 1
ELSE 0
END
ELSE 0
END AS bit_use_class_prerequisites
FROM Classes cl WITH (NOLOCK)
INNER JOIN tbl_class_detail tcd WITH (NOLOCK) ON tcd.int_class_id=cl.ClassID
LEFT JOIN tbl_class_detail_report cdr ON cdr.int_class_id=cl.ClassID
INNER JOIN Courses co WITH (NOLOCK) ON cl.CourseID = co.CourseID
INNER JOIN Subcategories sc WITH (NOLOCK) ON co.SubcategoryID = sc.SubcategoryID
INNER JOIN Categories c WITH (NOLOCK) ON sc.categoryid = c.categoryid
INNER JOIN FeatureConfiguration fc WITH (NOLOCK) ON fc.RegionID = cl.RegionID /*Added Dec 29 2015 Tracker 48379 Neo 00110 */
LEFT JOIN (
SELECT ClassID
, count(classid) AS WaitingListCount
FROM waitingLists WITH (NOLOCK)
GROUP BY ClassID
) wl on cl.classid = wl.classid /*19 APR 2012 NEO 00043 TRACKER 29256*/
INNER JOIN LessonReleaseTimes lrt WITH (NOLOCK) ON cl.LessonReleaseID=lrt.LessonReleaseID
LEFT JOIN CourseFees CF WITH (NOLOCK) ON co.courseid = cf.courseid
LEFT JOIN MembershipTypes M WITH (NOLOCK) ON cf.MembershipTypeID = M.MembershipTypeID
LEFT JOIN dbo.minMaxAge MM WITH (NOLOCK) on cl.ClassID = MM.ClassID
LEFT JOIN InstructionCalendar ic WITH (NOLOCK) on cl.TermID = ic.TermID
WHERE cl.ClassID = ?
AND cl.status IN ('A','C')
AND NOT EXISTS (
SELECT 1
FROM tbl_restriction_code_detail
WHERE int_table_id = 2 /*classes*/
AND int_primary_key_id = cl.ClassID
)
AND cl.IsContractTraining = ?
Query Parameter Value(s) -qgetscheduleDetails (Datasource=v8Testing8, Time=16ms, Records=1) in E:\Lumens\Versions\v81\model\course\classinformation\act_class_information.cfm @ 01:09:53.053SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT cl.ClassID
, cl.name
, class_listing_table.showLocation
, class_listing_table.LocationID
, CASE
WHEN co.ExternalCourseProviderID = 1
THEN ''
ELSE class_listing_table.Location
END AS Location
, class_listing_table.Instructors
, cdr.str_class_DaysOfWeek_display_line
, CASE chr_class_structure_type
WHEN 'B'
THEN (LTRIM(STR(MONTH(dat_start_date))) + '/' + LTRIM(STR(DAY(dat_start_date))) + '/' + STR(YEAR(dat_start_date), 4) + '- ' + LTRIM(STR(MONTH(dat_end_date))) + '/' + LTRIM(STR(DAY(dat_end_date))) + '/' + STR(YEAR(dat_end_date), 4))
WHEN 'F'
THEN CONVERT(VARCHAR, dat_start_date, 101)
END class_date
, REPLACE(REPLACE(RIGHT(LTRIM(RIGHT(CONVERT(VARCHAR, dat_start_date, 100), 7)), 7), 'AM', ' AM'), 'PM', ' PM') + ' - ' + REPLACE(REPLACE(RIGHT(LTRIM(RIGHT(CONVERT(VARCHAR, dat_end_date, 100), 7)), 7), 'AM', ' AM'), 'PM', ' PM') AS class_times
, CASE
WHEN chr_class_structure_type = 'B'
AND str_reocurring_pattern = 'Daily'
THEN (
CASE
WHEN int_daily_pattern_type = - 1
THEN /*everydays*/
CASE
WHEN int_interval_count = 1
THEN 'Daily '
WHEN int_interval_count >= 2
THEN 'Every ' + CAST(int_interval_count AS VARCHAR(10)) + ' Days '
ELSE ' '
END
WHEN int_daily_pattern_type = 0
THEN /*weekdays*/
CASE
WHEN left(datename(weekday, dat_start_date), 3) = 'Mon'
THEN 'Daily - Mon, Tue, Wed, Thu, Fri '
WHEN left(datename(weekday, dat_start_date), 3) = 'Tue'
THEN 'Daily - Tue, Wed, Thu, Fri, Mon '
WHEN left(datename(weekday, dat_start_date), 3) = 'Wed'
THEN 'Daily - Wed, Thu, Fri, Mon, Tue '
WHEN left(datename(weekday, dat_start_date), 3) = 'Thu'
THEN 'Daily - Thu, Fri, Mon, Tue, Wed '
WHEN left(datename(weekday, dat_start_date), 3) = 'Fri'
THEN 'Daily - Fri, Mon, Tue, Wed, Thu '
END
WHEN int_daily_pattern_type = 1
THEN /*Weekend*/
CASE
WHEN left(datename(weekday, dat_start_date), 3) = 'Sun'
THEN 'Daily - Sun, Sat '
WHEN left(datename(weekday, dat_start_date), 3) = 'Sat'
THEN 'Daily - Sat, Sun '
END
ELSE ' '
END
)
WHEN chr_class_structure_type = 'B'
AND str_reocurring_pattern = 'Weekly'
AND left(datename(weekday, dat_start_date), 3) = 'Sun'
AND isnull(bit_sunday, 0) = 1
THEN 'Weekly' + (
CASE
WHEN int_interval_count >= 2
THEN '(' + CAST(int_interval_count AS VARCHAR(10)) + ') - '
ELSE ' - '
END
) + (
'Sun' + CASE
WHEN bit_monday = 1
THEN ', Mon'
ELSE ''
END + CASE
WHEN bit_tuesday = 1
THEN ', Tue'
ELSE ''
END + CASE
WHEN bit_wednesday = 1
THEN ', Wed'
ELSE ''
END + CASE
WHEN bit_thursday = 1
THEN ', Thu'
ELSE ''
END + CASE
WHEN bit_friday = 1
THEN ', Fri'
ELSE ''
END + CASE
WHEN bit_saturday = 1
THEN ', Sat'
ELSE ''
END
)
WHEN chr_class_structure_type = 'B'
AND str_reocurring_pattern = 'Weekly'
AND left(datename(weekday, dat_start_date), 3) = 'Mon'
AND bit_monday = 1
THEN 'Weekly' + (
CASE
WHEN int_interval_count >= 2
THEN '(' + CAST(int_interval_count AS VARCHAR(10)) + ') - '
ELSE ' - '
END
) + (
'Mon' + CASE
WHEN bit_tuesday = 1
THEN ', Tue'
ELSE ''
END + CASE
WHEN bit_wednesday = 1
THEN ', Wed'
ELSE ''
END + CASE
WHEN bit_thursday = 1
THEN ', Thu'
ELSE ''
END + CASE
WHEN bit_friday = 1
THEN ', Fri'
ELSE ''
END + CASE
WHEN bit_saturday = 1
THEN ', Sat'
ELSE ''
END + CASE
WHEN isnull(bit_sunday, 0) = 1
THEN ', Sun'
ELSE ''
END
)
WHEN chr_class_structure_type = 'B'
AND str_reocurring_pattern = 'Weekly'
AND left(datename(weekday, dat_start_date), 3) = 'Tue'
AND bit_tuesday = 1
THEN 'Weekly' + (
CASE
WHEN int_interval_count >= 2
THEN '(' + CAST(int_interval_count AS VARCHAR(10)) + ') - '
ELSE ' - '
END
) + (
'Tue' + CASE
WHEN bit_wednesday = 1
THEN ', Wed'
ELSE ''
END + CASE
WHEN bit_thursday = 1
THEN ', Thu'
ELSE ''
END + CASE
WHEN bit_friday = 1
THEN ', Fri'
ELSE ''
END + CASE
WHEN bit_saturday = 1
THEN ', Sat'
ELSE ''
END + CASE
WHEN isnull(bit_sunday, 0) = 1
THEN ', Sun'
ELSE ''
END + CASE
WHEN bit_monday = 1
THEN ', Mon'
ELSE ''
END
)
WHEN chr_class_structure_type = 'B'
AND str_reocurring_pattern = 'Weekly'
AND left(datename(weekday, dat_start_date), 3) = 'Wed'
AND bit_wednesday = 1
THEN 'Weekly' + (
CASE
WHEN int_interval_count >= 2
THEN '(' + CAST(int_interval_count AS VARCHAR(10)) + ') - '
ELSE ' - '
END
) + (
'Wed' + CASE
WHEN bit_thursday = 1
THEN ', Thu'
ELSE ''
END + CASE
WHEN bit_friday = 1
THEN ', Fri'
ELSE ''
END + CASE
WHEN bit_saturday = 1
THEN ', Sat'
ELSE ''
END + CASE
WHEN isnull(bit_sunday, 0) = 1
THEN ', Sun'
ELSE ''
END + CASE
WHEN bit_monday = 1
THEN ', Mon'
ELSE ''
END + CASE
WHEN bit_tuesday = 1
THEN ', Tue'
ELSE ''
END
)
WHEN chr_class_structure_type = 'B'
AND str_reocurring_pattern = 'Weekly'
AND LEFT(DATENAME(weekday, dat_start_date), 3) = 'Thu'
AND bit_thursday = 1
THEN 'Weekly' + (
CASE
WHEN int_interval_count >= 2
THEN '(' + CAST(int_interval_count AS VARCHAR(10)) + ') - '
ELSE ' - '
END
) + (
'Thu' + CASE
WHEN bit_friday = 1
THEN ', Fri'
ELSE ''
END + CASE
WHEN bit_saturday = 1
THEN ', Sat'
ELSE ''
END + CASE
WHEN isnull(bit_sunday, 0) = 1
THEN ', Sun'
ELSE ''
END + CASE
WHEN bit_monday = 1
THEN ', Mon'
ELSE ''
END + CASE
WHEN bit_tuesday = 1
THEN ', Tue'
ELSE ''
END + CASE
WHEN bit_wednesday = 1
THEN ', Wed'
ELSE ''
END
)
WHEN chr_class_structure_type = 'B'
AND str_reocurring_pattern = 'Weekly'
AND LEFT(DATENAME(weekday, dat_start_date), 3) = 'Fri'
AND bit_friday = 1
THEN 'Weekly' + (
CASE
WHEN int_interval_count >= 2
THEN '(' + CAST(int_interval_count AS VARCHAR(10)) + ') - '
ELSE ' - '
END
) + (
'Fri' + CASE
WHEN bit_saturday = 1
THEN ', Sat'
ELSE ''
END + CASE
WHEN isnull(bit_sunday, 0) = 1
THEN ', Sun'
ELSE ''
END + CASE
WHEN bit_monday = 1
THEN ', Mon'
ELSE ''
END + CASE
WHEN bit_tuesday = 1
THEN ', Tue'
ELSE ''
END + CASE
WHEN bit_wednesday = 1
THEN ', Wed'
ELSE ''
END + CASE
WHEN bit_thursday = 1
THEN ', Thu'
ELSE ''
END
)
WHEN chr_class_structure_type = 'B'
AND str_reocurring_pattern = 'Weekly'
AND LEFT(DATENAME(weekday, dat_start_date), 3) = 'Sat'
AND bit_saturday = 1
THEN 'Weekly' + (
CASE
WHEN int_interval_count >= 2
THEN '(' + CAST(int_interval_count AS VARCHAR(10)) + ') - '
ELSE ' - '
END
) + (
'Sat' + CASE
WHEN isnull(bit_sunday, 0) = 1
THEN ', Sun'
ELSE ''
END + CASE
WHEN bit_monday = 1
THEN ', Mon'
ELSE ''
END + CASE
WHEN bit_tuesday = 1
THEN ', Tue'
ELSE ''
END + CASE
WHEN bit_wednesday = 1
THEN ', Wed'
ELSE ''
END + CASE
WHEN bit_thursday = 1
THEN ', Thu'
ELSE ''
END + CASE
WHEN bit_friday = 1
THEN ', Fri'
ELSE ''
END
)
WHEN chr_class_structure_type = 'B'
AND str_reocurring_pattern = 'Monthly'
THEN (
CASE
WHEN bit_monthly_pattern = 0
THEN 'Day ' + CAST(int_interval_count AS VARCHAR(10)) + ' of every ' + CASE
WHEN int_day_monthly_count > 1
THEN CAST(int_day_monthly_count AS VARCHAR(10)) + ' Months '
ELSE ' Month '
END
WHEN bit_monthly_pattern = 1
THEN CASE
WHEN int_month_interval = 0
THEN 'Last '
WHEN int_month_interval = 1
THEN 'First '
WHEN int_month_interval = 2
THEN 'Second '
WHEN int_month_interval = 3
THEN 'Third '
WHEN int_month_interval = 4
THEN 'Fourth '
ELSE ' '
END + CASE
WHEN int_month_week_day = 0
THEN 'Day '
WHEN int_month_week_day = 1
THEN 'Sunday '
WHEN int_month_week_day = 2
THEN 'Monday '
WHEN int_month_week_day = 3
THEN 'Tuesday '
WHEN int_month_week_day = 4
THEN 'Wednesday '
WHEN int_month_week_day = 5
THEN 'Thursday '
WHEN int_month_week_day = 6
THEN 'Friday '
WHEN int_month_week_day = 7
THEN 'Saturday '
ELSE ' '
END + 'of every ' + CASE
WHEN int_day_monthly_count > 1
THEN CAST(int_day_monthly_count AS VARCHAR(10)) + ' ' + 'Months '
ELSE 'Month '
END
ELSE ' '
END
)
WHEN chr_class_structure_type = 'B'
AND str_reocurring_pattern = 'Online 24/7'
THEN 'Online 24/7'
WHEN chr_class_structure_type = 'F'
THEN substring(datename(dw, dat_start_date), 1, 3)
END AS class_days
, (
SELECT Replace(Replace(Name, '''', ''), '"', '')
FROM Locations
WHERE LocationID = int_location_id
) AS location
, (
SELECT STUFF((
SELECT ', ' + (FName + ' ' + LName)
FROM Instructors I
INNER JOIN tbl_class_instructor tci ON I.InstructorID = tci.int_instructor_id
WHERE tci.int_class_id = cl.ClassID
FOR XML PATH('')
), 1, 1, '')
) AS Instuctors
, cls.int_schedule_id
, co.isonlinecourse
FROM tbl_ClassSchedules Cls
INNER JOIN Classes cl ON cl.ClassID = cls.ClassID
INNER JOIN Courses co ON co.CourseID = cl.CourseID
INNER JOIN LessonReleaseTimes lrt ON cl.LessonReleaseID = lrt.LessonReleaseID
INNER JOIN tbl_reoccuring_pattern trp ON trp.int_reoccuring_pattern_id = cls.int_reoccuring_pattern_id
LEFT JOIN tbl_class_listing class_listing_table ON class_listing_table.ClassID = Cls.ClassID
LEFT JOIN tbl_class_detail_report cdr ON cdr.int_class_id = Cls.ClassID
WHERE cls.ClassID = cl.ClassID
AND cls.int_schedule_id <> ''
AND cls.int_schedule_id IS NOT NULL
AND cl.ClassID = ?
ORDER BY dat_start_date ASC
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
Query Parameter Value(s) -qgetClassMbrshipDetails (Datasource=v8Testing8, Time=0ms, Records=0) in E:\Lumens\Versions\v81\model\course\classinformation\act_class_information.cfm @ 01:09:53.053SELECT mt.MembershipTypeID , CASE WHEN ( mt.bit_allow_student_purchase_mbrship = 1 ) THEN mt.Name END AS Name , MAX(CAST(mt.bit_display_classes_members_only AS INT)) AS bit_display_classes_members_only , MAX(CAST(mt.bit_allow_student_purchase_mbrship AS INT)) AS bit_allow_student_purchase_mbrship FROM MembershipTypes mt INNER JOIN Classes c WITH (NOLOCK) ON c.ClassID IN (?) CROSS APPLY STRING_SPLIT(c.str_limit_self_reg_mbrship_type_ids, ',') lmt WHERE lmt.value = CAST(mt.MembershipTypeID AS VARCHAR(10)) AND mt.STATUS = 'A' GROUP BY mt.MembershipTypeID , mt.NAME , mt.SortOrder , mt.bit_allow_student_purchase_mbrship ORDER BY mt.SortOrder Query Parameter Value(s) -qClassDisplayValues (Datasource=v8Testing8, Time=0ms, Records=1) in E:\Lumens\Versions\v81\model\course\classinformation\act_class_information.cfm @ 01:09:53.053SELECT str_class_listing_order FROM tbl_class_listing_order WITH (NOLOCK) qGetClassListingOrder (Datasource=v8Testing8, Time=0ms, Records=1) in E:\Lumens\Versions\v81\model\course\classinformation\act_class_information.cfm @ 01:09:53.053SELECT str_class_listing_order FROM tbl_class_listing_order WITH (NOLOCK) qTopThreeFetauredClass (Datasource=v8Testing8, Time=0ms, Records=1) in E:\Lumens\versions\v81\cfc\class\featured.cfc @ 01:09:53.053DECLARE @TimeZoneShift as float;
Set @TimeZoneShift = IsNull((select TimeZoneHourAdjustment from Regions where RegionID = 1),0)
SELECT top 3
cl.ClassID,
co.CourseID, co.Name as CourseName,
cl.Name as ClassName
FROM Courses co WITH (NOLOCK)
INNER JOIN Subcategories sc WITH (NOLOCK)
INNER JOIN Categories c WITH (NOLOCK) ON sc.categoryid = c.categoryid
ON co.SubcategoryID = sc.SubcategoryID
INNER JOIN Classes cl WITH (NOLOCK)
INNER JOIN LessonReleaseTimes lrt WITH (NOLOCK) ON cl.LessonReleaseID=lrt.LessonReleaseID
ON co.CourseID = cl.CourseID
LEFT JOIN CourseSeries cs WITH (NOLOCK)
ON cs.CourseSeriesID = co.CourseSeriesID
WHERE
co.regionid = 1
AND
cl.Status IN ('A', 'C')
AND
DateAdd(hh, @TimeZoneShift, dbo.fun_GetDateCT()) BETWEEN cl.HotPixStartDate AND DateAdd(ms, -10, IsNull(cl.HotPixEndDate,0))
ORDER BY co.Name, co.CourseNumber, cl.StartDate
qGetPeopleTookClass (Datasource=v8Testing8, Time=0ms, Records=0) in E:\Lumens\versions\v81\cfc\courses\classinformation\classinformation.cfc @ 01:09:53.053DECLARE @SubcategoryID AS INT
,@TimeZoneShift AS FLOAT
,@CurrentDate DATETIME2;
SET @TimeZoneShift = IsNull((
SELECT TimeZoneHourAdjustment
FROM Regions
WHERE RegionID = ?
), 0);
SET @CurrentDate = DATEADD(HOUR, @TimeZoneShift, dbo.fun_GetDateCT());
SET @SubcategoryID = (
SELECT co.SubCategoryID
FROM classes c WITH (NOLOCK)
INNER JOIN courses co WITH (NOLOCK) ON c.courseid = co.courseid
WHERE c.classid = ?
)
/* Popular courses based on the Learner's registrations
1. Get courseID's from all of Learner's registrations
2. Get LearnerID's from registrations to 1's courses
3. Get top 20 of count of registrations from 2's LearnerID's */
SELECT TOP 20 1 AS SourceOrder
, r.classid
, c.NAME AS ClassName
, co.subcategoryid
, co.courseid
, co.NAME AS coursename
, co.IsOnlineCourse
, count(co.courseid) AS ClassCount
FROM registrations r WITH (NOLOCK)
INNER JOIN classes c WITH (NOLOCK) ON r.classid = c.classid
INNER JOIN courses co WITH (NOLOCK) ON c.courseid = co.courseid
INNER JOIN tbl_class_detail tcd WITH (NOLOCK) ON tcd.int_class_id = c.classid
WHERE
r.learnerid IN (
SELECT r.learnerid
FROM registrations r WITH (NOLOCK)
INNER JOIN classes c WITH (NOLOCK) ON r.classid = c.classid
WHERE c.courseid IN (
SELECT c.courseID
FROM registrations r WITH (NOLOCK)
INNER JOIN classes c WITH (NOLOCK) ON r.classid = c.classid
WHERE r.learnerid IN (?)
AND R.IsCancelled = 0
)
AND r.CancelledBy IS NULL
)
/* Add other criteria below to only show courses which have available classes */
AND co.subcategoryid = @SubcategoryID
AND c.STATUS IN ('A')
AND c.remainingseats > 0
AND c.dat_public_registration_begin <= @CurrentDate
AND c.dat_public_registration_closes >= @CurrentDate
AND r.classID <> ?
AND tcd.bit_display_to_public = 1
AND NOT EXISTS (
SELECT 1
FROM tbl_restriction_code_detail
WHERE int_table_id = 2 /*classes*/
AND int_primary_key_id = c.ClassID
)
GROUP BY r.classid
, co.courseid
, co.subcategoryid
, c.NAME
, co.NAME
, co.IsOnlineCourse
HAVING sum(c.RemainingSeats) > 0
UNION
/* Popular courses based on a subcategory
1. Get courseID's from all of selected subcategory
2. Get top 20 of count of registrations from 1's CourseID's */
SELECT TOP 20 2 AS SourceOrder
, r.classid
, c.NAME AS ClassName
, co.subcategoryid
, co.courseid
, co.NAME AS coursename
, co.IsOnlineCourse
, count(r.RegistrationID) AS ClassCount
FROM registrations r WITH (NOLOCK)
INNER JOIN classes c WITH (NOLOCK) ON r.classid = c.classid
INNER JOIN courses co WITH (NOLOCK) ON c.courseid = co.courseid
INNER JOIN tbl_class_detail tcd WITH (NOLOCK) ON tcd.int_class_id = c.classid
/* Add other criteria below to only show courses which have available classes */
WHERE co.subcategoryid = @SubcategoryID
AND R.IsCancelled = 0
AND c.STATUS IN ('A')
AND c.remainingseats > 0
AND c.dat_public_registration_begin <= @CurrentDate
AND c.dat_public_registration_closes >= @CurrentDate
AND r.classID <> ?
AND tcd.bit_display_to_public = 1
AND NOT EXISTS (
SELECT 1
FROM tbl_restriction_code_detail
WHERE int_table_id = 2 /*classes*/
AND int_primary_key_id = c.ClassID
)
GROUP BY r.classid
, co.courseid
, co.subcategoryid
, c.NAME
, co.NAME
, co.IsOnlineCourse
HAVING sum(c.RemainingSeats) > 0
ORDER BY sourceorder
, ClassCount DESC
Query Parameter Value(s) -qGetClassListing (Datasource=v8Testing8, Time=16ms, Records=2) in E:\Lumens\Versions\v81\model\course\classinformation\act_class_information.cfm @ 01:09:53.053SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @MembershipID AS INT
, @TimeZoneShift AS FLOAT
, @LearnerID INT
, @SubcategoryID INT
, @CourseID INT
, @ClassID INT
, @RegionID INT;
SET @LearnerID = ?;
SET @RegionID = ?;
SET @CourseID = ?;
SET @ClassID = ?;
SET @MembershipID = IsNull((
SELECT MembershipID
FROM learners
WHERE learnerid = @LearnerID
), 0)
/**/
SET @TimeZoneShift = IsNull((
SELECT TimeZoneHourAdjustment
FROM Regions
WHERE RegionID = @RegionID
), 0)
SELECT DISTINCT C.MaterialsCost
/**/
/**/
, CASE
WHEN co.int_schedule_type_id = 2
THEN CASE
WHEN cl.dat_public_registration_begin < cl.dat_staff_registration_begin
THEN CAST(cl.dat_public_registration_begin AS VARCHAR) /*Modified: NEO 00083 bugfix*/
ELSE CAST(cl.dat_staff_registration_begin AS VARCHAR) /*Modified: NEO 00083 bugfix*/
END
ELSE cl.ClassStartDate
END AS ClassStartDate
/**/
, (
SELECT CASE
WHEN I.CatalogName IS NOT NULL
AND I.CatalogName <> ''
THEN I.CatalogName
ELSE isnull(I.FName, '') + ' ' + isnull(I.LName, '')
END
FROM instructors I
WHERE I.instructorid = CL.instructorid
) AS instructor
/**/
/**/
, CL.*
/**/
, cl.str_class_schedule_display AS ClassSchedule
,cl.InstructionMethod AS InstructionalMethod
,cl.DaysOfWeek AS DaysOfWeeks
,cdr.str_class_Instructor_display_line AS Instructors
,CASE
WHEN co.ExternalCourseProviderID = 1
THEN ''
ELSE cl.URLText
END AS CourseURLText
,CASE
WHEN co.ExternalCourseProviderID = 1
THEN ''
ELSE cd.str_url
END AS ClassURL
,CASE
WHEN co.ExternalCourseProviderID = 1
THEN ''
ELSE cd.str_url_text
END AS ClassURlText
,cd.str_default_text_for_call_message_class
/**/
,CASE
WHEN C.RequiresReleaseForm = 1
AND C.ReleaseFormNameId IS NOT NULL
THEN 1
ELSE 0
END AS RequiresReleaseForm
,cl.CourseTitle AS NAME
,((IsNull(cl.TotalSeats, 0) - IsNull(c.RemainingSeats, 0)) + isnull(ccd.ReservationCount, 0)) AS registrationCount
,c.remainingseats
,cl.TotalSeats - c.RemainingSeats AS FilledSeats
,CASE
WHEN cl.TotalSeats > 0
THEN CASE
WHEN cl.TotalSeats = c.remainingseats
THEN 'No registrations'
ELSE convert(VARCHAR(6), Round((convert(FLOAT, cl.TotalSeats - c.remainingseats)) / cl.TotalSeats * 100, 0)) + '% filled'
END
ELSE 'No Seats'
END AS Capacity
,IsNull(wl.WaitingListCount, 0) AS waitingList
,CASE
WHEN IsNull(wl.WaitingListCount, 0) > 0
THEN 1
ELSE 0
END AS bit_class_on_waiting_list
,CASE
WHEN (
c.remainingseats <= 0
OR IsNull(wl.WaitingListCount, 0) > 0
)
THEN 1
ELSE 0
END AS isfull
,CASE @MembershipID
WHEN 0
THEN
/* count distinct discounts */
(
SELECT count(*)
FROM (
SELECT DISTINCT clf.Cost_1
,clf.DiscountAmount_1
FROM ClassFees clf
INNER JOIN CourseFees cf ON cf.CourseFeeID = clf.CourseFeeID
WHERE cf.FeeTypeID != 7
AND clf.ClassID = cl.classid
AND clf.STATUS = 'A'
AND (
cf.FeeTypeID NOT IN (
4
,5
)
OR dbo.fun_GetDateCT() BETWEEN clf.StartDate
AND clf.Enddate
)
) tblTemp
)
ELSE (
SELECT count(ClassFeeID)
FROM learners l
INNER JOIN memberships m ON l.membershipid = m.membershipid
INNER JOIN coursefees cf ON m.membershiptypeid = cf.membershiptypeid
INNER JOIN classfees clf ON cf.coursefeeid = clf.coursefeeid
WHERE l.learnerid = 0
AND cf.FeeTypeID != 7
AND clf.ClassID = cl.ClassID
AND clf.STATUS = 'A'
AND (
cf.FeeTypeID NOT IN (
4
,5
)
OR dbo.fun_GetDateCT() BETWEEN clf.StartDate
AND clf.Enddate
)
)
END AS DiscountFeeCount
,CASE
WHEN @MembershipID > 0
THEN IsNull((
SELECT clf.DiscountAmount_1
FROM learners l
INNER JOIN memberships m ON l.membershipid = m.membershipid
INNER JOIN coursefees cf ON m.membershiptypeid = cf.membershiptypeid
INNER JOIN classfees clf ON cf.coursefeeid = clf.coursefeeid
WHERE l.learnerid = 94
AND cf.FeeTypeID != 7
AND clf.ClassID = cl.ClassID
AND clf.STATUS = 'A'
AND (
cf.FeeTypeID NOT IN (
4
,5
)
OR dbo.fun_GetDateCT() BETWEEN clf.StartDate
AND clf.Enddate
)
), 0)
WHEN
/* see if there is one distinct price for members, and if so, reflect that */
(
SELECT count(*)
FROM (
SELECT DISTINCT clf.Cost_1
,clf.DiscountAmount_1
FROM ClassFees clf
INNER JOIN CourseFees cf ON cf.CourseFeeID = clf.CourseFeeID
WHERE cf.FeeTypeID != 7
AND clf.ClassID = cl.classid
AND clf.STATUS = 'A'
AND (
cf.FeeTypeID NOT IN (
4
,5
)
OR dbo.fun_GetDateCT() BETWEEN clf.StartDate
AND clf.Enddate
)
) tblTemp
) = 1
THEN (
SELECT MAX(clf.DiscountAmount_1)
FROM ClassFees clf
INNER JOIN CourseFees cf ON cf.CourseFeeID = clf.CourseFeeID
WHERE cf.FeeTypeID != 7
AND clf.ClassID = cl.ClassID
AND clf.STATUS = 'A'
AND (
cf.FeeTypeID NOT IN (
4
,5
)
OR dbo.fun_GetDateCT() BETWEEN clf.StartDate
AND clf.Enddate
)
)
ELSE 0
END AS DiscountAmount
,
/**/
CASE @MembershipID
WHEN 0
THEN
/* count distinct member discounts */
(
SELECT count(*)
FROM (
SELECT DISTINCT clf.Cost_1
,clf.DiscountAmount_1
FROM ClassFees clf
INNER JOIN CourseFees cf ON cf.CourseFeeID = clf.CourseFeeID
WHERE cf.FeeTypeID != 7
AND clf.ClassID = cl.classid
AND clf.STATUS = 'A'
AND (cf.FeeTypeID IN (2))
) tblTemp
)
ELSE (
SELECT count(ClassFeeID)
FROM learners l
INNER JOIN memberships m ON l.membershipid = m.membershipid
INNER JOIN coursefees cf ON m.membershiptypeid = cf.membershiptypeid
INNER JOIN classfees clf ON cf.coursefeeid = clf.coursefeeid
WHERE l.learnerid = @LearnerID
AND cf.FeeTypeID != 7
AND clf.ClassID = cl.ClassID
AND clf.STATUS = 'A'
AND cf.FeeTypeID IN (2)
)
END AS MemberDiscountFeeCount
,CASE
WHEN @MembershipID > 0
THEN ISNull((
SELECT clf.DiscountAmount_1
FROM learners l
INNER JOIN memberships m ON l.membershipid = m.membershipid
INNER JOIN coursefees cf ON m.membershiptypeid = cf.membershiptypeid
INNER JOIN classfees clf ON cf.coursefeeid = clf.coursefeeid
WHERE l.learnerid = @LearnerID
AND cf.FeeTypeID != 7
AND clf.ClassID = cl.ClassID
AND clf.STATUS = 'A'
AND cf.FeeTypeID IN (2)
), 0)
WHEN
/* see if there is one distinct price for members, and if so, reflect that */
(
SELECT count(*)
FROM (
SELECT DISTINCT clf.Cost_1
,clf.DiscountAmount_1
FROM ClassFees clf
INNER JOIN CourseFees cf ON cf.CourseFeeID = clf.CourseFeeID
WHERE cf.FeeTypeID != 7
AND clf.ClassID = cl.classid
AND clf.STATUS = 'A'
AND cf.FeeTypeID IN (2)
) tblTemp
) = 1
THEN (
SELECT MIN(clf.DiscountAmount_1)
FROM ClassFees clf
INNER JOIN CourseFees cf ON cf.CourseFeeID = clf.CourseFeeID
WHERE cf.FeeTypeID != 7
AND clf.ClassID = cl.ClassID
AND clf.STATUS = 'A'
AND cf.FeeTypeID IN (2)
)
ELSE 0
END AS MinMemberDiscountAmount
,
/* */
CASE
WHEN EXISTS (
SELECT *
FROM Registrations R
WHERE R.classid = cl.classid
AND R.LearnerID = 0
AND R.IsCancelled = 0
)
THEN 1
ELSE 0
END AS bit_enrolled
,CASE
WHEN EXISTS (
SELECT *
FROM WaitingLists w
INNER JOIN WaitingListEntries we ON w.waitlistid = we.waitlistid
WHERE w.classid = cl.classid
AND we.LearnerID = 0
)
THEN 1
ELSE 0
END AS bit_on_waiting_list
,CASE
WHEN (
SELECT Birthdate
FROM learners
WHERE learnerid = 0
) IS NOT NULL
THEN CASE
WHEN (
datediff(yy, (
SELECT Birthdate
FROM learners
WHERE learnerid = 0
), CL.StartDate) < cl.MinAge
OR datediff(yy, (
SELECT Birthdate
FROM learners
WHERE learnerid = 0
), CL.StartDate) > cl.MaxAge
)
THEN 2
ELSE 0
END
ELSE 1
END AS ageLimit
,co.int_schedule_type_id AS ScheduleType
,cd.int_duration_type_id AS DurationType
,cd.int_quantity
,cd.int_time_period_id
,(
SELECT str_description
FROM tbl_time_periods
WHERE int_time_period_id = cd.int_time_period_id
) AS TimeDescription
,CASE
WHEN (
LEN(mx.minage) > 0
AND LEN(mx.maxage) > 0
)
THEN convert(VARCHAR, mx.minage) + ' - ' + convert(VARCHAR, mx.maxage)
ELSE ''
END AS ClassAges
,c.CEUs
,c.ContactHrs
,c.TotalSeats
/**/
,(
SELECT bit_uses_class_lottery
FROM tbl_class_detail
WHERE int_class_id = c.ClassID
) AS bit_uses_class_lottery
,(
SELECT dat_lottery_close_date
FROM tbl_class_detail
WHERE int_class_id = c.ClassID
) AS dat_lottery_close_date
,(
SELECT dat_lottery_run_date
FROM tbl_class_detail
WHERE int_class_id = c.ClassID
) AS dat_lottery_run_date
,(
SELECT int_lottery_status_id
FROM tbl_class_detail
WHERE int_class_id = c.ClassID
) AS int_lottery_status_id
,isnull((
SELECT count(lr.int_lottery_request_id)
FROM tbl_lottery_requests lr
WHERE lr.int_class_id = cl.classid
AND isNULL(lr.bit_removed, 0) = 0
AND lr.chr_status = 'A'
), 0) AS lotteryRequestCount
/**/
/**/
,CASE
WHEN chr_class_structure_type = 'B'
AND str_reocurring_pattern = 'Daily'
THEN (
CASE
WHEN int_daily_pattern_type = - 1
THEN /*everydays*/
CASE
WHEN int_interval_count = 1
THEN 'Daily '
WHEN int_interval_count >= 2
THEN 'Every ' + CAST(int_interval_count AS VARCHAR(10)) + ' Days '
ELSE ' '
END
WHEN int_daily_pattern_type = 0
THEN /*weekdays*/
CASE
WHEN left(datename(weekday, dat_start_date), 3) = 'Mon'
THEN 'Weekly - Mon, Tue, Wed, Thu, Fri '
WHEN left(datename(weekday, dat_start_date), 3) = 'Tue'
THEN 'Weekly - Tue, Wed, Thu, Fri, Mon '
WHEN left(datename(weekday, dat_start_date), 3) = 'Wed'
THEN 'Weekly - Wed, Thu, Fri, Mon, Tue '
WHEN left(datename(weekday, dat_start_date), 3) = 'Thu'
THEN 'Weekly - Thu, Fri, Mon, Tue, Wed '
WHEN left(datename(weekday, dat_start_date), 3) = 'Fri'
THEN 'Weekly - Fri, Mon, Tue, Wed, Thu '
END
WHEN int_daily_pattern_type = 1
THEN /*Weekend*/
CASE
WHEN left(datename(weekday, dat_start_date), 3) = 'Sun'
THEN 'Weekly - Sun, Sat '
WHEN left(datename(weekday, dat_start_date), 3) = 'Sat'
THEN 'Weekly - Sat, Sun '
END
ELSE ' '
END
)
WHEN chr_class_structure_type = 'B'
AND str_reocurring_pattern = 'Weekly'
AND left(datename(weekday, dat_start_date), 3) = 'Sun'
AND isnull(bit_sunday, 0) = 1
THEN 'Weekly' + (
CASE
WHEN int_interval_count >= 2
THEN '(' + CAST(int_interval_count AS VARCHAR(10)) + ') - '
ELSE ' - '
END
) + (
'Sun' + CASE
WHEN bit_monday = 1
THEN ', Mon'
ELSE ''
END + CASE
WHEN bit_tuesday = 1
THEN ', Tue'
ELSE ''
END + CASE
WHEN bit_wednesday = 1
THEN ', Wed'
ELSE ''
END + CASE
WHEN bit_thursday = 1
THEN ', Thu'
ELSE ''
END + CASE
WHEN bit_friday = 1
THEN ', Fri'
ELSE ''
END + CASE
WHEN bit_saturday = 1
THEN ', Sat'
ELSE ''
END
)
WHEN chr_class_structure_type = 'B'
AND str_reocurring_pattern = 'Weekly'
AND left(datename(weekday, dat_start_date), 3) = 'Mon'
AND bit_monday = 1
THEN 'Weekly' + (
CASE
WHEN int_interval_count >= 2
THEN '(' + CAST(int_interval_count AS VARCHAR(10)) + ') - '
ELSE ' - '
END
) + (
'Mon' + CASE
WHEN bit_tuesday = 1
THEN ', Tue'
ELSE ''
END + CASE
WHEN bit_wednesday = 1
THEN ', Wed'
ELSE ''
END + CASE
WHEN bit_thursday = 1
THEN ', Thu'
ELSE ''
END + CASE
WHEN bit_friday = 1
THEN ', Fri'
ELSE ''
END + CASE
WHEN bit_saturday = 1
THEN ', Sat'
ELSE ''
END + CASE
WHEN isnull(bit_sunday, 0) = 1
THEN ', Sun'
ELSE ''
END
)
WHEN chr_class_structure_type = 'B'
AND str_reocurring_pattern = 'Weekly'
AND left(datename(weekday, dat_start_date), 3) = 'Tue'
AND bit_tuesday = 1
THEN 'Weekly' + (
CASE
WHEN int_interval_count >= 2
THEN '(' + CAST(int_interval_count AS VARCHAR(10)) + ') - '
ELSE ' - '
END
) + (
'Tue' + CASE
WHEN bit_wednesday = 1
THEN ', Wed'
ELSE ''
END + CASE
WHEN bit_thursday = 1
THEN ', Thu'
ELSE ''
END + CASE
WHEN bit_friday = 1
THEN ', Fri'
ELSE ''
END + CASE
WHEN bit_saturday = 1
THEN ', Sat'
ELSE ''
END + CASE
WHEN isnull(bit_sunday, 0) = 1
THEN ', Sun'
ELSE ''
END + CASE
WHEN bit_monday = 1
THEN ', Mon'
ELSE ''
END
)
WHEN chr_class_structure_type = 'B'
AND str_reocurring_pattern = 'Weekly'
AND left(datename(weekday, dat_start_date), 3) = 'Wed'
AND bit_wednesday = 1
THEN 'Weekly' + (
CASE
WHEN int_interval_count >= 2
THEN '(' + CAST(int_interval_count AS VARCHAR(10)) + ') - '
ELSE ' - '
END
) + (
'Wed' + CASE
WHEN bit_thursday = 1
THEN ', Thu'
ELSE ''
END + CASE
WHEN bit_friday = 1
THEN ', Fri'
ELSE ''
END + CASE
WHEN bit_saturday = 1
THEN ', Sat'
ELSE ''
END + CASE
WHEN isnull(bit_sunday, 0) = 1
THEN ', Sun'
ELSE ''
END + CASE
WHEN bit_monday = 1
THEN ', Mon'
ELSE ''
END + CASE
WHEN bit_tuesday = 1
THEN ', Tue'
ELSE ''
END
)
WHEN chr_class_structure_type = 'B'
AND str_reocurring_pattern = 'Weekly'
AND LEFT(DATENAME(weekday, dat_start_date), 3) = 'Thu'
AND bit_thursday = 1
THEN 'Weekly' + (
CASE
WHEN int_interval_count >= 2
THEN '(' + CAST(int_interval_count AS VARCHAR(10)) + ') - '
ELSE ' - '
END
) + (
'Thu' + CASE
WHEN bit_friday = 1
THEN ', Fri'
ELSE ''
END + CASE
WHEN bit_saturday = 1
THEN ', Sat'
ELSE ''
END + CASE
WHEN isnull(bit_sunday, 0) = 1
THEN ', Sun'
ELSE ''
END + CASE
WHEN bit_monday = 1
THEN ', Mon'
ELSE ''
END + CASE
WHEN bit_tuesday = 1
THEN ', Tue'
ELSE ''
END + CASE
WHEN bit_wednesday = 1
THEN ', Wed'
ELSE ''
END
)
WHEN chr_class_structure_type = 'B'
AND str_reocurring_pattern = 'Weekly'
AND LEFT(DATENAME(weekday, dat_start_date), 3) = 'Fri'
AND bit_friday = 1
THEN 'Weekly' + (
CASE
WHEN int_interval_count >= 2
THEN '(' + CAST(int_interval_count AS VARCHAR(10)) + ') - '
ELSE ' - '
END
) + (
'Fri' + CASE
WHEN bit_saturday = 1
THEN ', Sat'
ELSE ''
END + CASE
WHEN isnull(bit_sunday, 0) = 1
THEN ', Sun'
ELSE ''
END + CASE
WHEN bit_monday = 1
THEN ', Mon'
ELSE ''
END + CASE
WHEN bit_tuesday = 1
THEN ', Tue'
ELSE ''
END + CASE
WHEN bit_wednesday = 1
THEN ', Wed'
ELSE ''
END + CASE
WHEN bit_thursday = 1
THEN ', Thu'
ELSE ''
END
)
WHEN chr_class_structure_type = 'B'
AND str_reocurring_pattern = 'Weekly'
AND LEFT(DATENAME(weekday, dat_start_date), 3) = 'Sat'
AND bit_saturday = 1
THEN 'Weekly' + (
CASE
WHEN int_interval_count >= 2
THEN '(' + CAST(int_interval_count AS VARCHAR(10)) + ') - '
ELSE ' - '
END
) + (
'Sat' + CASE
WHEN isnull(bit_sunday, 0) = 1
THEN ', Sun'
ELSE ''
END + CASE
WHEN bit_monday = 1
THEN ', Mon'
ELSE ''
END + CASE
WHEN bit_tuesday = 1
THEN ', Tue'
ELSE ''
END + CASE
WHEN bit_wednesday = 1
THEN ', Wed'
ELSE ''
END + CASE
WHEN bit_thursday = 1
THEN ', Thu'
ELSE ''
END + CASE
WHEN bit_friday = 1
THEN ', Fri'
ELSE ''
END
)
WHEN chr_class_structure_type = 'B'
AND str_reocurring_pattern = 'Monthly'
THEN (
CASE
WHEN bit_monthly_pattern = 0
THEN 'Day ' + CAST(int_interval_count AS VARCHAR(10)) + ' of every ' + CASE
WHEN int_day_monthly_count > 1
THEN CAST(int_day_monthly_count AS VARCHAR(10)) + ' Months '
ELSE ' Month '
END
WHEN bit_monthly_pattern = 1
THEN CASE
WHEN int_month_interval = 0
THEN 'Last '
WHEN int_month_interval = 1
THEN 'First '
WHEN int_month_interval = 2
THEN 'Second '
WHEN int_month_interval = 3
THEN 'Third '
WHEN int_month_interval = 4
THEN 'Fourth '
ELSE ' '
END + CASE
WHEN int_month_week_day = 0
THEN 'Day '
WHEN int_month_week_day = 1
THEN 'Sunday '
WHEN int_month_week_day = 2
THEN 'Monday '
WHEN int_month_week_day = 3
THEN 'Tuesday '
WHEN int_month_week_day = 4
THEN 'Wednesday '
WHEN int_month_week_day = 5
THEN 'Thursday '
WHEN int_month_week_day = 6
THEN 'Friday '
WHEN int_month_week_day = 7
THEN 'Saturday '
ELSE ' '
END + 'of every ' + CASE
WHEN int_day_monthly_count > 1
THEN CAST(int_day_monthly_count AS VARCHAR(10)) + ' ' + 'Months '
ELSE 'Month '
END
ELSE ' '
END
)
WHEN chr_class_structure_type = 'B'
AND str_reocurring_pattern = 'Online 24/7'
THEN 'Online 24/7'
WHEN chr_class_structure_type = 'F'
THEN substring(datename(dw, dat_start_date), 1, 3)
END AS class_days
/**/
,Cls.int_schedule_id AS ScheduleId
/**/
, cl.bit_allow_wcg_registration
, CL.bit_allow_waiting_lists
FROM tbl_class_listing CL
INNER JOIN classes c ON cl.classid = c.classid
LEFT JOIN (
SELECT ClassID
,count(classid) AS WaitingListCount
FROM waitingLists
GROUP BY ClassID
) wl ON cl.classid = wl.classid
LEFT JOIN (
SELECT int_class_id
,count(int_class_id) AS ReservationCount
FROM tbl_cart_class_detail
GROUP BY int_class_id
) ccd ON ccd.int_class_id = cl.classid
INNER JOIN Courses co ON co.CourseID = c.CourseID
LEFT JOIN tbl_class_detail cd ON cl.classid = cd.int_class_id
/**/
INNER JOIN tbl_ClassSchedules Cls ON Cls.ClassID = CL.classid
INNER JOIN tbl_reoccuring_pattern trp ON trp.int_reoccuring_pattern_id = Cls.int_reoccuring_pattern_id
/**/
/**/
LEFT JOIN tbl_class_detail_report cdr ON cdr.int_class_id = cl.ClassID
/**/
LEFT JOIN MinMaxAge mx ON mx.classID = C.classid
WHERE 1 = 1
AND c.STATUS IN ('A','C')
/* */
/**/
AND cl.courseid = @CourseID
/**/
AND CL.ClassID <> @ClassID
AND DateAdd(hh, @TimeZoneShift, dbo.fun_GetDateCT()) BETWEEN cl.AppearanceStartDate AND DateAdd(ms, -10, cl.AppearanceEndDate)
/**/
AND (CL.bit_display_to_public = 1)
/**/
AND NOT EXISTS (
SELECT 1
FROM tbl_restriction_code_detail
WHERE int_table_id = 2 /*classes*/
AND int_primary_key_id = cl.ClassID
)
ORDER BY CL.ClassName
,CL.StartDateOrder
,CL.ClassID
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
Query Parameter Value(s) -qGetSkipDate (Datasource=v8Testing8, Time=9ms, Records=0) in E:\Lumens\versions\v81\cfc\courses\classinformation\classinformation.cfc @ 01:09:53.053SELECT convert(VARCHAR, dat_skip_date, 101) AS dat_skip_date
FROM tbl_skip_dates WITH (NOLOCK)
WHERE int_class_id = ?
AND CONVERT(VARCHAR(50), dat_skip_date, 101) NOT IN (
SELECT CONVERT(VARCHAR(50), MeetingStart, 101)
FROM ClassMeetings
WHERE ClassID = ?
AND STATUS = 'A'
) /*Tracker 50452 Feb 29 2016 Neo 00198*/
AND bit_class_scheduled_global_date = ?
ORDER BY tbl_skip_dates.dat_skip_date
Query Parameter Value(s) -qGetInstructor (Datasource=v8Testing8, Time=0ms, Records=1) in E:\Lumens\versions\v81\cfc\courses\classinformation\classinformation.cfc @ 01:09:53.053SELECT InstructorID,FName,LName,int_bio_id,catalogName as InstrCatalogName
FROM tbl_class_instructor CI WITH (NOLOCK)
INNER JOIN Instructors IR WITH (NOLOCK) ON CI.int_instructor_id = IR.InstructorID
WHERE CI.int_class_id = ?
Query Parameter Value(s) -qGetMembershipCost (Datasource=v8Testing8, Time=0ms, Records=1) in E:\Lumens\versions\v81\cfc\courses\classinformation\classinformation.cfc @ 01:09:53.053SELECT max(AnnualFee) as AnnualFee FROM MembershipTypes WITH (NOLOCK) WHERE MaxNumberMembers = (SELECT min(MaxNumberMembers)FROM MembershipTypes) qGetClassSyllabus (Datasource=lumenscustomers, Time=0ms, Records=0) in E:\Lumens\versions\v81\cfc\courses\classinformation\classinformation.cfc @ 01:09:53.053SELECT cs.Description, cs.MeetingNumber FROM ECPCourseSyllabus cs WITH (NOLOCK) inner join ECPCourses c WITH (NOLOCK) on cs.ECPCourseID = c.ECPCourseID WHERE c.CourseCode = '' and c.ExternalProviderID = ? ORDER BY cs.MeetingNumber Query Parameter Value(s) -qCheckEnrolled (Datasource=v8Testing8, Time=15ms, Records=1) in E:\Lumens\Versions\v81\model\course\classinformation\act_class_information.cfm @ 01:09:53.053SELECT count(RegistrationID) AS rec_count FROM Registrations R WITH (NOLOCK) WHERE R.classid = ? AND R.LearnerID IN (?) AND ( R.IsCancelled = 0 OR R.IsCancelled IS NULL ) Query Parameter Value(s) -qCheckMultipleRegistration (Datasource=v8Testing8, Time=0ms, Records=1) in E:\Lumens\Versions\v81\model\course\classinformation\act_class_information.cfm @ 01:09:53.053SELECT bit_allow_multiple_registration FROM Classes WITH (NOLOCK) WHERE Classid = ?; Query Parameter Value(s) -qcheckWaitingList (Datasource=v8Testing8, Time=0ms, Records=1) in E:\Lumens\Versions\v81\model\course\classinformation\act_class_information.cfm @ 01:09:53.053SELECT Count(we.WaitListEntryID) AS WaitingListCount FROM WaitingLists w WITH (NOLOCK) INNER JOIN WaitingListEntries we WITH (NOLOCK) ON w.waitlistid = we.waitlistid WHERE w.classid = ? AND we.LearnerID IN (?) Query Parameter Value(s) -qGetFeatureConfigurationValues (Datasource=v8Testing8, Time=0ms, Records=1) in E:\Lumens\Versions\v81\model\course\classinformation\act_class_information.cfm @ 01:09:53.053SELECT bit_use_reg_age_limits FROM FeatureConfiguration WITH (NOLOCK) qGetClassIsOnlineBit (Datasource=v8Testing8, Time=0ms, Records=1) in E:\Lumens\Versions\v81\model\course\classinformation\act_class_information.cfm @ 01:09:53.053SELECT CO.IsOnlineCourse FROM Courses CO WITH (NOLOCK) INNER JOIN Classes CL WITH (NOLOCK) ON CL.CourseID = CO.CourseID WHERE CL.classid = ? Query Parameter Value(s) -qEvaluationComments (Datasource=v8Testing8, Time=16ms, Records=0) in E:\Lumens\versions\v81\cfc\courses\classinformation\classinformation.cfc @ 01:09:53.053SELECT int_eval_form_result_details_id ,bit_display_with_classinfo ,str_response_text FROM tbl_eval_form_results tefr INNER JOIN tbl_eval_form_result_details tefrd ON tefrd.int_eval_form_result_id = tefr.int_eval_form_result_id INNER JOIN tbl_eval_qtns teq ON teq.int_eval_qtn_id = tefrd.int_eval_qtn_id INNER JOIN tbl_eval_qtn_type teqt ON teqt.int_eval_qtn_type_id = teq.int_eval_qtn_type_id INNER JOIN Classes Cl ON Cl.ClassID = tefr.int_class_id INNER JOIN Courses C ON C.CourseID = CL.CourseID WHERE teqt.str_eval_qtn_type_name = 'Comment' AND C.CourseID IN ( SELECT CourseID FROM Classes WHERE ClassID IN (?) ) AND bit_display_with_classinfo = 1 Query Parameter Value(s) -qGetSectionNumberLabel (Datasource=v8Testing8, Time=0ms, Records=1) in E:\Lumens\Versions\v81\model\course\classinformation\act_class_information.cfm @ 01:09:53.053SELECT TOP 1 Label FROM base_field_customized WITH (NOLOCK) WHERE base_field = 'class_section' AND base_table_id = 2 AND customized_base_field_id IS NOT NULL qgetClassMbrshipDetails (Datasource=v8Testing8, Time=0ms, Records=0) in E:\Lumens\Versions\v81\model\course\classinformation\act_class_information.cfm @ 01:09:53.053SELECT mt.MembershipTypeID , CASE WHEN ( mt.bit_allow_student_purchase_mbrship = 1 ) THEN mt.Name END AS Name , MAX(CAST(mt.bit_display_classes_members_only AS INT)) AS bit_display_classes_members_only , MAX(CAST(mt.bit_allow_student_purchase_mbrship AS INT)) AS bit_allow_student_purchase_mbrship FROM MembershipTypes mt INNER JOIN Classes c WITH (NOLOCK) ON c.ClassID IN (?) CROSS APPLY STRING_SPLIT(c.str_limit_self_reg_mbrship_type_ids, ',') lmt WHERE lmt.value = CAST(mt.MembershipTypeID AS VARCHAR(10)) AND mt.STATUS = 'A' GROUP BY mt.MembershipTypeID , mt.NAME , mt.SortOrder , mt.bit_allow_student_purchase_mbrship ORDER BY mt.SortOrder Query Parameter Value(s) -qGetSheduleInfoNew (Datasource=v8Testing8, Time=16ms, Records=1) in E:\Lumens\Versions\v81\model\course\classinformation\act_class_information.cfm @ 01:09:53.053SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @Classid INT
, @RoomBit INT
, @UsesRoomMgmt BIT = 0;
SET @Classid = ?;
SELECT @RoomBit = COUNT(*)
FROM tbl_class_listing_order
WHERE SUBSTRING(str_class_listing_order, (PATINDEX('%:Classroom%', str_class_listing_order) - 3), 1) IN (1, 2, 3)
SELECT @UsesRoomMgmt = UsesRoomMgmt FROM FeatureConfiguration
SELECT cls.ClassScheduleID
, cl.ClassID
, co.IsOnlineCourse
, co.ExternalCourseProviderID
, cl.StartDate
, cl.EndDate
, lrt.Description AS LessonReleaseTimes
, rp.str_reocurring_pattern AS str_reoccuring_pattern
, CASE
WHEN chr_class_structure_type = 'B'
THEN FORMAT(dat_start_date, 'M/d/yyyy') + ' - ' + FORMAT(dat_end_date, 'M/d/yyyy')
WHEN chr_class_structure_type = 'F'
THEN FORMAT(dat_start_date, 'M/d/yyyy')
END AS Dates
, CASE
WHEN chr_class_structure_type = 'B'
THEN CASE
WHEN rp.str_reocurring_pattern = 'Daily'
THEN '' + CASE
WHEN int_daily_pattern_type = - 1
THEN /*everydays*/
CASE
WHEN int_interval_count = 1
THEN 'Daily - ' + dbo.fun_class_dates_setting(cls.ClassScheduleID, cls.ClassID) /**/
WHEN int_interval_count >= 2
THEN 'Every ' + CAST(Cls.int_interval_count AS VARCHAR(10)) + ' Days '
ELSE ' '
END
WHEN int_daily_pattern_type = 0
THEN /*weekdays*/
'Daily - Mon, Tue, Wed, Thu, Fri '
WHEN int_daily_pattern_type = 1
THEN /*Weekend*/
'Daily - Sun, Sat '
ELSE ' '
END
WHEN rp.str_reocurring_pattern = 'Weekly'
THEN 'Weekly' + CASE
WHEN int_interval_count >= 2
THEN '(' + CAST(Cls.int_interval_count AS VARCHAR(10)) + ') -'
ELSE ' -'
END + CONCAT_WS(',', CASE WHEN bit_sunday = 1 THEN ' Sun' END
, CASE WHEN bit_monday = 1 THEN ' Mon' END
, CASE WHEN bit_tuesday = 1 THEN ' Tue' END
, CASE WHEN bit_wednesday = 1 THEN ' Wed' END
, CASE WHEN bit_thursday = 1 THEN ' Thu' END
, CASE WHEN bit_friday = 1 THEN ' Fri' END
, CASE WHEN bit_saturday = 1 THEN ' Sat' END) + ' '
WHEN rp.str_reocurring_pattern = 'Monthly'
THEN
--'Monthly '
CASE
WHEN Cls.bit_monthly_pattern = 0
THEN 'Day ' + CAST(Cls.int_interval_count AS VARCHAR(10)) + ' of every ' + CASE
WHEN Cls.int_day_monthly_count > 1
THEN CAST(Cls.int_day_monthly_count AS VARCHAR(10)) + ' Months '
ELSE ' Month '
END
WHEN Cls.bit_monthly_pattern = 1
THEN
/*int_month_interval*/ /* FROM table tbl_month_day 1,2,3,4,0 for first, second, third, fourth, last - n */
/*int_month_week_day*/ /* FROM table tbl_month_week_day 0,1,2,3,4,5,6,7 for Day, Sun, Mon, Tue, Wed, Thu, Fri, Sat, last -8 */
CASE
WHEN int_month_interval = 0
THEN 'Last '
WHEN int_month_interval = 1
THEN 'First '
WHEN int_month_interval = 2
THEN 'Second '
WHEN int_month_interval = 3
THEN 'Third '
WHEN int_month_interval = 4
THEN 'Fourth '
ELSE ' '
END + CASE
WHEN int_month_week_day = 0
THEN 'Day '
WHEN int_month_week_day = 1
THEN 'Sunday '
WHEN int_month_week_day = 2
THEN 'Monday '
WHEN int_month_week_day = 3
THEN 'Tuesday '
WHEN int_month_week_day = 4
THEN 'Wednesday '
WHEN int_month_week_day = 5
THEN 'Thursday '
WHEN int_month_week_day = 6
THEN 'Friday '
WHEN int_month_week_day = 7
THEN 'Saturday '
ELSE ' '
END + 'of every ' + CASE
WHEN Cls.int_day_monthly_count > 1
THEN CAST(Cls.int_day_monthly_count AS VARCHAR(10)) + ' ' + 'Months '
ELSE 'Month '
END
ELSE ' '
END
WHEN rp.str_reocurring_pattern = 'Online 24/7'
THEN 'Online 24/7'
END
WHEN chr_class_structure_type = 'F'
THEN substring(datename(dw, dat_start_date), 1, 3)
ELSE ''
END AS ClassDays
, FORMAT(dat_start_date, 'hh:mm tt') + ' - ' + FORMAT(dat_end_date, 'hh:mm tt') AS Times
, CASE
WHEN (Cls.int_location_id) >= 1
THEN '<a class="ActionLink" href="#" onclick="javascript:callPopupLocation(' + CHAR(39) + convert(VARCHAR(10), cls.int_location_id) + + CHAR(39) + ')">' + (
SELECT Replace(Replace(rtrim(City), '''', ''), '"', '') + ', ' + Replace(Replace(NAME, '''', ''), '"', '')
FROM Locations
WHERE LocationID = cls.int_location_id
) + '</a>' + ' <a class="ActionLink" href="index.cfm?method=ClassInfo.GoogleMap&int_location_id=' + CAST(Cls.int_location_id AS VARCHAR(10)) + '" target="_blank">Map</a>' + CASE
WHEN @UsesRoomMgmt = 1
THEN CASE
WHEN cls.int_room_id >= 1
AND @RoomBit = 1
THEN CASE
WHEN (
SELECT Replace(Replace(rtrim(NAME), '''', ''), '"', '')
FROM Rooms
WHERE LocationID = cls.int_location_id
AND RoomID = cls.int_room_id
) != ''
THEN ', Room: ' + (
SELECT Replace(Replace(rtrim(NAME), '''', ''), '"', '')
FROM Rooms
WHERE LocationID = cls.int_location_id
AND RoomID = cls.int_room_id
)
ELSE ''
END
WHEN cls.str_room != ''
AND @RoomBit = 1
THEN ', Room: ' + cls.str_room
ELSE ''
END
WHEN cls.str_room != ''
AND @RoomBit = 1
THEN ', Room: ' + cls.str_room
ELSE ''
END
ELSE CASE
WHEN bfcv.bit_online_type = 1
THEN 'N/A - Online'
ELSE 'TBD'
END /**/
END AS Locations_tbl_Rcpt
, '' AS Locations_tbl_Rcpt_WORL
, cl.ClassID AS Class_ID
, cl.NAME AS NAME
, bfcv.field_value_description AS str_instructional_method
, int_instructional_method_id
, int_schedule_id
, dbo.fun_classWithSchedule_Instructor_display(cl.ClassID, cls.int_schedule_id, 'link') AS classWithSchedule_Instructor_displayNoTooltip
, dbo.fun_classWithSchedule_Instructor_display(cl.ClassID, cls.int_schedule_id, 'reciept') AS classWithSchedule_Instructor_displayLine
, (
SELECT STRING_AGG(I.FName + ' ' + I.LName, ', ') AS Instructor_Name
FROM Instructors I
INNER JOIN tbl_class_instructor tci ON tci.int_instructor_id = I.InstructorID
CROSS APPLY STRING_SPLIT(tci.str_schedule_id, ',') si
WHERE tci.int_class_id = @Classid
AND si.value = cls.int_schedule_id
) AS Instructor_Name
, int_contact_hrs
, int_number_of_sessions
, int_duration_weeks
, (
SELECT SUM(int_duration_weeks)
FROM tbl_classSchedules
WHERE ClassID = cls.ClassID
) AS NumberWeeks
, (
SELECT SUM(int_number_of_sessions)
FROM tbl_classSchedules
WHERE ClassID = cls.ClassID
) AS NumberSessions
, (
SELECT SUM(int_contact_hrs)
FROM tbl_classSchedules
WHERE ClassID = cls.ClassID
) AS contactHours
, cls.chr_class_structure_type
FROM tbl_ClassSchedules Cls
INNER JOIN Classes cl ON cl.ClassID = cls.ClassID
INNER JOIN Courses co ON co.CourseID = cl.CourseID
INNER JOIN LessonReleaseTimes lrt ON cl.LessonReleaseID = lrt.LessonReleaseID
INNER JOIN tbl_reoccuring_pattern rp ON rp.int_reoccuring_pattern_id = Cls.int_reoccuring_pattern_id
INNER JOIN base_field_customized_value bfcv
INNER JOIN base_field_customized bfc ON bfc.base_field_id = bfcv.base_field_id
ON bfcv.stored_field_value = CAST(Cls.int_instructional_method_id AS VARCHAR(10))
AND bfc.base_field = 'str_instructional_method'
WHERE 1 = 1
AND cls.ClassID = @ClassID
AND cls.int_schedule_id <> ''
AND cls.int_schedule_id IS NOT NULL
AND cl.ClassID = @ClassID
AND cls.STATUS = 'A'
ORDER BY dat_start_date;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
Query Parameter Value(s) -qNumberofSchedules (Datasource=v8Testing8, Time=0ms, Records=1) in E:\Lumens\Versions\v81\view\course\classinformation\dsp_class_information_template.cfm @ 01:09:53.053SELECT count(*) as schdulecount FROM qGetClassListing WHERE ClassId = 2621 GROUP BY ScheduleId qNumberofSchedules (Datasource=v8Testing8, Time=0ms, Records=1) in E:\Lumens\Versions\v81\view\course\classinformation\dsp_class_information_template.cfm @ 01:09:53.053SELECT count(*) as schdulecount FROM qGetClassListing WHERE ClassId = 2622 GROUP BY ScheduleId Scope Variables 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-3=_remove_; AWSALBAPP-0=AAAAAAAAAAApItCwzUfHdcD5DJpaIEAW55r/AgsnpHcq0wpGn74slcRXKLb0GTvhTDA5AVbeAxdO1yrmbMrD/0DoUNlZSPurkF7z1JPY0a9w+YcqIqLmUEGw6AKzM+Zrp3JD3AqLBuOXDQ==; AWSALBAPP-1=_remove_; LUMPERSIST=706EC4B093205F7584DF0286BFE7BF9D; JSESSIONID=7BD9450D6EA5323A40DE5953F1AF78F1.cfusion; AWSALBAPP-2=_remove_ HTTP_HOST=v8testing8.augusoft.net HTTP_REFERER= HTTP_URL=/index.cfm?int_class_id=2623&method=ClassInfo.ClassInformation 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=int_class_id=2623&method=ClassInfo.ClassInformation REMOTE_ADDR=172.31.102.171 REMOTE_HOST=172.31.102.171 REMOTE_USER= REQUEST_METHOD=GET SCRIPT_NAME=/index.cfm SERVER_NAME=v8testing8.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=AAAAAAAAAAApItCwzUfHdcD5DJpaIEAW55r/AgsnpHcq0wpGn74slcRXKLb0GTvhTDA5AVbeAxdO1yrmbMrD/0DoUNlZSPurkF7z1JPY0a9w YcqIqLmUEGw6AKzM Zrp3JD3AqLBuOXDQ AWSALBAPP-1=_remove_ AWSALBAPP-2=_remove_ AWSALBAPP-3=_remove_ JSESSIONID=7BD9450D6EA5323A40DE5953F1AF78F1.cfusion LUMPERSIST=706EC4B093205F7584DF0286BFE7BF9D Session Variables: actortypeid=90 addtoken=CFID=17896321&CFTOKEN=e1c02349ad74c0ed-F6705D9D-00E1-40D6-F9A4CD0126968432&jsessionid=7BD9450D6EA5323A40DE5953F1AF78F1.cfusion allowunmaskeddata=0 appname=v8Testing8 arrtrackflowregister=Array (17) basecfcs=versions.v81 basecode=/versions/v81 bit_canvas_failed_process=false bit_member=0 customcct=UA-78381026-1 customcfcs=Customers.v8Testing8 customerid=496 custompath=/Customers/v8Testing8/custom debug=0 debugoutput=1 docxdir=DocxHTML domain=v8testing8.augusoft.net ds=v8Testing8 enabledlivechat=false enabledsupportwidget=false enteredsiteusingipaddress=1 foundeo_firewall_ip=216.73.216.114 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=7BD9450D6EA5323A40DE5953F1AF78F1.cfusion sitepath=/Customers/v8Testing8 sslreturnurl=https://v8testing8.augusoft.net/ stylesheetpath=/Customers/v8Testing8/StyleSheets/style.css timeoutminutes=60 urltoken=CFID=17896323&CFTOKEN=208ab818f6a4df2f-F670E9FE-F7A4-3B34-3F14FD1192173107&jsessionid=7BD9450D6EA5323A40DE5953F1AF78F1.cfusion version=v81 URL Parameters: int_class_id=2623 method=ClassInfo.ClassInformationDebug Rendering Time: 15 ms |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
buy 
E:\Lumens\index.cfm