namespace TMSDAL.Query.EvaluationInsight
{
    /// <summary>
    /// Required indexes:
    ///   IX_TEVALUATIONINSIGHT_EVALUATIONPLANID on (EVALUATIONPLANID, TENANTID)
    ///   IX_TEVALUATIONINSIGHT_GENERATEDBYID on (GENERATEDBYID)
    /// </summary>
    public static class EvaluationInsightQB
    {
        // =====================================================
        // GET SINGLE
        // =====================================================
        public const string GET_EVALUATIONINSIGHT = @"
SELECT  ei.EVALUATIONINSIGHTID      AS EvaluationInsightId,
        ei.EVALUATIONPLANID         AS EvaluationPlanId,
        ep.PLANTITLE                AS PlanTitle,
        ei.INSTANCEID               AS InstanceId,
        ei.COHORTSIZE               AS CohortSize,
        ei.RESPONSECOUNT            AS ResponseCount,
        ei.RESPONSERATE             AS ResponseRate,
        ei.AVGRATING                AS AvgRating,
        ei.KEYTHEMES                AS KeyThemes,
        ei.SUMMARYNOTES             AS SummaryNotes,
        ei.RECOMMENDEDACTIONS       AS RecommendedActions,
        ei.SKILLIMPACTOBSERVED      AS SkillImpactObserved,
        ei.COMPETENCYIMPACT         AS CompetencyImpact,
        ei.GENERATEDBYID            AS GeneratedById,
        emp.EMPLOYEENAME            AS GeneratedByName,
        ei.GENERATEDON              AS GeneratedOn,
        ei.VERSION                  AS Version,
        ei.STATUS                   AS Status,
        ei.SORTORDER                AS SortOrder,
        ei.SOURCETYPE               AS SourceType,
        ei.TENANTID                 AS TenantId,
        ei.CREATEDBYID              AS CreatedById,
        ei.CREATEDON                AS CreatedOn,
        ei.MODIFIEDBYID             AS ModifiedById,
        ei.MODIFIEDON               AS ModifiedOn
FROM    DBO.TEVALUATIONINSIGHT ei
INNER JOIN DBO.MEVALUATIONPLAN ep
        ON ep.EVALUATIONPLANID = ei.EVALUATIONPLANID
INNER JOIN DBO.MEMPLOYEE emp
        ON emp.EMPLOYEEID = ei.GENERATEDBYID
WHERE   ei.EVALUATIONINSIGHTID = @EvaluationInsightId
AND     ei.TENANTID            = @TenantId";


        // =====================================================
        // GET LIST BY PLAN
        // =====================================================
        public const string GET_EVALUATIONINSIGHT_LIST = @"
SELECT  ei.EVALUATIONINSIGHTID      AS EvaluationInsightId,
        ei.EVALUATIONPLANID         AS EvaluationPlanId,
        ep.PLANTITLE                AS PlanTitle,
        ei.INSTANCEID               AS InstanceId,
        ei.COHORTSIZE               AS CohortSize,
        ei.RESPONSECOUNT            AS ResponseCount,
        ei.RESPONSERATE             AS ResponseRate,
        ei.AVGRATING                AS AvgRating,
        ei.KEYTHEMES                AS KeyThemes,
        ei.SUMMARYNOTES             AS SummaryNotes,
        ei.RECOMMENDEDACTIONS       AS RecommendedActions,
        ei.SKILLIMPACTOBSERVED      AS SkillImpactObserved,
        ei.COMPETENCYIMPACT         AS CompetencyImpact,
        ei.GENERATEDBYID            AS GeneratedById,
        emp.EMPLOYEENAME            AS GeneratedByName,
        ei.GENERATEDON              AS GeneratedOn,
        ei.VERSION                  AS Version,
        ei.STATUS                   AS Status,
        ei.TENANTID                 AS TenantId,
        ei.CREATEDBYID              AS CreatedById,
        ei.CREATEDON                AS CreatedOn,
        ei.MODIFIEDBYID             AS ModifiedById,
        ei.MODIFIEDON               AS ModifiedOn
FROM    DBO.TEVALUATIONINSIGHT ei
INNER JOIN DBO.MEVALUATIONPLAN ep
        ON ep.EVALUATIONPLANID = ei.EVALUATIONPLANID
INNER JOIN DBO.MEMPLOYEE emp
        ON emp.EMPLOYEEID = ei.GENERATEDBYID
WHERE   ei.EVALUATIONPLANID = @EvaluationPlanId
AND     ei.TENANTID         = @TenantId
ORDER BY ei.GENERATEDON DESC";


        // =====================================================
        // SAVE
        // =====================================================
        public const string SAVE_EVALUATIONINSIGHT = @"
INSERT INTO DBO.TEVALUATIONINSIGHT
(
    EVALUATIONINSIGHTID, EVALUATIONPLANID, INSTANCEID,
    COHORTSIZE, RESPONSECOUNT, RESPONSERATE, AVGRATING,
    KEYTHEMES, SUMMARYNOTES, RECOMMENDEDACTIONS,
    SKILLIMPACTOBSERVED, COMPETENCYIMPACT,
    GENERATEDBYID, GENERATEDON,
    VERSION, STATUS, SORTORDER, SOURCETYPE, TENANTID,
    CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
)
VALUES
(
    @EvaluationInsightId, @EvaluationPlanId, @InstanceId,
    @CohortSize, @ResponseCount, @ResponseRate, @AvgRating,
    @KeyThemes, @SummaryNotes, @RecommendedActions,
    @SkillImpactObserved, @CompetencyImpact,
    @GeneratedById, @GeneratedOn,
    @Version, @Status, @SortOrder, @SourceType, @TenantId,
    @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn
)";


        // =====================================================
        // UPDATE
        // =====================================================
        public const string UPDATE_EVALUATIONINSIGHT = @"
UPDATE  DBO.TEVALUATIONINSIGHT
SET     COHORTSIZE          = @CohortSize,
        RESPONSECOUNT       = @ResponseCount,
        RESPONSERATE        = @ResponseRate,
        AVGRATING           = @AvgRating,
        KEYTHEMES           = @KeyThemes,
        SUMMARYNOTES        = @SummaryNotes,
        RECOMMENDEDACTIONS  = @RecommendedActions,
        SKILLIMPACTOBSERVED = @SkillImpactObserved,
        COMPETENCYIMPACT    = @CompetencyImpact,
        MODIFIEDBYID        = @ModifiedById,
        MODIFIEDON          = @ModifiedOn
WHERE   EVALUATIONINSIGHTID = @EvaluationInsightId
AND     TENANTID            = @TenantId";


        // =====================================================
        // DELETE
        // =====================================================
        public const string DELETE_EVALUATIONINSIGHT = @"
DELETE FROM DBO.TEVALUATIONINSIGHT
WHERE   EVALUATIONINSIGHTID = @EvaluationInsightId
AND     TENANTID            = @TenantId";
    }
}