namespace TMSDAL.Query.AssessmentPaper
{
    /// <summary>
    /// Required indexes:
    ///   IX_MASSESSMENTPAPER_ASSESSMENTID on (ASSESSMENTID)
    ///   IX_MSESSIONASSESSMENT_TENANTID on (TENANTID)
    /// </summary>
    public static class AssessmentPaperQB
    {
        // =====================================================
        // GET SINGLE
        // =====================================================
        public const string GET_ASSESSMENTPAPER = @"
SELECT  ap.ASSESSMENTPAPERID    AS AssessmentPaperId,
        ap.ASSESSMENTID         AS AssessmentId,
        sa.ASSESSMENTTITLE      AS AssessmentTitle,
        ap.PAPERTITLE           AS PaperTitle,
        ap.SELECTIONMODE        AS SelectionMode,
        ap.TOTALQUESTIONS       AS TotalQuestions,
        ap.DURATIONMINS         AS DurationMins,
        ap.RANDOMISEORDER       AS RandomiseOrder,
        ap.SHOWONEATTIME        AS ShowOneAtTime,
        ap.ALLOWREVIEW          AS AllowReview,
        ap.ISLMSDELIVERED       AS IsLmsDelivered,
        ap.LMSASSESSMENTID      AS LmsAssessmentId
FROM    DBO.MASSESSMENTPAPER ap
INNER JOIN DBO.MSESSIONASSESSMENT sa
        ON sa.ASSESSMENTID = ap.ASSESSMENTID
WHERE   ap.ASSESSMENTPAPERID = @AssessmentPaperId";


        // =====================================================
        // GET LIST
        // =====================================================
        public const string GET_ASSESSMENTPAPER_LIST = @"
SELECT  ap.ASSESSMENTPAPERID    AS AssessmentPaperId,
        ap.ASSESSMENTID         AS AssessmentId,
        sa.ASSESSMENTTITLE      AS AssessmentTitle,
        ap.PAPERTITLE           AS PaperTitle,
        ap.SELECTIONMODE        AS SelectionMode,
        ap.TOTALQUESTIONS       AS TotalQuestions,
        ap.DURATIONMINS         AS DurationMins,
        ap.RANDOMISEORDER       AS RandomiseOrder,
        ap.SHOWONEATTIME        AS ShowOneAtTime,
        ap.ALLOWREVIEW          AS AllowReview,
        ap.ISLMSDELIVERED       AS IsLmsDelivered,
        ap.LMSASSESSMENTID      AS LmsAssessmentId
FROM    DBO.MASSESSMENTPAPER ap
INNER JOIN DBO.MSESSIONASSESSMENT sa
        ON sa.ASSESSMENTID = ap.ASSESSMENTID
WHERE   sa.TENANTID = @TenantId
ORDER BY ap.PAPERTITLE";


        // =====================================================
        // GET SELECTLIST
        // =====================================================
        public const string GET_SELECTLIST_ASSESSMENTPAPER = @"
SELECT  ap.ASSESSMENTPAPERID    AS AssessmentPaperId,
        ap.ASSESSMENTID         AS AssessmentId,
        sa.ASSESSMENTTITLE      AS AssessmentTitle,
        ap.PAPERTITLE           AS PaperTitle
FROM    DBO.MASSESSMENTPAPER ap
INNER JOIN DBO.MSESSIONASSESSMENT sa
        ON sa.ASSESSMENTID = ap.ASSESSMENTID
WHERE   sa.TENANTID = @TenantId
{DYNAMIC_WHERE}
ORDER BY ap.PAPERTITLE";


        // =====================================================
        // SAVE
        // =====================================================
        public const string SAVE_ASSESSMENTPAPER = @"
INSERT INTO DBO.MASSESSMENTPAPER
(
    ASSESSMENTPAPERID,
    ASSESSMENTID,
    PAPERTITLE,
    SELECTIONMODE,
    TOTALQUESTIONS,
    DURATIONMINS,
    RANDOMISEORDER,
    SHOWONEATTIME,
    ALLOWREVIEW,
    ISLMSDELIVERED,
    LMSASSESSMENTID
)
VALUES
(
    @AssessmentPaperId,
    @AssessmentId,
    @PaperTitle,
    @SelectionMode,
    @TotalQuestions,
    @DurationMins,
    @RandomiseOrder,
    @ShowOneAtTime,
    @AllowReview,
    @IsLmsDelivered,
    @LmsAssessmentId
)";


        // =====================================================
        // UPDATE
        // =====================================================
        public const string UPDATE_ASSESSMENTPAPER = @"
UPDATE  DBO.MASSESSMENTPAPER
SET     PAPERTITLE       = @PaperTitle,
        SELECTIONMODE    = @SelectionMode,
        TOTALQUESTIONS   = @TotalQuestions,
        DURATIONMINS     = @DurationMins,
        RANDOMISEORDER   = @RandomiseOrder,
        SHOWONEATTIME    = @ShowOneAtTime,
        ALLOWREVIEW      = @AllowReview,
        ISLMSDELIVERED   = @IsLmsDelivered,
        LMSASSESSMENTID  = @LmsAssessmentId
WHERE   ASSESSMENTPAPERID = @AssessmentPaperId";


        // =====================================================
        // DELETE
        // =====================================================
        public const string DELETE_ASSESSMENTPAPER = @"
DELETE FROM DBO.MASSESSMENTPAPER
WHERE   ASSESSMENTPAPERID = @AssessmentPaperId";
    }
}