namespace TMSDAL.Query.ProgrammeSession
{
    public static class TrainingProgrammeSessionQB
    {
        public const string GET_SESSION = @"
SELECT  st.SESSIONTOPICID           AS SessionTopicId,
        st.PROGRAMMESESSIONID       AS ProgrammeSessionId,
        ps.SESSIONCODE              AS SessionCode,
        ps.SESSIONTITLE             AS SessionTitle,
        st.PROGRAMMEID              AS ProgrammeId,
        tp.PROGRAMMECODE            AS ProgrammeCode,
        tp.PROGRAMMETITLE           AS ProgrammeTitle,
        st.SLNO                     AS SlNo,
        st.TOPICCODE                AS TopicCode,
        st.TOPICTITLE               AS TopicTitle,
        st.TOPICTYPE                AS TopicType,
        st.DURATIONHOURS            AS DurationHours,
        st.ISOPTIONAL               AS IsOptional,
        st.CONTENTSUMMARY           AS ContentSummary,
        st.LMSCONTENTID             AS LmsContentId,
        st.LMSCOURSEID              AS LmsCourseId,
        st.ISACTIVE                 AS IsActive
FROM    DBO.MSESSIONTOPIC st
LEFT JOIN DBO.MPROGRAMMESESSION ps
       ON ps.PROGRAMMESESSIONID = st.PROGRAMMESESSIONID
LEFT JOIN DBO.MTRAININGPROGRAMME tp
       ON tp.PROGRAMMEID = st.PROGRAMMEID
WHERE   ps.PROGRAMMESESSIONID = @ProgrammeSessionId";

        public const string GET_SESSIONS_LIST = @"
SELECT  st.SESSIONTOPICID           AS SessionTopicId,
        st.PROGRAMMESESSIONID       AS ProgrammeSessionId,
        ps.SESSIONCODE              AS SessionCode,
        ps.SESSIONTITLE             AS SessionTitle,
        st.PROGRAMMEID              AS ProgrammeId,
        tp.PROGRAMMECODE            AS ProgrammeCode,
        tp.PROGRAMMETITLE           AS ProgrammeTitle,
        st.SLNO                     AS SlNo,
        st.TOPICCODE                AS TopicCode,
        st.TOPICTITLE               AS TopicTitle,
        st.TOPICTYPE                AS TopicType,
        st.DURATIONHOURS            AS DurationHours,
        st.ISOPTIONAL               AS IsOptional,
        st.CONTENTSUMMARY           AS ContentSummary,
        st.LMSCONTENTID             AS LmsContentId,
        st.LMSCOURSEID              AS LmsCourseId,
        st.ISACTIVE                 AS IsActive
FROM    DBO.MSESSIONTOPIC st
LEFT JOIN DBO.MPROGRAMMESESSION ps
       ON ps.PROGRAMMESESSIONID = st.PROGRAMMESESSIONID
LEFT JOIN DBO.MTRAININGPROGRAMME tp
       ON tp.PROGRAMMEID = st.PROGRAMMEID
ORDER BY ps.SLNO";

        public const string GET_SELECTLIST_SESSION = @"
SELECT  ps.PROGRAMMESESSIONID  AS ProgrammeSessionId,
        ps.PROGRAMMEID         AS ProgrammeId,
        ps.SESSIONCODE         AS SessionCode,
        ps.SESSIONTITLE        AS SessionTitle,
        ps.SLNO                AS SlNo
FROM    DBO.MPROGRAMMESESSION ps
WHERE   ps.ISACTIVE  = 1
AND     ps.TENANTID  = @TenantId
{DYNAMIC_WHERE}
ORDER BY ps.SLNO";

        public const string SAVE_SESSION = @"
INSERT INTO DBO.MPROGRAMMESESSION
(
    PROGRAMMESESSIONID, PROGRAMMEID, SLNO,
    SESSIONCODE, SESSIONTITLE,
    SESSIONTYPE, SESSIONDELIVMODE,
    DURATIONHOURS,
    ISOPTIONAL, ISMANDATORYFORCERT, ISASSESSMENTREQUIRED,
    MINATTENDANCEPCT,
    DELIVERYNOTE, DOJOSTATIONREF,
    ISACTIVE,
    TENANTID
)
VALUES
(
    @ProgrammeSessionId, @ProgrammeId, @SlNo,
    @SessionCode, @SessionTitle,
    @SessionType, @SessionDelivMode,
    @DurationHours,
    @IsOptional, @IsMandatoryForCert, @IsAssessmentRequired,
    @MinAttendancePct,
    @DeliveryNote, @DojoStationRef,
    @IsActive,
    @TenantId
)";

        public const string UPDATE_SESSION = @"
UPDATE  DBO.MPROGRAMMESESSION
SET     SLNO                  = @SlNo,
        SESSIONCODE           = @SessionCode,
        SESSIONTITLE          = @SessionTitle,
        SESSIONTYPE           = @SessionType,
        SESSIONDELIVMODE      = @SessionDelivMode,
        DURATIONHOURS         = @DurationHours,
        ISOPTIONAL            = @IsOptional,
        ISMANDATORYFORCERT    = @IsMandatoryForCert,
        ISASSESSMENTREQUIRED  = @IsAssessmentRequired,
        MINATTENDANCEPCT      = @MinAttendancePct,
        DELIVERYNOTE          = @DeliveryNote,
        DOJOSTATIONREF        = @DojoStationRef,
        ISACTIVE              = @IsActive
WHERE   PROGRAMMESESSIONID = @ProgrammeSessionId
AND     TENANTID           = @TenantId";

        public const string DELETE_SESSION = @"
DELETE FROM DBO.MPROGRAMMESESSION
WHERE PROGRAMMESESSIONID = @ProgrammeSessionId
AND   TENANTID           = @TenantId";

        public const string DELETE_SESSIONS_BY_PROGRAMME = @"
DELETE FROM DBO.MPROGRAMMESESSION
WHERE PROGRAMMEID = @ProgrammeId
AND   TENANTID    = @TenantId";
    }
}