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 + "' )"); SqlHR sqlHR = new SqlHR(); sqlHR.sqlDataAdapterFillDatatable(stringBuilder.ToString(), ref dt); } catch (Exception) { throw; } return(dt); }
public List <SeasonalEmployee> GetSeasonalEmployees(DateTime from, DateTime to) { List <SeasonalEmployee> seasonalEmployees = new List <SeasonalEmployee>(); try { StringBuilder stringBuilder = new StringBuilder(); stringBuilder.Append(@"select distinct k.CardNo, ISNULL(z.Name,'') as EmpName from Kq_Source k left join ZlEmployee z on k.EmpID = z.ID where 1=1 "); stringBuilder.Append(" and EmpID is null "); stringBuilder.Append(" and CAST(FDateTime as date ) >='" + from.ToString("yyyyMMdd") + "' "); stringBuilder.Append(" and CAST(FDateTime as date ) <='" + to.ToString("yyyyMMdd") + "' "); DataTable dt = new DataTable(); SqlHR sqlHR = new SqlHR(); sqlHR.sqlDataAdapterFillDatatable(stringBuilder.ToString(), ref dt); for (int i = 0; i < dt.Rows.Count; i++) { seasonalEmployees.Add(new SeasonalEmployee { FingerCode = dt.Rows[i]["CardNo"].ToString(), Name = dt.Rows[i]["EmpName"].ToString(), Supplier = "" }); } } catch (Exception ex) { SystemLog.Output(SystemLog.MSG_TYPE.Err, "GetSeasonalEmployees(DateTime from, DateTime to)", ex.Message); } return(seasonalEmployees); }
public DataTable GetDataTableGanCong(string DateTime, string Dept, string Code) { DataTable dt = new DataTable(); try { StringBuilder stringBuilder = new StringBuilder(); stringBuilder.Append(@"select b.Memo,b.AllDays,e.Code,e.Name,t.LongName,t.Note, a.* from dbo.Kq_PaiBan a left join S_Session b on a.SessionID = b.ID left join ZlEmployee e on a.EmpID = e.ID left join ZlDept t on e.Dept = t.Code where 1=1 "); stringBuilder.Append(" and b.Memo like '%" + DateTime + "%'"); if (Dept != "") { stringBuilder.Append(" and t.LongName like '%" + Dept + "%'"); } if (Code != "") { stringBuilder.Append(" and e.Code like '%" + Code + "%'"); } stringBuilder.Append(" order by e.Code "); SqlHR sqlHR = new SqlHR(); sqlHR.sqlDataAdapterFillDatatable(stringBuilder.ToString(), ref dt); } catch (Exception) { throw; } return(dt); }
public List <Model.EmployeeData> GetEmployeeDatas(string code, string name, string dept, string sex, DateTime hireDate) { List <Model.EmployeeData> employeeDatas = new List <Model.EmployeeData>(); try { StringBuilder stringBuilder = new StringBuilder(); stringBuilder.Append(@"select eml.ID as EmpID, eml.Code as EmpCode,eml.Name as Name,dept.LongName as Dept, case when Sex = 0 then 'Male' when Sex = 1 then 'Female' else 'Undified' end as Sex, convert(char(10), PyDate, 103) as HireDate from ZlEmployee eml left join ZlDept dept on eml.Dept = dept.Code where 1 = 1 and State = 0 "); if (code != "") { stringBuilder.Append(" and eml.Code like '%" + code + "%'"); } if (name != "") { stringBuilder.Append(" and eml.Name like '%" + name + "%'"); } if (dept != "") { stringBuilder.Append(" and dept.LongName like '%" + dept + "%'"); } if (sex != "") { stringBuilder.Append(" and dept.LongName like '%" + sex + "%'"); } if (hireDate > DateTime.MinValue) { stringBuilder.Append(" and PyDate >= '" + hireDate.ToString("yyyyMMdd") + "'"); } stringBuilder.Append(" order by EmpID "); DataTable dt = new DataTable(); SqlHR sqlHR = new SqlHR(); sqlHR.sqlDataAdapterFillDatatable(stringBuilder.ToString(), ref dt); for (int i = 0; i < dt.Rows.Count; i++) { Model.EmployeeData employee = new Model.EmployeeData(); employee.EmpID = dt.Rows[i]["EmpID"].ToString(); employee.EmpCode = dt.Rows[i]["EmpCode"].ToString(); employee.Name = dt.Rows[i]["Name"].ToString(); employee.Dept = dt.Rows[i]["Dept"].ToString(); employee.Sex = dt.Rows[i]["Sex"].ToString(); employee.HiredDate = dt.Rows[i]["HireDate"].ToString(); employee.Status = "Working"; employeeDatas.Add(employee); } } catch (Exception ex) { throw; } return(employeeDatas); }
public List <Model.InoutData> GetInoutDatasFromDateTime(DateTime from, DateTime to) { List <Model.InoutData> inoutDatas = new List <Model.InoutData>(); try { StringBuilder stringBuilder = new StringBuilder(); stringBuilder.Append(@"select isnull(d.Name,'') as Dept,isnull(z.Code,'') as Code, isnull(z.Name,'') as Name,isnull(z.ID,'') as ID, k.CardNo,FDateTime,MachNo,CAST(FDateTime as date ) as DateFinger, convert(char(5), FDateTime, 108) as TimeFinger, case when convert(char(5), FDateTime, 108) >='19:00' and convert(char(5), FDateTime, 108) <'20:00' then 'In-Night' when convert(char(5), FDateTime, 108) >='07:00' and convert(char(5), FDateTime, 108) <'08:10' then 'In-Day' when convert(char(5), FDateTime, 108) >='05:00' and convert(char(5), FDateTime, 108) <='06:00' then 'Out-Night8' when convert(char(5), FDateTime, 108) >='08:10' and convert(char(5), FDateTime, 108) <='09:00' then 'Out-Night12' when convert(char(5), FDateTime, 108) >='17:00' and convert(char(5), FDateTime, 108) <='18:00' then 'Out-Day8' when convert(char(5), FDateTime, 108) >='20:00' and convert(char(5), FDateTime, 108) <='21:00' then 'Out-Day12' else 'Undefined' end as InOut from Kq_Source k left join ZlEmployee z on k.EmpID = z.ID left join ZlDept d on z.Dept = d.Code where 1=1 "); stringBuilder.Append(" and (z.Dept like '%999%' ) "); // stringBuilder.Append(" and (z.Dept like '%888%' or EmpID is null) "); stringBuilder.Append(" and CAST(FDateTime as date ) >='" + from.ToString("yyyyMMdd") + "' "); stringBuilder.Append(" and CAST(FDateTime as date ) <='" + to.ToString("yyyyMMdd") + "' "); stringBuilder.Append(" order by CAST(FDateTime as datetime ) "); DataTable dt = new DataTable(); SqlHR sqlHR = new SqlHR(); sqlHR.sqlDataAdapterFillDatatable(stringBuilder.ToString(), ref dt); for (int i = 0; i < dt.Rows.Count; i++) { inoutDatas.Add(new Model.InoutData { Dept = dt.Rows[i]["Dept"].ToString(), EmpCode = dt.Rows[i]["Code"].ToString(), Name = dt.Rows[i]["Name"].ToString(), EmpID = dt.Rows[i]["ID"].ToString(), CardNo = dt.Rows[i]["CardNo"].ToString(), FDateTime = (DateTime)dt.Rows[i]["FDateTime"], MachNo = dt.Rows[i]["MachNo"].ToString(), Date = dt.Rows[i]["DateFinger"].ToString(), Time = dt.Rows[i]["TimeFinger"].ToString(), InOut = dt.Rows[i]["InOut"].ToString() }); } } catch (Exception ex) { SystemLog.Output(SystemLog.MSG_TYPE.Err, "GetInoutDatasFromDateTime(DateTime from, DateTime to)", ex.Message); } return(inoutDatas); }
public DataTable GetDataPaipan(int SessionID, string IDEmp) { DataTable dtpainPan = new DataTable(); StringBuilder stringBuilder = new StringBuilder(); stringBuilder.Append(" select * from Kq_PaiBan where 1=1 "); stringBuilder.Append(" and SessionID = '" + SessionID + "' "); stringBuilder.Append(" and EmpID = '" + IDEmp + "' "); SqlHR sqlHR = new SqlHR(); sqlHR.sqlDataAdapterFillDatatable(stringBuilder.ToString(), ref dtpainPan); return(dtpainPan); }
public void LoadTabPageDataInOut() { string strSQl = "select distinct LongName,ISNULL(Note,'') as Note from dbo.ZlDept "; DataTable dt = new DataTable(); SqlHR sqlHR = new SqlHR(); sqlHR.sqlDataAdapterFillDatatable(strSQl, ref dt); GetDeptDescription = new Dictionary <string, string>(); for (int i = 0; i < dt.Rows.Count; i++) { GetDeptDescription.Add(dt.Rows[i][0].ToString(), dt.Rows[i][1].ToString()); } cb_department.Items.Clear(); cb_department.DataSource = GetDeptDescription.Keys.ToList(); }
public List <Model.InoutData> GetInoutDatasFromEmpFinger(string EmpFinger, DateTime from, DateTime to) { List <Model.InoutData> inoutDatas = new List <Model.InoutData>(); try { StringBuilder stringBuilder = new StringBuilder(); stringBuilder.Append(@"select CardNo,FDateTime,MachNo,CAST(FDateTime as date ) as DateFinger, convert(char(5), FDateTime, 108) as TimeFinger, case when convert(char(5), FDateTime, 108) >='19:00' and convert(char(5), FDateTime, 108) <'20:00' then 'In-Night' when convert(char(5), FDateTime, 108) >='07:00' and convert(char(5), FDateTime, 108) <'08:10' then 'In-Day' when convert(char(5), FDateTime, 108) >='05:00' and convert(char(5), FDateTime, 108) <='06:00' then 'Out-Night8' when convert(char(5), FDateTime, 108) >='08:10' and convert(char(5), FDateTime, 108) <='09:00' then 'Out-Night12' when convert(char(5), FDateTime, 108) >='17:00' and convert(char(5), FDateTime, 108) <='18:00' then 'Out-Day8' when convert(char(5), FDateTime, 108) >='20:00' and convert(char(5), FDateTime, 108) <='21:00' then 'Out-Day12' else 'Undefined' end as InOut from Kq_Source where 1=1 and Dept like '%999%' "); stringBuilder.Append(" and CardNo ='" + EmpFinger + "' "); stringBuilder.Append(" and CAST(FDateTime as date ) >='" + from.ToString("yyyyMMdd") + "' "); stringBuilder.Append(" and CAST(FDateTime as date ) <='" + to.ToString("yyyyMMdd") + "' "); stringBuilder.Append(" order by CAST(FDateTime as datetime ) "); DataTable dt = new DataTable(); SqlHR sqlHR = new SqlHR(); sqlHR.sqlDataAdapterFillDatatable(stringBuilder.ToString(), ref dt); for (int i = 0; i < dt.Rows.Count; i++) { inoutDatas.Add(new Model.InoutData { CardNo = dt.Rows[i]["CardNo"].ToString(), FDateTime = (DateTime)dt.Rows[i]["FDateTime"], MachNo = dt.Rows[i]["MachNo"].ToString(), Date = dt.Rows[i]["DateFinger"].ToString(), Time = dt.Rows[i]["TimeFinger"].ToString(), InOut = dt.Rows[i]["InOut"].ToString() }); } } catch (Exception ex) { SystemLog.Output(SystemLog.MSG_TYPE.Err, "GetInoutDatasFromEmpFinger ( string EmpFinger, DateTime from, DateTime to)", ex.Message); } return(inoutDatas); }
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 "); SqlHR sqlHR = new SqlHR(); 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%' "); SqlHR sqlHR = new SqlHR(); sqlHR.sqlDataAdapterFillDatatable(stringBuilder.ToString(), ref dt); } catch (Exception ex) { SystemLog.Output(SystemLog.MSG_TYPE.Err, "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 + "' "); SqlHR sqlHR = new SqlHR(); 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) { SystemLog.Output(SystemLog.MSG_TYPE.Err, "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 )"); SqlHR sqlHR = new SqlHR(); 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) { SystemLog.Output(SystemLog.MSG_TYPE.Err, "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 "); SqlHR sqlHR = new SqlHR(); sqlHR.sqlDataAdapterFillDatatable(stringBuilder.ToString(), ref dt); } catch (Exception) { throw; } return(dt); }
private void EmployeeUpdate_Load(object sender, EventArgs e) { SqlHR sqlHR = new SqlHR(); string strSql = "select distinct LongName , Note from ZlDept "; DataTable dt = new DataTable(); sqlHR.sqlDataAdapterFillDatatable(strSql, ref dt); var RowCb = dt.Columns[0]; List <string> depts = dt.AsEnumerable().Select(x => x[0].ToString()).ToList(); Dictionary <string, string> keyValuesDepts = new Dictionary <string, string>(); cb_department.DataSource = depts; cb_Sex.Items.Add("Male"); cb_Sex.Items.Add("Female"); cb_Status.Items.Add("Working"); cb_Status.Items.Add("Resigned"); txt_empCode.Text = _EmployeeData.EmpCode; txt_EmpName.Text = _EmployeeData.Name; cb_department.Text = _EmployeeData.Dept; cb_Sex.Text = _EmployeeData.Sex; dtpk_HireDate.Text = _EmployeeData.HiredDate; cb_Status.Text = _EmployeeData.Status; }
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' ) "); SqlHR sqlHR = new SqlHR(); sqlHR.sqlDataAdapterFillDatatable(stringBuilder.ToString(), ref dt); } catch (Exception ex) { SystemLog.Output(SystemLog.MSG_TYPE.Err, "GetDataTablelistAbsenceEmployee (DateTime date)", ex.Message); } return(dt); }
public double WorkingTimeCalculate(string InTime, string OutTime, DateTime workingDay, string GanCong, out string Status) { double WorkingTime = 0; if (InTime == null || InTime == "") { Status = "[abnormal-In]"; return(0); } if (OutTime == null || OutTime == "") { Status = "[abnormal-Out]"; return(0); } TimeSpan hourIn = TimeSpan.ParseExact(InTime, "h\\:mm", CultureInfo.CurrentCulture); TimeSpan hourOut = TimeSpan.ParseExact(OutTime, "h\\:mm", CultureInfo.CurrentCulture); TimeSpan HourIntarget = new TimeSpan(); DateTime DateInTarget = new DateTime(); TimeSpan HourOutTarget = new TimeSpan(); DateTime DateOutTarget = new DateTime(); DataTable dt = new DataTable(); string sqlQuery = " select * from WorkingSetting where id = '" + GanCong + "'"; SqlHR sqlHR = new SqlHR(); sqlHR.sqlDataAdapterFillDatatable(sqlQuery, ref dt); string tam = ""; if (dt.Rows.Count == 1) { if (dt.Rows[0]["InTime"].ToString().Contains("T") == false) { HourIntarget = TimeSpan.ParseExact(dt.Rows[0]["InTime"].ToString().Trim(), "h\\:mm", CultureInfo.CurrentCulture); DateInTarget = workingDay; } else { HourIntarget = TimeSpan.ParseExact(dt.Rows[0]["InTime"].ToString().Trim().Substring(1), "h\\:mm", CultureInfo.CurrentCulture); DateInTarget = workingDay.AddDays(1); } if (dt.Rows[0]["OutTime"].ToString().Contains("T") == false) { HourOutTarget = TimeSpan.ParseExact(dt.Rows[0]["OutTime"].ToString().Trim(), "h\\:mm", CultureInfo.CurrentCulture); DateOutTarget = workingDay; } else { HourOutTarget = TimeSpan.ParseExact(dt.Rows[0]["OutTime"].ToString().Trim().Substring(1), "h\\:mm", CultureInfo.CurrentCulture); DateOutTarget = workingDay.AddDays(1); } } string massage = ""; if (hourIn <= HourIntarget && hourOut >= HourOutTarget) { massage += "[Normal]"; WorkingTime = (HourOutTarget - HourIntarget).Add(-new TimeSpan(1, 0, 0)).TotalHours; WorkingTime = Math.Round(WorkingTime, 1); } if (hourIn > HourIntarget) { massage += "[In-Late]"; if (hourIn <= new TimeSpan(12, 0, 0)) { WorkingTime = (HourOutTarget - hourIn).Add(-new TimeSpan(1, 0, 0)).TotalHours; } else { WorkingTime = (HourOutTarget - hourIn).TotalHours; } WorkingTime = Math.Round(WorkingTime, 1); } if (hourOut < HourOutTarget) { massage += "[Out-Early]"; WorkingTime = (hourOut - HourIntarget).Add(-new TimeSpan(1, 0, 0)).TotalHours; WorkingTime = Math.Round(WorkingTime, 1); } if (hourOut < HourOutTarget && hourIn < HourIntarget) { WorkingTime = (hourOut - hourIn).Add(-new TimeSpan(1, 0, 0)).TotalHours; WorkingTime = Math.Round(WorkingTime, 1); } Status = massage; return(WorkingTime); }
public List <Model.InoutData> GetInoutDatas(string State, string EmpCode, string Dept, DateTime from, DateTime to) { List <Model.InoutData> inoutDatas = new List <Model.InoutData>(); try { StringBuilder stringBuilder = new StringBuilder(); stringBuilder.Append(@"select ISNULL(eml.ID,'') as EmpID, ISNULL(eml.Code,'') as EmpCode,ISNULL(eml.Name,'') as Name,ISNULL(dept.LongName,'') as Dept,ISNULL(InO.CardNo,'') as CardNo, FDateTime,convert(char(10), FDateTime, 103) as Date, convert(char(5), FDateTime, 108) as Time, case when (convert(char(5), FDateTime, 108) >'07:00' and convert(char(5), FDateTime, 108) < '15:00' ) then 'In-Time' when (convert(char(5), FDateTime, 108) > '15:00' and convert(char(5), FDateTime, 108) < '23:99' ) then 'Out-Time' else 'Undified' end as 'InOut', MachNo from ZlEmployee eml right join ZlDept dept on eml.Dept = dept.Code right join Kq_Source InO on InO.EmpID = eml.ID where 1=1 and (MachNo != '03' and MachNo !='10') "); if (State != "") { stringBuilder.Append(" and State = '" + State + "'"); } if (EmpCode != "") { stringBuilder.Append(" and eml.Code like '%" + EmpCode + "%'"); } if (Dept != "") { stringBuilder.Append(" and dept.LongName = '" + Dept + "'"); } stringBuilder.Append(" and CONVERT(date, FDateTime) >= '" + from.Date.ToString("yyyyMMdd") + "'"); stringBuilder.Append(" and CONVERT(date, FDateTime) <= '" + to.Date.ToString("yyyyMMdd") + "'"); stringBuilder.Append(" order by eml.ID, FDateTime "); SqlHR sqlHR = new SqlHR(); DataTable dt = new DataTable(); sqlHR.sqlDataAdapterFillDatatable(stringBuilder.ToString(), ref dt); for (int i = 0; i < dt.Rows.Count; i++) { Model.InoutData inout = new Model.InoutData(); inout.EmpID = dt.Rows[i]["EmpID"].ToString(); inout.EmpCode = dt.Rows[i]["EmpCode"].ToString(); inout.Name = dt.Rows[i]["Name"].ToString(); inout.Dept = dt.Rows[i]["Dept"].ToString(); inout.CardNo = dt.Rows[i]["CardNo"].ToString(); inout.FDateTime = (DateTime)dt.Rows[i]["FDateTime"]; inout.Date = dt.Rows[i]["Date"].ToString(); inout.Time = dt.Rows[i]["Time"].ToString(); inout.InOut = dt.Rows[i]["InOut"].ToString(); inout.MachNo = dt.Rows[i]["MachNo"].ToString(); inoutDatas.Add(inout); } } catch (Exception) { throw; } return(inoutDatas); }