namespace TMSDAL.Query.EvaluationPlan
{
    /// <summary>
    /// Required indexes:
    ///   IX_MEVALUATIONPLAN_TENANTID on (TENANTID)
    ///   IX_MEVALUATIONPLAN_ISACTIVE_TENANTID on (ISACTIVE, TENANTID)
    ///   IX_MEVALUATIONPLAN_PROGRAMMEID on (PROGRAMMEID)
    ///   IX_MEVALUATIONTEMPLATE_EVALUATIONPLANID on (EVALUATIONPLANID)
    /// </summary>
    public static class EvaluationPlanQB
    {
        // =====================================================
        // GET SINGLE PLAN
        // =====================================================
        public const string GET_EVALUATIONPLAN = @"
SELECT  ep.EVALUATIONPLANID     AS EvaluationPlanId,
        ep.PROGRAMMEID          AS ProgrammeId,
        tp.PROGRAMMETITLE       AS ProgrammeTitle,
        ep.PLANTITLE            AS PlanTitle,
        ep.KIRKPATRICK          AS Kirkpatrick,
        ep.EVALWINDOWDAYS       AS EvalWindowDays,
        ep.RESPONDENTTYPE       AS RespondentType,
        ep.EVALTRIGGER          AS EvalTrigger,
        ep.REMINDERDAYS         AS ReminderDays,
        ep.ISMULTIPROGRAMME     AS IsMultiProgramme,
        ep.ISACTIVE             AS IsActive,
        ep.VERSION              AS Version,
        ep.STATUS               AS Status,
        ep.SORTORDER            AS SortOrder,
        ep.SOURCETYPE           AS SourceType,
        ep.TENANTID             AS TenantId,
        ep.CREATEDBYID          AS CreatedById,
        ep.CREATEDON            AS CreatedOn,
        ep.MODIFIEDBYID         AS ModifiedById,
        ep.MODIFIEDON           AS ModifiedOn
FROM    DBO.MEVALUATIONPLAN ep
LEFT JOIN DBO.MTRAININGPROGRAMME tp
        ON tp.PROGRAMMEID = ep.PROGRAMMEID
WHERE   ep.EVALUATIONPLANID = @EvaluationPlanId
AND     ep.TENANTID         = @TenantId";


        // =====================================================
        // GET LIST
        // =====================================================
        public const string GET_EVALUATIONPLAN_LIST = @"
SELECT  ep.EVALUATIONPLANID     AS EvaluationPlanId,
        ep.PROGRAMMEID          AS ProgrammeId,
        tp.PROGRAMMETITLE       AS ProgrammeTitle,
        ep.PLANTITLE            AS PlanTitle,
        ep.KIRKPATRICK          AS Kirkpatrick,
        ep.EVALWINDOWDAYS       AS EvalWindowDays,
        ep.RESPONDENTTYPE       AS RespondentType,
        ep.EVALTRIGGER          AS EvalTrigger,
        ep.REMINDERDAYS         AS ReminderDays,
        ep.ISMULTIPROGRAMME     AS IsMultiProgramme,
        ep.ISACTIVE             AS IsActive,
        ep.VERSION              AS Version,
        ep.STATUS               AS Status,
        ep.SORTORDER            AS SortOrder,
        ep.SOURCETYPE           AS SourceType,
        ep.TENANTID             AS TenantId,
        ep.CREATEDBYID          AS CreatedById,
        ep.CREATEDON            AS CreatedOn,
        ep.MODIFIEDBYID         AS ModifiedById,
        ep.MODIFIEDON           AS ModifiedOn
FROM    DBO.MEVALUATIONPLAN ep
LEFT JOIN DBO.MTRAININGPROGRAMME tp
        ON tp.PROGRAMMEID = ep.PROGRAMMEID
WHERE   ep.TENANTID = @TenantId
ORDER BY ep.SORTORDER, ep.PLANTITLE";


        // =====================================================
        // GET SELECTLIST — Criteria Based
        // =====================================================
        public const string GET_SELECTLIST_EVALUATIONPLAN = @"
SELECT  ep.EVALUATIONPLANID     AS EvaluationPlanId,
        ep.PLANTITLE            AS PlanTitle,
        ep.KIRKPATRICK          AS Kirkpatrick,
        ep.RESPONDENTTYPE       AS RespondentType,
        ep.ISMULTIPROGRAMME     AS IsMultiProgramme,
        ep.ISACTIVE             AS IsActive,
        ep.SORTORDER            AS SortOrder
FROM    DBO.MEVALUATIONPLAN ep
WHERE   ep.TENANTID = @TenantId
AND     ep.ISACTIVE = 1
{DYNAMIC_WHERE}
ORDER BY SortOrder, PlanTitle";


        // =====================================================
        // SAVE PLAN
        // =====================================================
        public const string SAVE_EVALUATIONPLAN = @"
INSERT INTO DBO.MEVALUATIONPLAN
(
    EVALUATIONPLANID,
    PROGRAMMEID,
    PLANTITLE,
    KIRKPATRICK,
    EVALWINDOWDAYS,
    RESPONDENTTYPE,
    EVALTRIGGER,
    REMINDERDAYS,
    ISMULTIPROGRAMME,
    ISACTIVE,
    VERSION,
    STATUS,
    SORTORDER,
    SOURCETYPE,
    TENANTID,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON
)
VALUES
(
    @EvaluationPlanId,
    @ProgrammeId,
    @PlanTitle,
    @Kirkpatrick,
    @EvalWindowDays,
    @RespondentType,
    @EvalTrigger,
    @ReminderDays,
    @IsMultiProgramme,
    @IsActive,
    @Version,
    @Status,
    @SortOrder,
    @SourceType,
    @TenantId,
    @CreatedById,
    @CreatedOn,
    @ModifiedById,
    @ModifiedOn
)";


        // =====================================================
        // UPDATE PLAN
        // =====================================================
        public const string UPDATE_EVALUATIONPLAN = @"
UPDATE  DBO.MEVALUATIONPLAN
SET     PROGRAMMEID      = @ProgrammeId,
        PLANTITLE        = @PlanTitle,
        KIRKPATRICK      = @Kirkpatrick,
        EVALWINDOWDAYS   = @EvalWindowDays,
        RESPONDENTTYPE   = @RespondentType,
        EVALTRIGGER      = @EvalTrigger,
        REMINDERDAYS     = @ReminderDays,
        ISMULTIPROGRAMME = @IsMultiProgramme,
        ISACTIVE         = @IsActive,
        VERSION          = @Version,
        STATUS           = @Status,
        SORTORDER        = @SortOrder,
        MODIFIEDBYID     = @ModifiedById,
        MODIFIEDON       = @ModifiedOn
WHERE   EVALUATIONPLANID = @EvaluationPlanId
AND     TENANTID         = @TenantId";


        // =====================================================
        // DELETE PLAN
        // =====================================================
        public const string DELETE_EVALUATIONPLAN = @"
DELETE FROM DBO.MEVALUATIONPLAN
WHERE   EVALUATIONPLANID = @EvaluationPlanId
AND     TENANTID         = @TenantId";


        // =====================================================
        // GET TEMPLATES BY PLAN
        // =====================================================
        public const string GET_TEMPLATES_BY_PLAN = @"
SELECT  et.EVALUATIONTEMPLATEID AS EvaluationTemplateId,
        et.EVALUATIONPLANID     AS EvaluationPlanId,
        ep.PLANTITLE            AS PlanTitle,
        et.TEMPLATETITLE        AS TemplateTitle,
        et.TEMPLATEVERSIONNO    AS TemplateVersionNo,
        et.QUESTIONSJSON        AS QuestionsJson,
        et.INTROTEXT            AS IntroText,
        et.ISACTIVE             AS IsActive
FROM    DBO.MEVALUATIONTEMPLATE et
INNER JOIN DBO.MEVALUATIONPLAN ep
        ON ep.EVALUATIONPLANID = et.EVALUATIONPLANID
WHERE   et.EVALUATIONPLANID = @EvaluationPlanId";


        // =====================================================
        // GET TEMPLATES BY PLAN IDs (for List)
        // =====================================================
        public const string GET_TEMPLATES_BY_PLAN_IDS = @"
SELECT  et.EVALUATIONTEMPLATEID AS EvaluationTemplateId,
        et.EVALUATIONPLANID     AS EvaluationPlanId,
        ep.PLANTITLE            AS PlanTitle,
        et.TEMPLATETITLE        AS TemplateTitle,
        et.TEMPLATEVERSIONNO    AS TemplateVersionNo,
        et.QUESTIONSJSON        AS QuestionsJson,
        et.INTROTEXT            AS IntroText,
        et.ISACTIVE             AS IsActive
FROM    DBO.MEVALUATIONTEMPLATE et
INNER JOIN DBO.MEVALUATIONPLAN ep
        ON ep.EVALUATIONPLANID = et.EVALUATIONPLANID
WHERE   et.EVALUATIONPLANID IN @EvaluationPlanIds";


        // =====================================================
        // GET SINGLE TEMPLATE
        // =====================================================
        public const string GET_EVALUATIONTEMPLATE = @"
SELECT  et.EVALUATIONTEMPLATEID AS EvaluationTemplateId,
        et.EVALUATIONPLANID     AS EvaluationPlanId,
        ep.PLANTITLE            AS PlanTitle,
        et.TEMPLATETITLE        AS TemplateTitle,
        et.TEMPLATEVERSIONNO    AS TemplateVersionNo,
        et.QUESTIONSJSON        AS QuestionsJson,
        et.INTROTEXT            AS IntroText,
        et.ISACTIVE             AS IsActive
FROM    DBO.MEVALUATIONTEMPLATE et
INNER JOIN DBO.MEVALUATIONPLAN ep
        ON ep.EVALUATIONPLANID = et.EVALUATIONPLANID
WHERE   et.EVALUATIONTEMPLATEID = @EvaluationTemplateId";


        // =====================================================
        // SAVE TEMPLATE
        // =====================================================
        public const string SAVE_EVALUATIONTEMPLATE = @"
INSERT INTO DBO.MEVALUATIONTEMPLATE
(
    EVALUATIONTEMPLATEID,
    EVALUATIONPLANID,
    TEMPLATETITLE,
    TEMPLATEVERSIONNO,
    QUESTIONSJSON,
    INTROTEXT,
    ISACTIVE
)
VALUES
(
    @EvaluationTemplateId,
    @EvaluationPlanId,
    @TemplateTitle,
    @TemplateVersionNo,
    @QuestionsJson,
    @IntroText,
    @IsActive
)";


        // =====================================================
        // UPDATE TEMPLATE
        // =====================================================
        public const string UPDATE_EVALUATIONTEMPLATE = @"
UPDATE  DBO.MEVALUATIONTEMPLATE
SET     TEMPLATETITLE     = @TemplateTitle,
        TEMPLATEVERSIONNO = @TemplateVersionNo,
        QUESTIONSJSON     = @QuestionsJson,
        INTROTEXT         = @IntroText,
        ISACTIVE          = @IsActive
WHERE   EVALUATIONTEMPLATEID = @EvaluationTemplateId";


        // =====================================================
        // DELETE TEMPLATE
        // =====================================================
        public const string DELETE_EVALUATIONTEMPLATE = @"
DELETE FROM DBO.MEVALUATIONTEMPLATE
WHERE   EVALUATIONTEMPLATEID = @EvaluationTemplateId";


        // =====================================================
        // DELETE ALL TEMPLATES BY PLAN
        // =====================================================
        public const string DELETE_TEMPLATES_BY_PLAN = @"
DELETE FROM DBO.MEVALUATIONTEMPLATE
WHERE   EVALUATIONPLANID = @EvaluationPlanId";
    }
}