namespace TCMSDAL.QueryBuilders; /// /// SQL constants for Defect Management and CAPA gap reporting. /// Required index: TTCMSDEFECT(TENANTID, STATUS) INCLUDE (MODULEID, PRIORITY, ROOTCAUSEPATTERNID) /// public static class TCMSDefectQB { // ── Pattern library ─────────────────────────────────────────────────────── public const string GET_DEFECT_PATTERN_LIST = @" SELECT PATTERNID AS PatternId, PATTERNCODE AS PatternCode, DESCRIPTION AS Description, ISACTIVE AS IsActive FROM TTCMSDEFECTPATTERN WHERE TENANTID = @TenantId AND ISACTIVE = 1 ORDER BY PATTERNCODE"; public const string SAVE_DEFECT_PATTERN = @" IF EXISTS (SELECT 1 FROM TTCMSDEFECTPATTERN WHERE PATTERNID = @PatternId AND TENANTID = @TenantId) UPDATE TTCMSDEFECTPATTERN SET PATTERNCODE = @PatternCode, DESCRIPTION = @Description, ISACTIVE = @IsActive, MODIFIEDBYID = @ModifiedById, MODIFIEDON = GETDATE() WHERE PATTERNID = @PatternId AND TENANTID = @TenantId ELSE INSERT INTO TTCMSDEFECTPATTERN (PATTERNCODE, DESCRIPTION, ISACTIVE, TENANTID, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON) VALUES (@PatternCode, @Description, @IsActive, @TenantId, @CreatedById, GETDATE(), @CreatedById, GETDATE())"; // ── Defect CRUD ─────────────────────────────────────────────────────────── public const string GET_DEFECT = @" SELECT d.DEFECTID AS DefectId, d.TITLE AS Title, d.MODULEID AS ModuleId, m.MODULENAME AS ModuleName, d.ENTITY AS Entity, d.OPERATION AS Operation, d.STEPSTOPRODUCE AS StepsToReproduce, d.EXPECTEDRESULT AS ExpectedResult, d.ACTUALRESULT AS ActualResult, d.PRIORITY AS Priority, d.ROOTCAUSEPATTERNID AS RootCausePatternId, p.PATTERNCODE AS PatternCode, d.REQUIRESRCA AS RequiresRca, d.STATUS AS Status, d.LINKEDTESTCASEID AS LinkedTestCaseId, tc.TESTCASECODE AS LinkedTestCaseCode, d.TENANTID AS TenantId, d.CREATEDBYID AS CreatedById, d.CREATEDON AS CreatedOn, d.MODIFIEDBYID AS ModifiedById, d.MODIFIEDON AS ModifiedOn FROM TTCMSDEFECT d JOIN MTCMSMODULE m ON d.MODULEID = m.MODULEID LEFT JOIN TTCMSDEFECTPATTERN p ON d.ROOTCAUSEPATTERNID = p.PATTERNID LEFT JOIN MTCMSTESTCASE tc ON d.LINKEDTESTCASEID = tc.TESTCASEID WHERE d.DEFECTID = @DefectId AND d.TENANTID = @TenantId"; public const string GET_DEFECT_LIST = @" SELECT d.DEFECTID AS DefectId, d.TITLE AS Title, m.MODULENAME AS ModuleName, d.ENTITY AS Entity, d.PRIORITY AS Priority, d.STATUS AS Status, p.PATTERNCODE AS PatternCode, tc.TESTCASECODE AS LinkedTestCaseCode, d.REQUIRESRCA AS RequiresRca, d.CREATEDON AS CreatedOn FROM TTCMSDEFECT d JOIN MTCMSMODULE m ON d.MODULEID = m.MODULEID LEFT JOIN TTCMSDEFECTPATTERN p ON d.ROOTCAUSEPATTERNID = p.PATTERNID LEFT JOIN MTCMSTESTCASE tc ON d.LINKEDTESTCASEID = tc.TESTCASEID WHERE d.TENANTID = @TenantId AND (@ModuleId IS NULL OR d.MODULEID = @ModuleId) AND (@Status IS NULL OR d.STATUS = @Status) AND (@RootCausePatternId IS NULL OR d.ROOTCAUSEPATTERNID = @RootCausePatternId) ORDER BY d.CREATEDON DESC"; public const string INSERT_DEFECT = @" INSERT INTO TTCMSDEFECT (TITLE, MODULEID, ENTITY, OPERATION, STEPSTOPRODUCE, EXPECTEDRESULT, ACTUALRESULT, PRIORITY, ROOTCAUSEPATTERNID, REQUIRESRCA, STATUS, LINKEDTESTCASEID, TENANTID, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON) VALUES (@Title, @ModuleId, @Entity, @Operation, @StepsToReproduce, @ExpectedResult, @ActualResult, @Priority, @RootCausePatternId, @RequiresRca, 'Open', @LinkedTestCaseId, @TenantId, @CreatedById, GETDATE(), @CreatedById, GETDATE()); SELECT SCOPE_IDENTITY();"; public const string UPDATE_DEFECT = @" UPDATE TTCMSDEFECT SET STATUS = @Status, ROOTCAUSEPATTERNID = @RootCausePatternId, REQUIRESRCA = @RequiresRca, LINKEDTESTCASEID = @LinkedTestCaseId, MODIFIEDBYID = @ModifiedById, MODIFIEDON = GETDATE() WHERE DEFECTID = @DefectId AND TENANTID = @TenantId"; // ── CAPA gap report ─────────────────────────────────────────────────────── public const string GET_CAPA_GAP_REPORT = @" SELECT p.PATTERNCODE AS PatternCode, p.DESCRIPTION AS PatternDesc, COUNT(d.DEFECTID) AS DefectCount, m.MODULENAME AS ModuleName, m.MODULEID AS ModuleId, CASE WHEN cov.MODULEID IS NOT NULL THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END AS HasCoverage, ISNULL(cov.CoveredCount, 0) AS CoveredCount FROM TTCMSDEFECTPATTERN p JOIN TTCMSDEFECT d ON d.ROOTCAUSEPATTERNID = p.PATTERNID AND d.TENANTID = @TenantId CROSS JOIN MTCMSMODULE m LEFT JOIN ( SELECT tc.MODULEID, tc.ROOTCAUSEPATTERNID, COUNT(*) AS CoveredCount FROM MTCMSTESTCASE tc WHERE tc.ROOTCAUSEPATTERNID IS NOT NULL AND tc.TENANTID = @TenantId GROUP BY tc.MODULEID, tc.ROOTCAUSEPATTERNID ) cov ON cov.MODULEID = m.MODULEID AND cov.ROOTCAUSEPATTERNID = p.PATTERNID WHERE p.TENANTID = @TenantId AND p.ISACTIVE = 1 GROUP BY p.PATTERNCODE, p.DESCRIPTION, m.MODULENAME, m.MODULEID, cov.MODULEID, cov.CoveredCount ORDER BY DefectCount DESC, m.MODULENAME"; // ── Schema (one-time setup) ─────────────────────────────────────────────── public const string CREATE_DEFECT_TABLES = @" IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TTCMSDEFECTPATTERN') BEGIN CREATE TABLE TTCMSDEFECTPATTERN ( PATTERNID INT IDENTITY(1,1) PRIMARY KEY, PATTERNCODE NVARCHAR(60) NOT NULL, DESCRIPTION NVARCHAR(200) NOT NULL, ISACTIVE BIT NOT NULL DEFAULT 1, TENANTID INT NOT NULL, CREATEDBYID INT NOT NULL, CREATEDON DATETIME NOT NULL DEFAULT GETDATE(), MODIFIEDBYID INT NOT NULL, MODIFIEDON DATETIME NOT NULL DEFAULT GETDATE(), CONSTRAINT UQ_DEFECTPATTERN_CODE UNIQUE (TENANTID, PATTERNCODE) ); END IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TTCMSDEFECT') BEGIN CREATE TABLE TTCMSDEFECT ( DEFECTID INT IDENTITY(1,1) PRIMARY KEY, TITLE NVARCHAR(200) NOT NULL, MODULEID INT NOT NULL, ENTITY NVARCHAR(100) NOT NULL DEFAULT '', OPERATION NVARCHAR(60) NOT NULL DEFAULT '', STEPSTOPRODUCE NVARCHAR(MAX) NOT NULL DEFAULT '', EXPECTEDRESULT NVARCHAR(MAX) NOT NULL DEFAULT '', ACTUALRESULT NVARCHAR(MAX) NOT NULL DEFAULT '', PRIORITY NVARCHAR(5) NOT NULL DEFAULT 'P2', ROOTCAUSEPATTERNID INT NULL, REQUIRESRCA BIT NOT NULL DEFAULT 0, STATUS NVARCHAR(20) NOT NULL DEFAULT 'Open', LINKEDTESTCASEID INT NULL, TENANTID INT NOT NULL, CREATEDBYID INT NOT NULL, CREATEDON DATETIME NOT NULL DEFAULT GETDATE(), MODIFIEDBYID INT NOT NULL, MODIFIEDON DATETIME NOT NULL DEFAULT GETDATE(), CONSTRAINT FK_DEFECT_MODULE FOREIGN KEY (MODULEID) REFERENCES MTCMSMODULE(MODULEID), CONSTRAINT FK_DEFECT_PATTERN FOREIGN KEY (ROOTCAUSEPATTERNID) REFERENCES TTCMSDEFECTPATTERN(PATTERNID), CONSTRAINT FK_DEFECT_TESTCASE FOREIGN KEY (LINKEDTESTCASEID) REFERENCES MTCMSTESTCASE(TESTCASEID) ); CREATE INDEX IX_TTCMSDEFECT_TENANT_STATUS ON TTCMSDEFECT (TENANTID, STATUS) INCLUDE (MODULEID, PRIORITY, ROOTCAUSEPATTERNID); END"; }