public DataTable GetDataTableCountShiftbyDept(string Dept, int date)
        {
            DataTable dt = new DataTable();

            try
            {
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append(" select COUNT(e.Code) as CountInDirect from ZlEmployee e  ");
                stringBuilder.Append(" left join Kq_PaiBan p on p.EmpID = e.ID  ");
                stringBuilder.Append(" where State ='0' ");
                stringBuilder.Append(" and  e.Dept = '" + Dept + "' ");
                stringBuilder.Append(" and p.B" + date + " = '04' ");
                stringBuilder.Append(@"  and SessionID =
(select MAX(SessionID) from ZlEmployee e 
left join Kq_PaiBan p on p.EmpID = e.ID ");
                stringBuilder.Append(" where e.Dept = '" + Dept + "' )");


                SqlHRFinger sqlHR = new SqlHRFinger();
                sqlHR.sqlDataAdapterFillDatatable(stringBuilder.ToString(), ref dt);
            }
            catch (Exception)
            {
                throw;
            }
            return(dt);
        }
        public DataTable GetDataTableCountShiftbyDeptShift(string Dept, int date)

        {
            DataTable dt = new DataTable();

            try

            {
                StringBuilder stringBuilder = new StringBuilder();

                stringBuilder.Append(" select e.Dept, p.B" + date + ",w.InTime as Intime, w.OutTime as Outtime, count(B" + date + ") as EmpQty from Kq_PaiBan p ");

                stringBuilder.Append(" right join ZlEmployee e on p.EmpID = e.ID ");

                stringBuilder.Append(" right join ZlDept d on e.Dept = d.Code ");

                stringBuilder.Append(" right join WorkingSetting w  on  w.Id = p.B" + date);

                stringBuilder.Append(" where   e.State ='0' and e.Dept not like '%999%'  and SessionID = ( ");

                stringBuilder.Append(@" select max(SessionID) from Kq_PaiBan b right join ZlEmployee a on b.EmpID = a.ID

 where a.Dept = '" + Dept + "' ) ");

                // stringBuilder.Append(" and p.B" + date + " is not null");

                stringBuilder.Append(" and e.Dept ='" + Dept + "' ");

                stringBuilder.Append(" group by e.Dept, w.InTime, w.OutTime, B" + date);

                stringBuilder.Append(" order by e.Dept ");

                SqlHRFinger sqlHR = new SqlHRFinger();

                sqlHR.sqlDataAdapterFillDatatable(stringBuilder.ToString(), ref dt);
            }

            catch (Exception)

            {
                throw;
            }

            return(dt);
        }
        public DataTable GetUpCodeDept()
        {
            DataTable dt = new DataTable();

            try
            {
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append(" select * from ZlDept where TreeLevel = 1 and Code not like '%999%' ");
                //  stringBuilder.Append(" select * from ZlDept where TreeLevel = 1 and Code not like '%888%' and Code not like '%666%' ");
                SqlHRFinger sqlHR = new SqlHRFinger();
                sqlHR.sqlDataAdapterFillDatatable(stringBuilder.ToString(), ref dt);
            }
            catch (Exception ex)
            {
                Logfile.Output(StatusLog.Error, "DataTable  GetUpCodeDept()", ex.Message);
            }
            return(dt);
        }
        public List <EmployeeAttendance> GetEmployeeAttendancesNightShift(DateTime date, int SessionID)
        {
            List <EmployeeAttendance> employeeAttendances = new List <EmployeeAttendance>();

            try
            {
                DataTable     dt            = new DataTable();
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append(@"
 select distinct  e.Code,e.Name, e.Dept, z.Name as DeptName, z.Manager from ZlEmployee e
 left join ZlDept z on e.Dept = z.Code
  left join Kq_Source s on e.ID = s.EmpID
 left join Kq_PaiBan p on e.ID = p.EmpID 
 where  (e.Dept not like '%999%' and e.Dept not like '%888%') and e.State = '0'  and 
e.ID  in (select distinct EmpID from Kq_Source where 1=1 ");
                stringBuilder.Append(" and ((cast(FDateTime as date)  = cast( '" + date.ToString("yyyyMMdd") + "' as date ) ");
                stringBuilder.Append(" and convert(char(5), FDateTime, 108) >='12:00:01'  and convert(char(5), FDateTime, 108) < '23:59:00')   ");
                stringBuilder.Append(" or (cast(FDateTime as date)  = cast( '" + date.AddDays(1).ToString("yyyyMMdd") + "' as date ) ");
                stringBuilder.Append("  and convert(char(5), FDateTime, 108) >='00:01:00'  and convert(char(5), FDateTime, 108) < '12:00:00')) ");
                stringBuilder.Append(" and EmpID is not null ) ");

                stringBuilder.Append(" and p.B" + date.Day + "  in ('03','07') and p.SessionID = '" + SessionID + "' ");
                SqlHRFinger sqlHR = new SqlHRFinger();
                sqlHR.sqlDataAdapterFillDatatable(stringBuilder.ToString(), ref dt);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    employeeAttendances.Add(new EmployeeAttendance
                    {
                        Date     = date.ToString("dd.MM.yyyy"),
                        EmpCode  = dt.Rows[i]["Code"].ToString(),
                        EmpName  = dt.Rows[i]["Name"].ToString(),
                        DeptCode = dt.Rows[i]["Dept"].ToString(),
                        Dept     = dt.Rows[i]["DeptName"].ToString(),
                        Manager  = dt.Rows[i]["Manager"].ToString(),
                        Shift    = "Night"
                    });
                }
            }
            catch (Exception ex)
            {
                Logfile.Output(StatusLog.Error, "GetEmployeeAttendances(DateTime date)", ex.Message);
            }
            return(employeeAttendances);
        }
        public List <EmployeeAttendance> GetEmployeeAttendancesSeasonal(DateTime date)
        {
            List <EmployeeAttendance> employeeAttendances = new List <EmployeeAttendance>();

            try
            {
                DataTable     dt            = new DataTable();
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append(@" select distinct  e.Code,e.Name, e.Dept, z.Name as DeptName, z.Manager from ZlEmployee e
 left join ZlDept z on e.Dept = z.Code
 left join Kq_Source s on s.EmpID = e.ID
 where  e.Dept  like '%999%'   and 

 e.ID  in (select EmpID from Kq_Source where 1=1
");
                stringBuilder.Append(" and cast(FDateTime as date)  = cast( '" + date.ToString("yyyyMMdd") + "' as date )");
                stringBuilder.Append("  and convert(char(5), FDateTime, 108) >='00:00:01'  and convert(char(5), FDateTime, 108) < '23:59:00' ) ");
                stringBuilder.Append("  and cast(FDateTime as date)  = cast( '" + date.ToString("yyyyMMdd") + "' as date )");

                SqlHRFinger sqlHR = new SqlHRFinger();
                sqlHR.sqlDataAdapterFillDatatable(stringBuilder.ToString(), ref dt);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    employeeAttendances.Add(new EmployeeAttendance
                    {
                        Date     = date.ToString("dd.MM.yyyy"),
                        EmpCode  = dt.Rows[i]["Code"].ToString(),
                        EmpName  = dt.Rows[i]["Name"].ToString(),
                        DeptCode = dt.Rows[i]["Dept"].ToString(),
                        Dept     = dt.Rows[i]["DeptName"].ToString(),
                        Manager  = dt.Rows[i]["Manager"].ToString(),
                    });
                }
            }
            catch (Exception ex)
            {
                Logfile.Output(StatusLog.Error, "GetEmployeeAttendances(DateTime date)", ex.Message);
            }
            return(employeeAttendances);
        }
        public DataTable GetCountEmployeeByUpcode(string Upcode)
        {
            DataTable dt = new DataTable();

            try
            {
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append(@"select  e.Dept, d.DeptDesc, d.LongName, d.UpCode,isnull(d.Note,'') as Note,isnull(d.Manager,'') as Manager, count (e.Code) as countEmployee  
from ZlEmployee e
left join ZlDept d on e.Dept = d.Code 
where   e.State =0 and e.Dept not like '%999%' ");
                stringBuilder.Append(" and  d.UpCode = '" + Upcode + "' ");
                stringBuilder.Append(" group by e.Dept, d.DeptDesc, d.LongName, d.UpCode, d.Note, d.Manager ");
                stringBuilder.Append(" order by e.Dept ");
                SqlHRFinger sqlHR = new SqlHRFinger();
                sqlHR.sqlDataAdapterFillDatatable(stringBuilder.ToString(), ref dt);
            }
            catch (Exception)
            {
                throw;
            }
            return(dt);
        }
        public DataTable GetDataTablelistAbsenceEmployee(DateTime date)
        {
            DataTable dt = new DataTable();

            try
            {
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append(@"
select distinct  e.Code,e.Name, e.Dept, z.Name as DeptName, z.Manager from ZlEmployee e
 left join ZlDept z on e.Dept = z.Code
  left join Kq_Source s on e.emp_finger = s.CardNo
 where e.State = 0 and e.Dept not like '%999%'   and 
e.emp_finger not in (select CardNo from Kq_Source where 1=1 ");
                stringBuilder.Append(" and cast(FDateTime as date)  = cast( '" + date.ToString("yyyyMMdd") + "' ");
                stringBuilder.Append(" and convert(char(5), FDateTime, 108) >='00:00:01'  and convert(char(5), FDateTime, 108) < '23:59:00' ) ");
                SqlHRFinger sqlHR = new SqlHRFinger();
                sqlHR.sqlDataAdapterFillDatatable(stringBuilder.ToString(), ref dt);
            }
            catch (Exception ex)
            {
                Logfile.Output(StatusLog.Error, "GetDataTablelistAbsenceEmployee (DateTime date)", ex.Message);
            }
            return(dt);
        }