namespace TMSDAL.Query.ProgrammeSessionTopic
{
    public static class TrainingProgrammeSessionTopicQB
    {
        public const string GET_TOPIC = @"
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   st.SESSIONTOPICID = @SessionTopicId";

        public const string GET_TOPICS_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 st.SLNO";

        public const string GET_SELECTLIST_TOPIC = @"
SELECT  st.SESSIONTOPICID       AS SessionTopicId,
        st.PROGRAMMESESSIONID   AS ProgrammeSessionId,
        st.TOPICCODE            AS TopicCode,
        st.TOPICTITLE           AS TopicTitle,
        st.SLNO                 AS SlNo
FROM    DBO.MSESSIONTOPIC st
WHERE   st.ISACTIVE = 1
{DYNAMIC_WHERE}
ORDER BY SlNo";

        public const string SAVE_TOPIC = @"
INSERT INTO DBO.MSESSIONTOPIC
(
    SESSIONTOPICID, PROGRAMMESESSIONID, PROGRAMMEID,
    SLNO,
    TOPICCODE, TOPICTITLE,
    TOPICTYPE,
    DURATIONHOURS,
    ISOPTIONAL,
    CONTENTSUMMARY,
    LMSCONTENTID, LMSCOURSEID,
    ISACTIVE
)
VALUES
(
    @SessionTopicId, @ProgrammeSessionId, @ProgrammeId,
    @SlNo,
    @TopicCode, @TopicTitle,
    @TopicType,
    @DurationHours,
    @IsOptional,
    @ContentSummary,
    @LmsContentId, @LmsCourseId,
    @IsActive
)";

        public const string UPDATE_TOPIC = @"
UPDATE  DBO.MSESSIONTOPIC
SET     SLNO              = @SlNo,
        TOPICCODE         = @TopicCode,
        TOPICTITLE        = @TopicTitle,
        TOPICTYPE         = @TopicType,
        DURATIONHOURS     = @DurationHours,
        ISOPTIONAL        = @IsOptional,
        CONTENTSUMMARY    = @ContentSummary,
        LMSCONTENTID      = @LmsContentId,
        LMSCOURSEID       = @LmsCourseId,
        ISACTIVE          = @IsActive
WHERE   SESSIONTOPICID = @SessionTopicId";

        public const string DELETE_TOPIC = @"
DELETE FROM DBO.MSESSIONTOPIC
WHERE SESSIONTOPICID = @SessionTopicId";

        public const string DELETE_TOPICS_BY_SESSION = @"
DELETE FROM DBO.MSESSIONTOPIC
WHERE PROGRAMMESESSIONID = @ProgrammeSessionId";

        public const string DELETE_TOPICS_BY_PROGRAMME = @"
DELETE FROM DBO.MSESSIONTOPIC
WHERE PROGRAMMEID = @ProgrammeId";
    }
}