namespace TMSDAL.Query.LMSBridge
{
    public static class LMSBridgeQB
    {
        public const string GET_LMSBRIDGE = @"
SELECT
    b.LMSBRIDGEID               AS LMSBridgeId,
    b.LMSBRIDGECODE             AS LMSBridgeCode,
    b.LMSBRIDGENAME             AS LMSBridgeName,
    b.TMSINSTANCEID             AS TMSInstanceId,
    b.TMSPROGRAMMEID            AS TMSProgrammeId,
    b.TMSPROGRAMMESESSIONID     AS TMSProgrammeSessionId,
    b.TMSSESSIONTOPICID         AS TMSSessionTopicId,
    b.LMSCONTENTID              AS LMSContentId,
    b.LMSCOURSEID               AS LMSCourseId,
    b.LMSENROLLMENTID           AS LMSEnrollmentId,
    b.SYNCDIRECTION             AS SyncDirection,
    b.SYNCSTATUS                AS SyncStatus,
    b.LASTSYNCEDON              AS LastSyncedOn,
    b.REMARKS                   AS Remarks,
    b.TENANTID                  AS TenantId,
    b.CREATEDBYID               AS CreatedById,
    b.CREATEDON                 AS CreatedOn,
    b.MODIFIEDBYID              AS ModifiedById,
    b.MODIFIEDON                AS ModifiedOn
FROM DBO.TLMSBRIDGE b
WHERE b.LMSBRIDGEID = @LMSBridgeId
  AND b.TENANTID = @TenantId;";

        public const string SAVE_LMSBRIDGE = @"
INSERT INTO DBO.TLMSBRIDGE
(
    LMSBRIDGEID,
    LMSBRIDGECODE,
    LMSBRIDGENAME,
    TMSINSTANCEID,
    TMSPROGRAMMEID,
    TMSPROGRAMMESESSIONID,
    TMSSESSIONTOPICID,
    LMSCONTENTID,
    LMSCOURSEID,
    LMSENROLLMENTID,
    SYNCDIRECTION,
    SYNCSTATUS,
    LASTSYNCEDON,
    REMARKS,
    TENANTID,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON
)
VALUES
(
    @LMSBridgeId,
    @LMSBridgeCode,
    @LMSBridgeName,
    @TMSInstanceId,
    @TMSProgrammeId,
    @TMSProgrammeSessionId,
    @TMSSessionTopicId,
    @LMSContentId,
    @LMSCourseId,
    @LMSEnrollmentId,
    @SyncDirection,
    @SyncStatus,
    @LastSyncedOn,
    @Remarks,
    @TenantId,
    @CreatedById,
    @CreatedOn,
    @ModifiedById,
    @ModifiedOn
);";

        public const string UPDATE_LMSBRIDGE = @"
UPDATE DBO.TLMSBRIDGE
SET
    LMSBRIDGECODE           = @LMSBridgeCode,
    LMSBRIDGENAME           = @LMSBridgeName,
    TMSINSTANCEID           = @TMSInstanceId,
    TMSPROGRAMMEID          = @TMSProgrammeId,
    TMSPROGRAMMESESSIONID   = @TMSProgrammeSessionId,
    TMSSESSIONTOPICID       = @TMSSessionTopicId,
    LMSCONTENTID            = @LMSContentId,
    LMSCOURSEID             = @LMSCourseId,
    LMSENROLLMENTID         = @LMSEnrollmentId,
    SYNCDIRECTION           = @SyncDirection,
    SYNCSTATUS              = @SyncStatus,
    LASTSYNCEDON            = @LastSyncedOn,
    REMARKS                 = @Remarks,
    MODIFIEDBYID            = @ModifiedById,
    MODIFIEDON              = @ModifiedOn
WHERE LMSBRIDGEID = @LMSBridgeId
  AND TENANTID = @TenantId;";

        public const string DELETE_LMSBRIDGE = @"
DELETE FROM DBO.TLMSBRIDGE
WHERE LMSBRIDGEID = @LMSBridgeId
  AND TENANTID = @TenantId;";

        public const string GET_SELECTLIST_LMSBRIDGE = @"
SELECT
    b.LMSBRIDGEID       AS LMSBridgeId,
    b.LMSBRIDGECODE     AS LMSBridgeCode,
    b.LMSBRIDGENAME     AS LMSBridgeName
FROM DBO.TLMSBRIDGE b
WHERE b.TENANTID = @TenantId
ORDER BY b.LMSBRIDGENAME;";

        public const string GET_LMSBRIDGE_LIST = @"
SELECT
    b.LMSBRIDGEID               AS LMSBridgeId,
    b.LMSBRIDGECODE             AS LMSBridgeCode,
    b.LMSBRIDGENAME             AS LMSBridgeName,
    b.TMSINSTANCEID             AS TMSInstanceId,
    ti.INSTANCETITLE            AS InstanceTitle,
    b.TMSPROGRAMMEID            AS TMSProgrammeId,
    tp.PROGRAMMETITLE           AS ProgrammeName,
    b.TMSPROGRAMMESESSIONID     AS TMSProgrammeSessionId,
    ps.SESSIONTITLE             AS SessionName,
    b.TMSSESSIONTOPICID         AS TMSSessionTopicId,
    st.TOPICTITLE               AS TopicName,
    b.LMSCONTENTID              AS LMSContentId,
    b.LMSCOURSEID               AS LMSCourseId,
    b.LMSENROLLMENTID           AS LMSEnrollmentId,
    b.SYNCDIRECTION             AS SyncDirection,
    b.SYNCSTATUS                AS SyncStatus,
    b.LASTSYNCEDON              AS LastSyncedOn,
    b.REMARKS                   AS Remarks,
    b.CREATEDON                 AS CreatedOn
FROM DBO.TLMSBRIDGE b
LEFT JOIN DBO.TTRAININGINSTANCE ti
    ON ti.INSTANCEID = b.TMSINSTANCEID
LEFT JOIN DBO.MTRAININGPROGRAMME tp
    ON tp.PROGRAMMEID = b.TMSPROGRAMMEID
LEFT JOIN DBO.MPROGRAMMESESSION ps
    ON ps.PROGRAMMESESSIONID = b.TMSPROGRAMMESESSIONID
LEFT JOIN DBO.MSESSIONTOPIC st
    ON st.SESSIONTOPICID = b.TMSSESSIONTOPICID
WHERE b.TENANTID = @TenantId
ORDER BY b.LMSBRIDGENAME;";
    }
}