namespace WiDAL.Query.Wi; public class WiAssignmentQB { // Index: IX_MWIASSIGNMENT_DIMENSIONS on (TENANTID, ROUTINGOPERATIONID, WORKCENTERID, WORKSTATIONID, MODELID, ITEMGROUPID, ISACTIVE) //public const string GET_BY_WI = @" // SELECT a.WIASSIGNMENTID, a.WIID, a.ROUTINGID, a.ROUTINGOPERATIONID, // a.WORKCENTERID, a.WORKSTATIONID, a.ITEMGROUPID, a.MODELID, a.LINEID, // a.SPECIFICITYSCORE, a.EFFECTIVEFROM, a.EFFECTIVETO, a.REMARKS, // CAST(a.ISACTIVE AS BIT) AS IsActive, // a.VERSION, a.STATUS, a.SORTORDER, a.CREATEDBYID, a.MODIFIEDBYID, a.SOURCETYPE, a.TENANTID,a.WIASSIGNMENTNAME // FROM WI.MWIASSIGNMENT a // WHERE a.WIASSIGNMENTID = @wiAssignmentId // AND a.TENANTID = @TenantId // AND a.STATUS = 1"; public const string GET_BY_ID = @"SELECT a.WIASSIGNMENTID, a.WIASSIGNMENTNAME AS AssignmentName, a.WIID AS WiId, wi.WICODE AS WiCode, wi.WITITLE AS WiTitle, a.ROUTINGID AS RoutingId, r.ROUTINGNAME AS RoutingName, r.ROUTINGCODE AS RoutingCode, a.ROUTINGOPERATIONID AS RoutingOperationId, a.WORKCENTERID AS WorkCenterId, wc.WORKCENTERCODE AS WorkCenterCode, wc.WORKCENTERNAME AS WorkCenterName, a.WORKSTATIONID AS WorkStationId, ws.WORKSTATIONNAME AS WorkStationName, ws.WORKSTATIONCODE AS WorkStationCode, a.ITEMGROUPID AS ItemGroupId, ig.ITEMGROUPNAME AS ItemGroupName, ig.ITEMGROUPCODE AS ItemGroupCode, a.MODELID AS ModelId, m.MODELCODE AS ModelCode, m.MODELNAME AS ModelName, a.LINEID AS LineId, pl.LINECODE AS LineCode, pl.LINENAME AS LineName, a.ENTITYTYPE AS EntityType, a.ENTITYID AS EntityId, a.SPECIFICITYSCORE AS SpecificityScore, a.EFFECTIVEFROM AS EffectiveFrom, a.EFFECTIVETO AS EffectiveTo, a.REMARKS AS Remarks, a.VERSION AS Version, a.STATUS AS Status, a.SORTORDER AS SortOrder, a.CREATEDBYID AS CreatedById, cu.USERNAME AS CreatedByName, a.CREATEDON, a.MODIFIEDBYID AS ModifiedById, mu.USERNAME AS ModifiedByName, a.MODIFIEDON, a.SOURCETYPE AS SourceType, a.TENANTID AS TenantId FROM WI.MWIASSIGNMENT a LEFT JOIN WI.MWI wi ON wi.WIID = a.WIID LEFT JOIN MROUTING r ON r.ROUTINGID = a.ROUTINGID LEFT JOIN MROUTINGOPERATION ro ON ro.ROUTINGOPERATIONID = a.ROUTINGOPERATIONID LEFT JOIN DBO.MWORKCENTER wc ON wc.WORKCENTERID = a.WORKCENTERID LEFT JOIN DBO.MWORKSTATION ws ON ws.WORKSTATIONID = a.WORKSTATIONID LEFT JOIN MITEMGROUP ig ON ig.ITEMGROUPID = a.ITEMGROUPID LEFT JOIN MMODEL m ON m.MODELID = a.MODELID LEFT JOIN MPRODUCTIONLINE pl ON pl.LINEID = a.LINEID LEFT JOIN MUSER cu ON cu.USERID = a.CREATEDBYID LEFT JOIN MUSER mu ON mu.USERID = a.MODIFIEDBYID LEFT JOIN MCLIENT c ON c.CLIENTID = a.TENANTID WHERE a.WIASSIGNMENTID = @wiAssignmentId AND a.TENANTID = @TenantId AND a.STATUS = 1;"; public const string GET_SCHEDULES_BY_ASSIGNMENT = @" SELECT WIACTIVITYSCHEDULEID, WIASSIGNMENTID, ACTIVITYTYPE, TRIGGEREVENT, TRIGGERFREQUENCYN, EXECUTIONORDER, CAST(ISBLOCKINGNEXT AS BIT) AS IsBlockingNext, CAST(REQUIRESSUPERVISOR AS BIT) AS RequiresSupervisor, CAST(ALLOWSKIP AS BIT) AS AllowSkip, CAST(ISACTIVE AS BIT) AS IsActive, REMARKS FROM WI.MWIACTIVITYSCHEDULE WHERE WIASSIGNMENTID = @wiAssignmentId AND ISACTIVE = 1 ORDER BY EXECUTIONORDER"; public const string INSERT_ASSIGNMENT = @" INSERT INTO WI.MWIASSIGNMENT (WIASSIGNMENTID, WIID, ROUTINGID, ROUTINGOPERATIONID, WORKCENTERID, WORKSTATIONID, ITEMGROUPID, MODELID, LINEID, ENTITYTYPE, ENTITYID, SPECIFICITYSCORE, EFFECTIVEFROM, EFFECTIVETO, REMARKS, VERSION, STATUS, SORTORDER, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SOURCETYPE, TENANTID,WIASSIGNMENTNAME) VALUES (@WiAssignmentId, @WiId, @RoutingId, @RoutingOperationId, @WorkCenterId, @WorkStationId, @ItemGroupId, @ModelId, @LineId, @EntityType, @EntityId, @SpecificityScore, @EffectiveFrom, @EffectiveTo, @Remarks, @Version, @Status, @SortOrder, @CreatedById, GETDATE(), @ModifiedById, GETDATE(), @SourceType, @TenantId, @AssignmentName)"; public const string UPDATE_ASSIGNMENT = @" UPDATE WI.MWIASSIGNMENT SET ROUTINGID = @RoutingId, ROUTINGOPERATIONID = @RoutingOperationId, WORKCENTERID = @WorkCenterId, WORKSTATIONID = @WorkStationId, ITEMGROUPID = @ItemGroupId, MODELID = @ModelId, LINEID = @LineId, ENTITYTYPE = @EntityType, ENTITYID = @EntityId, SPECIFICITYSCORE = @SpecificityScore, EFFECTIVEFROM = @EffectiveFrom, EFFECTIVETO = @EffectiveTo, REMARKS = @Remarks, STATUS = @Status, VERSION = @Version, MODIFIEDBYID = @ModifiedById, MODIFIEDON = GETDATE(), WIASSIGNMENTNAME = @AssignmentName WHERE WIASSIGNMENTID = @WiAssignmentId AND TENANTID = @TenantId"; public const string DELETE_SCHEDULES_BY_ASSIGNMENT = @" DELETE FROM WI.MWIACTIVITYSCHEDULE WHERE WIASSIGNMENTID = @WiAssignmentId"; public const string INSERT_SCHEDULE = @" INSERT INTO WI.MWIACTIVITYSCHEDULE (WIACTIVITYSCHEDULEID, WIASSIGNMENTID, ACTIVITYTYPE, TRIGGEREVENT, TRIGGERFREQUENCYN, EXECUTIONORDER, ISBLOCKINGNEXT, REQUIRESSUPERVISOR, ALLOWSKIP, ISACTIVE, REMARKS) VALUES (@WiActivityScheduleId, @WiAssignmentId, @ActivityType, @TriggerEvent, @TriggerFrequencyN, @ExecutionOrder, @IsBlockingNext, @RequiresSupervisor, @AllowSkip, @IsActive, @Remarks)"; // Index: IX_MWIASSIGNMENT_DIMENSIONS public const string RESOLVE_FOR_WORKSTATION = @" SELECT a.WIASSIGNMENTID, a.WIID, a.SPECIFICITYSCORE, a.ROUTINGID AS RoutingId, a.ROUTINGOPERATIONID AS RoutingOperationId, a.WORKCENTERID AS WorkCenterId, a.WORKSTATIONID AS WorkStationId, a.MODELID AS ModelId, a.ITEMGROUPID AS ItemGroupId, sch.WIACTIVITYSCHEDULEID, sch.ACTIVITYTYPE, sch.TRIGGEREVENT, sch.TRIGGERFREQUENCYN, sch.EXECUTIONORDER, CAST(sch.ISBLOCKINGNEXT AS BIT) AS IsBlockingNext, CAST(sch.REQUIRESSUPERVISOR AS BIT) AS RequiresSupervisor, CAST(sch.ALLOWSKIP AS BIT) AS AllowSkip FROM WI.MWIASSIGNMENT a JOIN WI.MWIACTIVITYSCHEDULE sch ON sch.WIASSIGNMENTID = a.WIASSIGNMENTID WHERE a.TENANTID = @TenantId AND a.STATUS = 1 AND sch.ISACTIVE = 1 AND sch.TRIGGEREVENT = @TriggerEvent AND (a.WORKSTATIONID = @WorkStationId OR a.WORKSTATIONID = -1) AND (a.WORKCENTERID = @WorkCenterId OR a.WORKCENTERID = -1) AND (a.ROUTINGID = @RoutingId OR a.ROUTINGID = -1) AND (a.ROUTINGOPERATIONID = @RoutingOpId OR a.ROUTINGOPERATIONID = -1) AND (a.MODELID = @ModelId OR a.MODELID = -1) AND (a.ITEMGROUPID = @ItemGroupId OR a.ITEMGROUPID = -1) AND (a.EFFECTIVEFROM IS NULL OR a.EFFECTIVEFROM <= CAST(GETDATE() AS DATE)) AND (a.EFFECTIVETO IS NULL OR a.EFFECTIVETO >= CAST(GETDATE() AS DATE)) ORDER BY a.SPECIFICITYSCORE DESC, sch.EXECUTIONORDER ASC"; // Zero-match diagnostic — same dimension WHERE as RESOLVE_FOR_WORKSTATION, but no schedule // join and no effective-date filter, so the BLL can tell apart "no assignment matches these // dimensions at all" from "one matches but is outside its effective window or has no // schedule for this trigger." Only run on the zero-match path — never the hot path. public const string DIAGNOSE_FOR_WORKSTATION = @" SELECT a.WIASSIGNMENTID AS WiAssignmentId, a.EFFECTIVEFROM, a.EFFECTIVETO FROM WI.MWIASSIGNMENT a WHERE a.TENANTID = @TenantId AND a.STATUS = 1 AND (a.WORKSTATIONID = @WorkStationId OR a.WORKSTATIONID = -1) AND (a.WORKCENTERID = @WorkCenterId OR a.WORKCENTERID = -1) AND (a.ROUTINGID = @RoutingId OR a.ROUTINGID = -1) AND (a.ROUTINGOPERATIONID = @RoutingOpId OR a.ROUTINGOPERATIONID = -1) AND (a.MODELID = @ModelId OR a.MODELID = -1) AND (a.ITEMGROUPID = @ItemGroupId OR a.ITEMGROUPID = -1)"; // Generic path — any non-manufacturing consumer (Quality, HR, Compliance, FM) resolving // a WI attached via (EntityType, EntityId) instead of Routing/WorkCenter/Workstation. // Index: IX_MWIASSIGNMENT_ENTITY on (ENTITYTYPE, ENTITYID, TENANTID) public const string RESOLVE_FOR_ENTITY = @" SELECT a.WIASSIGNMENTID, a.WIID, a.SPECIFICITYSCORE, a.ROUTINGID AS RoutingId, a.ROUTINGOPERATIONID AS RoutingOperationId, a.WORKCENTERID AS WorkCenterId, a.WORKSTATIONID AS WorkStationId, a.MODELID AS ModelId, a.ITEMGROUPID AS ItemGroupId, a.ENTITYTYPE AS EntityType, a.ENTITYID AS EntityId, sch.WIACTIVITYSCHEDULEID, sch.ACTIVITYTYPE, sch.TRIGGEREVENT, sch.TRIGGERFREQUENCYN, sch.EXECUTIONORDER, CAST(sch.ISBLOCKINGNEXT AS BIT) AS IsBlockingNext, CAST(sch.REQUIRESSUPERVISOR AS BIT) AS RequiresSupervisor, CAST(sch.ALLOWSKIP AS BIT) AS AllowSkip FROM WI.MWIASSIGNMENT a JOIN WI.MWIACTIVITYSCHEDULE sch ON sch.WIASSIGNMENTID = a.WIASSIGNMENTID WHERE a.TENANTID = @TenantId AND a.STATUS = 1 AND sch.ISACTIVE = 1 AND sch.TRIGGEREVENT = @TriggerEvent AND a.ENTITYTYPE = @EntityType AND (a.ENTITYID = @EntityId OR a.ENTITYID = -1) AND (a.EFFECTIVEFROM IS NULL OR a.EFFECTIVEFROM <= CAST(GETDATE() AS DATE)) AND (a.EFFECTIVETO IS NULL OR a.EFFECTIVETO >= CAST(GETDATE() AS DATE)) ORDER BY a.SPECIFICITYSCORE DESC, sch.EXECUTIONORDER ASC"; // Zero-match diagnostic for RESOLVE_FOR_ENTITY — see DIAGNOSE_FOR_WORKSTATION. public const string DIAGNOSE_FOR_ENTITY = @" SELECT a.WIASSIGNMENTID AS WiAssignmentId, a.EFFECTIVEFROM, a.EFFECTIVETO FROM WI.MWIASSIGNMENT a WHERE a.TENANTID = @TenantId AND a.STATUS = 1 AND a.ENTITYTYPE = @EntityType AND (a.ENTITYID = @EntityId OR a.ENTITYID = -1)"; public const string GET_SELECTLIST_WIASSIGNMENT = @"SELECT WIASSIGNMENTID AS Id, WIASSIGNMENTNAME AS Name FROM WI.MWIASSIGNMENT ORDER BY WIASSIGNMENTNAME;"; // Cascade child first — MWIACTIVITYSCHEDULE.WIASSIGNMENTID FKs to this table, so any // assignment with a schedule (the normal case — SaveAssignment always writes at least // one) 547'd on a bare DELETE. Deliberately NOT cascading further into TWIEXECUTION — // if real execution history exists against one of this assignment's schedules, the // delete should still fail rather than silently destroy audit data. public const string DELETE_CASCADE_ACTIVITY_SCHEDULES = @" DELETE FROM WI.MWIACTIVITYSCHEDULE WHERE WIASSIGNMENTID = @wiAssignmentId"; public const string DELETE_WIASSIGNMENT = @"DELETE FROM WI.MWIASSIGNMENT WHERE WIASSIGNMENTID = @wiAssignmentId;"; public const string GET_ASSIGNMENT_LIST = @"SELECT wa.WIASSIGNMENTID AS WiAssignmentId, wa.WIID AS WiId, wi.WICODE AS WiCode, wa.ROUTINGID AS RoutingId, r.ROUTINGCODE AS RoutingCode, r.ROUTINGNAME AS RoutingName, wa.ROUTINGOPERATIONID AS RoutingOperationId, wa.WORKCENTERID AS WorkCenterId, wa.WORKSTATIONID AS WorkStationId, ws.WORKSTATIONCODE AS WorkStationCode, ws.WORKSTATIONNAME AS WorkStationName, wa.ITEMGROUPID AS ItemGroupId, wa.MODELID AS ModelId, wa.LINEID AS LineId, wa.ENTITYTYPE AS EntityType, wa.ENTITYID AS EntityId, wa.SPECIFICITYSCORE AS SpecificityScore, wa.EFFECTIVEFROM AS EffectiveFrom, wa.EFFECTIVETO AS EffectiveTo, wa.REMARKS AS Remarks, wa.VERSION AS Version, wa.STATUS AS Status, wa.SORTORDER AS SortOrder, wa.CREATEDBYID AS CreatedById, wa.CREATEDON AS CreatedOn, wa.MODIFIEDBYID AS ModifiedById, wa.MODIFIEDON AS ModifiedOn, wa.SOURCETYPE AS SourceType, wa.TENANTID AS TenantId, wa.WIASSIGNMENTNAME AS AssignmentName FROM WI.MWIASSIGNMENT wa INNER JOIN WI.MWI wi ON wi.WIID = wa.WIID LEFT JOIN MROUTING r ON r.ROUTINGID = wa.ROUTINGID LEFT JOIN MROUTINGOPERATION ro ON ro.ROUTINGOPERATIONID = wa.ROUTINGOPERATIONID LEFT JOIN DBO.MWORKSTATION ws ON ws.WORKSTATIONID = wa.WORKSTATIONID;"; }