namespace FrameworkDAL.Query.Taxonomy
{
    /// <summary>
    /// Required indexes:
    ///   IX_MCOURSE_TENANTID on (TENANTID)
    ///   IX_MCOURSE_PARENTCOURSEID on (PARENTCOURSEID)
    /// Note: TENANTID = -1 means shared global course visible to all tenants.
    /// </summary>
    public static class CourseQB
    {
        public const string GET_COURSE = @"
SELECT  c.COURSEID              AS CourseId,
        c.COURSECODE            AS CourseCode,
        c.COURSENAME            AS CourseName,
        c.COURSEDESCRIPTION     AS CourseDescription,
        c.PARENTCOURSEID        AS ParentCourseId,
        c.ISSHARED              AS IsShared,
        c.INDUSTRYTAG           AS IndustryTag,
        c.ISACTIVE              AS IsActive,
        c.VERSION               AS Version,
        c.STATUS                AS Status,
        c.SORTORDER             AS SortOrder,
        c.SOURCETYPE            AS SourceType,
        c.TENANTID              AS TenantId,
        c.CREATEDBYID           AS CreatedById,
        c.CREATEDON             AS CreatedOn,
        c.MODIFIEDBYID          AS ModifiedById,
        c.MODIFIEDON            AS ModifiedOn
FROM    DBO.MCOURSE c
WHERE   c.COURSEID = @CourseId
AND    (c.TENANTID = @TenantId OR c.TENANTID = -1)";


        public const string GET_SELECTLIST_COURSE = @"
SELECT  c.COURSEID              AS Id,
        c.COURSECODE            AS Code,
        c.COURSENAME            AS Name
FROM    DBO.MCOURSE c
WHERE  (c.TENANTID = @TenantId OR c.TENANTID = -1)
AND     c.ISACTIVE = 1
AND     c.COURSEID <> -1
ORDER BY c.SORTORDER, c.COURSENAME";


        public const string SAVE_COURSE = @"
INSERT INTO DBO.MCOURSE
(
    COURSEID, COURSECODE, COURSENAME, COURSEDESCRIPTION, PARENTCOURSEID, ISSHARED,
    INDUSTRYTAG, ISACTIVE, VERSION, STATUS, SORTORDER, SOURCETYPE, TENANTID,
    CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
)
VALUES
(
    @CourseId, @CourseCode, @CourseName, @CourseDescription, @ParentCourseId, @IsShared,
    @IndustryTag, @IsActive, @Version, @Status, @SortOrder, @SourceType, @TenantId,
    @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn
)";


        public const string UPDATE_COURSE = @"
UPDATE  DBO.MCOURSE
SET     COURSECODE        = @CourseCode,
        COURSENAME        = @CourseName,
        COURSEDESCRIPTION = @CourseDescription,
        PARENTCOURSEID    = @ParentCourseId,
        ISSHARED          = @IsShared,
        INDUSTRYTAG       = @IndustryTag,
        ISACTIVE          = @IsActive,
        VERSION           = @Version,
        STATUS            = @Status,
        SORTORDER         = @SortOrder,
        MODIFIEDBYID      = @ModifiedById,
        MODIFIEDON        = @ModifiedOn
WHERE   COURSEID = @CourseId
AND    (TENANTID = @TenantId OR TENANTID = -1)";


        public const string DELETE_COURSE = @"
DELETE FROM DBO.MCOURSE
WHERE   COURSEID = @CourseId
AND     TENANTID = @TenantId";
    }
}