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=0ms, Records=10) in E:\Lumens\Versions\v81\checkPermission.cfm @ 13:09:36.036SELECT 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=v8Testing5, Time=0ms, Records=1) in E:\Lumens\Versions\v81\Administration\SQL\FeatureConfigurationQry.cfm @ 13:09:36.036SET 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\v81\globalVariables.cfm @ 13:09:36.036SELECT regionId,orgID FROM featureConfigurationQry WHERE baseurl LIKE '%v8testing5.augusoft.net%' MenuQry (Datasource=v8Testing5, Time=15ms, Records=11) in E:\Lumens\Versions\v81\Administration\SQL\MenuQry.cfm @ 13:09:36.036SET 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', 'Scholarships') 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', 'Scholarships') 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=v8Testing5, Time=0ms, Records=1) in E:\Lumens\Versions\v81\Administration\SQL\MenuQry.cfm @ 13:09:36.036SET 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=v8Testing5, Time=0ms, Records=109) in E:\Lumens\Versions\v81\cfc\systemoptions\siteconfiguration\siteconfiguration.cfc @ 13:09:36.036SELECT 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=v8Testing5, Time=0ms, Records=3) in E:\Lumens\Versions\v81\cfc\systemoptions\siteconfiguration\siteconfiguration.cfc @ 13:09:36.036select * from tbl_social_media_details WHERE int_region_id = ? order by int_order asc Query Parameter Value(s) -qGraphics (Datasource=v8Testing5, Time=0ms, Records=1) in E:\Lumens\Versions\v81\cfc\systemoptions\siteconfiguration\siteconfiguration.cfc @ 13:09:36.036select * from UIVariables where RegionID = ? Query Parameter Value(s) -qUserNavItems (Datasource=v8Testing5, Time=0ms, Records=1) in E:\Lumens\versions\V81\cfc\systemoptions\poweruser\poweruser.cfc @ 13:09:36.036SELECT 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=v8Testing5, Time=0ms, Records=1) in E:\Lumens\versions\V81\cfc\systemoptions\poweruser\poweruser.cfc @ 13:09:36.036SELECT 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=v8Testing5, Time=0ms, Records=0) in E:\Lumens\Application.cfc @ 13:09:36.036DECLARE @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=v8Testing5, Time=0ms, Records=0) in E:\Lumens\Application.cfc @ 13:09:36.036DECLARE @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=v8Testing5, Time=0ms, Records=1) in E:\Lumens\Versions\v81\header.cfm @ 13:09:36.036SELECT 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=v8Testing5, Time=0ms, Records=1) in E:\Lumens\Versions\v81\cfc\systemoptions\siteconfiguration\siteconfiguration.cfc @ 13:09:36.036select * from UIVariables where RegionID = ? Query Parameter Value(s) -qTempMenu (Datasource=, Time=0ms, Records=9) in E:\Lumens\Versions\v81\vendor\site_templates\shared\includes\menu_action.cfm @ 13:09:36.036select * from qTempMenu where MenuItemID NOT IN (2,606) qSocialMediaHeader (Datasource=, Time=0ms, Records=3) in E:\Lumens\Versions\v81\vendor\site_templates\shared\includes\nav-social-media.cfm @ 13:09:36.036select * from qSocialMedia order by int_order desc qGetDonateUserNavLabel (Datasource=v8Testing5, Time=0ms, Records=1) in E:\Lumens\Versions\v81\header.cfm @ 13:09:36.036SELECT 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=v8Testing5, Time=0ms, Records=1) in E:\Lumens\Versions\v81\header.cfm @ 13:09:36.036SELECT 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=v8Testing5, Time=0ms, Records=1) in E:\Lumens\Versions\v81\header.cfm @ 13:09:36.036SELECT isNull(str_user_nav_label,'') AS str_menu_label FROM tbl_user_nav_menu_label WHERE int_actor_type_id = ? AND str_menu_label LIKE 'DONATE' AND str_display_condition='application.appvars[ri].bit_allow_student_donation EQ 1 AND application.appvars[ri].bit_display_donate_in_navmenu EQ 1'; Query Parameter Value(s) -qTempCourseMenu (Datasource=, Time=0ms, Records=2) in E:\Lumens\Versions\v81\vendor\site_templates\template1\includes\nav-main.cfm @ 13:09:36.036select * from qCourseMenu where int_parent_id = ? and MenuItemType = ? and Label <> ? and int_parent_id<>0 Query Parameter Value(s) -qTempCourseMenu (Datasource=, Time=0ms, Records=1) in E:\Lumens\Versions\v81\vendor\site_templates\template1\includes\nav-main.cfm @ 13:09:36.036select * from qCourseMenu where int_parent_id = ? and MenuItemType = ? and Label <> ? and int_parent_id<>0 Query Parameter Value(s) -qTempCourseMenu (Datasource=, Time=0ms, Records=1) in E:\Lumens\Versions\v81\vendor\site_templates\template1\includes\nav-main.cfm @ 13:09:36.036select * from qCourseMenu where int_parent_id = ? and MenuItemType = ? and Label <> ? and int_parent_id<>0 Query Parameter Value(s) -qTempMenu (Datasource=, Time=0ms, Records=0) in E:\Lumens\Versions\v81\vendor\site_templates\template1\includes\nav-main.cfm @ 13:09:36.036select * from qMenu where int_parent_id = 481 qTempMenu (Datasource=, Time=0ms, Records=0) in E:\Lumens\Versions\v81\vendor\site_templates\template1\includes\nav-main.cfm @ 13:09:36.036select * from qMenu where int_parent_id = 492 qTempMenu (Datasource=, Time=0ms, Records=0) in E:\Lumens\Versions\v81\vendor\site_templates\template1\includes\nav-main.cfm @ 13:09:36.036select * from qMenu where int_parent_id = 532 qTempMenu (Datasource=, Time=0ms, Records=0) in E:\Lumens\Versions\v81\vendor\site_templates\template1\includes\nav-main.cfm @ 13:09:36.036select * from qMenu where int_parent_id = 556 qTempMenu (Datasource=, Time=0ms, Records=0) in E:\Lumens\Versions\v81\vendor\site_templates\template1\includes\nav-main.cfm @ 13:09:36.036select * from qMenu where int_parent_id = 510 qTempMenu (Datasource=, Time=0ms, Records=0) in E:\Lumens\Versions\v81\vendor\site_templates\template1\includes\nav-main.cfm @ 13:09:36.036select * from qMenu where int_parent_id = 517 qTempMenu (Datasource=, Time=0ms, Records=0) in E:\Lumens\Versions\v81\vendor\site_templates\template1\includes\nav-main.cfm @ 13:09:36.036select * from qMenu where int_parent_id = 5 qTempMenu (Datasource=, Time=0ms, Records=0) in E:\Lumens\Versions\v81\vendor\site_templates\template1\includes\nav-main.cfm @ 13:09:36.036select * from qMenu where int_parent_id = 7 qTempMenu (Datasource=, Time=0ms, Records=0) in E:\Lumens\Versions\v81\vendor\site_templates\template1\includes\nav-main.cfm @ 13:09:36.036select * from qMenu where int_parent_id = 458 qGetOrganisationPhoneQry (Datasource=v8Testing5, Time=0ms, Records=1) in E:\Lumens\Versions\v81\model\course\classlisting\act_class_listing.cfm @ 13:09:36.036SET 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=v8Testing5, Time=0ms, Records=0) in E:\Lumens\Versions\v81\model\course\classlisting\act_class_listing.cfm @ 13:09:36.036SELECT 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=v8Testing5, Time=0ms, Records=1) in E:\Lumens\Versions\v81\model\course\classlisting\act_class_listing.cfm @ 13:09:36.036SELECT 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=v8Testing5, Time=0ms, Records=0) in E:\Lumens\Versions\v81\model\course\classlisting\act_class_listing.cfm @ 13:09:36.036SELECT 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=v8Testing5, Time=0ms, Records=1) in E:\Lumens\Versions\v81\model\course\classlisting\act_class_listing.cfm @ 13:09:36.036Select Name ,[dbo].[fun_strip_html](Name) as NameNoHTML from categories WITH (NOLOCK) where CategoryID = ? Query Parameter Value(s) -rs_sub_category_details (Datasource=v8Testing5, Time=0ms, Records=1) in E:\Lumens\Versions\v81\model\course\classlisting\act_class_listing.cfm @ 13:09:36.036SELECT NAME ,Description ,str_description_no_html AS DescriptionNoHTML ,Status FROM subcategories WITH (NOLOCK) WHERE SubCategoryID = ? AND CategoryID = ? Query Parameter Value(s) -rs_contents (Datasource=v8Testing5, Time=0ms, Records=1) in E:\Lumens\Versions\v81\model\course\classlisting\act_class_listing.cfm @ 13:09:36.036select contents from contents WITH (NOLOCK) where path= ? Query Parameter Value(s) -qTopThreeFetauredClass (Datasource=v8Testing5, Time=0ms, Records=0) in E:\Lumens\versions\v81\cfc\class\featured.cfc @ 13:09:36.036DECLARE @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=v8Testing5, Time=0ms, Records=0) in E:\Lumens\versions\v81\cfc\class\recommended.cfc @ 13:09:36.036/*
What should happen : Refer Tracker 43106
Best Match for the 'Recommended Classes' box for a Student
Lumens will compare the courses for all the classes in which
the student has been registered (but not those for which the registrations have been cancelled or transferred)
to the courses that other students, who also registered in the same classes as the student, have been registered in (but not those for which the registrations have been cancelled or transferred).
Lumens will determine the 20 courses which are
1) the parents of the classes most frequently registered for and
2) are currently available for the student to register in.
Lumens will display the Class Names of the three (3) classes that are children of the most frequently registered
courses from that list in the 'Recommended Courses' box.
If the student has not registered for any classes, or if the user is a guest, then Lumens will determine the 20 classes that are most frequently registered for in the Category being viewed that are available for registration for this student.
Once determined, Lumens will display the Class Names of the three (3) courses from that list.
Aswin 01/02/2018 : If the student has not registered for any classes - I dont think this is handled.
Also - arguments.recommended_type is not asked for by the spec.
*/
DECLARE @LearnerID AS INT
, @SubcategoryID AS INT;
SET @LearnerID = 0;
SET @SubcategoryID = ?;
SELECT TOP 20 co.NAME
, co.courseid
, co.subcategoryid
, c.ClassID
, c.NAME AS class_name
, count(co.courseid) AS RegCount
FROM registrations r WITH (NOLOCK)
INNER JOIN classes c WITH (NOLOCK) ON r.classid = c.classid
INNER JOIN tbl_class_detail tcd WITH (NOLOCK) ON tcd.int_class_id = c.classid
INNER JOIN courses co WITH (NOLOCK) ON c.courseid = co.courseid
WHERE 1 = 1
AND c.dat_public_registration_closes > dbo.fun_GetDateCT()
AND c.STATUS IN (
'A'
, 'C'
)
AND co.status in ('A')
AND tcd.bit_display_to_public = 1
AND NOT EXISTS (
SELECT 1
FROM tbl_restriction_code_detail
WHERE int_table_id = 2 /*classes*/
AND int_primary_key_id = c.ClassID
)
GROUP BY co.courseid
, co.NAME
, co.subcategoryid
, c.ClassID
, c.NAME
HAVING SUM(c.RemainingSeats) > 1
ORDER BY RegCount DESC
Query Parameter Value(s) -qGetClassListingOrder (Datasource=v8Testing5, Time=0ms, Records=1) in E:\Lumens\Versions\v81\model\course\classlisting\act_class_listing.cfm @ 13:09:36.036select str_class_listing_order from tbl_class_listing_order qIsAllCatalogMeansAllClass (Datasource=v8Testing5, Time=0ms, Records=1) in E:\Lumens\Versions\v81\model\course\classlisting\act_class_listing.cfm @ 13:09:36.036SELECT COUNT(catalogid) AS IsAllCatalogMeansAllClass FROM CATALOGS WHERE CatalogID = 1 AND NAME = 'All Catalogs' qIsAllCatalogMeansAllClass (Datasource=v8Testing5, Time=0ms, Records=1) in E:\Lumens\Versions\v81\model\course\classlisting\act_class_listing.cfm @ 13:09:36.036SELECT COUNT(catalogid) AS IsAllCatalogMeansAllClass FROM CATALOGS WHERE CatalogID = 1 AND NAME = 'All Catalogs' qIsAllCatalogMeansAllClass (Datasource=v8Testing5, Time=0ms, Records=1) in E:\Lumens\Versions\v81\model\course\classlisting\act_class_listing.cfm @ 13:09:36.036SELECT COUNT(catalogid) AS IsAllCatalogMeansAllClass FROM CATALOGS WHERE CatalogID = 1 AND NAME = 'All Catalogs' qGetClassListing (Datasource=v8Testing5, Time=78ms, Records=1) in E:\Lumens\Versions\v81\model\course\classlisting\act_class_listing.cfm @ 13:09:36.036SET 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 (
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 NOT EXISTS (
SELECT 1
FROM tbl_restriction_code_detail
WHERE int_table_id = 2 /*classes*/
AND int_primary_key_id = cl.ClassID
)
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 *****/
/**************************************************************************/
INSERT INTO @ClassIDTable3
SELECT DISTINCT ClassListingID
,0 AS Rank
,0 AS Rank2
FROM tbl_class_listing cl
INNER JOIN tbl_certificates_classes tcc1 ON tcc1.int_certificate_id = CL.int_certificate_id AND tcc1.int_certificate_group_id = 0
INNER JOIN Courses co ON co.CourseID = tcc1.int_course_id
INNER JOIN tbl_certificates tc ON tc.int_certificate_id = CL.int_certificate_id AND tc.chr_status = 'P' AND tc.bit_use_certificate_groups = 0
WHERE CL.str_class_type = 'cf'
/*- 8/28/2012 tracker 30987-*/
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
)
/*BEGIN 13 Aug 2012 PAV TRACKER 30987 SEARCH VALUES */
/*END 13 Aug 2012 PAV TRACKER 30987 SEARCH VALUES */
/*BEGIN 18 JAN 2012 NEO 00043 TRACKER 27847 SEARCH VALUES */
/* BEGIN : 09 SEP 2014 NEO 00159 TRACKER 42510 */
/* END : 09 SEP 2014 NEO 00159 TRACKER 42510 */
/*END 18 JAN 2012 NEO 00043 TRACKER 27847 SEARCH VALUES */
/* 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 */
/* -8/17/2012 tracker 31042*/
AND
/* BEGIN 09 MAY 2014 NEO 0088 TRACKER 40819 */
@currentTimeZoneOffsetDate BETWEEN cl.AppearanceStartDate
AND CL.AppearanceEndDate
/* END 09 MAY 2014 NEO 0088 TRACKER 40819 */
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
/**/
DELETE FROM @ClassIDTable3 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 */
/**************************************************************************/
/***** 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 */
,CASE
WHEN cl.bit_uses_class_lottery IS NOT NULL
THEN cl.bit_uses_class_lottery
ELSE 0
END AS bit_uses_class_lottery
,CASE
WHEN cl.bit_uses_class_lottery IS NOT NULL
AND cl.dat_lottery_close_date IS NOT NULL
THEN cl.dat_lottery_close_date
ELSE ''
END AS dat_lottery_close_date
,CASE
WHEN cl.bit_uses_class_lottery IS NOT NULL
AND cl.dat_lottery_run_date IS NOT NULL
THEN cl.dat_lottery_run_date
ELSE ''
END AS dat_lottery_run_date
,CASE
WHEN cl.bit_uses_class_lottery IS NOT NULL
THEN cl.int_lottery_status_id
ELSE 0
END AS int_lottery_status_id
,CASE
WHEN cl.bit_uses_class_lottery IS NOT NULL
THEN cl.int_lottery_type_id
ELSE 0
END AS int_lottery_type_id
,isnull((
SELECT count(lr.int_lottery_request_id)
FROM tbl_lottery_requests lr
WHERE lr.int_class_id = cl.classid
AND ISNULL(lr.int_removed_by_id, 0) = 0
), 0) AS lotteryRequestCount
/* 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
, CAST (CASE WHEN cl.int_badge_integration_id > 1 THEN 1 ELSE 0 END AS BIT) AS bit_use_badges
, cl.str_badge_template_label
, tbit.str_template_name
,CL.int_certificate_group_id
, CASE
WHEN cl.bit_use_class_prerequisites = 1 THEN
CASE
WHEN (SELECT COUNT(tpc.int_prerequisite_class_id)
FROM tbl_prerequisite_class tpc
INNER JOIN tbl_prerequisites tp ON tp.int_prerequisites_id = tpc.int_prerequisites_id
INNER JOIN tbl_prerequisite_status PS ON PS.int_prerequisite_status_id = tp.int_prerequisite_status_id
WHERE tpc.int_class_id = CL.ClassID
AND (tp.str_available_for IN ('Class','All'))
AND tpc.str_status = 'A'
AND PS.chr_prerequisite_status = 'A') > 0
THEN 1
ELSE 0
END
ELSE 0
END 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 tbl_badge_integration_templates tbit ON cl.int_badge_integration_template_id = tbit.int_badge_integration_template_id
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 */
,0 AS bit_uses_class_lottery
,'' AS dat_lottery_close_date
,'' AS dat_lottery_run_date
,'' AS int_lottery_status_id
,'' AS int_lottery_type_id
,0 AS lotteryRequestCount
/* 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
, 0 AS bit_use_badges
, NULL AS str_badge_template_label
, NULL AS str_template_name
,CL.int_certificate_group_id
, 0 AS bit_use_class_prerequisites
, '' AS CertificateSlug
, '' AS str_special_notice
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 **********************/
/**************************************************************************************************/
/*Begin Author: Neo 00098 Description : Certificates UC-16 , Date : 18 JUL 2011*/
UNION
/* gets Certificates */
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 */
,'' AS RequiresReleaseForm
,'' AS int_age_limit
/*Added Jan 17 2013 AI Membership Enhancement UC 10 Neo 00110*/
,0 AS InCourseSeries
/* 2/8/2012 tracker 28153 */
,'' AS SeriesRegs
/* '' AS NonSeriesRegs, */
,'' 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
,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
,'' AS coursegraphic
,sc.str_subCat_graphic
,ca.str_Cat_graphic
,'' AS str_class_graphic_filename
,CL.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*/
,tc.str_certificate_name 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
,0 AS waitingList
,0 bit_class_on_waiting_list
,0 AS isfull
,0 AS bit_enrolled
/*Added 06 AUG 2012 NEO 00049 Rochester Community Schools Register for a Class Multiple Times */
,0 AS bit_allow_multiple_registration
,0 AS bit_on_waiting_list
/* 0 as ageLimit, */
,0 AS learnerAlreadyInCart
,tct.str_certificate_type_name AS typeName
,st.Email AS Email
/* Begin: May 31,2012 NEO 00091 Tracker:29766 */
,st.FName
,st.LName
/* End: May 31,2012 NEO 00091 Tracker:29766 */
,tc.int_courses_required_completion AS ClassesToComplete
,tc.int_courses_offered AS classCount
,1 AS sortorder
,tc.bit_display_to_public AS bitDisplay
,tc.bit_allow_public_registration AS bitRegistration
,tc.int_certificate_id
/* Added July 11 2012 Tracker 30411 Neo 00110*/
,tc.bit_multiple_elective_groups
/*Added July 11 2012 Tracker 30411 Neo 00110*/
/* Begin Author 00098 : Neo AI Course Series change 20 July 2011*/
,'' AS CourseSeriesType
,0 AS SeriesGroupMatFeeAdv
/* End Author 00098 : Neo AI Course Series Use Case :20 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 */
,tc.bit_use_program_sections AS UseProgramSection
,'' AS ProgramSectionStartDate
,'' AS ProgramSectionEndDate
,'' AS isAllProgramSectionClassesAvailable
,tc.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 */
,1 AS bit_disp_cs_to_public /* 30 JUL 2014 NEO 00159 TRACKER 39672 */
/* Begin NEO 00164 1st DEC 2014 OLLI Lottery UC-08 */
,0 AS bit_uses_class_lottery
,'' AS dat_lottery_close_date
,'' AS dat_lottery_run_date
,'' AS int_lottery_status_id
,'' AS int_lottery_type_id
,0 AS lotteryRequestCount
/* 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
, NULL AS str_series_icon
, NULL AS str_series_icon_alt_descr
, 0 AS AvailableItemCount /* change to certificate_item count */
, cl.bit_allow_wcg_registration
, CL.bit_allow_waiting_lists
, tc.str_default_text_for_call_message_class
, tc.bit_use_badges
, tc.str_badge_template_label
, tbit.str_template_name
,CL.int_certificate_group_id
, 0 AS bit_use_class_prerequisites
, tc.str_url_slug AS CertificateSlug
, tc.str_special_notice
FROM @ClassIDTable3 CT3
INNER JOIN tbl_class_listing CL ON CT3.ClassListingID = CL.ClassListingID
INNER JOIN tbl_certificates tc ON CL.int_certificate_id = tc.int_certificate_id
AND tc.chr_status = 'P' /* Author: Neo s 00045 Description : Certificates UC-16 Issue Fix Date: 25 Aug 2011 */
INNER JOIN tbl_certificate_type tct ON tc.int_certificate_type_id = tct.int_certificate_type_id
INNER JOIN Staff st ON tc.int_staff_contact_id = st.StaffID
/* 09/15/2011 00097 For: Certificates UC-16 Issue List Begin*/
INNER JOIN tbl_certificates_classes tcc1 ON tcc1.int_certificate_id = CL.int_certificate_id
/* 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
/* 09/15/2011 00097 For: Certificates UC-16 Issue List End*/
INNER JOIN tbl_class_listing tcl ON tcl.classid = tcc1.int_class_id /* TRACKER 36067 AUG 20 2013 NEO 0126*/
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 */
/* Begin : Tracker 49643 31 Dec 2015 Neo 00198 */
LEFT JOIN tbl_badge_integration_templates tbit ON tc.int_badge_integration_template_id = tbit.int_badge_integration_template_id
/* End : Tracker 49643 31 Dec 2015 Neo 00198 */
WHERE CL.str_class_type = 'cf' AND tc.bit_use_certificate_groups = 0
AND CL.int_certificate_group_id = 0
/* PAV 6 Feb 2012 added so a URL can retrieve an entire category - Tracker 28161 */
/*Author: 00097 Date: 29 Aug 2011 Task: Certificate Issue begin*/
AND tc.bit_display_to_public = 1
/*Author: 00097 Date: 29 Aug 2011 Task: Certificate Issue End*/
/* BEGIN : TRACKER 36067 AUG 20 2013 NEO 0126*/
/* END : TRACKER 36067 AUG 20 2013 NEO 0126*/
/*Begin:Tracker 39813 Apr 14 2014 Neo 0126 */
/*End:Tracker 39813 Apr 14 2014 Neo 0126 */
/* BEGIN: NEO 00091 Date:Sep 25,2012 AI V7.3 Full-Time Certificate Use Cases 2012-08-03 UC-13 */
/*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=1) in E:\Lumens\Versions\v81\model\course\classlisting\act_class_listing.cfm @ 13:09:36.036SELECT 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\v81\model\course\classlisting\act_class_listing.cfm @ 13:09:36.036SELECT * FROM qGetClassListingPagination ORDER BY str_class_type DESC , ClStartDate,ClassNameSort qgetCertificateIdFromClassId (Datasource=v8Testing5, Time=17ms, Records=2) in E:\Lumens\Versions\v81\model\course\classlisting\act_class_listing.cfm @ 13:09:36.036SELECT DISTINCT tcc.int_certificate_id , tc.bit_use_program_sections , tc.str_url_slug AS CertificateSlug , 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) -qgetType (Datasource=v8Testing5, Time=0ms, Records=1) in E:\Lumens\Versions\v81\model\course\classlisting\act_class_listing.cfm @ 13:09:36.036SELECT str_certificate_type_name
,(
SELECT str_certificate_name
FROM tbl_certificates WITH (NOLOCK)
WHERE int_certificate_id = ?
)
+
ISNULL((
SELECT ' - ' + str_group_name + ' (' + str_number + ')'
FROM tbl_certificate_groups WITH (NOLOCK)
WHERE int_certificate_group_id = ?
),'')
AS str_certificate_name
FROM tbl_certificate_type WITH (NOLOCK)
WHERE int_certificate_type_id IN (
SELECT int_certificate_type_id
FROM tbl_certificates WITH (NOLOCK)
WHERE int_certificate_id = ?
)
Query Parameter Value(s) -qgetType (Datasource=v8Testing5, Time=0ms, Records=1) in E:\Lumens\Versions\v81\model\course\classlisting\act_class_listing.cfm @ 13:09:36.036SELECT str_certificate_type_name
,(
SELECT str_certificate_name
FROM tbl_certificates WITH (NOLOCK)
WHERE int_certificate_id = ?
)
+
ISNULL((
SELECT ' - ' + str_group_name + ' (' + str_number + ')'
FROM tbl_certificate_groups WITH (NOLOCK)
WHERE int_certificate_group_id = ?
),'')
AS str_certificate_name
FROM tbl_certificate_type WITH (NOLOCK)
WHERE int_certificate_type_id IN (
SELECT int_certificate_type_id
FROM tbl_certificates WITH (NOLOCK)
WHERE int_certificate_id = ?
)
Query Parameter Value(s) -qCount (Datasource=v8Testing5, Time=0ms, Records=1) in E:\Lumens\Versions\v81\model\course\classlisting\act_class_listing.cfm @ 13:09:36.036SELECT Count(*) AS Counter FROM tbl_cart_class_detail WHERE SessionID = ? AND int_actor_type_id = 90 AND int_class_id = ? 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=v8testing5.augusoft.net HTTP_REFERER= HTTP_URL=/index.cfm?int_category_id=2&int_sub_category_id=20&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=int_category_id=2&int_sub_category_id=20&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=v8testing5.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=20B114FD082D724E079BDFA9A3BC1F17 Session Variables: actortypeid=90 addtoken=CFID=17337609&CFTOKEN=a19323509e59512a-6FB93515-E5CE-214F-67604F787EFF935D&jsessionid=01DD073D62F127555D087D8430C95A6C.cfusion allowunmaskeddata=0 appname=v8Testing5 arrtrackflowregister=Array (3) basecfcs=versions.v81 basecode=/versions/v81 bit_canvas_failed_process=false bit_member=0 customcct= customcfcs=Customers.v8Testing5 customerid=493 custompath=/Customers/v8Testing5/custom debug=0 debugoutput=1 docxdir=DocxHTML domain=v8testing5.augusoft.net ds=v8Testing5 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=01DD073D62F127555D087D8430C95A6C.cfusion sitepath=/Customers/v8Testing5 sslreturnurl=https://v8testing5.augusoft.net/ stylesheetpath=/Customers/v8Testing5/StyleSheets/style.css timeoutminutes=60 urltoken=CFID=17337609&CFTOKEN=a19323509e59512a-6FB93515-E5CE-214F-67604F787EFF935D&jsessionid=01DD073D62F127555D087D8430C95A6C.cfusion version=v81 URL Parameters: int_category_id=2 int_sub_category_id=20 method=ClassListing.ClassListingDisplayDebug Rendering Time: 16 ms |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
buy 