namespace FrameworkDAL.Query.Taxonomy
{
    /// <summary>
    /// Required indexes:
    ///   IX_MSUBJECT_COURSEID on (COURSEID)
    ///   IX_MSUBJECT_TENANTID on (TENANTID)
    /// </summary>
    public static class SubjectQB
    {
        public const string GET_SUBJECT = @"
SELECT  s.SUBJECTID             AS SubjectId,
        s.COURSEID               AS CourseId,
        s.SUBJECTCODE            AS SubjectCode,
        s.SUBJECTNAME            AS SubjectName,
        s.SLNO                   AS SlNo,
        s.WEIGHTPCT              AS WeightPct,
        s.ISACTIVE               AS IsActive,
        s.VERSION                AS Version,
        s.STATUS                 AS Status,
        s.SORTORDER              AS SortOrder,
        s.SOURCETYPE             AS SourceType,
        s.TENANTID               AS TenantId,
        s.CREATEDBYID            AS CreatedById,
        s.CREATEDON              AS CreatedOn,
        s.MODIFIEDBYID           AS ModifiedById,
        s.MODIFIEDON             AS ModifiedOn
FROM    DBO.MSUBJECT s
WHERE   s.SUBJECTID = @SubjectId
AND    (s.TENANTID = @TenantId OR s.TENANTID = -1)";


        public const string GET_SELECTLIST_SUBJECT = @"
SELECT  s.SUBJECTID             AS Id,
        s.SUBJECTCODE            AS Code,
        s.SUBJECTNAME            AS Name,
        s.COURSEID                AS CourseId
FROM    DBO.MSUBJECT s
WHERE  (s.TENANTID = @TenantId OR s.TENANTID = -1)
AND     s.ISACTIVE = 1
AND     s.SUBJECTID <> -1
AND    (@CourseId = -1 OR s.COURSEID = @CourseId)
ORDER BY s.SORTORDER, s.SUBJECTNAME";


        public const string SAVE_SUBJECT = @"
INSERT INTO DBO.MSUBJECT
(
    SUBJECTID, COURSEID, SUBJECTCODE, SUBJECTNAME, SLNO, WEIGHTPCT,
    ISACTIVE, VERSION, STATUS, SORTORDER, SOURCETYPE, TENANTID,
    CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
)
VALUES
(
    @SubjectId, @CourseId, @SubjectCode, @SubjectName, @SlNo, @WeightPct,
    @IsActive, @Version, @Status, @SortOrder, @SourceType, @TenantId,
    @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn
)";


        public const string UPDATE_SUBJECT = @"
UPDATE  DBO.MSUBJECT
SET     COURSEID     = @CourseId,
        SUBJECTCODE  = @SubjectCode,
        SUBJECTNAME  = @SubjectName,
        SLNO         = @SlNo,
        WEIGHTPCT    = @WeightPct,
        ISACTIVE     = @IsActive,
        VERSION      = @Version,
        STATUS       = @Status,
        SORTORDER    = @SortOrder,
        MODIFIEDBYID = @ModifiedById,
        MODIFIEDON   = @ModifiedOn
WHERE   SUBJECTID = @SubjectId
AND    (TENANTID = @TenantId OR TENANTID = -1)";


        public const string DELETE_SUBJECT = @"
DELETE FROM DBO.MSUBJECT
WHERE   SUBJECTID = @SubjectId
AND     TENANTID  = @TenantId";
    }
}