namespace FrameworkDAL.Query.Taxonomy
{
    /// <summary>
    /// Required indexes:
    ///   IX_MLEARNINGOBJECT_TOPICID on (TOPICID)
    ///   IX_MLEARNINGOBJECT_TENANTID on (TENANTID)
    /// LOType/Difficulty value meanings — see LearningObjectDTO.
    /// </summary>
    public static class LearningObjectQB
    {
        public const string GET_LEARNINGOBJECT = @"
SELECT  lo.LEARNINGOBJECTID     AS LearningObjectId,
        lo.LOTYPE                 AS LoType,
        lo.SOURCEENTITYID          AS SourceEntityId,
        lo.TOPICID                  AS TopicId,
        lo.TITLE                     AS Title,
        lo.DESCRIPTION                AS Description,
        lo.DURATIONMIN                 AS DurationMin,
        lo.DIFFICULTY                   AS Difficulty,
        lo.ISSHARED                      AS IsShared,
        lo.ISACTIVE                       AS IsActive,
        lo.VERSION                         AS Version,
        lo.STATUS                           AS Status,
        lo.SORTORDER                         AS SortOrder,
        lo.SOURCETYPE                         AS SourceType,
        lo.TENANTID                           AS TenantId,
        lo.CREATEDBYID                         AS CreatedById,
        lo.CREATEDON                            AS CreatedOn,
        lo.MODIFIEDBYID                         AS ModifiedById,
        lo.MODIFIEDON                            AS ModifiedOn
FROM    DBO.MLEARNINGOBJECT lo
WHERE   lo.LEARNINGOBJECTID = @LearningObjectId
AND    (lo.TENANTID = @TenantId OR lo.TENANTID = -1)";


        public const string GET_LEARNINGOBJECT_BY_SOURCE = @"
SELECT  lo.LEARNINGOBJECTID     AS LearningObjectId,
        lo.LOTYPE                 AS LoType,
        lo.SOURCEENTITYID          AS SourceEntityId,
        lo.TOPICID                  AS TopicId,
        lo.TITLE                     AS Title,
        lo.DESCRIPTION                AS Description,
        lo.DURATIONMIN                 AS DurationMin,
        lo.DIFFICULTY                   AS Difficulty,
        lo.ISSHARED                      AS IsShared,
        lo.ISACTIVE                       AS IsActive,
        lo.VERSION                         AS Version,
        lo.STATUS                           AS Status,
        lo.SORTORDER                         AS SortOrder,
        lo.SOURCETYPE                         AS SourceType,
        lo.TENANTID                           AS TenantId,
        lo.CREATEDBYID                         AS CreatedById,
        lo.CREATEDON                            AS CreatedOn,
        lo.MODIFIEDBYID                         AS ModifiedById,
        lo.MODIFIEDON                            AS ModifiedOn
FROM    DBO.MLEARNINGOBJECT lo
WHERE   lo.LOTYPE = @LoType
AND     lo.SOURCEENTITYID = @SourceEntityId
AND    (lo.TENANTID = @TenantId OR lo.TENANTID = -1)";


        public const string GET_SELECTLIST_LEARNINGOBJECT = @"
SELECT  lo.LEARNINGOBJECTID     AS Id,
        lo.TITLE                  AS Code,
        lo.TITLE                   AS Name
FROM    DBO.MLEARNINGOBJECT lo
WHERE  (lo.TENANTID = @TenantId OR lo.TENANTID = -1)
AND     lo.ISACTIVE = 1
AND    (@TopicId = -1 OR lo.TOPICID = @TopicId)
AND    (@LoType = -1 OR lo.LOTYPE = @LoType)
ORDER BY lo.SORTORDER, lo.TITLE";


        public const string SAVE_LEARNINGOBJECT = @"
INSERT INTO DBO.MLEARNINGOBJECT
(
    LEARNINGOBJECTID, LOTYPE, SOURCEENTITYID, TOPICID, TITLE, DESCRIPTION, DURATIONMIN, DIFFICULTY, ISSHARED,
    ISACTIVE, VERSION, STATUS, SORTORDER, SOURCETYPE, TENANTID,
    CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
)
VALUES
(
    @LearningObjectId, @LoType, @SourceEntityId, @TopicId, @Title, @Description, @DurationMin, @Difficulty, @IsShared,
    @IsActive, @Version, @Status, @SortOrder, @SourceType, @TenantId,
    @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn
)";


        public const string UPDATE_LEARNINGOBJECT = @"
UPDATE  DBO.MLEARNINGOBJECT
SET     LOTYPE          = @LoType,
        SOURCEENTITYID  = @SourceEntityId,
        TOPICID         = @TopicId,
        TITLE           = @Title,
        DESCRIPTION     = @Description,
        DURATIONMIN     = @DurationMin,
        DIFFICULTY      = @Difficulty,
        ISSHARED        = @IsShared,
        ISACTIVE        = @IsActive,
        VERSION         = @Version,
        STATUS          = @Status,
        SORTORDER       = @SortOrder,
        MODIFIEDBYID    = @ModifiedById,
        MODIFIEDON      = @ModifiedOn
WHERE   LEARNINGOBJECTID = @LearningObjectId
AND    (TENANTID = @TenantId OR TENANTID = -1)";


        public const string DELETE_LEARNINGOBJECT = @"
DELETE FROM DBO.MLEARNINGOBJECT
WHERE   LEARNINGOBJECTID = @LearningObjectId
AND     TENANTID          = @TenantId";
    }
}