namespace TMSDAL.Query.EvaluationInstance
{
    /// <summary>
    /// Required indexes:
    ///   IX_TEVALUATIONINSTANCE_EVALUATIONPLANID on (EVALUATIONPLANID, TENANTID)
    ///   IX_TEVALUATIONINSTANCE_EMPLOYEEID on (EMPLOYEEID)
    ///   IX_TEVALUATIONINSTANCE_EVALSTATUS on (EVALSTATUS)
    /// </summary>
    public static class EvaluationInstanceQB
    {
        // =====================================================
        // GET SINGLE
        // =====================================================
        public const string GET_EVALUATIONINSTANCE = @"
SELECT  ei.EVALUATIONINSTANCEID     AS EvaluationInstanceId,
        ei.EVALUATIONPLANID         AS EvaluationPlanId,
        ep.PLANTITLE                AS PlanTitle,
        ei.EVALUATIONTEMPLATEID     AS EvaluationTemplateId,
        et.TEMPLATETITLE            AS TemplateTitle,
        ei.ENROLLMENTID             AS EnrollmentId,
        ei.EMPLOYEEID               AS EmployeeId,
        emp.EMPLOYEENAME            AS EmployeeName,
        ei.RESPONDENTID             AS RespondentId,
        res.EMPLOYEENAME            AS RespondentName,
        ei.PERIODCOVEREDFROM        AS PeriodCoveredFrom,
        ei.PERIODCOVEREDTO          AS PeriodCoveredTo,
        ei.DUEDATE                  AS DueDate,
        ei.EVALSTATUS               AS EvalStatus,
        ei.REMINDERSENTCOUNT        AS ReminderSentCount,
        ei.LASTREMINDERSENTAT       AS LastReminderSentAt,
        ei.COMPLETEDAT              AS CompletedAt,
        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.TEVALUATIONINSTANCE ei
INNER JOIN DBO.MEVALUATIONPLAN ep
        ON ep.EVALUATIONPLANID = ei.EVALUATIONPLANID
INNER JOIN DBO.MEVALUATIONTEMPLATE et
        ON et.EVALUATIONTEMPLATEID = ei.EVALUATIONTEMPLATEID
INNER JOIN DBO.MEMPLOYEE emp
        ON emp.EMPLOYEEID = ei.EMPLOYEEID
LEFT JOIN DBO.MEMPLOYEE res
        ON res.EMPLOYEEID = ei.RESPONDENTID
WHERE   ei.EVALUATIONINSTANCEID = @EvaluationInstanceId
AND     ei.TENANTID             = @TenantId";


        // =====================================================
        // GET LIST — Criteria Based
        // =====================================================
        public const string GET_EVALUATIONINSTANCE_LIST = @"
SELECT  ei.EVALUATIONINSTANCEID     AS EvaluationInstanceId,
        ei.EVALUATIONPLANID         AS EvaluationPlanId,
        ep.PLANTITLE                AS PlanTitle,
        ei.EVALUATIONTEMPLATEID     AS EvaluationTemplateId,
        et.TEMPLATETITLE            AS TemplateTitle,
        ei.ENROLLMENTID             AS EnrollmentId,
        ei.EMPLOYEEID               AS EmployeeId,
        emp.EMPLOYEENAME            AS EmployeeName,
        ei.RESPONDENTID             AS RespondentId,
        res.EMPLOYEENAME            AS RespondentName,
        ei.PERIODCOVEREDFROM        AS PeriodCoveredFrom,
        ei.PERIODCOVEREDTO          AS PeriodCoveredTo,
        ei.DUEDATE                  AS DueDate,
        ei.EVALSTATUS               AS EvalStatus,
        ei.REMINDERSENTCOUNT        AS ReminderSentCount,
        ei.LASTREMINDERSENTAT       AS LastReminderSentAt,
        ei.COMPLETEDAT              AS CompletedAt,
        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.TEVALUATIONINSTANCE ei
INNER JOIN DBO.MEVALUATIONPLAN ep
        ON ep.EVALUATIONPLANID = ei.EVALUATIONPLANID
INNER JOIN DBO.MEVALUATIONTEMPLATE et
        ON et.EVALUATIONTEMPLATEID = ei.EVALUATIONTEMPLATEID
INNER JOIN DBO.MEMPLOYEE emp
        ON emp.EMPLOYEEID = ei.EMPLOYEEID
LEFT JOIN DBO.MEMPLOYEE res
        ON res.EMPLOYEEID = ei.RESPONDENTID
WHERE   ei.TENANTID = @TenantId
{DYNAMIC_WHERE}
ORDER BY ei.DUEDATE DESC";


        // =====================================================
        // SAVE
        // =====================================================
        public const string SAVE_EVALUATIONINSTANCE = @"
INSERT INTO DBO.TEVALUATIONINSTANCE
(
    EVALUATIONINSTANCEID, EVALUATIONPLANID, EVALUATIONTEMPLATEID,
    ENROLLMENTID, EMPLOYEEID, RESPONDENTID,
    PERIODCOVEREDFROM, PERIODCOVEREDTO, DUEDATE,
    EVALSTATUS, REMINDERSENTCOUNT, LASTREMINDERSENTAT, COMPLETEDAT,
    VERSION, STATUS, SORTORDER, SOURCETYPE, TENANTID,
    CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
)
VALUES
(
    @EvaluationInstanceId, @EvaluationPlanId, @EvaluationTemplateId,
    @EnrollmentId, @EmployeeId, @RespondentId,
    @PeriodCoveredFrom, @PeriodCoveredTo, @DueDate,
    @EvalStatus, @ReminderSentCount, @LastReminderSentAt, @CompletedAt,
    @Version, @Status, @SortOrder, @SourceType, @TenantId,
    @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn
)";


        // =====================================================
        // UPDATE STATUS
        // =====================================================
        public const string UPDATE_EVALUATIONINSTANCE_STATUS = @"
UPDATE  DBO.TEVALUATIONINSTANCE
SET     EVALSTATUS         = @EvalStatus,
        REMINDERSENTCOUNT  = @ReminderSentCount,
        LASTREMINDERSENTAT = @LastReminderSentAt,
        COMPLETEDAT        = @CompletedAt,
        MODIFIEDBYID       = @ModifiedById,
        MODIFIEDON         = @ModifiedOn
WHERE   EVALUATIONINSTANCEID = @EvaluationInstanceId
AND     TENANTID             = @TenantId";


        // =====================================================
        // DELETE
        // =====================================================
        public const string DELETE_EVALUATIONINSTANCE = @"
DELETE FROM DBO.TEVALUATIONINSTANCE
WHERE   EVALUATIONINSTANCEID = @EvaluationInstanceId
AND     TENANTID             = @TenantId";
    }
}