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; }