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 static int GetsessionID(DateTime dtGet)
{
int intSessionID = 0;
DateTime firstDay = new DateTime(dtGet.Year, dtGet.Month, 1);
DateTime LastDate = new DateTime(dtGet.Year, dtGet.Month, DateTime.DaysInMonth(dtGet.Year, dtGet.Month));
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append(" select ID from S_Session where 1=1 ");
stringBuilder.Append(" and CAST(Date0 as datetime) >= '" + firstDay.ToString("yyyyMMdd") + "' ");
stringBuilder.Append(" and CAST(Date0 as datetime) <= '" + LastDate.ToString("yyyyMMdd") + "' ");
SqlHRFinger sqlHR = new SqlHRFinger();
string strGet = sqlHR.sqlExecuteScalarString(stringBuilder.ToString());
if (strGet != "")
{
intSessionID = int.Parse(strGet);
}
return(intSessionID);
}
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);
}