namespace PERMDAL.QueryBuilders;

public static class BellCurveQB
{
    // Only finalized appraisals (Status = 4) feed the bell curve.
    // Percentages are computed in BLL from EmployeeCount + TotalEmployees.
    public const string GET_BELL_CURVE = @"
        SELECT  rs.GRADELETTER           AS GradeLetter,
                rs.GRADENAME             AS GradeName,
                rs.SLABFROM              AS SlabFrom,
                rs.SLABTO                AS SlabTo,
                COUNT(a.APPRAISALID)     AS EmployeeCount
        FROM    TAPPRAISAL a
        INNER JOIN MRATINGSCALE rs
                ON  a.FINALPERCENTAGE BETWEEN rs.SLABFROM AND rs.SLABTO
                AND rs.TENANTID  = a.TENANTID
                AND rs.STATUS    = 1
        WHERE   a.APPRAISALPLANID = @AppraisalPlanId
        AND     a.TENANTID        = @TenantId
        AND     (@OUID = 0        OR a.OUID = @OUID)
        AND     a.STATUS          = 4
        GROUP BY rs.GRADELETTER, rs.GRADENAME, rs.SLABFROM, rs.SLABTO
        ORDER BY rs.SLABFROM";

    public const string GET_PLAN_NAME = @"
        SELECT  NAME
        FROM    MAPPRAISALPLAN
        WHERE   APPRAISALPLANID = @AppraisalPlanId
        AND     TENANTID        = @TenantId";
}