using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace PayRollDAL.Query.Calendar
{
    public static class CalendarQB
    {
        public const string GET_EMPLOYEE_BASED_ON_LEVEL = @"
        WITH CTE AS
        (
            SELECT 
                EMPLOYEEID AS ParentEmployeeId,
                EMPLOYEEID AS ChildEmployeeId,
                REPORTINGTOEMPLOYEEID,
                WORKOUID,
                0 AS EmployeeLevel,
                ISCROSSOU
            FROM MEMPLOYEE
            WHERE EMPLOYEEID = @EmployeeId

            UNION ALL

            SELECT
                p.ParentEmployeeId,
                b.EMPLOYEEID,
                b.REPORTINGTOEMPLOYEEID,
                b.WORKOUID,
                p.EmployeeLevel + 1,
                b.ISCROSSOU
            FROM CTE p
            INNER JOIN MEMPLOYEE b
                ON b.REPORTINGTOEMPLOYEEID = p.ChildEmployeeId
               AND b.REPORTINGTOEMPLOYEEID <> -1
        )
        SELECT DISTINCT
            c.ChildEmployeeId AS EmployeeId,
            e.EMPLOYEECODE    AS EmployeeCode,
            e.EMPLOYEENAME    AS EmployeeName,
            c.EmployeeLevel
        FROM CTE c
        INNER JOIN MEMPLOYEE e
            ON e.EMPLOYEEID = c.ChildEmployeeId
        WHERE
            (
                @EmployeeLevel = -1
                OR c.EmployeeLevel <= @EmployeeLevel
            )
            AND e.EMPLOYEESTATUS = 0
            AND e.Status IN (1)
            --AND (c.WORKOUID = @WorkOUId OR c.ISCROSSOU = 0)
        ORDER BY
            c.EmployeeLevel,
            e.EMPLOYEENAME;
        ";
    }
}