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 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 string GanCongMa(string code, string YearMonth, int Date)
{
string GanCong = "";
try
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append(@"select B" + Date.ToString());
stringBuilder.Append(@" from dbo.Kq_PaiBan a
left join S_Session b on a.SessionID = b.ID
left join ZlEmployee e on a.EmpID = e.ID
where 1=1 ");
stringBuilder.Append(" and e.Code ='" + code + "'");
stringBuilder.Append(" and b.Memo like '%" + YearMonth + "%'");
SqlHR sqlHR = new SqlHR();
GanCong = sqlHR.sqlExecuteScalarString(stringBuilder.ToString());
if (GanCong != "")
{
GanCong = GanCong.Trim();
}
}
catch (Exception)
{
throw;
}
return(GanCong);
}
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 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") + "' ");
SqlHR sqlHR = new SqlHR();
string strGet = sqlHR.sqlExecuteScalarString(stringBuilder.ToString());
if (strGet != "")
{
intSessionID = int.Parse(strGet);
}
return(intSessionID);
}
public int GetSessionID(DateTime date)
{
int SessionID = -1;
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append(" select ID from S_Session where 1 = 1 ");
stringBuilder.Append(" and (CAST('" + date.ToString("yyyy-MM-dd") + "' as datetime)) BETWEEN CAST(Date0 as datetime) and CAST(Date1 as datetime)");
SqlHR sqlHR = new SqlHR();
string value = sqlHR.sqlExecuteScalarString(stringBuilder.ToString());
try
{
SessionID = int.Parse(value);
}
catch (Exception)
{
SessionID = -1;
}
return(SessionID);
}
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);
}
