private List<AttendanceReport> GetAttendanceReport(int employeeNumber, DateTime dtFrom, DateTime dtTo)
{
string orderBy = "WorkFrom ASC";
OleDbCommand odCom = BuildSelectCmd("AttendanceReport", "*", "WorkFrom >=@Date_1 AND WorkFrom <= @Date_2 AND EmployeeNumber =" + employeeNumber, orderBy,
new object[] { "@Date_1", dtFrom, "@Date_2", dtTo });
List<AttendanceReport> attendanceReports = new List<AttendanceReport>();
AttendanceReport attendanceReport = null;
OleDbDataReader odRdr = odCom.ExecuteReader();
while (odRdr.Read())
{
attendanceReport = new AttendanceReport();
attendanceReport.DayTypeID = (int)odRdr["DayTypeID"];
attendanceReport.EmployeeNumber = (int)odRdr["EmployeeNumber"];
attendanceReport.OvertimeHour1 = (double)odRdr["OvertimeHour1"];
attendanceReport.OvertimeHour2 = (double)odRdr["OvertimeHour2"];
attendanceReport.OvertimeHour3 = (double)odRdr["OvertimeHour3"];
attendanceReport.OvertimeHour4 = (double)odRdr["OvertimeHour4"];
attendanceReport.OvertimeRate1 = (double)odRdr["OvertimeRate1"];
attendanceReport.OvertimeRate2 = (double)odRdr["OvertimeRate2"];
attendanceReport.OvertimeRate3 = (double)odRdr["OvertimeRate3"];
attendanceReport.OvertimeRate4 = (double)odRdr["OvertimeRate4"];
attendanceReport.PayPeriodID = (int)odRdr["PayPeriodID"];
attendanceReport.RegularHour = (double)odRdr["RegularHour"];
attendanceReport.RegularRate = (double)odRdr["RegularRate"];
attendanceReport.WorkFrom = (DateTime)odRdr["WorkFrom"];
attendanceReport.WorkTo = (DateTime)odRdr["WorkTo"];
attendanceReport.ID = (int)odRdr["ID"];
attendanceReport.AttendanceRecordIDList = odRdr["AttendanceRecordIDList"].ToString();
attendanceReports.Add(attendanceReport);
}
odRdr.Close();
return attendanceReports;
}
private void GetRegularOvertime(ref AttendanceReport attReport, double totalHour)
{
double wCalRegularHour = attReport.RegularHour;
double wCalOvertimeHour1 = attReport.OvertimeHour1;
double wCalOvertimeHour2 = attReport.OvertimeHour2;
double wCalOvertimeHour3 = attReport.OvertimeHour3;
double wCalOvertimeHour4 = attReport.OvertimeHour4;
double regularHour = 0;
double overtimeHour1 = 0;
double overtimeHour2 = 0;
double overtimeHour3 = 0;
double overtimeHour4 = 0;
if (totalHour >= wCalRegularHour && wCalRegularHour >= 0)
{
regularHour = wCalRegularHour;
totalHour -= wCalRegularHour;
//NEW
if (wCalOvertimeHour1 > 0)
{
if (totalHour > wCalOvertimeHour1)
overtimeHour1 = wCalOvertimeHour1;
else
overtimeHour1 = totalHour;
totalHour -= wCalOvertimeHour1;
}
if (wCalOvertimeHour2 > 0)
{
if (totalHour > wCalOvertimeHour2)
overtimeHour2 = wCalOvertimeHour2;
else
overtimeHour2 = totalHour;
totalHour -= wCalOvertimeHour2;
}
if (wCalOvertimeHour3 > 0)
{
if (totalHour > wCalOvertimeHour3)
overtimeHour3 = wCalOvertimeHour3;
else
overtimeHour3 = totalHour;
totalHour -= wCalOvertimeHour3;
}
if (wCalOvertimeHour4 > 0)
{
if (totalHour > wCalOvertimeHour4)
overtimeHour4 = wCalOvertimeHour4;
else
overtimeHour4 = totalHour;
totalHour -= wCalOvertimeHour4;
}
//END NEW
//if (wCalOvertimeHour1 > 0)
//{
// if (totalHour > wCalOvertimeHour1)
// {
// overtimeHour1 = wCalOvertimeHour1;
// totalHour -= wCalOvertimeHour1;
// if (wCalOvertimeHour2 > 0)
// {
// if (totalHour > wCalOvertimeHour2)
// {
// overtimeHour2 = wCalOvertimeHour2;
// totalHour -= wCalOvertimeHour2;
// if (wCalOvertimeHour3 > 0)
// {
// if (totalHour > wCalOvertimeHour3)
// {
// overtimeHour3 = wCalOvertimeHour3;
// totalHour -= wCalOvertimeHour3;
// if (wCalOvertimeHour4 > 0)
// overtimeHour4 = totalHour > wCalOvertimeHour4 ? wCalOvertimeHour4 : totalHour;
// }
// else
// overtimeHour3 = totalHour;
// }
// }
// else
// overtimeHour2 = totalHour;
// }
// }
// else
// overtimeHour1 = totalHour;
//}
//else
// regularHour = totalHour > wCalRegularHour ? wCalRegularHour : totalHour;
}
else
{
regularHour = totalHour;// > wCalRegularHour ? wCalRegularHour : totalHour;
}
attReport.RegularHour = Math.Round(regularHour, 2);
attReport.OvertimeHour1 = Math.Round(overtimeHour1, 2);
attReport.OvertimeHour2 = Math.Round(overtimeHour2, 2);
attReport.OvertimeHour3 = Math.Round(overtimeHour3, 2);
attReport.OvertimeHour4 = Math.Round(overtimeHour4, 2);
}
private bool AddUpdateAttendaceReport(AttendanceRecord attRecord, AttendanceReport attReport)
{
int employeeNumber = (attReport == null) ? attRecord.EmployeeNumber : attReport.EmployeeNumber;
Config config = GetConfig();
WorkingCalendar workingCalendar = GetWorkingCalendarByEmployee(employeeNumber);
int payPeriodID = workingCalendar.PayPeriodID;
DateTime dRegularWorkingFrom = workingCalendar.RegularWorkingFrom;
DateTime dRegularWorkingTo = workingCalendar.RegularWorkingTo;
int earliestBeforeEntry = workingCalendar.EarliestBeforeEntry;
int lastestAfterExit = workingCalendar.LastestAfterExit;
int graceForwardToEntry = workingCalendar.GraceForwardToEntry;
int graceBackwardToExit = workingCalendar.GraceBackwardToExit;
DateTime dWorkingFrom, dWorkingTo;
string attIdList = "";
int reportId = 0;
bool attRecordDateChanged = false;
if (attReport == null) //add a record
{
dWorkingFrom = GetWorkingDayByAttendanceRecord(attRecord); //TODO could pass the workingCalendar here
dWorkingTo = dWorkingFrom.Date.AddHours(dRegularWorkingTo.Hour).AddMinutes(dRegularWorkingTo.Minute + lastestAfterExit);
if (dWorkingFrom.CompareTo(dWorkingTo) == 1)
dWorkingTo.AddDays(1);
if (attRecord.Time.CompareTo(dWorkingFrom) == -1 || attRecord.Time.CompareTo(dWorkingTo) == 1)
{
return false;
}
OleDbCommand odCom = BuildSelectCmd("AttendanceReport", "*", "EmployeeNumber=@Empl AND WorkFrom=@WorkFrom",
new object[] { "@Empl", employeeNumber, "@WorkFrom", dWorkingFrom });
OleDbDataReader odRdr = odCom.ExecuteReader();
if (odRdr.Read())
{
reportId = (int)odRdr["ID"];
attIdList = odRdr["AttendanceRecordIDList"].ToString();
attIdList += "{" + attRecord.ID + "}";
odRdr.Close();
}
}
else //update a report
{
if (attRecord != null && attRecord.Time.Date.CompareTo(attReport.WorkFrom.Date) != 0) //record's date changed
{
attIdList = attReport.AttendanceRecordIDList.Replace("{" + attRecord.ID + "}", "");
attRecordDateChanged = true;
}
else
{
attIdList = attReport.AttendanceRecordIDList;
attRecordDateChanged = false;
}
dWorkingFrom = attReport.WorkFrom;
dWorkingTo = attReport.WorkTo;
reportId = attReport.ID;
}
if (reportId > 0) //calculate or re-calculate
{
string listId = attIdList.Replace("{", "").Replace("}", ",").TrimEnd(',');
bool b1 = true;
if (listId != "")
{
OleDbCommand odCom = BuildSelectCmd("AttendanceRecord", "*", "ID IN (" + listId + ")");
OleDbDataReader odRdr = odCom.ExecuteReader();
List<DateTime> timeLogs = new List<DateTime>();
while (odRdr.Read())
{
//TODO round value
timeLogs.Add(Util.RoundDateTime((DateTime)odRdr["Time"], config.RecordRoundingValue));
//timeLogs.Add((DateTime)odRdr["Time"]);
}
odRdr.Close();
timeLogs.Sort();
List<Break> breakList = GetBreakListByWorkingCalendar(workingCalendar.ID);
long totalTicks = 0;
for (int i = 0; i < timeLogs.Count - 1; i++)
{
if (i % 2 == 0)
{
DateTime timeIn = timeLogs[i];
DateTime timeOut = timeLogs[i + 1];
DateTime timeFrom = timeIn.Date.AddHours(dRegularWorkingFrom.Hour).AddMinutes(dRegularWorkingFrom.Minute);
DateTime timeTo = timeOut.Date.AddHours(dRegularWorkingTo.Hour).AddMinutes(dRegularWorkingTo.Minute);
if (timeFrom.CompareTo(timeTo) == 1)
timeTo = timeTo.AddDays(1);
double distanceMinute1 = TimeSpan.FromTicks(timeFrom.Ticks - timeIn.Ticks).TotalMinutes;
if (distanceMinute1 > 0 && graceForwardToEntry >= distanceMinute1)
{
timeIn = timeFrom;
}
double distanceMinute2 = TimeSpan.FromTicks(timeOut.Ticks - timeTo.Ticks).TotalMinutes;
if (distanceMinute2 > 0 && graceBackwardToExit >= distanceMinute2)
{
timeOut = timeTo;
}
Break _break = breakList.Find(delegate(Break b)
{
DateTime breakFrom = dWorkingFrom.Date.AddHours(b.From.Hour).AddMinutes(b.From.Minute);
DateTime breakTo = dWorkingFrom.Date.AddHours(b.To.Hour).AddMinutes(b.To.Minute);
if (breakFrom.CompareTo(breakTo) == 1)
breakTo = breakTo.AddDays(1);
//return if belong to 1 in 4 cases:
//1.breakFrom, timeIn, breakTo, timeOut
//2.timeIn, breakFrom, timeOut, breakTo
//3.timeIn, breakFrom, breakTo, timeOut
//4.breakFrom, timeIn, timeOut, breakTo
return ((timeIn.CompareTo(breakFrom) < 0 && timeOut.CompareTo(breakFrom) > 0)
|| (timeIn.CompareTo(breakTo) < 0 && timeOut.CompareTo(breakTo) > 0)
|| (timeIn.CompareTo(breakFrom) >= 0 && timeOut.CompareTo(breakTo) <= 0));
});
if (_break != null)
{
DateTime breakFrom = dWorkingFrom.Date.AddHours(_break.From.Hour).AddMinutes(_break.From.Minute);
DateTime breakTo = dWorkingFrom.Date.AddHours(_break.To.Hour).AddMinutes(_break.To.Minute);
if (breakFrom.CompareTo(breakTo) == 1)
breakTo = breakTo.AddDays(1);
if (timeIn.CompareTo(breakFrom) >= 0 && timeIn.CompareTo(breakTo) <= 0 && timeOut.CompareTo(breakTo) >= 0)
{//breakFrom, timeIn, breakTo, timeOut
if (_break.Paid)
totalTicks += timeOut.Ticks - timeIn.Ticks;
else
totalTicks += timeOut.Ticks - breakTo.Ticks;
}
else if (timeIn.CompareTo(breakFrom) <= 0 && timeOut.CompareTo(breakFrom) >= 0 && timeOut.CompareTo(breakTo) <= 0)
{//timeIn, breakFrom, timeOut, breakTo
if (_break.Paid)
totalTicks += timeOut.Ticks - timeIn.Ticks;
else
totalTicks += breakFrom.Ticks - timeIn.Ticks;
}
else if (timeIn.CompareTo(breakFrom) <= 0 && timeOut.CompareTo(breakTo) >= 0)
{//timeIn, breakFrom, breakTo, timeOut
if (_break.Paid)
totalTicks += timeOut.Ticks - timeIn.Ticks;
else
totalTicks += timeOut.Ticks - timeIn.Ticks - (breakTo.Ticks - breakFrom.Ticks);
}
else if (timeIn.CompareTo(breakFrom) >= 0 && timeOut.CompareTo(breakTo) <= 0)
{//breakFrom, timeIn, timeOut, breakTo
if (_break.Paid)
totalTicks = timeOut.Ticks - timeIn.Ticks;
else
totalTicks += 0;
}
}
else
{
totalTicks += timeOut.Ticks - timeIn.Ticks;
}
}
else
{
try
{
DateTime timeOut = timeLogs[i];
DateTime timeIn = timeLogs[i + 1];
DateTime timeFrom = timeOut.Date.AddHours(dRegularWorkingFrom.Hour).AddMinutes(dRegularWorkingFrom.Minute);
DateTime timeTo = timeIn.Date.AddHours(dRegularWorkingTo.Hour).AddMinutes(dRegularWorkingTo.Minute);
if (timeFrom.CompareTo(timeTo) == 1)
timeTo = timeTo.AddDays(1);
double distanceMinute1 = (timeFrom.Ticks - timeOut.Ticks) / 600000000; //TODO use TimeSpan
if (distanceMinute1 > 0 && graceForwardToEntry >= distanceMinute1)
{
timeOut = timeFrom;
}
double distanceMinute2 = (timeIn.Ticks - timeTo.Ticks) / 600000000; //TODO use TimeSpan
if (distanceMinute2 > 0 && graceBackwardToExit >= distanceMinute2)
{
timeIn = timeTo;
}
Break _break = breakList.Find(delegate(Break b)
{
DateTime breakFrom = dWorkingFrom.Date.AddHours(b.From.Hour).AddMinutes(b.From.Minute);
DateTime breakTo = dWorkingFrom.Date.AddHours(b.To.Hour).AddMinutes(b.To.Minute);
if (breakFrom.CompareTo(breakTo) == 1)
breakTo = breakTo.AddDays(1);
return ((timeOut.CompareTo(breakFrom) < 0 && timeIn.CompareTo(breakFrom) > 0)
|| (timeOut.CompareTo(breakTo) < 0 && timeIn.CompareTo(breakTo) > 0)
|| (timeOut.CompareTo(breakFrom) >= 0 && timeIn.CompareTo(breakTo) <= 0));
});
if (_break != null)
{
if (_break.Paid)
{
DateTime breakFrom = dWorkingFrom.Date.AddHours(_break.From.Hour).AddMinutes(_break.From.Minute);
DateTime breakTo = dWorkingFrom.Date.AddHours(_break.To.Hour).AddMinutes(_break.To.Minute);
if (breakFrom.CompareTo(breakTo) == 1)
breakTo = breakTo.AddDays(1);
if (timeOut.CompareTo(breakFrom) >= 0 && timeOut.CompareTo(breakTo) <= 0 && timeIn.CompareTo(breakTo) >= 0)
{//breakFrom, timeOut, breakTo, timeIn
totalTicks += breakTo.Ticks - timeOut.Ticks;
}
else if (timeOut.CompareTo(breakFrom) <= 0 && timeIn.CompareTo(breakFrom) >= 0 && timeIn.CompareTo(breakTo) <= 0)
{//timeOut, breakFrom, timeIn, breakTo
totalTicks += timeIn.Ticks - breakFrom.Ticks;
}
else if (timeOut.CompareTo(breakFrom) <= 0 && timeIn.CompareTo(breakTo) >= 0)
{//timeOut, breakFrom, breakTo, timeIn
totalTicks += breakTo.Ticks - breakFrom.Ticks;
}
else if (timeOut.CompareTo(breakFrom) >= 0 && timeIn.CompareTo(breakTo) <= 0)
{//breakFrom, timeOut, timeOut, breakTo
totalTicks += timeOut.Ticks - timeIn.Ticks;
}
}
}
}
catch (IndexOutOfRangeException) { }
}
}
double totalHour = TimeSpan.FromTicks(totalTicks).TotalHours;
PaymentRate paymentRate = GetPaymentRateByEmployeeAndWorkDay(employeeNumber, dWorkingFrom);
AttendanceReport attendanceReport = new AttendanceReport();
attendanceReport.ID = reportId;
attendanceReport.AttendanceRecordIDList = attIdList;
attendanceReport.DayTypeID = paymentRate.DayTypeID;
attendanceReport.EmployeeNumber = employeeNumber;
attendanceReport.RegularHour = paymentRate.NumberOfRegularHours;
attendanceReport.OvertimeHour1 = paymentRate.NumberOfOvertime1;
attendanceReport.OvertimeHour2 = paymentRate.NumberOfOvertime2;
attendanceReport.OvertimeHour3 = paymentRate.NumberOfOvertime3;
attendanceReport.OvertimeHour4 = paymentRate.NumberOfOvertime4;
attendanceReport.RegularRate = paymentRate.RegularRate;
attendanceReport.OvertimeRate1 = paymentRate.OvertimeRate1;
attendanceReport.OvertimeRate2 = paymentRate.OvertimeRate2;
attendanceReport.OvertimeRate3 = paymentRate.OvertimeRate3;
attendanceReport.OvertimeRate4 = paymentRate.OvertimeRate4;
attendanceReport.PayPeriodID = workingCalendar.PayPeriodID;
attendanceReport.WorkFrom = dWorkingFrom;
attendanceReport.WorkTo = dWorkingTo;
//TODO should pass PaymentRate as it's more clear
GetRegularOvertime(ref attendanceReport, totalHour);
b1 = UpdateAttendanceReport(attendanceReport);
}
else
{
b1 = DeleteAttendanceReport(attReport.ID);
}
bool b2 = true;
if (attRecordDateChanged)
{
b2 = AddUpdateAttendaceReport(attRecord, null);
}
return (b1 && b2);
}
else
{
workingCalendar.RegularWorkingFrom = dWorkingFrom;
workingCalendar.RegularWorkingTo = dWorkingTo;
return AddAttendanceReport(attRecord, workingCalendar);
}
}
private AttendanceReport AttendanceLogReportByEmplWfrom(int employeeNumber, DateTime dWorkingFrom)
{
OleDbCommand odCom = BuildSelectCmd("AttendanceReport", "*", "EmployeeNumber=@Empl AND WorkFrom=@WorkFrom",
new object[] { "@Empl", employeeNumber, "@WorkFrom", dWorkingFrom });
OleDbDataReader odRdr = odCom.ExecuteReader();
AttendanceReport attendanceReport = null;
if (odRdr.Read())
{
attendanceReport = new AttendanceReport();
attendanceReport.DayTypeID = (int)odRdr["DayTypeID"];
attendanceReport.EmployeeNumber = (int)odRdr["EmployeeNumber"];
attendanceReport.OvertimeHour1 = (double)odRdr["OvertimeHour1"];
attendanceReport.OvertimeHour2 = (double)odRdr["OvertimeHour2"];
attendanceReport.OvertimeHour3 = (double)odRdr["OvertimeHour3"];
attendanceReport.OvertimeHour4 = (double)odRdr["OvertimeHour4"];
attendanceReport.OvertimeRate1 = (double)odRdr["OvertimeRate1"];
attendanceReport.OvertimeRate2 = (double)odRdr["OvertimeRate2"];
attendanceReport.OvertimeRate3 = (double)odRdr["OvertimeRate3"];
attendanceReport.OvertimeRate4 = (double)odRdr["OvertimeRate4"];
attendanceReport.PayPeriodID = (int)odRdr["PayPeriodID"];
attendanceReport.RegularHour = (double)odRdr["RegularHour"];
attendanceReport.RegularRate = (double)odRdr["RegularRate"];
if (typeof(DBNull) != odRdr["WorkFrom"].GetType())
attendanceReport.WorkFrom = (DateTime)odRdr["WorkFrom"];
if (typeof(DBNull) != odRdr["WorkTo"].GetType())
attendanceReport.WorkTo = (DateTime)odRdr["WorkTo"];
attendanceReport.ID = (int)odRdr["ID"];
attendanceReport.AttendanceRecordIDList = odRdr["AttendanceRecordIDList"].ToString();
odRdr.Close();
}
return attendanceReport;
}
public bool UpdateAttendanceReport(AttendanceReport attendanceReport)
{
OleDbCommand odCom1 = BuildUpdateCmd("AttendanceReport",
new string[] { "DayTypeID", "EmployeeNumber", "OvertimeHour1", "OvertimeHour2", "OvertimeHour3", "OvertimeHour4",
"OvertimeRate1","OvertimeRate2","OvertimeRate3","OvertimeRate4","PayPeriodID","RegularHour","RegularRate",
"WorkFrom","WorkTo","AttendanceRecordIDList"},
new object[] { attendanceReport.DayTypeID, attendanceReport.EmployeeNumber, attendanceReport.OvertimeHour1,
attendanceReport.OvertimeHour2,attendanceReport.OvertimeHour3,attendanceReport.OvertimeHour4, attendanceReport.OvertimeRate1,
attendanceReport.OvertimeRate2,attendanceReport.OvertimeRate3, attendanceReport.OvertimeRate4,attendanceReport.PayPeriodID,
attendanceReport.RegularHour,attendanceReport.RegularRate,attendanceReport.WorkFrom,attendanceReport.WorkTo,attendanceReport.AttendanceRecordIDList},
"ID=@ID", new object[] { "@ID", attendanceReport.ID }
);
return ExecuteNonQuery(odCom1) > 0;
}
private bool AddAttendanceReport(AttendanceRecord attRecord, WorkingCalendar workingCalendar)
{
string attRecordIDs = "{" + attRecord.ID + "}";
PaymentRate paymentRate = GetPaymentRateByAttendanceRecord(attRecord);
AttendanceReport attendanceReport = new AttendanceReport();
attendanceReport.DayTypeID = paymentRate.DayTypeID;
attendanceReport.EmployeeNumber = attRecord.EmployeeNumber;
attendanceReport.RegularHour = 0;
attendanceReport.OvertimeHour1 = 0;
attendanceReport.OvertimeHour2 = 0;
attendanceReport.OvertimeHour3 = 0;
attendanceReport.OvertimeHour4 = 0;
attendanceReport.RegularRate = paymentRate.RegularRate;
attendanceReport.OvertimeRate1 = paymentRate.OvertimeRate1;
attendanceReport.OvertimeRate2 = paymentRate.OvertimeRate2;
attendanceReport.OvertimeRate3 = paymentRate.OvertimeRate3;
attendanceReport.OvertimeRate4 = paymentRate.OvertimeRate4;
attendanceReport.PayPeriodID = workingCalendar.PayPeriodID;
attendanceReport.WorkFrom = workingCalendar.RegularWorkingFrom;
attendanceReport.WorkTo = workingCalendar.RegularWorkingTo;
attendanceReport.AttendanceRecordIDList = attRecordIDs;
return AddAttendanceReport(attendanceReport);
}
public int AddAttendanceReport(AttendanceReport attendanceReport, bool returnID)
{
OleDbCommand odCom1 = BuildInsertCmd("AttendanceReport",
new string[] { "DayTypeID", "EmployeeNumber", "OvertimeHour1", "OvertimeHour2", "OvertimeHour3", "OvertimeHour4",
"OvertimeRate1","OvertimeRate2","OvertimeRate3","OvertimeRate4","PayPeriodID","RegularHour","RegularRate",
"WorkFrom","WorkTo","AttendanceRecordIDList"},
new object[] { attendanceReport.DayTypeID, attendanceReport.EmployeeNumber, attendanceReport.OvertimeHour1,
attendanceReport.OvertimeHour2,attendanceReport.OvertimeHour3,attendanceReport.OvertimeHour4, attendanceReport.OvertimeRate1,
attendanceReport.OvertimeRate2,attendanceReport.OvertimeRate3, attendanceReport.OvertimeRate4,attendanceReport.PayPeriodID,
attendanceReport.RegularHour,attendanceReport.RegularRate,attendanceReport.WorkFrom,attendanceReport.WorkTo,attendanceReport.AttendanceRecordIDList}
);
if (odCom1.ExecuteNonQuery() > 0)
{
if (returnID)
{
odCom1.CommandText = "SELECT @@IDENTITY";
return Convert.ToInt32(odCom1.ExecuteScalar().ToString());
}
else
{
return 1;
}
}
return -1;
}
public bool AddAttendanceReport(AttendanceReport attendanceReport)
{
return AddAttendanceReport(attendanceReport, false) > 0;
}
public List<AttendanceReport> GetAttendanceReportList()
{
OleDbCommand odCom = BuildSelectCmd("AttendanceReport", "*", null);
OleDbDataReader odRdr = odCom.ExecuteReader();
List<AttendanceReport> attendanceReportList = new List<AttendanceReport>();
AttendanceReport attendanceReport = null;
while (odRdr.Read())
{
attendanceReport = new AttendanceReport();
attendanceReport.ID = Convert.ToInt32(odRdr["ID"]);
attendanceReport.EmployeeNumber = Convert.ToInt32(odRdr["EmployeeNumber"]);
attendanceReport.WorkFrom = Convert.ToDateTime(odRdr["WorkFrom"]);
attendanceReport.WorkTo = Convert.ToDateTime(odRdr["WorkTo"]);
attendanceReport.RegularHour = Convert.ToDouble(odRdr["RegularHour"]);
attendanceReport.RegularRate = Convert.ToDouble(odRdr["RegularRate"]);
attendanceReport.OvertimeHour1 = Convert.ToDouble(odRdr["OvertimeHour1"]);
attendanceReport.OvertimeRate1 = Convert.ToDouble(odRdr["OvertimeRate1"]);
attendanceReport.OvertimeHour2 = Convert.ToDouble(odRdr["OvertimeHour2"]);
attendanceReport.OvertimeRate2 = Convert.ToDouble(odRdr["OvertimeRate2"]);
attendanceReport.OvertimeHour3 = Convert.ToDouble(odRdr["OvertimeHour3"]);
attendanceReport.OvertimeRate3 = Convert.ToDouble(odRdr["OvertimeRate3"]);
attendanceReport.OvertimeHour4 = Convert.ToDouble(odRdr["OvertimeHour4"]);
attendanceReport.OvertimeRate4 = Convert.ToDouble(odRdr["OvertimeRate4"]);
attendanceReport.DayTypeID = Convert.ToInt16(odRdr["DayTypeID"]);
attendanceReport.PayPeriodID = Convert.ToInt16(odRdr["PayPeriodID"]);
attendanceReport.AttendanceRecordIDList = odRdr["AttendanceRecordIDList"].ToString();
attendanceReportList.Add(attendanceReport);
}
odRdr.Close();
return attendanceReportList;
}
public AttendanceReport GetAttendanceReportByAttendanceRecord(int attendanceRecordID)
{
OleDbCommand odCom = BuildSelectCmd("AttendanceReport", "*", "AttendanceRecordIDList LIKE '%{" + attendanceRecordID + "}%'");
OleDbDataReader odRdr = odCom.ExecuteReader();
AttendanceReport attendanceReport = null;
if (odRdr.Read())
{
attendanceReport = new AttendanceReport();
attendanceReport.DayTypeID = (int)odRdr["DayTypeID"];
attendanceReport.EmployeeNumber = (int)odRdr["EmployeeNumber"];
attendanceReport.OvertimeHour1 = (double)odRdr["OvertimeHour1"];
attendanceReport.OvertimeHour2 = (double)odRdr["OvertimeHour2"];
attendanceReport.OvertimeHour3 = (double)odRdr["OvertimeHour3"];
attendanceReport.OvertimeHour4 = (double)odRdr["OvertimeHour4"];
attendanceReport.OvertimeRate1 = (double)odRdr["OvertimeRate1"];
attendanceReport.OvertimeRate2 = (double)odRdr["OvertimeRate2"];
attendanceReport.OvertimeRate3 = (double)odRdr["OvertimeRate3"];
attendanceReport.OvertimeRate4 = (double)odRdr["OvertimeRate4"];
attendanceReport.PayPeriodID = (int)odRdr["PayPeriodID"];
attendanceReport.RegularHour = (double)odRdr["RegularHour"];
attendanceReport.RegularRate = (double)odRdr["RegularRate"];
if (typeof(DBNull) != odRdr["WorkFrom"].GetType())
attendanceReport.WorkFrom = (DateTime)odRdr["WorkFrom"];
if (typeof(DBNull) != odRdr["WorkTo"].GetType())
attendanceReport.WorkTo = (DateTime)odRdr["WorkTo"];
attendanceReport.ID = (int)odRdr["ID"];
attendanceReport.AttendanceRecordIDList = odRdr["AttendanceRecordIDList"].ToString();
}
odRdr.Close();
return attendanceReport;
}
public List<AttendanceReport> GetAttendanceReport(int companyID, int departmentID, DateTime dtFrom, DateTime dtTo, int columnIndex, bool isOrderByAcs)
{
List<AttendanceReport> attendanceReportList = new List<AttendanceReport>();
List<string> lEmplNumbers = GetEmployeeNumberList(companyID, departmentID);
if (lEmplNumbers == null || lEmplNumbers.Count == 0)
return attendanceReportList;
string sEmplNumbers = string.Join(",", lEmplNumbers.ToArray());
string orderBy = "EmployeeNumber, WorkFrom";
if (columnIndex == 0 || columnIndex == 1)
orderBy = "EmployeeNumber" + (isOrderByAcs ? " ASC" : " DESC") + ", WorkFrom ASC";
else if (columnIndex == 2)
orderBy = "WorkFrom" + (isOrderByAcs ? " ASC" : " DESC") + ", EmployeeNumber ASC";
OleDbCommand odCom = BuildSelectCmd("AttendanceReport", "*", "WorkFrom >=@Date_1 AND WorkFrom <= @Date_2 AND EmployeeNumber in(" + sEmplNumbers + ")", orderBy,
new object[] { "@Date_1", dtFrom, "@Date_2", dtTo });
AttendanceReport attendanceReport = null;
OleDbDataReader odRdr = odCom.ExecuteReader();
while (odRdr.Read())
{
attendanceReport = new AttendanceReport();
attendanceReport.DayTypeID = (int)odRdr["DayTypeID"];
attendanceReport.EmployeeNumber = (int)odRdr["EmployeeNumber"];
attendanceReport.OvertimeHour1 = (double)odRdr["OvertimeHour1"];
attendanceReport.OvertimeHour2 = (double)odRdr["OvertimeHour2"];
attendanceReport.OvertimeHour3 = (double)odRdr["OvertimeHour3"];
attendanceReport.OvertimeHour4 = (double)odRdr["OvertimeHour4"];
attendanceReport.OvertimeRate1 = (double)odRdr["OvertimeRate1"];
attendanceReport.OvertimeRate2 = (double)odRdr["OvertimeRate2"];
attendanceReport.OvertimeRate3 = (double)odRdr["OvertimeRate3"];
attendanceReport.OvertimeRate4 = (double)odRdr["OvertimeRate4"];
attendanceReport.PayPeriodID = (int)odRdr["PayPeriodID"];
attendanceReport.RegularHour = (double)odRdr["RegularHour"];
attendanceReport.RegularRate = (double)odRdr["RegularRate"];
attendanceReport.WorkFrom = (DateTime)odRdr["WorkFrom"];
attendanceReport.WorkTo = (DateTime)odRdr["WorkTo"];
attendanceReport.ID = (int)odRdr["ID"];
attendanceReport.AttendanceRecordIDList = odRdr["AttendanceRecordIDList"].ToString();
attendanceReportList.Add(attendanceReport);
}
odRdr.Close();
return attendanceReportList;
}
