namespace FrameworkDAL.Query.Taxonomy
{
    /// <summary>
    /// Required indexes:
    ///   IX_MTOPIC_SUBJECTID on (SUBJECTID)
    ///   IX_MTOPIC_PARENTTOPICID on (PARENTTOPICID)
    ///   IX_MTOPIC_TENANTID on (TENANTID)
    /// </summary>
    public static class TopicQB
    {
        public const string GET_TOPIC = @"
SELECT  t.TOPICID               AS TopicId,
        t.SUBJECTID              AS SubjectId,
        t.PARENTTOPICID          AS ParentTopicId,
        t.TOPICCODE              AS TopicCode,
        t.TOPICNAME              AS TopicName,
        t.TOPICDIFFICULTY        AS TopicDifficulty,
        t.SKILLID                AS SkillId,
        t.ESTIMATEDMINUTES       AS EstimatedMinutes,
        t.ISACTIVE               AS IsActive,
        t.VERSION                AS Version,
        t.STATUS                 AS Status,
        t.SORTORDER              AS SortOrder,
        t.SOURCETYPE             AS SourceType,
        t.TENANTID               AS TenantId,
        t.CREATEDBYID            AS CreatedById,
        t.CREATEDON              AS CreatedOn,
        t.MODIFIEDBYID           AS ModifiedById,
        t.MODIFIEDON             AS ModifiedOn
FROM    DBO.MTOPIC t
WHERE   t.TOPICID = @TopicId
AND    (t.TENANTID = @TenantId OR t.TENANTID = -1)";


        public const string GET_SELECTLIST_TOPIC = @"
SELECT  t.TOPICID               AS Id,
        t.TOPICCODE              AS Code,
        t.TOPICNAME              AS Name,
        t.SUBJECTID              AS SubjectId
FROM    DBO.MTOPIC t
WHERE  (t.TENANTID = @TenantId OR t.TENANTID = -1)
AND     t.ISACTIVE = 1
AND     t.TOPICID <> -1
AND    (@SubjectId = -1 OR t.SUBJECTID = @SubjectId)
ORDER BY t.SORTORDER, t.TOPICNAME";


        public const string SAVE_TOPIC = @"
INSERT INTO DBO.MTOPIC
(
    TOPICID, SUBJECTID, PARENTTOPICID, TOPICCODE, TOPICNAME, TOPICDIFFICULTY, SKILLID, ESTIMATEDMINUTES,
    ISACTIVE, VERSION, STATUS, SORTORDER, SOURCETYPE, TENANTID,
    CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
)
VALUES
(
    @TopicId, @SubjectId, @ParentTopicId, @TopicCode, @TopicName, @TopicDifficulty, @SkillId, @EstimatedMinutes,
    @IsActive, @Version, @Status, @SortOrder, @SourceType, @TenantId,
    @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn
)";


        public const string UPDATE_TOPIC = @"
UPDATE  DBO.MTOPIC
SET     SUBJECTID        = @SubjectId,
        PARENTTOPICID    = @ParentTopicId,
        TOPICCODE        = @TopicCode,
        TOPICNAME        = @TopicName,
        TOPICDIFFICULTY  = @TopicDifficulty,
        SKILLID          = @SkillId,
        ESTIMATEDMINUTES = @EstimatedMinutes,
        ISACTIVE         = @IsActive,
        VERSION          = @Version,
        STATUS           = @Status,
        SORTORDER        = @SortOrder,
        MODIFIEDBYID     = @ModifiedById,
        MODIFIEDON       = @ModifiedOn
WHERE   TOPICID = @TopicId
AND    (TENANTID = @TenantId OR TENANTID = -1)";


        public const string DELETE_TOPIC = @"
DELETE FROM DBO.MTOPIC
WHERE   TOPICID  = @TopicId
AND     TENANTID = @TenantId";
    }
}