Debugging Information
Execution Time
SQL Queries checkAccessControlRules (Datasource=LumensCustomers, Time=0ms, Records=10) in E:\Lumens\Versions\v81QA\checkPermission.cfm @ 12:16:39.039 SELECT DISTINCT ACA.int_actor_type_id FROM tbl_access_control_rules ACR INNER JOIN tbl_access_control_allow ACA ON ACR.int_access_rule_id = ACA.int_access_rule_id WHERE str_fuse_action LIKE ? AND ISNULL(ACA.int_actor_type_id,'')<>'' AND ACA.bit_allow = 1 Query Parameter Value(s) - MenuQry (Datasource=v8Template3, Time=0ms, Records=10) in E:\Lumens\Versions\v81QA\Administration\SQL\MenuQry.cfm @ 12:16:39.039 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 Query Parameter Value(s) - qGetDashboardName (Datasource=v8Template3, Time=0ms, Records=1) in E:\Lumens\Versions\v81QA\Administration\SQL\MenuQry.cfm @ 12:16:39.039 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 = ? Query Parameter Value(s) - qLearnerfields (Datasource=v8Template3, Time=0ms, Records=95) in E:\Lumens\versions\v81QA\cfc\systemoptions\siteconfiguration\siteconfiguration.cfc @ 12:16:39.039 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 Query Parameter Value(s) - qsocialNetFieldDetails (Datasource=v8Template3, Time=16ms, Records=4) in E:\Lumens\versions\v81QA\cfc\systemoptions\siteconfiguration\siteconfiguration.cfc @ 12:16:39.039 select * from tbl_social_media_details WHERE int_region_id = ? order by int_order asc Query Parameter Value(s) - qGraphics (Datasource=v8Template3, Time=0ms, Records=1) in E:\Lumens\versions\v81QA\cfc\systemoptions\siteconfiguration\siteconfiguration.cfc @ 12:16:39.039 select * from UIVariables where RegionID = ? Query Parameter Value(s) - qUserNavItems (Datasource=v8Template3, Time=0ms, Records=0) in E:\Lumens\versions\v81QA\cfc\systemoptions\poweruser\poweruser.cfc @ 12:16:39.039 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 Query Parameter Value(s) - qFooterNavItems (Datasource=v8Template3, Time=0ms, Records=0) in E:\Lumens\versions\v81QA\cfc\systemoptions\poweruser\poweruser.cfc @ 12:16:39.039 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 Query Parameter Value(s) - qLinkedSites (Datasource=v8Template3, Time=0ms, Records=0) in E:\Lumens\Application.cfc @ 12:16:39.039 DECLARE @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=v8Template3, Time=16ms, Records=1) in E:\Lumens\Versions\v81QA\header.cfm @ 12:16:39.039 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 qTempMenu (Datasource=, Time=0ms, Records=7) in E:\Lumens\Versions\v81QA\vendor\site_templates\shared\includes\menu_action.cfm @ 12:16:39.039 select * from qTempMenu where MenuItemID NOT IN (544,615,2) qGetDonateUserNavLabel (Datasource=v8Template3, Time=0ms, Records=1) in E:\Lumens\Versions\v81QA\header.cfm @ 12:16:39.039 SELECT isNull(str_user_nav_label,'') AS str_menu_label FROM tbl_user_nav_menu_label WHERE int_actor_type_id = ? AND str_menu_label LIKE 'DONATE' AND str_display_condition='application.appvars[ri].bit_allow_student_donation EQ 1 AND application.appvars[ri].bit_display_donate_in_navmenu EQ 1'; Query Parameter Value(s) - qSocialMediaHeader (Datasource=, Time=0ms, Records=4) in E:\Lumens\Versions\v81QA\vendor\site_templates\shared\includes\nav-social-media.cfm @ 12:16:39.039 select * from qSocialMedia order by int_order desc qTempCourseMenu (Datasource=, Time=0ms, Records=1) in E:\Lumens\Versions\v81QA\vendor\site_templates\template3\includes\nav-main.cfm @ 12:16:39.039 select * 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\v81QA\vendor\site_templates\template3\includes\nav-main.cfm @ 12:16:39.039 select * 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\v81QA\vendor\site_templates\template3\includes\nav-main.cfm @ 12:16:39.039 select * from qMenu where int_parent_id = 515 qTempMenu (Datasource=, Time=0ms, Records=0) in E:\Lumens\Versions\v81QA\vendor\site_templates\template3\includes\nav-main.cfm @ 12:16:39.039 select * from qMenu where int_parent_id = 521 qTempMenu (Datasource=, Time=0ms, Records=0) in E:\Lumens\Versions\v81QA\vendor\site_templates\template3\includes\nav-main.cfm @ 12:16:39.039 select * from qMenu where int_parent_id = 522 qTempMenu (Datasource=, Time=0ms, Records=0) in E:\Lumens\Versions\v81QA\vendor\site_templates\template3\includes\nav-main.cfm @ 12:16:39.039 select * from qMenu where int_parent_id = 5 qTempMenu (Datasource=, Time=0ms, Records=0) in E:\Lumens\Versions\v81QA\vendor\site_templates\template3\includes\nav-main.cfm @ 12:16:39.039 select * from qMenu where int_parent_id = 6 qTempMenu (Datasource=, Time=0ms, Records=0) in E:\Lumens\Versions\v81QA\vendor\site_templates\template3\includes\nav-main.cfm @ 12:16:39.039 select * from qMenu where int_parent_id = 7 qTempMenu (Datasource=, Time=0ms, Records=0) in E:\Lumens\Versions\v81QA\vendor\site_templates\template3\includes\nav-main.cfm @ 12:16:39.039 select * from qMenu where int_parent_id = 458 qGetOrganisationPhoneQry (Datasource=v8Template3, Time=0ms, Records=1) in E:\Lumens\Versions\v81QA\model\course\classlisting\act_class_listing.cfm @ 12:16:39.039 SET NOCOUNT ON SELECT rg.Phone1 ,rg.Phone1Ext ,rg.email FROM Regions rg WHERE rg.RegionID = ? AND rg.STATUS = 'A' Query Parameter Value(s) - qGetSectionNumberLabel (Datasource=v8Template3, Time=0ms, Records=0) in E:\Lumens\Versions\v81QA\model\course\classlisting\act_class_listing.cfm @ 12:16:39.039 SELECT 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 FeatureConfigurationText (Datasource=v8Template3, Time=16ms, Records=1) in E:\Lumens\Versions\v81QA\model\course\classlisting\act_class_listing.cfm @ 12:16:39.039 SELECT 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' qGetCatSubCatByClassID (Datasource=v8Template3, Time=0ms, Records=0) in E:\Lumens\Versions\v81QA\model\course\classlisting\act_class_listing.cfm @ 12:16:39.039 SELECT sc.CategoryId , sc.SubCategoryID FROM tbl_class_listing tcl WITH (NOLOCK) INNER JOIN Subcategories sc WITH (NOLOCK) ON sc.SubCategoryID = tcl.SubCategoryID WHERE tcl.classid = ?; Query Parameter Value(s) - rs_category_detail (Datasource=v8Template3, Time=15ms, Records=1) in E:\Lumens\Versions\v81QA\model\course\classlisting\act_class_listing.cfm @ 12:16:39.039 Select Name ,[dbo].[fun_strip_html](Name) as NameNoHTML from categories WITH (NOLOCK) where CategoryID = ? Query Parameter Value(s) - rs_sub_category_details (Datasource=v8Template3, Time=0ms, Records=1) in E:\Lumens\Versions\v81QA\model\course\classlisting\act_class_listing.cfm @ 12:16:39.039 SELECT NAME ,Description ,str_description_no_html AS DescriptionNoHTML ,Status FROM subcategories WITH (NOLOCK) WHERE SubCategoryID = ? AND CategoryID = ? Query Parameter Value(s) - rs_contents (Datasource=v8Template3, Time=16ms, Records=1) in E:\Lumens\Versions\v81QA\model\course\classlisting\act_class_listing.cfm @ 12:16:39.039 select contents from contents WITH (NOLOCK) where path= ? Query Parameter Value(s) - qRecommendedClass (Datasource=v8Template3, Time=113ms, Records=0) in E:\Lumens\versions\v81QA\cfc\class\recommended.cfc @ 12:16:39.039 /* 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 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) - qGetClassListingOrder (Datasource=v8Template3, Time=0ms, Records=1) in E:\Lumens\Versions\v81QA\model\course\classlisting\act_class_listing.cfm @ 12:16:39.039 select str_class_listing_order from tbl_class_listing_order qIsAllCatalogMeansAllClass (Datasource=v8Template3, Time=15ms, Records=1) in E:\Lumens\Versions\v81QA\model\course\classlisting\act_class_listing.cfm @ 12:16:39.039 SELECT COUNT(catalogid) AS IsAllCatalogMeansAllClass FROM CATALOGS WHERE CatalogID = 1 AND NAME = 'All Catalogs' qIsAllCatalogMeansAllClass (Datasource=v8Template3, Time=0ms, Records=1) in E:\Lumens\Versions\v81QA\model\course\classlisting\act_class_listing.cfm @ 12:16:39.039 SELECT COUNT(catalogid) AS IsAllCatalogMeansAllClass FROM CATALOGS WHERE CatalogID = 1 AND NAME = 'All Catalogs' qGetClassListing (Datasource=v8Template3, Time=520ms, Records=1) in E:\Lumens\Versions\v81QA\model\course\classlisting\act_class_listing.cfm @ 12:16:40.040 SET NOCOUNT ON SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED DECLARE @MembershipID AS INT ,@AllcatalogId AS INT/*TRACKER 37132 : SEP 24 2013 NEO 0126 */ ,@SearchKey VARCHAR(500) ,@SubCategoryID INT ,@StrSubCategoryID VARCHAR(20) ,@CategoryID INT ,@classtype INT ,@CourseName VARCHAR(255) ,@StartDate DATETIME ,@EndDate DATETIME ,@StartTimeHour INT ,@StartTimeMinute INT ,@EndTimeHour INT ,@EndTimeMinute INT ,@City VARCHAR(255) ,@ActorID INT ,@LearnerID INT ,@ClassID INT ,@InstructorID INT ,@CourseNumber VARCHAR(30) ,@LocationID INT ,@CourseSeriesID INT ,@containsearchKey VARCHAR(510) ,@bit_use_guest_checkout BIT , @currentTimeZoneOffsetDate DATETIME2 = ?; SET @LearnerID = ? SET @MembershipID = IsNull(( SELECT MembershipID FROM learners WHERE learnerid = @LearnerID ), 0) SET @AllcatalogId = ( SELECT catalogid FROM catalogs WHERE NAME = 'All Catalogs' ) /*TRACKER 37132 : SEP 24 2013 NEO 0126 */ SET @SubCategoryID = ?; SET @StrSubCategoryID = ?; SET @CategoryID = ?; SET @SearchKey = ?; SET @containsearchKey = '"'+replace(@SearchKey,'"','')+'*"'; SET @bit_use_guest_checkout = ?; /*new advanced search fields */ DECLARE @durationTypeID INT , @ceus decimal(5,2) , @ContactHours decimal(10,2) , @InstructionalMethodId INT , @ClassRoom VARCHAR(30) , @LMSID INT , @BadgeID INT , @BadgeTemplateID INT , @PublicRegistrationStarts DATETIME , @PublicRegistrationEnds DATETIME , @MinAge INT , @MaxAge INT , @LimitSelfRegistrationMembershipTypeID INT , @QtyRegistration BIT , @CountTowardsBenefitsMembershipTypeID BIT , @AllowMultipleRegistrations BIT , @RequiresRegFee BIT , @UsesLottery BIT , @LotteryRunDate DATE , @LotteryCloseDate DATE , @LotteryTypeID INT , @ClassUnlockKey VARCHAR(100) , @MinPrice DECIMAL(10,2) , @MaxPrice DECIMAL(10,2); /*new advanced search fields */ /* Table variables to populate the list of ID's to build the query from. This change improved performance by 40%+. These have the majority of the conditions AND are joined to each of the union sections. */ DECLARE @ClassIDTable1 TABLE ( ClassListingID INT NOT NULL , Rank INT , Rank2 INT );/* for first part of Union */ DECLARE @ClassIDTable2 TABLE ( ClassListingID INT NOT NULL , Rank INT , Rank2 INT );/* for second part of Union */ DECLARE @ClassIDTable3 TABLE ( ClassListingID INT NOT NULL , Rank INT , Rank2 INT );/* for third part of Union */ DECLARE @ClassIDTable4 TABLE ( ClassListingID INT NOT NULL , Rank INT , Rank2 INT );/* for fourth part of Union */ DECLARE @ClassIDTable5 TABLE ( ClassListingID INT NOT NULL , Rank INT , Rank2 INT );/* */ /**************************************************************************/ /******* Begin Populate ID's for first part of union for classes **********/ /**************************************************************************/ INSERT INTO @ClassIDTable1 SELECT DISTINCT ClassListingID ,0 AS Rank ,0 AS Rank2 FROM tbl_class_listing cl INNER JOIN Courses co ON co.CourseID = cl.CourseID WHERE 1 = 1 /* 7/7/12/2012 tracker 20200 */ /* end tracker 20200*/ AND ( Cl.SubCategoryID = @SubCategoryID OR CL.str_alt_subcategory_ids LIKE ''+@StrSubCategoryID+',%' OR CL.str_alt_subcategory_ids LIKE '%,'+@StrSubCategoryID+',%' OR CL.str_alt_subcategory_ids LIKE '%,'+@StrSubCategoryID+'' OR CL.str_alt_subcategory_ids = @StrSubCategoryID ) AND @currentTimeZoneOffsetDate BETWEEN cl.AppearanceStartDate AND CL.AppearanceEndDate /* BEGIN : 09 SEP 2014 NEO 00159 TRACKER 42510 */ /* END : 09 SEP 2014 NEO 00159 TRACKER 42510 */ AND (CL.bit_display_to_public = 1) /* Begin Dec 07 2012 AIMembership Enhancement UC 10 Neo 00110 */ AND ISNULL(cl.bit_use_guest_checkout,0) = @bit_use_guest_checkout /**/ AND ( @durationTypeID IS NULL OR cl.int_duration_type_id = @durationTypeID ) AND ( @ClassRoom IS NULL OR cl.ClassRoom LIKE '%'+@ClassRoom+'%' ) AND ( @PublicRegistrationStarts IS NULL OR cl.dat_public_registration_begin >= @PublicRegistrationStarts ) AND ( @PublicRegistrationEnds IS NULL OR cl.dat_public_registration_closes <= @PublicRegistrationEnds ) AND ( @MinAge IS NULL OR cl.MinAge >= @MinAge ) AND ( @MaxAge IS NULL OR cl.MaxAge <= @MaxAge ) AND ( @LimitSelfRegistrationMembershipTypeID IS NULL OR EXISTS ( SELECT 1 FROM STRING_SPLIT(cl.str_limit_self_reg_mbrship_type_ids, ',') WHERE value = @LimitSelfRegistrationMembershipTypeID ) ) AND ( @CountTowardsBenefitsMembershipTypeID IS NULL OR EXISTS ( SELECT 1 FROM STRING_SPLIT(cl.str_count_towards_membership_benefits, ',') WHERE value = @CountTowardsBenefitsMembershipTypeID ) ) AND ( @MinPrice IS NULL OR cl.Cost >= @MinPrice ) AND ( @MaxPrice IS NULL OR cl.Cost <= @MaxPrice ) AND ( @UsesLottery IS NULL OR cl.bit_uses_class_lottery = @UsesLottery ) AND ( @LotteryCloseDate IS NULL OR cl.dat_lottery_close_date = @LotteryCloseDate ) AND ( @LotteryRunDate IS NULL OR cl.dat_lottery_run_date = @LotteryRunDate ) AND ( @LotteryTypeID IS NULL OR cl.int_lottery_type_id = @LotteryTypeID ) AND ( @BadgeID IS NULL OR cl.int_badge_integration_id = @BadgeID ) AND ( @BadgeTemplateID IS NULL OR cl.int_badge_integration_template_id = @BadgeTemplateID ) AND ( @ceus IS NULL OR cl.CEUs = @ceus ) AND ( @ContactHours IS NULL OR cl.ContactHrs = @ContactHours ) AND ( @LMSID IS NULL OR cl.int_lms_integration_id = @LMSID ) AND ( @QtyRegistration IS NULL OR cl.AllowQtyPurchase = @QtyRegistration ) AND ( @AllowMultipleRegistrations IS NULL OR cl.bit_allow_multiple_registration = @AllowMultipleRegistrations ) AND ( @RequiresRegFee IS NULL OR cl.regFeeRequired = @RequiresRegFee ) /* these require joins to other tables and can result in peformance degradation */ AND ( @InstructionalMethodID IS NULL OR EXISTS ( SELECT 1 FROM tbl_ClassSchedules WHERE ClassID = cl.ClassID AND int_instructional_method_id = @InstructionalMethodID ) ) AND ( @ClassUnlockKey IS NULL OR EXISTS ( SELECT TOP 1 1 FROM tbl_restriction_code_detail rcd INNER JOIN tbl_table t ON rcd.int_table_id = t.int_table_id INNER JOIN tbl_restriction_code rc ON rc.int_restriction_code_id = rcd.int_restriction_code_id WHERE t.str_original_table_name = 'Classes' AND rcd.int_primary_key_id = cl.ClassID AND rc.str_restriction_code = @ClassUnlockKey ) ) /**/ /* don't delete if Class ID is the keyword because rank will be null */ IF ( SELECT Count(CourseID) FROM Courses WHERE CourseNumber = @SearchKey ) = 0 /* Delete null rank records if the keyword is NOT a course number */ BEGIN DELETE FROM @ClassIDTable1 WHERE ( rank IS NULL AND rank2 IS NULL /* PAV 14 Jul 2017 - changed from OR to AND to resolve issue where tenses of words were not returning correct records */ ) AND ( classlistingid NOT IN ( SELECT classlistingid FROM tbl_class_listing WHERE (ClassName LIKE '%'+@SearchKey+'%') OR (DescriptionNoHTML LIKE '%'+@SearchKey+'%') ) ) /* Tracker 42483,42624,42606 Sep 10 2014 Neo 0126 */ END /**************************************************************************/ /***** End Populate ID's for first part of union for classes **************/ /**************************************************************************/ /**************************************************************************/ /***** BEGIN Populate ID's for second part of union for course series *****/ /**************************************************************************/ /*00097 11/10/2011 Tracker 27177 */ INSERT INTO @ClassIDTable2 SELECT DISTINCT ClassListingID ,0 AS Rank ,0 AS Rank2 FROM tbl_class_listing cl INNER JOIN tbl_class_course_series_group ccsg ON ccsg.int_Series_group_id = CL.int_course_series_group_id INNER JOIN CourseSeriesGroups CSG ON CSG.SeriesGroupID = CL.int_course_series_group_id LEFT JOIN Courses co ON co.CourseID = cl.CourseID WHERE CL.str_class_type = 'CS' AND ( SELECT COUNT(c2.classid) FROM tbl_class_course_series_group cg2 INNER JOIN classes c2 ON c2.classid = cg2.int_class_id WHERE cg2.int_series_group_id = ccsg.int_series_group_id AND c2.STATUS NOT IN ('X') ) = ( SELECT COUNT(cg3.int_class_id) FROM tbl_class_course_series_group cg3 WHERE cg3.int_series_group_id = ccsg.int_series_group_id ) /*END 15 JUNE 2012 NEO V 0049 TRACKER 30029*/ AND ( SELECT count(cl1.classid) FROM tbl_class_course_series_group cg INNER JOIN classes cl1 ON cl1.classid = cg.int_class_id WHERE cg.int_series_group_id = ccsg.int_series_group_id AND cl1.STATUS IN ('A','C') ) > 0 AND cl.chr_status = 'A' /* Begin Tracker 27353 19 Dec 2011 */ /* End Tracker 27353 19 Dec 2011 */ /* BEGIN : 09 SEP 2014 NEO 00159 TRACKER 42510 */ /* END : 09 SEP 2014 NEO 00159 TRACKER 42510 */ AND ( Cl.SubCategoryID = @SubCategoryID OR CL.str_alt_subcategory_ids LIKE ''+@StrSubCategoryID+',%' OR CL.str_alt_subcategory_ids LIKE '%,'+@StrSubCategoryID+',%' OR CL.str_alt_subcategory_ids LIKE '%,'+@StrSubCategoryID+'' OR CL.str_alt_subcategory_ids = @StrSubCategoryID ) /* 7/7/12/2012 tracker 20200 */ /* end tracker 20200*/ /* BEGIN PAV 7 Jan 2013 allow search for single course series */ /* END PAV 7 Jan 2013 allow search for single course series */ /* Begin: 00097 11232011 Tracker: 27171 */ AND /* BEGIN 09 MAY 2014 NEO 0088 TRACKER 40819 */ @currentTimeZoneOffsetDate BETWEEN cl.AppearanceStartDate AND CL.AppearanceEndDate /* END 09 MAY 2014 NEO 0088 TRACKER 40819 */ /* End: 00097 11232011 Tracker: 27171 */ /* Tracker 40369 Jan 16 2015 Neo 00159 */ AND ( SELECT count(clR.classid) FROM tbl_class_course_series_group cgR INNER JOIN classes clR ON clR.classid = cgR.int_class_id WHERE cgR.int_series_group_id = ccsg.int_series_group_id AND clR.STATUS IN ('R') ) = 0 AND ISNULL(cl.bit_use_guest_checkout,0) = @bit_use_guest_checkout /**/ AND ( @durationTypeID IS NULL OR cl.int_duration_type_id = @durationTypeID ) AND ( @ClassRoom IS NULL OR cl.ClassRoom LIKE '%'+@ClassRoom+'%' ) AND ( @PublicRegistrationStarts IS NULL OR cl.dat_public_registration_begin >= @PublicRegistrationStarts ) AND ( @PublicRegistrationEnds IS NULL OR cl.dat_public_registration_closes <= @PublicRegistrationEnds ) AND ( @MinAge IS NULL OR cl.MinAge >= @MinAge ) AND ( @MaxAge IS NULL OR cl.MaxAge <= @MaxAge ) AND ( @LimitSelfRegistrationMembershipTypeID IS NULL OR EXISTS ( SELECT 1 FROM STRING_SPLIT(cl.str_limit_self_reg_mbrship_type_ids, ',') WHERE value = @LimitSelfRegistrationMembershipTypeID ) ) AND ( @CountTowardsBenefitsMembershipTypeID IS NULL OR EXISTS ( SELECT 1 FROM STRING_SPLIT(cl.str_count_towards_membership_benefits, ',') WHERE value = @CountTowardsBenefitsMembershipTypeID ) ) AND ( @MinPrice IS NULL OR cl.Cost >= @MinPrice ) AND ( @MaxPrice IS NULL OR cl.Cost <= @MaxPrice ) AND ( @UsesLottery IS NULL OR cl.bit_uses_class_lottery = @UsesLottery ) AND ( @LotteryCloseDate IS NULL OR cl.dat_lottery_close_date = @LotteryCloseDate ) AND ( @LotteryRunDate IS NULL OR cl.dat_lottery_run_date = @LotteryRunDate ) AND ( @LotteryTypeID IS NULL OR cl.int_lottery_type_id = @LotteryTypeID ) AND ( @BadgeID IS NULL OR cl.int_badge_integration_id = @BadgeID ) AND ( @BadgeTemplateID IS NULL OR cl.int_badge_integration_template_id = @BadgeTemplateID ) AND 1 = CASE WHEN @Ceus IS NULL THEN 1 ELSE 0 END AND 1 = CASE WHEN @ContactHours IS NULL THEN 1 ELSE 0 END AND 1 = CASE WHEN @LMSID IS NULL THEN 1 ELSE 0 END AND 1 = CASE WHEN @QtyRegistration IS NULL THEN 1 ELSE 0 END AND 1 = CASE WHEN @AllowMultipleRegistrations IS NULL THEN 1 ELSE 0 END AND 1 = CASE WHEN @RequiresRegFee IS NULL THEN 1 ELSE 0 END AND 1 = CASE WHEN @InstructionalMethodID IS NULL THEN 1 ELSE 0 END AND 1 = CASE WHEN @ClassUnlockKey IS NULL THEN 1 ELSE 0 END /**/ /* don't delete if Class ID is the keyword because rank will be null */ IF ( SELECT Count(CourseID) FROM Courses WHERE CourseNumber = @SearchKey ) = 0 /* Delete null rank records if the keyword is NOT a course number */ BEGIN DELETE FROM @ClassIDTable2 WHERE (rank IS NULL AND rank2 IS NULL) END /**************************************************************************/ /***** END Populate ID's for second part of union for course series *******/ /**************************************************************************/ /**************************************************************************/ /***** BEGIN Populate ID's for third part of union for certificates *****/ /**************************************************************************/ /**************************************************************************/ /***** END Populate ID's for third part of union for certificates *******/ /**************************************************************************/ /*****************************************************************************************/ /***** Begin Populate ID's for fourth part of union for certificate program sections *****/ /*****************************************************************************************/ /******************************************************************************************/ /***** END Populate ID's for fourth part of union for certificates program sections *******/ /******************************************************************************************/ /********************************************************************************/ /***** BEGIN Populate ID's for fifth part of union for certificate groups *****/ /********************************************************************************/ /********************************************************************************/ /***** END Populate ID's for fifth part of union for certificate groups *****/ /********************************************************************************/ /**************************************************************************************************/ /******************** Begin select from first part of union for classes ***************************/ /**************************************************************************************************/ SELECT DISTINCT 0 AS Rank ,0 AS Rank2 /* MODIFY NEO 00106 Flexible Schedules 06 SEP 2013 */ ,str_class_schedule_display_student_login AS str_class_schedule_display ,showLocation AS showLocation ,showCity AS showCity ,showRoom AS showRoom ,showCityLocation AS showCityLocation ,CASE WHEN cl.bit_disp_instr_method_to_public = 1 THEN instructionMethod ELSE '' END AS InstructionalMethod ,DaysOfWeek AS DaysOfWeek ,Instructors AS Instructor ,CASE WHEN co.ExternalCourseProviderID = 1 THEN '' ELSE CL.URLText END AS CourseURLText ,CASE WHEN co.ExternalCourseProviderID = 1 THEN '' ELSE CL.ClassURL END AS ClassURL ,CASE WHEN co.ExternalCourseProviderID = 1 THEN '' ELSE CL.ClassURlText END AS ClassURlText /* MODIFY NEO 00106 Flexible Schedules 06 SEP 2013 */ ,c.AllowQtyPurchase AS AllowQtyPurchase /* NEO 00083 Membership enhancement UC 10 SS2 */ ,c.RequiresReleaseForm AS RequiresReleaseForm ,'' AS int_age_limit /*Added Jan 17 2013 AI Membership Enhancement UC 10 Neo 00110*/ ,CASE WHEN csj.int_series_group_id IS NULL THEN 0 ELSE 1 END AS InCourseSeries /* JS 10/29/2009 tracker 16972 */ /*- 2/8/2012 tracker 28153 -*/ ,IsNull(( SELECT count(registrationid) FROM registrations WHERE classid = cl.classid AND iscancelled = 0 AND int_series_group_id > 0 ), 0) AS SeriesRegs ,IsNull(( SELECT count(registrationID) FROM registrations WHERE classID = cl.classID AND Iscancelled = 0 ), 0) AS TotalRegs ,IsNull(( SELECT sum(int_reserved_seats) FROM tbl_class_course_series_group WHERE int_class_ID = cl.classID ), 0) AS ReservedSeats /* End tracker 28153 */ /*Author :00097 date: 26 Aug 2011 Task: Certificate Issue Begin*/ ,CL.DescriptionNoHTML AS description ,CL.description AS descriptionFull /*Author :00097 date: 26 Aug 2011 Task: Certificate Issue End*/ ,( CL.TotalSeats - ( SELECT isnull(SUM(ccsg1.int_reserved_seats), 0) FROM tbl_class_course_series_group ccsg1 WHERE ccsg1.int_class_id = CL.ClassID ) ) AS ClassTotalSeats /*00097 01/27/2012 Tracker:27948*/ ,CL.ClassListingID ,CL.ClassID ,CL.CourseID ,CL.SubCategoryID ,CL.CourseNumber ,CL.int_delivery_type_id /* Added Neo 00049 22nd Jan 2013 Flexible schedules II F-05 */ ,CL.CourseTitle ,CL.NAME , CASE WHEN co.ExternalCourseProviderID = 1 THEN '' ELSE CL.CourseURL END AS CourseURL , CASE WHEN co.ExternalCourseProviderID = 1 THEN '' ELSE CL.URLText END AS URLText ,CL.IsOnlineCourse ,CL.Keywords ,CL.ClassName ,CL.CatalogID ,CL.SectionNumber ,CL.classStartDate ,CL.classEndDate ,CL.StartDate ,CL.StartDateOrder ,CL.EndDate ,CL.EndDateOrder ,CL.AppearanceStartDate ,CL.AppearanceEndDate ,CL.HotPixStartDate ,CL.HotPixEndDate ,CL.dat_public_registration_begin ,CL.dat_public_registration_closes ,CL.dat_staff_registration_begin ,CL.str_alt_subcategory_ids ,CL.IsFeaturedSpeaker ,CL.ClassRating ,CL.ClassRatingCount ,CL.NumberSessions ,CL.NumberWeeks ,CL.DaysOfWeek ,CL.Cost ,CL.CostNoMaterialsFee ,CL.MaterialsCost ,CL.LocationID ,CASE WHEN co.ExternalCourseProviderID = 1 THEN '' ELSE CL.Location END AS Location ,CL.City ,CL.ClassRoom ,CL.SpecialNotice ,CL.TotalSeats ,CL.InternalComments ,CL.Comments ,CL.PublicRegistration ,CL.contactPhone /*,CL.InCourseSeries*/ ,CL.LessonReleaseTimes ,CL.NumberofSessions ,CL.NumberofWeeks ,CL.StartDate1 ,CL.EndDate1 ,CL.CourseLogoPath ,CL.AltCourseLogoPath ,CL.bit_display_course_logo ,CL.TimeZoneHourAdjustment ,CL.MinAge ,CL.MaxAge ,CL.str_class_time_display ,CL.int_certificate_id ,CL.str_class_icon ,co.coursegraphic ,sc.str_subCat_graphic ,ca.str_Cat_graphic ,c.str_class_graphic_filename ,CL.str_class_icon_alt_descr ,csj.int_series_group_id int_course_series_group_id ,CL.int_course_series_id ,CL.str_class_type ,CL.chr_status ,CL.int_duration_type_id ,CL.int_quantity ,CL.bit_display_to_public ,CL.dat_publish_date ,CL.GoNumber AS GoNumber ,CL.int_program_section_id ,CL.str_count_towards_membership_benefits ,CL.StaffDisplayStartDate ,CL.StaffDisplayEndDate ,convert(DATETIME, convert(VARCHAR, cl.classStartDate, 101) + ' ' + IsNull(cl.starttime1, '0:00'), 121) AS ClStartDate /*30 MAR 2012 NEO 00043 TRACKER 28840*/ ,convert(DATETIME, convert(VARCHAR, cl.classEndDate, 101) + ' ' + cl.endtime1, 121) AS ClEndDate /*30 MAR 2012 NEO 00043 TRACKER 28840*/ ,cl.CourseTitle AS NAME ,c.dat_staff_registration_begin AS dat_staff_registration_begin ,co.CourseNumber AS CourseNo ,co.int_schedule_type_id AS ScheduleType ,CL.int_duration_type_id AS DurationType ,CL.int_quantity ,CL.int_time_period_id ,( SELECT str_description FROM tbl_time_periods WHERE int_time_period_id = CL.int_time_period_id ) AS TimeDescription ,(SELECT count(R.RegistrationID) FROM registrations R WHERE R.classID = CL.classID AND R.IsCancelled = 0) AS registrationCount ,c.TotalSeats - (SELECT count(registrationid) from registrations where classid = c.classid and iscancelled = 0) as remainingseats ,( SELECT count(R.RegistrationID) FROM registrations R WHERE R.classID = CL.classID AND R.IsCancelled = 0 AND ( R.int_series_group_id = 0 OR R.int_series_group_id IS NULL ) ) AS FilledSeats /*00097 01/27/2012 Tracker:27948*/ ,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 /* 10 July 2012 Poornima Added a check to see if the registration is a quantity registration */ WHEN EXISTS ( SELECT RegistrationID FROM Registrations R WHERE R.classid = cl.classid AND R.LearnerID = @LearnerID AND R.IsCancelled = 0 AND IsNull(R.bit_qty_reg_additional, 0) = 0 ) THEN 1 ELSE 0 END AS bit_enrolled /*Added 06 AUG 2012 NEO 00049 Rochester Community Schools Register for a Class Multiple Times */ ,c.bit_allow_multiple_registration ,CASE WHEN EXISTS ( SELECT w.WaitListID FROM WaitingLists w INNER JOIN WaitingListEntries we ON w.waitlistid = we.waitlistid WHERE w.classid = cl.classid AND we.LearnerID = @LearnerID ) THEN 1 ELSE 0 END AS bit_on_waiting_list /* Author : Neo 00007 Date : 13 Oct 2010 Function : For Tracker 20377 - Collect whether the class is in cart for that learner. */ /* Aswin 20 Feb 2015 updated the incart logic*/ ,( SELECT CASE WHEN ( SELECT COUNT(DISTINCT int_learner_id) FROM tbl_cart_class_detail tcd INNER JOIN classes ON classes.classid = tcd.int_class_id WHERE tcd.int_class_id = Cl.ClassID AND classes.bit_allow_multiple_registration = 0 /* some customers use household as group having large number of members */ AND int_learner_id IN (?) ) = ? THEN 1 ELSE 0 END ) AS learnerAlreadyInCart /* 00097 NLUAT-3256 14 Aug 2012 */ /* Aswin 20 Feb 2015 updated the incart logic*/ ,'' AS typeName ,'' AS Email ,'' AS FName ,'' AS LName ,'' AS ClassesToComplete ,'' AS classCount ,0 AS sortorder ,'' AS bitDisplay ,'' AS bitRegistration /* END For Tracker 20377 - Collect whether the class is in cart for that learner.-*/ ,'' AS int_certificate_id /* Added July 11 2012 Tracker 30411 Neo 00110*/ ,'' AS bit_multiple_elective_groups /* Added July 11 2012 Tracker 30411 Neo 00110*/ /* Begin Author 00097 : Neo AI Course Series change 19 July 2011*/ , ( SELECT str_course_series_type_name FROM tbl_course_series_type WHERE int_course_series_type_id = csj.int_course_series_type_id ) AS CourseSeriesType ,0 AS SeriesGroupMatFeeAdv /* End Author 00097 : Neo AI Course Series Use Case :19 July 2011*/ /* Neo D 00045 AI Certificates Added Features 2011-10-14 Feature Change 1 Date:Nov 17,2011*/ ,( SELECT sum(ISNULL(int_reserved_seats, 0)) FROM tbl_certificates_classes WHERE int_class_id = c.ClassID ) AS int_certificate_reserved_seats ,( SELECT sum(ISNULL(int_remaining_seats, 0)) FROM tbl_certificates_classes WHERE int_class_id = c.ClassID ) AS int_certificate_remaining_seats ,( SELECT sum(ISNULL(int_reserved_seats, 0)) FROM tbl_class_course_series_group tccsg1 INNER JOIN courseseries cs1 ON tccsg1.int_course_series_id = cs1.courseseriesid WHERE int_class_id = c.ClassID AND STATUS <> 'I' ) AS int_courseseries_reserved_seats /*Tracker 38702 jan 14 2014 Neo 0126 */ ,( SELECT sum(ISNULL(int_remaining_seats, 0)) FROM tbl_class_course_series_group tccsg1 INNER JOIN courseseries cs1 ON tccsg1.int_course_series_id = cs1.courseseriesid WHERE int_class_id = c.ClassID AND STATUS <> 'I' ) AS int_courseseries_remaining_seats /*Tracker 38702 jan 14 2014 Neo 0126 */ /* End AI Certificates Added Features 2011-10-14 Feature Change 1 */ ,IsNull(( SELECT count(registrationid) FROM registrations WHERE classid = c.classid AND iscancelled = 0 AND int_certificate_id > 0 ), 0) AS int_certificate_regs /* Added Feb 20 2012 Fun: Tracker 28377,28043 Neo s 00045 */ /* BEGIN PAV 2 May 2012 Class listing grouping */ ,co.ExternalCourseProviderID /* END PAV 2 May 2012 Class listing grouping */ ,CASE WHEN ( LEN(cl.minage) > 0 AND LEN(cl.maxage) > 0 ) THEN convert(VARCHAR, cl.minage) + ' - ' + convert(VARCHAR, cl.maxage) ELSE '' END AS ClassAges /* BEGIN: NEO 00091 Date:Sep 20,2012 AI V7.3 Full-Time Certificate Use Cases 2012-08-03 UC-13 */ ,'' AS UseProgramSection ,'' AS ProgramSectionStartDate ,'' AS ProgramSectionEndDate ,'' AS isAllProgramSectionClassesAvailable ,'' AS bit_use_prerequisite ,'' AS isProgramSectionSeatsAvailable /* END: NEO 00091 Date:Sep 20,2012 AI V7.3 Full-Time Certificate Use Cases 2012-08-03 UC-13 */ /* Begin Dec 07 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 LEFT JOIN Classes c 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 LEFT JOIN Classes c 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 07 2012 AIMembership Enhancement UC 10 Neo 00110 */ ,CASE WHEN c.contactPhone IS NULL THEN co.contactPhone ELSE c.contactPhone END AS contactphonenumber ,c.ContactHrs ,c.CEUs ,c.flt_PLUs /*Modify NEO 00187 03 JUN 2015 PLU&Registration Transfer UC-04*/ /* Begin NEO 00049 Frederick Discount Price Display 12th Feb 2013 */ ,stuff(( SELECT ',' + CAST(cf.NAME AS VARCHAR(100)) + '||' + CAST(CAST(cl.CostNoMaterialsFee - clf.DiscountAmount_1 AS money) AS VARCHAR(100)) FROM ClassFees clf LEFT JOIN CourseFees cf ON clf.CourseFeeID = cf.CourseFeeID WHERE ClassID = cl.classID AND clf.STATUS = 'A' AND cf.NAME > '' FOR XML path('') ), 1, 1, '') optionalAttributeDiscount /* End NEO 00049 Frederick Discount Price Display 12th Feb 2013 */ ,tcdt.str_delivery_type /* Begin NEO 00088 AI VCCS Modifications Group 3 09 April 2014 */ ,ISNULL(c.QualityScore, 0) AS QualityScore ,ISNULL(( SELECT int_quality_score_upper_limit FROM featureconfiguration WHERE regionid = 1 ), 1) AS QualityScoreUpperLimit ,ISNULL(( SELECT SUM(QualityScore) FROM Classes WHERE courseid = c.CourseID ), 1) AS QualityScoreSum ,( SELECT COUNT(ClassID) FROM Classes WHERE courseid = c.CourseID AND ISNULL(QualityScore, 0) > 0 ) AS ClassesWithQualityScore /* End NEO 00088 AI VCCS Modifications Group 3 09 April 2014 */ ,1 AS bit_disp_cs_to_public /* 30 JUL 2014 NEO 00159 TRACKER 39672 */ /* Begin NEO 00164 1st DEC 2014 OLLI Lottery UC-08 */ /* End NEO 00164 1st DEC 2014 OLLI Lottery UC-08 */ ,( SELECT TOP 1 LTrim(FName) + ',' + LName AS InstructorName FROM tbl_class_instructor INNER JOIN instructors ON instructors.instructorid = tbl_class_instructor.int_instructor_id WHERE tbl_class_instructor.int_class_id = c.classid ORDER BY InstructorName ) AS str_instructorname /*extra cols for QofQ sorting*/ , lower(cl.ClassName) as ClassNameSort , lower(CL.Location) as LocationSort , lower(CL.CourseNumber) as CourseNumberSort , lower(( SELECT TOP 1 CASE WHEN LEN(LTrim(RTrim(CatalogName))) > 0 THEN CatalogName ELSE LTrim(FName) + ',' + LName END AS InstructorName FROM tbl_class_instructor INNER JOIN instructors ON instructors.instructorid = tbl_class_instructor.int_instructor_id WHERE tbl_class_instructor.int_class_id = c.classid ORDER BY InstructorName )) AS InstructorNameSort /*extra cols for QofQ sorting*/ , cl.dat_public_registration_begin AS TermPublicRegistrationBegin , c.TermID , NULL str_series_icon , NULL str_series_icon_alt_descr , 0 AS AvailableItemCount , cl.bit_allow_wcg_registration , CL.bit_allow_waiting_lists , CL.str_default_text_for_call_message_class ,CL.int_certificate_group_id , 0 AS bit_use_class_prerequisites FROM @ClassIDTable1 CT1 INNER JOIN tbl_class_listing CL ON CT1.ClassListingID = CL.ClassListingID /* PAV Tracker 25230 7/26/2011 added for full-text change */ /* PAV Tracker 26142 9/15/2011 altered to LEFT to accomodate using keyword field for ClassID - plus added use of FeeTextTable in AND criteria below */ INNER JOIN classes c ON cl.classid = c.classid INNER JOIN Courses co ON co.CourseID = c.CourseID 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 /* LEFT JOIN(SELECT int_time_period_id as TimePeriod from tbl_time_periods) tp on cd.int_time_period_id= tp.TimePeriod */ LEFT JOIN ( SELECT s.int_class_id , min(s.int_series_group_id) int_series_group_id , MIN(ISNULL(cs.int_course_series_type_id, 3)) int_course_series_type_id /*default is series?*/ FROM tbl_class_course_series_group s INNER JOIN courseseries cs ON s.int_course_series_id = cs.courseseriesid INNER JOIN courseseriesgroups csg ON s.int_Series_group_id = csg.SeriesGroupID WHERE cs.STATUS = 'A' AND csg.STATUS = 'A' AND ( SELECT COUNT(ClassID) FROM Classes WHERE classid IN ( SELECT int_class_id FROM tbl_class_course_series_group WHERE int_Series_group_id = s.int_Series_group_id ) AND Classes.STATUS = 'X' ) = 0 GROUP BY s.int_class_id ) csj ON csj.int_class_id = cl.classid /* JS 10/29/2009 tracker 16372 Tracker 42084 Jul 24 2014 neo 00126 */ /* PAV 6 Feb 2012 added next 2 joins so a URL can retrieve an entire category - Tracker 28161 */ LEFT JOIN SubCategories sc ON sc.SubCategoryID = cl.SubCategoryID LEFT JOIN Categories ca ON ca.CategoryID = sc.CategoryID LEFT JOIN tbl_course_delivery_types tcdt ON tcdt.int_delivery_type_id = cl.int_delivery_type_id /* 01 JULY 2014 NEO 00088 TRACKER 41851 */ WHERE 1 = 1 /* PAV Tracker 26142 9/15/2011 moved to here to accomodate using keyword field for ClassID - when a join doesn't work */ AND c.status IN ('A','C')/* JS 11/15/2009 Tracker 16558 */ /* Below condition for checking whether user can register on the basis of comparison with display start on AND display end on date for open-ended schedule type */ AND @currentTimeZoneOffsetDate BETWEEN cl.AppearanceStartDate AND CL.AppearanceEndDate /* Below condition for checking whether students can register on basis of comparison with public registration begins AND public registration closes */ AND c.IsContractTraining = 0/* TRACKER 36774 AUG 20 2013 NEO 0126*/ /* Begin:Tracker 39813 Apr 14 2014 Neo 0126 */ /* End:Tracker 39813 Apr 14 2014 Neo 0126 */ /* End Dec 07 2012 AIMembership Enhancement UC 10 Neo 00110 */ /**************************************************************************************************/ /******************** End select from first part of union for classes *****************************/ /**************************************************************************************************/ /**************************************************************************************************/ /******************** Begin select from second part of union for course series ********************/ /**************************************************************************************************/ /*00097 11/10/2011 Tracker 27177 */ UNION /* gets Course Series */ SELECT DISTINCT 0 AS Rank ,0 AS Rank2 /* MODIFY NEO 00106 Flexible Schedules 06 SEP 2013 */ ,'' AS str_class_schedule_display ,'' AS showLocation ,'' AS showCity ,'' AS showRoom ,'' AS showCityLocation ,'' AS InstructionalMethod ,'' AS DaysOfWeek ,'' AS Instructor ,( SELECT URLText FROM Courses WHERE CourseID IN ( SELECT CourseID FROM Classes WHERE ClassID = cl.ClassID ) ) AS CourseURLText ,( SELECT str_url FROM tbl_class_detail WHERE int_class_id = cl.ClassID ) AS ClassURL ,( SELECT str_url_text FROM tbl_class_detail WHERE int_class_id = cl.ClassID ) AS ClassURlText , /* MODIFY NEO 00106 Flexible Schedules 06 SEP 2013 */ 0 AS AllowQtyPurchase ,/* NEO 00083 Membership enhancement UC 10 SS2 */ ISNULL(csg.bit_requires_release_form, 0) AS RequiresReleaseForm /* Tracker 44934 Feb 16 2015 Neo 0126 */ ,'' AS int_age_limit /*Added Jan 17 2013 AI Membership Enhancement UC 10 Neo 00110*/ ,1 AS InCourseSeries /* 2/8/2012 tracker 28153 */ ,'' AS SeriesRegs ,'' AS TotalRegs ,'' AS ReservedSeats /* End Tracker 28153 */ /*Author :00097 date: 26 Aug 2011 Task: Certificate Issue Begin*/ ,CL.DescriptionNoHTML AS description ,CL.description AS descriptionFull /*Author :00097 date: 26 Aug 2011 Task: Certificate Issue End*/ ,( CL.TotalSeats - ( SELECT isnull(SUM(ccsg1.int_reserved_seats), 0) FROM tbl_class_course_series_group ccsg1 WHERE ccsg1.int_class_id = CL.ClassID ) ) AS ClassTotalSeats /*00097 01/27/2012 Tracker:27948*/ ,CL.ClassListingID ,CL.ClassID ,CL.CourseID ,CL.SubCategoryID ,CL.CourseNumber ,CL.int_delivery_type_id /* Added Neo 00049 22nd Jan 2013 Flexible schedules II F-05 */ ,CL.CourseTitle ,CL.NAME ,CL.CourseURL ,CL.URLText ,CL.IsOnlineCourse ,CL.Keywords ,CL.ClassName ,CL.CatalogID ,CL.SectionNumber ,CL.classStartDate ,CL.classEndDate ,CL.StartDate ,CL.StartDateOrder ,CL.EndDate ,CL.EndDateOrder ,CL.AppearanceStartDate ,CL.AppearanceEndDate ,CL.HotPixStartDate ,CL.HotPixEndDate ,CL.dat_public_registration_begin ,CL.dat_public_registration_closes ,CL.dat_staff_registration_begin ,CL.str_alt_subcategory_ids ,CL.IsFeaturedSpeaker ,CL.ClassRating ,CL.ClassRatingCount ,CL.NumberSessions ,CL.NumberWeeks ,CL.DaysOfWeek ,CL.Cost ,CL.CostNoMaterialsFee ,CL.MaterialsCost ,CL.LocationID ,CL.Location ,CL.City ,CL.ClassRoom ,CL.SpecialNotice ,CL.TotalSeats ,CL.InternalComments ,CL.Comments ,CL.PublicRegistration ,CASE WHEN CL.contactPhone <> '' THEN CL.contactPhone ELSE ?/* Tracker 40369 Jan 16 2015 Neo 00159 */ END AS contactPhone ,CL.LessonReleaseTimes ,CL.NumberofSessions ,CL.NumberofWeeks ,CL.StartDate1 ,CL.EndDate1 ,CL.CourseLogoPath ,CL.AltCourseLogoPath ,CL.bit_display_course_logo ,CL.TimeZoneHourAdjustment ,CL.MinAge ,CL.MaxAge ,CL.str_class_time_display ,CL.int_certificate_id ,CL.str_class_icon ,'' AS coursegraphic ,sc.str_subCat_graphic ,ca.str_Cat_graphic ,cs.str_series_graphic AS str_class_graphic_filename ,cs.str_series_graphic_alt_descr AS str_class_icon_alt_descr ,CL.int_course_series_group_id ,CL.int_course_series_id ,CL.str_class_type ,CL.chr_status ,CL.int_duration_type_id ,CL.int_quantity ,CL.bit_display_to_public ,CL.dat_publish_date , NULL AS GoNumber ,CL.int_program_section_id ,CL.str_count_towards_membership_benefits ,CL.StaffDisplayStartDate ,CL.StaffDisplayEndDate ,convert(DATETIME, convert(VARCHAR, cl.classStartDate, 101) + ' ' + IsNull(cl.starttime1, '0:00'), 121) AS ClStartDate /*30 MAR 2012 NEO 00043 TRACKER 28840*/ ,convert(DATETIME, convert(VARCHAR, cl.classEndDate, 101) + ' ' + cl.endtime1, 121) AS ClEndDate /*30 MAR 2012 NEO 00043 TRACKER 28840*/ ,'' AS NAME ,'' AS dat_staff_registration_begin ,'' AS CourseNo ,0 AS ScheduleType ,0 AS DurationType ,0 AS int_quantity ,0 AS int_time_period_id ,'' AS TimeDescription ,0 AS registrationCount ,0 remainingseats ,0 AS FilledSeats ,WL.WaitingListCount AS waitingList ,0 bit_class_on_waiting_list ,0 AS isfull /* 09/29/2011 00097 Lisa_CourseSeriesTesting_2011-9-30 Begin*/ ,CASE /* 10 July 2012 Poornima Added a check to see if the registration is a quantity registration */ WHEN EXISTS ( SELECT RegistrationID FROM Registrations R WHERE R.int_series_group_id = CL.int_course_series_group_id AND R.LearnerID = @LearnerID AND R.IsCancelled = 0 AND IsNull(R.bit_qty_reg_additional, 0) = 0 ) THEN 1 ELSE 0 END AS bit_enrolled /*Added 06 AUG 2012 NEO 00049 Rochester Community Schools Register for a Class Multiple Times */ ,0 AS bit_allow_multiple_registration /* 09/29/2011 00097 Lisa_CourseSeriesTesting_2011-9-30 End*/ ,0 AS bit_on_waiting_list /* 0 as ageLimit, */ /* 09/29/2011 00097 For:Course Series begin*/ /* BEGIN: NEO 00091 AI Company Qty Reg Use Cases 2013-02-21 - F12 Date:18 July 2013 */ /* END: NEO 00091 AI Company Qty Reg Use Cases 2013-02-21 - F12 Date:18 July 2013 */ /* Aswin 20 Feb 2015 updated the incart logic*/ ,( SELECT CASE WHEN ( SELECT COUNT(DISTINCT ccd.int_learner_id) FROM tbl_cart_class_detail ccd WHERE /* some customers use household as group having large number of members */ ccd.int_learner_id IN (?) AND ccd.int_Series_group_id = ccsg.int_Series_group_id ) = ? THEN 1 ELSE 0 END ) AS learnerAlreadyInCart /* Aswin 20 Feb 2015 updated the incart logic*/ /* 09/29/2011 00097 For:Course Series End*/ ,'' AS typeName ,'' AS Email ,'' AS FName ,'' AS LName ,'' AS ClassesToComplete ,'' AS classCount ,1 AS sortorder ,'' AS bitDisplay ,'' AS bitRegistration ,'' AS int_certificate_id /*Added July 11 2012 Tracker 30411 Neo 00110*/ ,'' AS bit_multiple_elective_groups /*Added July 11 2012 Tracker 30411 Neo 00110*/ /* Begin Author 00097 : Neo AI Course Series change 19 July 2011*/ ,cst.str_course_series_type_name AS CourseSeriesType ,csg.SeriesGroupMatFeeAdv /* End Author 00097 : Neo AI Course Series Use Case :19 July 2011*/ /* Neo D 00045 AI Certificates Added Features 2011-10-14 Feature Change 1 Date:Nov 17,2011*/ ,NULL AS int_certificate_reserved_seats ,NULL AS int_certificate_remaining_seats ,NULL AS int_courseseries_reserved_seats ,NULL AS int_courseseries_remaining_seats /* End AI Certificates Added Features 2011-10-14 Feature Change 1 */ ,NULL AS int_certificate_regs /* Added Feb 20 2012 Fun: Tracker 28377,28043 Neo s 00045 */ /* BEGIN PAV 2 May 2012 Class listing grouping */ ,NULL AS ExternalCourseProviderID /* END PAV 2 May 2012 Class listing grouping */ ,'' AS ClassAges /* BEGIN: NEO 00091 Date:Sep 20,2012 AI V7.3 Full-Time Certificate Use Cases 2012-08-03 UC-13 */ ,'' AS UseProgramSection ,'' AS ProgramSectionStartDate ,'' AS ProgramSectionEndDate ,'' AS isAllProgramSectionClassesAvailable ,'' AS bit_use_prerequisite ,'' AS isProgramSectionSeatsAvailable /* END: NEO 00091 Date:Sep 20,2012 AI V7.3 Full-Time Certificate Use Cases 2012-08-03 UC-13 */ /* Begin Dec 07 2012 AIMembership Enhancement UC 10 Neo 00110 */ ,'' AS str_limit_self_reg_mbrship_type_ids ,'' AS bit_display_classes_members_only ,'' AS bit_allow_student_purchase_mbrship ,'' AS contactphonenumber /* End Dec 07 2012 AIMembership Enhancement UC 10 Neo 00110 */ ,0 AS ContactHrs ,0 AS CEUs ,0 AS flt_PLUs /*Modify NEO 00187 03 JUN 2015 PLU&Registration Transfer UC-04*/ ,'' AS optionalAttributeDiscount /* Added NEO 00049 Frederick Discount Price Display 12th Feb 2013 */ ,tcdt.str_delivery_type /* Begin NEO 00088 AI VCCS Modifications Group 3 09 April 2014 */ ,0 AS QualityScore ,0 AS QualityScoreUpperLimit ,0 AS QualityScoreSum ,0 AS ClassesWithQualityScore /* End NEO 00088 AI VCCS Modifications Group 3 09 April 2014 */ ,csg.bit_disp_cs_to_public /* 30 JUL 2014 NEO 00159 TRACKER 39672 */ /* Begin NEO 00164 1st DEC 2014 OLLI Lottery UC-08 */ /* End NEO 00164 1st DEC 2014 OLLI Lottery UC-08 */ ,'' AS str_instructorname /*extra cols for QofQ sorting*/ , lower(cl.ClassName) as ClassNameSort , lower(CL.Location) as LocationSort , lower(CL.CourseNumber) as CourseNumberSort , '' InstructorNameSort /*extra cols for QofQ sorting*/ , NULL AS TermPublicRegistrationBegin , NULL AS TermID , cs.str_series_icon , cs.str_series_icon_alt_descr , 0 AS AvailableItemCount /* change to series count */ , cl.bit_allow_wcg_registration , CL.bit_allow_waiting_lists , CSG.str_default_text_for_call_message_class ,CL.int_certificate_group_id , 0 AS bit_use_class_prerequisites FROM @ClassIDTable2 CT2 INNER JOIN tbl_class_listing CL ON CT2.ClassListingID = CL.ClassListingID INNER JOIN CourseSeriesGroups CSG ON CSG.SeriesGroupID = CL.int_course_series_group_id INNER JOIN tbl_course_series_type cst ON cst.int_course_series_type_id = CSG.int_course_series_type_id /* 09/29/2011 00097 For:Course Series begin*/ INNER JOIN tbl_class_course_series_group ccsg ON ccsg.int_Series_group_id = CL.int_course_series_group_id /* 09/29/2011 00097 For:Course Series End*/ /* PAV 6 Feb 2012 added next 2 joins so a URL can retrieve an entire category - Tracker 28161 */ LEFT JOIN SubCategories sc ON sc.SubCategoryID = cl.SubCategoryID LEFT JOIN Categories ca ON ca.CategoryID = sc.CategoryID INNER JOIN CourseSeries CS ON CS.CourseSeriesID = CSG.CourseSeriesID /* 04 APR 2013 NEO 00088 TRACKER 34397 */ LEFT JOIN tbl_course_delivery_types tcdt ON tcdt.int_delivery_type_id = cl.int_delivery_type_id /* 01 JULY 2014 NEO 00088 TRACKER 41851 */ LEFT JOIN (SELECT int_course_series_group_id, count(int_course_series_group_id) as WaitingListCount FROM waitingLists where int_course_series_group_id is not null group by int_course_series_group_id) wl on cl.int_course_series_group_id = wl.int_course_series_group_id WHERE 1 = 1 AND CS.Status = 'A'/* 04 APR 2013 NEO 00088 TRACKER 34397 */ /* NEO 00106 OCT 14 2014 Tracker 43197 */ AND CS.bit_disp_cs_to_public = 1 /* NEO 00106 OCT 14 2014 Tracker 43197 */ /* PAV 6 Feb 2012 added so a URL can retrieve an entire category - Tracker 28161 */ /* End Author G : Neo AI Course Series Use Case :06 28 June 2011*/ /**************************************************************************************************/ /******************** End select from second part of union for course series **********************/ /**************************************************************************************************/ /**************************************************************************************************/ /******************** Begin select from third part of union for Certificates **********************/ /**************************************************************************************************/ /*End Author: Neo 00098 Description : Certificates UC-16 , Date : 18 JUL 2011*/ /**************************************************************************************************/ /******************** End select from third of union for Certificates ****************************/ /**************************************************************************************************/ /**************************************************************************************************/ /******************** Begin select from fourth part of union for Program Sections *****************/ /**************************************************************************************************/ /**************************************************************************************************/ /******************** End select from fourth part of union for Program Sections *******************/ /**************************************************************************************************/ /********************************************************************************************************/ /******************** Begin select from fifth part of union for Certificate Groups **********************/ /********************************************************************************************************/ /*End Author: Neo 00098 Description : Certificates UC-16 , Date : 18 JUL 2011*/ /********************************************************************************************************/ /******************** Begin select from fifth part of union for Certificate Groups **********************/ /********************************************************************************************************/ /*Author: 00097 Date: 27 Aug 2011 Task: Certificate Issue Begin*/ ORDER BY CL.str_class_type Desc , ClassName,CLSTARTDATE,CourseNumber /*Author: 00097 Date: 27 Aug 2011 Task: Certificate Issue End*/ SET TRANSACTION ISOLATION LEVEL READ COMMITTED Query Parameter Value(s) - qGetClassListingPagination (Datasource=, Time=16ms, Records=1) in E:\Lumens\Versions\v81QA\model\course\classlisting\act_class_listing.cfm @ 12:16:40.040 SELECT 1 AS total_rec_count ,* FROM qGetClassListing WHERE str_class_type = 'cf' UNION SELECT 1 AS total_rec_count ,* FROM qGetClassListing WHERE str_class_type = 'ps' AND isProgramSectionSeatsAvailable = 1 UNION SELECT 1 AS total_rec_count ,* FROM qGetClassListing WHERE str_class_type = 'cs' AND bit_disp_cs_to_public = 1 UNION SELECT 1 AS total_rec_count ,* FROM qGetClassListing WHERE str_class_type NOT IN ( 'cf' ,'ps' ,'cs' ) qGetClassListingPagination2 (Datasource=, Time=0ms, Records=1) in E:\Lumens\Versions\v81QA\model\course\classlisting\act_class_listing.cfm @ 12:16:40.040 SELECT * FROM qGetClassListingPagination ORDER BY str_class_type DESC , ClassNameSort,ClStartDate,CourseNumberSort q (Datasource=v8Template3, Time=16ms, Records=1) in E:\Lumens\Versions\v81QA\model\course\classlisting\act_class_listing.cfm @ 12:16:40.040 DECLARE @currentTimeZoneOffsetDate AS DATETIME2 = ?; SELECT TermID , Code , NAME , STATUS FROM InstructionCalendar WHERE 1 = 1 AND Status IN ('A','F') AND CatalogStartDate < @currentTimeZoneOffsetDate AND CatalogEndDate > @currentTimeZoneOffsetDate ORDER BY CatalogStartDate Query Parameter Value(s) - qgetCertificateIdFromClassId (Datasource=v8Template3, Time=16ms, Records=0) in E:\Lumens\Versions\v81QA\model\course\classlisting\act_class_listing.cfm @ 12:16:40.040 SELECT DISTINCT tcc.int_certificate_id , tc.bit_use_program_sections , tcc.int_program_section_id , tcc.int_certificate_group_id FROM tbl_certificates_classes tcc WITH (NOLOCK) INNER JOIN tbl_certificates tc WITH (NOLOCK) ON tc.int_certificate_id = tcc.int_certificate_id AND tc.chr_status = 'p' LEFT JOIN ProgramSectionClasses PSC WITH (NOLOCK) ON PSC.ProgramSectionID = tcc.int_program_section_id AND PSC.ClassID = tcc.int_class_id AND PSC.ProgramSectionID IN ( SELECT int_program_section_id FROM tbl_class_listing WITH (NOLOCK) ) WHERE int_class_id = ? AND ( tc.bit_use_certificate_groups = 0 OR ( tc.bit_use_certificate_groups = 1 AND ( SELECT COUNT(tcg.int_certificate_group_id) FROM tbl_certificate_groups tcg WHERE tcg.int_certificate_id = tcc.int_certificate_id AND tcg.int_certificate_group_id = tcc.int_certificate_group_id AND tcg.Status = 'A' ) > 0 AND tcc.int_remaining_seats > 0 ) ) AND bit_display_to_public = 1 Query Parameter Value(s) - qCount (Datasource=v8Template3, Time=0ms, Records=1) in E:\Lumens\Versions\v81QA\model\course\classlisting\act_class_listing.cfm @ 12:16:40.040 SELECT Count(*) AS Counter FROM tbl_cart_class_detail WHERE SessionID = ? AND int_actor_type_id = 90 AND int_class_id = ? Query Parameter Value(s) - qgetClassMbrshipDetails (Datasource=v8Template3, Time=15ms, Records=0) in E:\Lumens\Versions\v81QA\model\course\classlisting\act_class_listing.cfm @ 12:16:40.040 SELECT 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) - qSocialMediaHeader (Datasource=, Time=0ms, Records=4) in E:\Lumens\Versions\v81QA\vendor\site_templates\shared\includes\nav-social-media.cfm @ 12:16:40.040 select * from qSocialMedia order by int_order desc 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=LUMPERSIST=4BDBC7125E81DA449D58153A60CC9F00; JSESSIONID=1937FE31479E32C425C1A2DCFF35623E.cfusion; AWSALBAPP-1=_remove_; AWSALBAPP-0=AAAAAAAAAAAL45mIDMi0AAfq0/XOwzULDC+cZqASUBtAzSvoCj82Yry6AqqrgmsbJyZL0i6ENPp+Dz9s6N7CNEwqtC5HT8yyC+8RjUvXCZoTdqj4a9hyHirTWaD3uGuVVryH+9q6/J/0gw==; AWSALBAPP-3=_remove_; AWSALBAPP-2=_remove_ HTTP_HOST=v8template3.augusoft.net HTTP_REFERER=https://v8template3.augusoft.net/index.cfm?method=ClassListing.ClassListingDisplay&int_category_id=2&int_sub_category_id=6&int_catalog_id=&frmCoursesMenu=1 HTTP_URL=/index.cfm?method=ClassListing.ClassListingDisplay&int_category_id=2&int_sub_category_id=6&int_catalog_id= HTTP_USER_AGENT=Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com) LOCAL_ADDR=172.31.100.167 PATH_INFO= PATH_TRANSLATED=E:\Lumens\index.cfm QUERY_STRING=method=ClassListing.ClassListingDisplay&int_category_id=2&int_sub_category_id=6&int_catalog_id= REMOTE_ADDR=172.31.103.217 REMOTE_HOST=172.31.103.217 REMOTE_USER= REQUEST_METHOD=GET SCRIPT_NAME=/index.cfm SERVER_NAME=v8template3.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=AAAAAAAAAAAL45mIDMi0AAfq0/XOwzULDC cZqASUBtAzSvoCj82Yry6AqqrgmsbJyZL0i6ENPp Dz9s6N7CNEwqtC5HT8yyC 8RjUvXCZoTdqj4a9hyHirTWaD3uGuVVryH 9q6/J/0gw AWSALBAPP-1=_remove_ AWSALBAPP-2=_remove_ AWSALBAPP-3=_remove_ JSESSIONID=1937FE31479E32C425C1A2DCFF35623E.cfusion LUMPERSIST=4BDBC7125E81DA449D58153A60CC9F00 Session Variables: actortypeid=90 addtoken=CFID=10157083&CFTOKEN=ab0b3762b05bba41-E60C9BC9-E98A-138D-953E6DAB72474B1C&jsessionid=1937FE31479E32C425C1A2DCFF35623E.cfusion allowunmaskeddata=0 appname=v8Template3 arr_item_url=Array (1) arrtrackflowregister=Array (4) basecfcs=versions.v81QA basecode=/versions/v81QA bit_canvas_failed_process=false bit_member=0 customcct= customcfcs=Customers.v8Template3 customerid=488 custompath=/Customers/v8Template3/custom debug=0 debugoutput=1 docxdir=DocxHTML domain=v8template3.augusoft.net ds=v8Template3 enabledlivechat=false enabledsupportwidget=false enteredsiteusingipaddress=1 foundeo_firewall_ip=3.17.156.231 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=1937FE31479E32C425C1A2DCFF35623E.cfusion sitepath=/Customers/v8Template3 sslreturnurl=https://v8template3.augusoft.net/ stylesheetpath=/Customers/v8Template3/StyleSheets/style.css timeoutminutes=60 urltoken=CFID=10157084&CFTOKEN=9aa54991ed5e54ef-E60CB3E7-CE6E-7EC8-AE94293BB42C8C48&jsessionid=1937FE31479E32C425C1A2DCFF35623E.cfusion version=v81QA URL Parameters: int_catalog_id= int_category_id=2 int_sub_category_id=6 method=ClassListing.ClassListingDisplayDebug Rendering Time: 32 ms |