namespace TCMSDAL.QueryBuilders; /// /// SQL constants for TTCMSTESTRUN + TTCMSRUNRESULT. /// Required indexes: /// IX_TTCMSTESTRUN_PROJECT (TENANTID, PROJECTID, STATUS) /// IX_TTCMSRUNRESULT_RUN (TESTRUNID) /// IX_TTCMSRUNRESULT_TC (TESTCASEID, EXECUTEDAT DESC) /// public static class TCMSTestRunQB { // ── TestRun ────────────────────────────────────────────────────────────── public const string GET_TESTRUN = @" SELECT TR.TESTRUNID AS TestRunId, TR.RUNCODE AS RunCode, TR.RUNNAME AS RunName, TR.PROJECTID AS ProjectId, P.PROJECTNAME AS ProjectName, TR.MODULEID AS ModuleId, M.MODULENAME AS ModuleName, TR.RUNTYPE AS RunType, TR.SUITETYPE AS SuiteType, TR.STATUS AS Status, TR.LEADEXECUTOR AS LeadExecutor, TR.SCHEDULEDSTART AS ScheduledStart, TR.SCHEDULEDEND AS ScheduledEnd, TR.ACTUALSTART AS ActualStart, TR.ACTUALEND AS ActualEnd, ISNULL(AGG.TOTAL, 0) AS TotalCases, ISNULL(AGG.PASSED, 0) AS PassedCount, ISNULL(AGG.FAILED, 0) AS FailedCount, ISNULL(AGG.SKIPPED, 0) AS SkippedCount, ISNULL(AGG.BLOCKED, 0) AS BlockedCount, ISNULL(AGG.PASSPCT, 0) AS PassPct, TR.TENANTID AS TenantId, TR.CREATEDBY AS CreatedBy, TR.CREATEDDATE AS CreatedDate FROM TTCMSTESTRUN TR JOIN MTCMSPROJECT P ON P.PROJECTID = TR.PROJECTID LEFT JOIN MTCMSMODULE M ON M.MODULEID = TR.MODULEID OUTER APPLY ( SELECT COUNT(*) AS TOTAL, SUM(CASE WHEN RESULT = 'Pass' THEN 1 ELSE 0 END) AS PASSED, SUM(CASE WHEN RESULT = 'Fail' THEN 1 ELSE 0 END) AS FAILED, SUM(CASE WHEN RESULT = 'Skip' THEN 1 ELSE 0 END) AS SKIPPED, SUM(CASE WHEN RESULT = 'Blocked' THEN 1 ELSE 0 END) AS BLOCKED, CAST(SUM(CASE WHEN RESULT = 'Pass' THEN 1.0 ELSE 0 END) / NULLIF(COUNT(*),0)*100 AS DECIMAL(5,2)) AS PASSPCT FROM TTCMSRUNRESULT WHERE TESTRUNID = TR.TESTRUNID ) AGG WHERE TR.TESTRUNID = @TestRunId AND TR.TENANTID = @TenantId"; public const string GET_TESTRUN_LIST = @" SELECT TR.TESTRUNID AS TestRunId, TR.RUNCODE AS RunCode, TR.RUNNAME AS RunName, TR.RUNTYPE AS RunType, TR.SUITETYPE AS SuiteType, TR.STATUS AS Status, P.PROJECTNAME AS ProjectName, M.MODULENAME AS ModuleName, ISNULL(AGG.TOTAL, 0) AS TotalCases, ISNULL(AGG.PASSPCT, 0) AS PassPct, TR.ACTUALSTART AS ActualStart, TR.LEADEXECUTOR AS LeadExecutor FROM TTCMSTESTRUN TR JOIN MTCMSPROJECT P ON P.PROJECTID = TR.PROJECTID AND P.TENANTID = TR.TENANTID LEFT JOIN MTCMSMODULE M ON M.MODULEID = TR.MODULEID OUTER APPLY ( SELECT COUNT(*) AS TOTAL, CAST(SUM(CASE WHEN RESULT='Pass' THEN 1.0 ELSE 0 END)/NULLIF(COUNT(*),0)*100 AS DECIMAL(5,2)) AS PASSPCT FROM TTCMSRUNRESULT WHERE TESTRUNID = TR.TESTRUNID ) AGG WHERE TR.TENANTID = @TenantId AND (@ProjectId IS NULL OR TR.PROJECTID = @ProjectId) AND (@ModuleId IS NULL OR TR.MODULEID = @ModuleId) AND (@RunType IS NULL OR TR.RUNTYPE = @RunType) AND (@Status IS NULL OR TR.STATUS = @Status) ORDER BY TR.CREATEDDATE DESC"; public const string SAVE_TESTRUN = @" INSERT INTO TTCMSTESTRUN (RUNCODE, RUNNAME, PROJECTID, MODULEID, RUNTYPE, SUITETYPE, STATUS, LEADEXECUTOR, SCHEDULEDSTART, SCHEDULEDEND, TENANTID, CREATEDBY, CREATEDDATE) VALUES (@RunCode, @RunName, @ProjectId, @ModuleId, @RunType, @SuiteType, @Status, @LeadExecutor, @ScheduledStart, @ScheduledEnd, @TenantId, @CreatedBy, GETUTCDATE())"; public const string UPDATE_TESTRUN_STATUS = @" UPDATE TTCMSTESTRUN SET STATUS = @Status, ACTUALSTART = CASE WHEN @Status = 'InProgress' THEN GETUTCDATE() ELSE ACTUALSTART END, ACTUALEND = CASE WHEN @Status IN ('Completed','Cancelled') THEN GETUTCDATE() ELSE ACTUALEND END, MODIFIEDBY = @ModifiedBy, MODIFIEDDATE = GETUTCDATE() WHERE TESTRUNID = @TestRunId AND TENANTID = @TenantId"; // ── RunResult ──────────────────────────────────────────────────────────── public const string GET_RUN_RESULTS = @" SELECT RR.RESULTID AS ResultId, RR.TESTRUNID AS TestRunId, RR.TESTCASEID AS TestCaseId, TC.TCID AS TcId, TC.TITLE AS TestCaseTitle, RR.RESULT AS Result, RR.FAILUREREASON AS FailureReason, RR.SCREENSHOT AS Screenshot, RR.NOTES AS Notes, RR.EXECUTIONMS AS ExecutionMs, RR.EXECUTEDBY AS ExecutedBy, RR.EXECUTEDAT AS ExecutedAt, RR.TENANTID AS TenantId FROM TTCMSRUNRESULT RR JOIN MTCMSTESTCASE TC ON TC.TESTCASEID = RR.TESTCASEID WHERE RR.TESTRUNID = @TestRunId AND RR.TENANTID = @TenantId ORDER BY TC.TCID"; public const string SAVE_RUN_RESULT = @" INSERT INTO TTCMSRUNRESULT (TESTRUNID, TESTCASEID, RESULT, FAILUREREASON, SCREENSHOT, NOTES, EXECUTIONMS, EXECUTEDBY, EXECUTEDAT, TENANTID) VALUES (@TestRunId, @TestCaseId, @Result, @FailureReason, @Screenshot, @Notes, @ExecutionMs, @ExecutedBy, GETUTCDATE(), @TenantId)"; public const string UPSERT_RUN_RESULT = @" MERGE TTCMSRUNRESULT AS T USING (SELECT @TestRunId AS TESTRUNID, @TestCaseId AS TESTCASEID, @TenantId AS TENANTID) AS S ON (T.TESTRUNID = S.TESTRUNID AND T.TESTCASEID = S.TESTCASEID AND T.TENANTID = S.TENANTID) WHEN MATCHED THEN UPDATE SET RESULT = @Result, FAILUREREASON = @FailureReason, NOTES = @Notes, EXECUTIONMS = @ExecutionMs, EXECUTEDBY = @ExecutedBy, EXECUTEDAT = GETUTCDATE() WHEN NOT MATCHED THEN INSERT (TESTRUNID, TESTCASEID, RESULT, FAILUREREASON, SCREENSHOT, NOTES, EXECUTIONMS, EXECUTEDBY, EXECUTEDAT, TENANTID) VALUES (@TestRunId, @TestCaseId, @Result, @FailureReason, @Screenshot, @Notes, @ExecutionMs, @ExecutedBy, GETUTCDATE(), @TenantId);"; // ── Dashboard ──────────────────────────────────────────────────────────── public const string GET_DASHBOARD = @" SELECT M.MODULECODE AS ModuleCode, M.MODULENAME AS ModuleName, COUNT(DISTINCT TR.TESTRUNID) AS TotalRuns, CAST(AVG(CAST(AGG.PASSPCT AS FLOAT)) AS DECIMAL(5,2)) AS AvgPassPct, MAX(CASE WHEN TR.TESTRUNID = LAST_RUN.LASTRUNID THEN AGG.PASSED END) AS LastRunPass, MAX(CASE WHEN TR.TESTRUNID = LAST_RUN.LASTRUNID THEN AGG.FAILED END) AS LastRunFail, MAX(CASE WHEN TR.TESTRUNID = LAST_RUN.LASTRUNID THEN AGG.SKIPPED END) AS LastRunSkip, MAX(TR.ACTUALSTART) AS LastRunDate FROM MTCMSMODULE M JOIN TTCMSTESTRUN TR ON TR.MODULEID = M.MODULEID AND TR.TENANTID = M.TENANTID OUTER APPLY ( SELECT COUNT(*) AS TOTAL, SUM(CASE WHEN RESULT='Pass' THEN 1 ELSE 0 END) AS PASSED, SUM(CASE WHEN RESULT='Fail' THEN 1 ELSE 0 END) AS FAILED, SUM(CASE WHEN RESULT='Skip' THEN 1 ELSE 0 END) AS SKIPPED, CAST(SUM(CASE WHEN RESULT='Pass' THEN 1.0 ELSE 0 END)/NULLIF(COUNT(*),0)*100 AS DECIMAL(5,2)) AS PASSPCT FROM TTCMSRUNRESULT WHERE TESTRUNID = TR.TESTRUNID ) AGG CROSS APPLY ( SELECT TOP 1 TESTRUNID AS LASTRUNID FROM TTCMSTESTRUN WHERE MODULEID = M.MODULEID AND TENANTID = M.TENANTID ORDER BY ACTUALSTART DESC ) LAST_RUN WHERE M.TENANTID = @TenantId AND (@ProjectId IS NULL OR TR.PROJECTID = @ProjectId) GROUP BY M.MODULECODE, M.MODULENAME ORDER BY AvgPassPct"; // ── Auto-number helper ─────────────────────────────────────────────────── public const string GET_NEXT_RUN_SEQUENCE = @" SELECT ISNULL(MAX(CAST(SUBSTRING(RUNCODE, 9, 10) AS INT)), 0) + 1 FROM TTCMSTESTRUN WHERE TENANTID = @TenantId AND RUNCODE LIKE 'RUN-' + CAST(YEAR(GETUTCDATE()) AS VARCHAR) + '-%'"; }