namespace FrameworkDAL.Query.TestRun
{
    public static class TestRunQB
    {
        // Criteria (ApplyCriteria) is appended directly after "WHERE 1 = 1" below,
        // so this constant must NOT close the FilteredTestRun CTE.
        public const string GET_SELECTLIST_TESTRUN_BASE = @"
    WITH FilteredTestRun AS (
        SELECT
            TR.TESTRUNID      AS Id,
            TR.TESTRUNNUMBER  AS Number,
            TR.TESTRUNDATE    AS RunDate,
            TR.TESTRUNDB      AS DB,
            TR.TESTRUNBYID    AS ById,
            U.USERCODE        AS ByCode,
            U.USERNAME        AS ByName,
            TR.TESTRUNREMARKS AS Remarks,
            TR.TESTSETID      AS TestSetId,
            TS.TESTSETNAME    AS TestSetName
        FROM MTESTRUN TR
        LEFT JOIN MUSER    U  ON U.USERID     = TR.TESTRUNBYID
        LEFT JOIN MTESTSET TS ON TS.TESTSETID = TR.TESTSETID
        WHERE 1 = 1";

        public const string GET_SELECTLIST_TESTRUN_PAGING_SUFFIX = @"
    ),
    PagedTestRun AS (
        SELECT *, ROW_NUMBER() OVER (ORDER BY Id DESC) AS RowNum
        FROM FilteredTestRun
    )
    SELECT
        Id,
        Number,
        RunDate,
        DB,
        ById,
        ByCode,
        ByName,
        Remarks,
        TestSetId,
        TestSetName
    FROM PagedTestRun
    WHERE (@firstnumber = -1 AND @maxresult = -1) OR (RowNum BETWEEN @firstnumber AND @maxresult);";

        public const string GET_SELECTLIST_TESTRUN_COUNT_BASE = @"
    SELECT COUNT(*)
    FROM MTESTRUN TR
    WHERE 1 = 1";
    }
}