namespace EnablementDAL.Query.Show { // Moved from GB5Framework/FrameworkDAL/Query/TourShow/TourShowQB.cs — SQL text unchanged except // GET_MODULE_PICKLIST, which now inlines MMODULE's select-list query directly instead of // referencing FrameworkDAL.Query.Module.ModuleQB (EnablementDAL has no project reference to // FrameworkDAL — this module reaches other modules' tables via raw SQL only, matching the // existing pattern already used here for MSHOWSPACE/MSHOWCHANNEL/MCONTENTCATEGORY). public class ShowQB { public const string GET_SHOW = @"SELECT --Tshow Table s.SPACEID AS SpaceId, s.SHOWNATURE AS ShowNature, s.SHOWNAME AS ShowName, s.DISPLAYNAME AS DisplayName, s.DESCRIPTION AS Description, s.OBJECTIVE AS Objective, s.MODULEID AS ModuleId, s.CONTENTCATEGORYID AS ContentCategoryId, s.CONDITIONTYPE AS ConditionType, s.SHOWCONDITIONS AS ShowConditions, s.STARTTYPE AS StartType, s.SHOWTYPE AS ShowType, s.REMEMBERSTAGE AS RememberStage, s.DISPLAYPROGRESS AS DisplayProgress, s.TAGS AS Tags, s.CREATEDBYID AS CreatedById, s.CREATEDON AS CreatedOn, s.MODIFIEDBYID AS ModifiedById, s.MODIFIEDON AS ModifiedOn, s.SORTORDER AS SortOrder, s.STATUS AS Status, s.VERSION AS Version, s.SOURCETYPE AS SourceType, s.TENANTID AS TenantId, s.PRIORITY AS Priority, s.THEME AS Theme, s.AUTOADVANCE AS AutoAdvance, s.DISPLAYPOSITION AS DisplayPosition, s.DISPLAYDURATION AS DisplayDuration, s.STARTDATE AS StartDate, s.EXPIRYDATE AS ExpiryDate, s.ISSHOWTEMPLATE AS IsShowTemplate, s.USERGROUPID AS UserGroupID, s.CHANNELID AS ChannelID, s.SUBSCRIPTIONTYPEID AS SubscriptionTypeID, s.APPROVALSTATUS AS ApprovalStatus, s.APPROVEDON AS ApprovedOn, s.APPROVEDBYID AS ApprovedByID, --s.CMSCHANNELID AS CmsChannelId, --TShowStep Table ss.SHOWSTEPID AS ShowStepId, ss.SHOWID AS ShowId, ss.STEPNAME AS StepName, ss.STEPTYPE AS StepType, ss.STEPTEXT AS StepText, ss.MENUID AS MenuId, ss.CONTENTID AS ContentId, ss.STEPORDER AS StepOrder, ss.CONTENT AS Content, ss.ISSTEPTEMPLATE AS IsStepTemplate, ss.STEPDURATION AS StepDuration, ss.ISACTIVE AS IsActive, ss.METADATA AS MetaData, --TShowStepDetail Table ssd.SHOWSTEPDETAILID AS ShowStepDetailId, ssd.SHOWSTEPID AS ShowStepId, ssd.ACTIONTYPE AS ActionType, ssd.BUTTONTEXT AS ButtonText, ssd.ACTIONORDER AS ActionOrder, ssd.TARGETSTEPID AS TargetStepId, ssd.WAITTIME AS WaitTime, ssd.CONDITION AS Condition, ssd.ISDEFAULT AS IsDefault, ssd.BUTTONSTYLE AS ButtonStyle FROM TShow s LEFT JOIN TShowStep ss ON s.SHOWID = ss.SHOWID LEFT JOIN TShowStepDetail ssd ON ss.SHOWSTEPID = ssd.SHOWSTEPID WHERE s.SHOWID = @showid ORDER BY s.SHOWID, ss.SHOWSTEPID, ssd.SHOWSTEPDETAILID;"; public const string GET_SELECTLIST_SHOW = @"SELECT -- TShow s.SHOWID AS ShowId, s.SPACEID AS SpaceId, s.SHOWNATURE AS ShowNature, s.SHOWNAME AS ShowName, s.DISPLAYNAME AS DisplayName, s.DESCRIPTION AS Description, s.OBJECTIVE AS Objective, s.MODULEID AS ModuleId, s.CONTENTCATEGORYID AS ContentCategoryId, s.CONDITIONTYPE AS ConditionType, s.SHOWCONDITIONS AS ShowConditions, s.STARTTYPE AS StartType, s.SHOWTYPE AS ShowType, s.REMEMBERSTAGE AS RememberStage, s.DISPLAYPROGRESS AS DisplayProgress, s.TAGS AS Tags, s.CREATEDBYID AS CreatedById, s.CREATEDON AS CreatedOn, s.MODIFIEDBYID AS ModifiedById, s.MODIFIEDON AS ModifiedOn, s.SORTORDER AS SortOrder, s.STATUS AS Status, s.VERSION AS Version, s.SOURCETYPE AS SourceType, s.TENANTID AS TenantId, s.PRIORITY AS Priority, s.THEME AS Theme, s.AUTOADVANCE AS AutoAdvance, s.DISPLAYPOSITION AS DisplayPosition, s.DISPLAYDURATION AS DisplayDuration, s.STARTDATE AS StartDate, s.EXPIRYDATE AS ExpiryDate, s.ISSHOWTEMPLATE AS IsShowTemplate, s.USERGROUPID AS UserGroupID, s.CHANNELID AS ChannelID, s.SUBSCRIPTIONTYPEID AS SubscriptionTypeID, s.APPROVALSTATUS AS ApprovalStatus, s.APPROVEDON AS ApprovedOn, s.APPROVEDBYID AS ApprovedByID, -- TShowStep ss.SHOWSTEPID AS ShowStepId, ss.STEPNAME AS StepName, ss.STEPTYPE AS StepType, ss.STEPTEXT AS StepText, ss.MENUID AS MenuId, ss.CONTENTID AS ContentId, ss.STEPORDER AS StepOrder, ss.CONTENT AS Content, ss.ISSTEPTEMPLATE AS IsStepTemplate, ss.STEPDURATION AS StepDuration, ss.ISACTIVE AS IsActive, ss.METADATA AS MetaData, -- TShowStepDetail ssd.SHOWSTEPDETAILID AS ShowStepDetailId, ssd.ACTIONTYPE AS ActionType, ssd.BUTTONTEXT AS ButtonText, ssd.BUTTONSTYLE AS ButtonStyle, ssd.ACTIONORDER AS ActionOrder, ssd.TARGETSTEPID AS TargetStepId, ssd.WAITTIME AS WaitTime, ssd.CONDITION AS Condition, ssd.ISDEFAULT AS IsDefault FROM TShow s LEFT JOIN TShowStep ss ON s.SHOWID = ss.SHOWID LEFT JOIN TShowStepDetail ssd ON ss.SHOWSTEPID = ssd.SHOWSTEPID ORDER BY s.SHOWID, ss.SHOWSTEPID, ssd.SHOWSTEPDETAILID;"; public const string SAVE_SHOW = @"INSERT INTO TSHOW ( SPACEID, SHOWNATURE, SHOWNAME, DISPLAYNAME, DESCRIPTION, OBJECTIVE, MODULEID, CONTENTCATEGORYID, CONDITIONTYPE, SHOWCONDITIONS, STARTTYPE, SHOWTYPE, REMEMBERSTAGE, DISPLAYPROGRESS, TAGS, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SORTORDER, STATUS, VERSION, SOURCETYPE, TENANTID, PRIORITY, THEME, AUTOADVANCE, DISPLAYPOSITION, DISPLAYDURATION, STARTDATE, EXPIRYDATE, ISSHOWTEMPLATE, USERGROUPID, CHANNELID, SUBSCRIPTIONTYPEID, APPROVALSTATUS, APPROVEDON, APPROVEDBYID --CMSCHANNELID ) OUTPUT INSERTED.ShowId VALUES ( @SpaceId, @ShowNature, @ShowName, @DisplayName, @Description, @Objective, @ModuleId, @ContentCategoryId, @ConditionType, @ShowConditions, @StartType, @ShowType, @RememberStage, @DisplayProgress, @Tags, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn, @SortOrder, @Status, @Version, @SourceType, @TenantId, @Priority, @Theme, @AutoAdvance, @DisplayPosition, @DisplayDuration, @StartDate, @ExpiryDate, @IsShowTemplate, @UserGroupID, @ChannelID, @SubscriptionTypeID, @ApprovalStatus, @ApprovedOn, @ApprovedByID --@CmsChannelId );"; public const string SAVE_SHOWSTEP = @"INSERT INTO TSHOWSTEP ( SHOWID, STEPNAME, STEPTYPE, STEPTEXT, MENUID, CONTENTID, STEPORDER, ISSTEPTEMPLATE, STEPDURATION, ISACTIVE, CONTENT, [METADATA] ) OUTPUT INSERTED.ShowStepId VALUES ( @ShowId, @StepName, @StepType, @StepText, @MenuId, @ContentId, @StepOrder, @IsStepTemplate, @StepDuration, @IsActive, @Content, @Metadata );"; public const string SAVE_SHOWSTEPDETAIL = @"INSERT INTO TSHOWSTEPDETAIL ( SHOWSTEPID, ACTIONTYPE, BUTTONTEXT, BUTTONSTYLE, ACTIONORDER, TARGETSTEPID, WAITTIME, [CONDITION], ISDEFAULT ) OUTPUT INSERTED.ShowStepDetailId VALUES ( @ShowStepId, @ActionType, @ButtonText, @ButtonStyle, @ActionOrder, @TargetStepId, @WaitTime, @Condition, @IsDefault );"; public const string UPDATE_SHOW = @"UPDATE TSHOW SET SPACEID = @SpaceId, SHOWNATURE = @ShowNature, SHOWNAME = @ShowName, DISPLAYNAME = @DisplayName, DESCRIPTION = @Description, OBJECTIVE = @Objective, MODULEID = @ModuleId, CONTENTCATEGORYID = @ContentCategoryId, CONDITIONTYPE = @ConditionType, SHOWCONDITIONS = @ShowConditions, STARTTYPE = @StartType, SHOWTYPE = @ShowType, REMEMBERSTAGE = @RememberStage, DISPLAYPROGRESS = @DisplayProgress, TAGS = @Tags, MODIFIEDBYID = @ModifiedById, MODIFIEDON = @ModifiedOn, SORTORDER = @SortOrder, STATUS = @Status, VERSION = @Version, SOURCETYPE = @SourceType, TENANTID = @TenantId, PRIORITY = @Priority, THEME = @Theme, AUTOADVANCE = @AutoAdvance, DISPLAYPOSITION = @DisplayPosition, DISPLAYDURATION = @DisplayDuration, STARTDATE = @StartDate, EXPIRYDATE = @ExpiryDate, ISSHOWTEMPLATE = @IsShowTemplate, USERGROUPID = @UserGroupID, CHANNELID = @ChannelID, SUBSCRIPTIONTYPEID = @SubscriptionTypeID, APPROVALSTATUS = @ApprovalStatus, APPROVEDON = @ApprovedOn, APPROVEDBYID = @ApprovedByID --CMSCHANNELID = @CmsChannelId WHERE SHOWID = @ShowId;"; public const string UPDATE_SHOWSTEP = @"UPDATE TShowStep SET SHOWID = @ShowId, STEPNAME = @StepName, STEPTYPE = @StepType, STEPTEXT = @StepText, MENUID = @MenuId, CONTENTID = @ContentId, STEPORDER = @StepOrder, ISSTEPTEMPLATE = @IsStepTemplate, STEPDURATION = @StepDuration, ISACTIVE = @IsActive, CONTENT = @Content, METADATA = @MetaData WHERE ShowStepId = @ShowStepId AND ShowId = @ShowId;"; public const string UPDATE_SHOWSTEPDETAIL = @"UPDATE TShowStepDetail SET SHOWSTEPID = @ShowStepId, ACTIONTYPE = @ActionType, BUTTONTEXT = @ButtonText, BUTTONSTYLE = @ButtonStyle, ACTIONORDER = @ActionOrder, TARGETSTEPID = @TargetStepId, WAITTIME = @WaitTime, CONDITION = @Condition, ISDEFAULT = @IsDefault WHERE ShowStepDetailId = @ShowStepDetailId AND ShowStepId = @ShowStepId;"; public const string DELETE_SHOW = @" DELETE FROM TShow WHERE SHOWID = @ShowId;"; public const string DELETE_SHOWSTEP = @" DELETE FROM TShowStep WHERE SHOWID = @ShowId;"; public const string DELETE_SHOWSTEPDETAIL = @" DELETE FROM TShowStepDetail WHERE SHOWSTEPID = @ShowStepId;"; // ------------------------------------------------------------------------- // Soft delete — TSHOW is referenced by FKs from other Enablement tables // (TSHOWRULE, TSHOWSCHEDULE, TSHOWCONTENTLINK, TSHOWCHANNEL, TSHOWRUN), // none of which cascade. A hard DELETE would violate those constraints, // so retracting a Show sets STATUS = 0 instead. // ------------------------------------------------------------------------- public const string SOFT_DELETE_SHOW = @" UPDATE TSHOW SET STATUS = 0, MODIFIEDBYID = @ModifiedById, MODIFIEDON = @ModifiedOn WHERE SHOWID = @ShowId;"; // Blocks deletion while a user is mid-flow (RUNSTATE = 0 = InProgress). public const string CHECK_ACTIVE_RUNS_FOR_SHOW = @" SELECT COUNT(1) FROM TSHOWRUN WHERE SHOWID = @ShowId AND RUNSTATE = 0;"; // ------------------------------------------------------------------------- // Picklists for the Show authoring form. ContentCategory is owned by CMSDAL // (MCONTENTCATEGORY) and Module by FrameworkDAL (MMODULE) — both referenced // here by raw SQL only, no cross-project type dependency. Space/Channel // (MSHOWSPACE/MSHOWCHANNEL) are now same-project tables, kept here unchanged. // ------------------------------------------------------------------------- public const string GET_MODULE_PICKLIST = @" SELECT MODULEID AS Id, MODULECODE AS Code, MODULENAME AS Name FROM MMODULE;"; public const string GET_SPACE_PICKLIST = @" SELECT SPACEID AS Id, SPACECODE AS Code, SPACENAME AS Name FROM MSHOWSPACE WHERE ISACTIVE = 1 AND STATUS <> 0 AND TENANTID = @TenantId ORDER BY SORTORDER;"; public const string GET_CHANNEL_PICKLIST = @" SELECT CHANNELID AS Id, CHANNELCODE AS Code, CHANNELNAME AS Name FROM MSHOWCHANNEL WHERE ISACTIVE = 1 AND STATUS <> 0 AND TENANTID = @TenantId ORDER BY SORTORDER;"; public const string GET_CONTENTCATEGORY_PICKLIST = @" SELECT CONTENTCATEGORYID AS Id, CONTENTCATEGORYCODE AS Code, CONTENTCATEGORYNAME AS Name FROM MCONTENTCATEGORY WHERE STATUS <> 0 AND TENANTID = @TenantId ORDER BY SORTORDER;"; // NOTE: MUSERGROUP has no TENANTID column (global, not tenant-scoped) — // confirmed against Docs/GB5-Schema-Registry.md, do not add a tenant filter here. public const string GET_USERGROUP_PICKLIST = @" SELECT USERGROUPID AS Id, USERGROUPCODE AS Code, USERGROUPNAME AS Name FROM MUSERGROUP WHERE STATUS <> 0 ORDER BY SORTORDER;"; } }