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=10) in E:\Lumens\Versions\v81QA\checkPermission.cfm @ 16:02:54.054SELECT 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) -FeatureConfigurationQry (Datasource=v8Testing1, Time=0ms, Records=1) in E:\Lumens\Versions\v81QA\Administration\SQL\FeatureConfigurationQry.cfm @ 16:02:54.054SET NOCOUNT ON
DECLARE @UsesRefundRequest CHAR(1)
SET @UsesRefundRequest = 0
SET @UsesRefundRequest = (
SELECT 1
FROM RefundPaymentMethods
WHERE RefundPaymentMethodID = 7
AND STATUS = 'A'
)
SELECT TOP 1 rg.RegionID
, rg.NAME
, rg.Address1
, rg.Address2
, rtrim(rg.City) + ', ' + rg.STATE + ' ' + rg.Zip AS CSZ
, rg.Phone1
, rg.SiteTitle
, rg.Footer
/**/
, rg.prefooter
, rg.brandingfooter
/**/
, rg.MetaText
, rg.EmailAux4
, IsNull((
SELECT Fee
FROM Products
WHERE Code = 'REGFEE'
AND STATUS = 'A'
AND RegionID = rg.RegionID
), 0.00) AS RegistrationFee
, IsNull((
SELECT Fee
FROM Products
WHERE Code = 'DEFERRED_PAYMENT_FEE'
AND STATUS = 'A'
AND RegionID = rg.RegionID
), 0.00) AS deferred_payment_fee
, fc.*
, @UsesRefundRequest AS UsesRefundRequest
, rg.privacy_data_mask_id
, rg.str_google_verify
, rg.str_yahoo_verify
, rg.str_hubspot_portal_id
, rg.str_hubspot_salog_version
, rg.str_hubspot_ppa
, rg.str_genoo_api_key
, rg.str_genoo_tracking_code
, rg.TimeZoneHourAdjustment
/**/
/**/
/**/
, e.str_erp_name
/**/
/**/
, CAST(DecryptByKeyAutoCert(cert_id('LumensCert'), NULL, ec.varbin_erp_password) AS NVARCHAR(500)) AS str_erp_password_decrypted
/**/
/**/
, rg.theURL
, rg.URLText
, rg.str_analytics_linked_domains
, rg.GTMContainerID
, rg.str_social_media_sharing
, ec.*
, (
SELECT TOP 1 Description
FROM PaymentMethods
WHERE PaymentMethodID >= 200
AND STATUS = 'A'
) AS str_internal_payment_method_name
FROM FeatureConfiguration fc
INNER JOIN Regions rg ON fc.RegionID = rg.RegionID
LEFT JOIN tbl_erp e ON fc.int_erp_id = e.int_erp_id
LEFT JOIN tbl_erp_config ec ON ec.str_erp_url <> ''
WHERE rg.STATUS = 'A'
qryGetRegion (Datasource=, Time=0ms, Records=1) in E:\Lumens\Versions\v81QA\globalVariables.cfm @ 16:02:54.054SELECT regionId,orgID FROM featureConfigurationQry WHERE baseurl LIKE '%v8testing1.augusoft.net%' MenuQry (Datasource=v8Testing1, Time=0ms, Records=17) in E:\Lumens\Versions\v81QA\Administration\SQL\MenuQry.cfm @ 16:02:54.054SET 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=v8Testing1, Time=0ms, Records=1) in E:\Lumens\Versions\v81QA\Administration\SQL\MenuQry.cfm @ 16:02:54.054SET 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=v8Testing1, Time=0ms, Records=101) in E:\Lumens\versions\v81QA\cfc\systemoptions\siteconfiguration\siteconfiguration.cfc @ 16:02:54.054SELECT 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=v8Testing1, Time=0ms, Records=3) in E:\Lumens\versions\v81QA\cfc\systemoptions\siteconfiguration\siteconfiguration.cfc @ 16:02:54.054select * from tbl_social_media_details WHERE int_region_id = ? order by int_order asc Query Parameter Value(s) -qGraphics (Datasource=v8Testing1, Time=0ms, Records=1) in E:\Lumens\versions\v81QA\cfc\systemoptions\siteconfiguration\siteconfiguration.cfc @ 16:02:54.054select * from UIVariables where RegionID = ? Query Parameter Value(s) -qUserNavItems (Datasource=v8Testing1, Time=0ms, Records=1) in E:\Lumens\Versions\v81QA\cfc\systemoptions\poweruser\poweruser.cfc @ 16:02:54.054SELECT 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=v8Testing1, Time=0ms, Records=0) in E:\Lumens\Versions\v81QA\cfc\systemoptions\poweruser\poweruser.cfc @ 16:02:54.054SELECT 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) -qUser (Datasource=v8Testing1, Time=0ms, Records=0) in E:\Lumens\Application.cfc @ 16:02:54.054DECLARE @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) -qUser (Datasource=v8Testing1, Time=16ms, Records=0) in E:\Lumens\Application.cfc @ 16:02:54.054DECLARE @expireRequestsAfter DATETIME2 = ?; DELETE FROM tbl_cart_class_detail WHERE EnteredDate <= @expireRequestsAfter; DELETE FROM tbl_cart_merchandise_detail WHERE EnteredDate <= @expireRequestsAfter; Query Parameter Value(s) -variables.custom_scripts (Datasource=v8Testing1, Time=0ms, Records=1) in E:\Lumens\Versions\v81QA\header.cfm @ 16:02:54.054SELECT 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=v8Testing1, Time=0ms, Records=1) in E:\Lumens\versions\v81QA\cfc\systemoptions\siteconfiguration\siteconfiguration.cfc @ 16:02:54.054select * from UIVariables where RegionID = ? Query Parameter Value(s) -qTempMenu (Datasource=, Time=0ms, Records=14) in E:\Lumens\Versions\v81QA\vendor\site_templates\shared\includes\menu_action.cfm @ 16:02:54.054select * from qTempMenu where MenuItemID NOT IN (645,496,526) qSocialMediaHeader (Datasource=, Time=0ms, Records=3) in E:\Lumens\Versions\v81QA\vendor\site_templates\shared\includes\nav-social-media.cfm @ 16:02:54.054select * from qSocialMedia order by int_order desc qGetDonateUserNavLabel (Datasource=v8Testing1, Time=0ms, Records=1) in E:\Lumens\Versions\v81QA\header.cfm @ 16:02:54.054SELECT 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=v8Testing1, Time=0ms, Records=1) in E:\Lumens\Versions\v81QA\header.cfm @ 16:02:54.054SELECT 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=v8Testing1, Time=0ms, Records=1) in E:\Lumens\Versions\v81QA\header.cfm @ 16:02:54.054SELECT 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) -qTempMenu (Datasource=, Time=0ms, Records=0) in E:\Lumens\Versions\v81QA\vendor\site_templates\template1\includes\nav-main.cfm @ 16:02:54.054select * from qMenu where int_parent_id = 574 qTempMenu (Datasource=, Time=0ms, Records=0) in E:\Lumens\Versions\v81QA\vendor\site_templates\template1\includes\nav-main.cfm @ 16:02:54.054select * from qMenu where int_parent_id = 2 qTempMenu (Datasource=, Time=0ms, Records=4) in E:\Lumens\Versions\v81QA\vendor\site_templates\template1\includes\nav-main.cfm @ 16:02:54.054select * from qMenu where int_parent_id = 534 qTempMenu (Datasource=, Time=0ms, Records=2) in E:\Lumens\Versions\v81QA\vendor\site_templates\template1\includes\nav-main.cfm @ 16:02:54.054select * from qMenu where int_parent_id = 540 qTempMenu (Datasource=, Time=0ms, Records=0) in E:\Lumens\Versions\v81QA\vendor\site_templates\template1\includes\nav-main.cfm @ 16:02:54.054select * from qMenu where int_parent_id = 7 qTempMenu (Datasource=, Time=0ms, Records=0) in E:\Lumens\Versions\v81QA\vendor\site_templates\template1\includes\nav-main.cfm @ 16:02:54.054select * from qMenu where int_parent_id = 518 qTempMenu (Datasource=, Time=0ms, Records=0) in E:\Lumens\Versions\v81QA\vendor\site_templates\template1\includes\nav-main.cfm @ 16:02:54.054select * from qMenu where int_parent_id = 551 qGetOrganisationPhoneQry (Datasource=v8Testing1, Time=8ms, Records=1) in E:\Lumens\Versions\v81QA\model\course\classlisting\act_class_listing.cfm @ 16:02:54.054SET 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=v8Testing1, Time=16ms, Records=0) in E:\Lumens\Versions\v81QA\model\course\classlisting\act_class_listing.cfm @ 16:02:54.054SELECT 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=v8Testing1, Time=49ms, Records=1) in E:\Lumens\Versions\v81QA\model\course\classlisting\act_class_listing.cfm @ 16:02:54.054SELECT 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=v8Testing1, Time=16ms, Records=0) in E:\Lumens\Versions\v81QA\model\course\classlisting\act_class_listing.cfm @ 16:02:54.054SELECT 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_contents (Datasource=v8Testing1, Time=0ms, Records=1) in E:\Lumens\Versions\v81QA\model\course\classlisting\act_class_listing.cfm @ 16:02:54.054select contents from contents WITH (NOLOCK) where path= ? Query Parameter Value(s) -qTopThreeFetauredClass (Datasource=v8Testing1, Time=78ms, Records=0) in E:\Lumens\versions\v81QA\cfc\class\featured.cfc @ 16:02:54.054DECLARE @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
qRecommendedClass (Datasource=v8Testing1, Time=17ms, Records=0) in E:\Lumens\versions\v81QA\cfc\class\recommended.cfc @ 16:02:54.054/*
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=v8Testing1, Time=16ms, Records=1) in E:\Lumens\Versions\v81QA\model\course\classlisting\act_class_listing.cfm @ 16:02:54.054select str_class_listing_order from tbl_class_listing_order qIsAllCatalogMeansAllClass (Datasource=v8Testing1, Time=0ms, Records=1) in E:\Lumens\Versions\v81QA\model\course\classlisting\act_class_listing.cfm @ 16:02:54.054SELECT COUNT(catalogid) AS IsAllCatalogMeansAllClass FROM CATALOGS WHERE CatalogID = 1 AND NAME = 'All Catalogs' qGetClassListing (Datasource=v8Testing1, Time=408ms, Records=0) in E:\Lumens\Versions\v81QA\model\course\classlisting\act_class_listing.cfm @ 16:02:54.054SET 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 @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 @currentTimeZoneOffsetDate BETWEEN cl.AppearanceStartDate
AND CL.AppearanceEndDate
/* Aswin Jan 7 2015 Tracker 44389 */
AND @currentTimeZoneOffsetDate BETWEEN cl.HotPixStartDate AND cl.HotPixEndDate
/* 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 (
CL.str_limit_self_reg_mbrship_type_ids IS NULL
OR CL.str_limit_self_reg_mbrship_type_ids = '0'
OR CL.str_limit_self_reg_mbrship_type_ids = ''
OR EXISTS (
SELECT 1
FROM STRING_SPLIT(CL.str_limit_self_reg_mbrship_type_ids, ',')
WHERE value IN (
SELECT MembershipTypeID
FROM MembershipTypes
WHERE bit_display_classes_members_only = 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 ( @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 *****/
/**************************************************************************/
/**************************************************************************/
/***** 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
, (
SELECT COUNT(int_item_class_id)
FROM tbl_merchandise_item_class mic
INNER JOIN tbl_merchandise_item mi ON mi.int_item_id = mic.int_item_id
WHERE mi.bit_available_with_class_or_course = 1
AND mi.dat_student_display_start <= @currentTimeZoneOffsetDate
AND mi.bit_merchandise_item_available_to_students=1
AND 1 = CASE
WHEN bit_item_expire = 1
THEN CASE
WHEN @currentTimeZoneOffsetDate < mi.dat_item_expire
THEN 1
ELSE 0
END
ELSE 1
END
AND mic.int_class_id = c.ClassID
) 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
, '' AS CertificateSlug
, '' AS str_special_notice
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 ********************/
/**************************************************************************************************/
/**************************************************************************************************/
/******************** 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 ,
CLSTARTDATE,ClassName
/*Author: 00097 Date: 27 Aug 2011 Task: Certificate Issue End*/
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
Query Parameter Value(s) -qGetClassListingPagination (Datasource=, Time=0ms, Records=0) in E:\Lumens\Versions\v81QA\model\course\classlisting\act_class_listing.cfm @ 16:02:54.054SELECT 0 AS total_rec_count ,* FROM qGetClassListing WHERE str_class_type = 'cf' UNION SELECT 0 AS total_rec_count ,* FROM qGetClassListing WHERE str_class_type = 'ps' AND isProgramSectionSeatsAvailable = 1 UNION SELECT 0 AS total_rec_count ,* FROM qGetClassListing WHERE str_class_type = 'cs' AND bit_disp_cs_to_public = 1 UNION SELECT 0 AS total_rec_count ,* FROM qGetClassListing WHERE str_class_type NOT IN ( 'cf' ,'ps' ,'cs' ) qGetClassListingPagination2 (Datasource=, Time=0ms, Records=0) in E:\Lumens\Versions\v81QA\model\course\classlisting\act_class_listing.cfm @ 16:02:54.054SELECT * FROM qGetClassListingPagination ORDER BY str_class_type DESC , ClStartDate,ClassNameSort ContentsQry (Datasource=v8Testing1, Time=0ms, Records=1) in E:\Lumens\Versions\v81QA\SQL\ContentsQry.cfm @ 16:02:54.054SELECT ContentID , OrgID , RegionID , Section , Path , Contents , ContentsNoHTML , Description , Name , emailTemplate , str_default_condition , str_page_title , str_meta_description , str_keywords FROM Contents WHERE Path = ? AND orgid = ? AND regionid= ? Query Parameter Value(s) -Stored Procedures
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= HTTP_HOST=v8testing1.augusoft.net HTTP_REFERER= HTTP_URL=/index.cfm?bit_featured=1&method=classlisting.classlistingdisplay 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=bit_featured=1&method=classlisting.classlistingdisplay REMOTE_ADDR=172.31.103.195 REMOTE_HOST=172.31.103.195 REMOTE_USER= REQUEST_METHOD=GET SCRIPT_NAME=/index.cfm SERVER_NAME=v8testing1.augusoft.net SERVER_PORT=80 SERVER_PORT_SECURE=0 SERVER_PROTOCOL=HTTP/1.1 SERVER_SOFTWARE=Microsoft-IIS/10.0 WEB_SERVER_API= Cookie Variables: LUMPERSIST=11EC315DBF6059E3B2836CF5435F3DE6 Session Variables: actortypeid=90 addtoken=CFID=17340366&CFTOKEN=96e5c3c590e177cc-75EBBD7D-0604-F322-B7FCC9207BA0CD8B&jsessionid=66EC611DC448B0F42F5C1C68E7CEC826.cfusion allowunmaskeddata=0 appname=v8Testing1 arrtrackflowregister=Array (3) basecfcs=versions.v81QA basecode=/versions/v81QA bit_canvas_failed_process=false bit_member=0 customcct= customcfcs=Customers.v8Testing1 customerid=484 custompath=/Customers/v8Testing1/custom debug=0 debugoutput=1 docxdir=DocxHTML domain=v8testing1.augusoft.net ds=v8Testing1 enabledlivechat=false enabledsupportwidget=false enteredsiteusingipaddress=1 foundeo_firewall_ip=216.73.216.101 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=66EC611DC448B0F42F5C1C68E7CEC826.cfusion sitepath=/Customers/v8Testing1 sslreturnurl=https://v8testing1.augusoft.net/ stylesheetpath=/Customers/v8Testing1/StyleSheets/style.css timeoutminutes=60 urltoken=CFID=17340366&CFTOKEN=96e5c3c590e177cc-75EBBD7D-0604-F322-B7FCC9207BA0CD8B&jsessionid=66EC611DC448B0F42F5C1C68E7CEC826.cfusion version=v81QA URL Parameters: bit_featured=1 method=classlisting.classlistingdisplayDebug Rendering Time: 16 ms |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
buy 