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 bool ReProcessAttendanceReport(AttendanceRecord attRecord)
{
int id = AddAttendanceRecord(attRecord);
if (id > 0)
{
string[] strRecod = System.IO.File.ReadAllLines("attendanceRecords.txt");
List<string> strRecords = new List<string>(strRecod);
strRecords.RemoveAt(0);
System.IO.File.WriteAllLines("attendanceRecords.txt", strRecords.ToArray());
return true;
}
return false;
}
public bool UpdateAttendanceRecord(AttendanceRecord attRecord)
{
if (attRecord == null || IsInvalidAttendanceRecord(attRecord, true))
return false;
BeginTransaction();
OleDbCommand odCom = BuildUpdateCmd("AttendanceRecord",
new string[] { "EmployeeNumber", "Note", "Time" },
new object[] { attRecord.EmployeeNumber, attRecord.Note, attRecord.Time },
"ID=@ID", new object[] { "@ID", attRecord.ID }
);
bool oRs1 = ExecuteNonQuery(odCom) > 0 ? true : false;
bool oRs2 = true;
AttendanceReport attendanceReport = GetAttendanceReportByAttendanceRecord(attRecord.ID);
if (attendanceReport != null)
{
oRs2 = AddUpdateAttendaceReport(attRecord, attendanceReport);
}
if (oRs1 && oRs2)
{
CommitTransaction();
return true;
}
else
{
RollbackTransaction();
return false;
}
}
private void AddTestMultiShiftNonFlexi(ref IDataController _dtCtrl)
{
#region add test company
Company com = new Company();
com.Name = DateTime.Now.Ticks.ToString();
com.ID = _dtCtrl.AddCompany(com);
#endregion
#region add test department
Department dep = new Department();
dep.CompanyID = com.ID;
dep.Name = DateTime.Now.Ticks.ToString();
dep.SupDepartmentID = 0; //root
dep.ID = _dtCtrl.AddDepartment(dep);
#endregion
#region add test working calendar
WorkingCalendar wCal = new WorkingCalendar();
wCal.Name = DateTime.Now.Ticks.ToString();
wCal.WorkOnMonday = true;
wCal.WorkOnTuesday = true;
wCal.WorkOnWednesday = true;
wCal.WorkOnThursday = true;
wCal.WorkOnFriday = true;
wCal.GraceForwardToEntry = 30;
wCal.GraceBackwardToExit = 30;
wCal.EarliestBeforeEntry = 60;
wCal.LastestAfterExit = 60;
List<Shift> shiftList = new List<Shift>();
Shift shift1 = new Shift();
shift1.From = new DateTime(2000, 2, 2, 8, 0, 0);
shift1.To = new DateTime(2000, 2, 2, 12, 0, 0);
shiftList.Add(shift1);
Shift shift2 = new Shift();
shift2.From = new DateTime(2000, 2, 2, 14, 0, 0);
shift2.To = new DateTime(2000, 2, 2, 18, 0, 0);
shiftList.Add(shift2);
Shift shift3 = new Shift();
shift3.From = new DateTime(2000, 2, 2, 20, 0, 0);
shift3.To = new DateTime(2000, 2, 3, 0, 0, 0);
shiftList.Add(shift3);
List<Break> breakList = new List<Break>();
//Break break1 = new Break();
//break1.From = new DateTime(2000, 2, 2, 12, 0, 0);
//break1.To = new DateTime(2000, 2, 2, 13, 0, 0);
//break1.Name = "break1";
//break1.Paid = true;
//breakList.Add(break1);
List<Holiday> holidayList = new List<Holiday>();
PaymentRate workingDayPaymentRate = new PaymentRate();
workingDayPaymentRate.NumberOfRegularHours = 4;
workingDayPaymentRate.RegularRate = 100;
workingDayPaymentRate.NumberOfOvertime1 = 1;
workingDayPaymentRate.OvertimeRate1 = 200;
PaymentRate nonWorkingDayPaymentRate = workingDayPaymentRate;
PaymentRate holidayPaymentRate = workingDayPaymentRate;
PayPeriod payPeriod = new PayPeriod();
payPeriod.CustomPeriod = 5;
payPeriod.PayPeriodTypeID = 5; //custom
payPeriod.StartFrom = new DateTime(2010, 1, 1);
wCal.ID = _dtCtrl.AddWorkingCalendar(wCal, shiftList, breakList, holidayList, workingDayPaymentRate, nonWorkingDayPaymentRate, holidayPaymentRate, payPeriod);
#endregion
#region add test employee
Employee emp = new Employee();
emp.Active = true;
emp.ActiveFrom = DateTime.Today;
emp.ActiveTo = DateTime.Today.AddDays(1);
emp.Address = DateTime.Now.Ticks.ToString();
emp.Birthday = DateTime.Today.AddYears(-20);
emp.DepartmentID = dep.ID;
emp.EmployeeNumber = 0;
emp.FirstName = DateTime.Now.Ticks.ToString();
emp.JobDescription = DateTime.Now.Ticks.ToString();
emp.HiredDate = DateTime.Today;
emp.LeftDate = DateTime.Today.AddYears(1);
emp.LastName = DateTime.Now.Ticks.ToString();
emp.PhoneNumber = DateTime.Now.Ticks.ToString();
emp.WorkingCalendarID = wCal.ID;
emp.PayrollNumber = _dtCtrl.AddEmployee(emp, new List<Terminal>());
#endregion
#region add test att records 1
//att1 : expected totalHours: 4
AttendanceRecord att11 = new AttendanceRecord();
att11.EmployeeNumber = emp.EmployeeNumber;
att11.Time = new DateTime(2010, 1, 1, 8, 0, 0);
att11.ID = _dtCtrl.AddAttendanceRecord(att11);
AttendanceRecord att12 = new AttendanceRecord();
att12.EmployeeNumber = emp.EmployeeNumber;
att12.Time = new DateTime(2010, 1, 1, 12, 0, 0);
att12.ID = _dtCtrl.AddAttendanceRecord(att12);
//att2 : expected totalHours: 4
AttendanceRecord att13 = new AttendanceRecord();
att13.EmployeeNumber = emp.EmployeeNumber;
att13.Time = new DateTime(2010, 1, 1, 14, 0, 0);
att13.ID = _dtCtrl.AddAttendanceRecord(att13);
AttendanceRecord att14 = new AttendanceRecord();
att14.EmployeeNumber = emp.EmployeeNumber;
att14.Time = new DateTime(2010, 1, 1, 18, 0, 0);
att14.ID = _dtCtrl.AddAttendanceRecord(att14);
//att3 : expected totalHours: 4
AttendanceRecord att21 = new AttendanceRecord();
att21.EmployeeNumber = emp.EmployeeNumber;
att21.Time = new DateTime(2010, 1, 1, 20, 0, 0);
att21.ID = _dtCtrl.AddAttendanceRecord(att21);
AttendanceRecord att22 = new AttendanceRecord();
att22.EmployeeNumber = emp.EmployeeNumber;
att22.Time = new DateTime(2010, 1, 2, 0, 0, 0);
att22.ID = _dtCtrl.AddAttendanceRecord(att22);
//att4 : expected totalHours: 2.67
AttendanceRecord att31 = new AttendanceRecord();
att31.EmployeeNumber = emp.EmployeeNumber;
att31.Time = new DateTime(2010, 1, 2, 8, 15, 0);
att31.ID = _dtCtrl.AddAttendanceRecord(att31);
AttendanceRecord att32 = new AttendanceRecord();
att32.EmployeeNumber = emp.EmployeeNumber;
att32.Time = new DateTime(2010, 1, 2, 11, 0, 0);
att32.ID = _dtCtrl.AddAttendanceRecord(att32);
//att4 : expected totalHours: 4 + 1 over
AttendanceRecord att41 = new AttendanceRecord();
att41.EmployeeNumber = emp.EmployeeNumber;
att41.Time = new DateTime(2010, 1, 2, 14, 00, 0);
att41.ID = _dtCtrl.AddAttendanceRecord(att41);
AttendanceRecord att42 = new AttendanceRecord();
att42.EmployeeNumber = emp.EmployeeNumber;
att42.Time = new DateTime(2010, 1, 2, 19, 0, 0);
att42.ID = _dtCtrl.AddAttendanceRecord(att42);
//att4 : expected totalHours:
AttendanceRecord att51 = new AttendanceRecord();
att51.EmployeeNumber = emp.EmployeeNumber;
att51.Time = new DateTime(2010, 1, 2, 20, 06, 0);
att51.ID = _dtCtrl.AddAttendanceRecord(att51);
AttendanceRecord att52 = new AttendanceRecord();
att52.EmployeeNumber = emp.EmployeeNumber;
att52.Time = new DateTime(2010, 1, 2, 23, 2, 0);
att52.ID = _dtCtrl.AddAttendanceRecord(att52);
#endregion
}
public List<AttendanceRecord> GetReprocessAttendanceReport(string _employeeNumberList, DateTime _dReprocessFrom, DateTime _dReprocessTo)
{
List<AttendanceRecord> attendanceRecords = new List<AttendanceRecord>();
AttendanceRecord attRecord = null;
string attRcList = "";
string attRpList = "";
OleDbCommand odCom = BuildSelectCmd("AttendanceReport", "*", "WorkFrom >=@Date_1 AND WorkFrom <= @Date_2 AND EmployeeNumber in(" + _employeeNumberList + ")",
new object[] { "@Date_1", _dReprocessFrom, "@Date_2", _dReprocessTo });
OleDbDataReader odRdr = odCom.ExecuteReader();
while (odRdr.Read())
{
attRpList += odRdr["ID"].ToString() + ",";
attRcList += odRdr["AttendanceRecordIDList"].ToString();
}
odRdr.Close();
if (attRpList == "")
return null;
attRpList = attRpList.TrimEnd(',');
attRcList = attRcList.Replace("{", "").Replace("}", ",").TrimEnd(',');
System.IO.StreamWriter strw = System.IO.File.AppendText("attendanceRecords.txt");
odCom = BuildSelectCmd("AttendanceRecord", "*", "ID in(" + attRcList + ")");
odRdr = odCom.ExecuteReader();
while (odRdr.Read())
{
attRecord = new AttendanceRecord();
attRecord.ID = (int)odRdr["ID"];
attRecord.EmployeeNumber = (int)odRdr["EmployeeNumber"];
attRecord.Note = odRdr["Note"].ToString();
//attRecord.PhotoData = odRdr["PhotoData"].ToString();
attRecord.Time = (DateTime)odRdr["Time"];
attendanceRecords.Add(attRecord);
strw.WriteLine(attRecord.ID + "," + attRecord.EmployeeNumber + "," + attRecord.Time + "," + attRecord.Note);// + "," + attRecord.PhotoData);
}
odRdr.Close();
strw.Flush();
strw.Close();
odCom = BuildDelCmd("AttendanceRecord", "ID in(" + attRcList + ")");
ExecuteNonQuery(odCom);
odCom = BuildDelCmd("AttendanceReport", "ID in(" + attRpList + ")");
ExecuteNonQuery(odCom);
return attendanceRecords;
}
private DateTime GetWorkingDayByAttendanceRecord(AttendanceRecord attRecord)
{
WorkingCalendar wCal = GetWorkingCalendarByEmployee(attRecord.EmployeeNumber);
DateTime dRegularWorkingFrom = wCal.RegularWorkingFrom;
DateTime wFrom = new DateTime(attRecord.Time.Year, attRecord.Time.Month, attRecord.Time.Day, dRegularWorkingFrom.Hour,
dRegularWorkingFrom.Minute, dRegularWorkingFrom.Second).AddMinutes(-1 * wCal.EarliestBeforeEntry);
if (attRecord.Time.CompareTo(wFrom) == 1) //after earliest allowed entry
{
if (attRecord.Time.CompareTo(wFrom.AddDays(1)) != -1) //TODO how could this happen?
return wFrom.AddDays(1);
else
return wFrom;
}
else
{
if (Is1stday(attRecord))
return wFrom;
else
return wFrom.AddDays(-1);
}
}
private bool IsInvalidAttendanceRecord(AttendanceRecord attRecord, bool forUpdate)
{
int attRecInterval = GetAttendanceRecordInterval();
OleDbCommand odCom;
if (forUpdate)
odCom = BuildSelectCmd("AttendanceRecord", "ID", "ID<>@ID AND EmployeeNumber=@EmployeeNumber AND Time>@Time_1 AND Time<@Time_2",
new object[] { "@ID", attRecord.ID, "@EmployeeNumber", attRecord.EmployeeNumber,
"@Time_1", attRecord.Time.AddMinutes(-attRecInterval), "@Time_2", attRecord.Time.AddMinutes(attRecInterval) });
else
odCom = BuildSelectCmd("AttendanceRecord", "ID", "EmployeeNumber=@EmployeeNumber AND Time>@Time_1 AND Time<@Time_2",
new object[] { "@EmployeeNumber", attRecord.EmployeeNumber,
"@Time_1", attRecord.Time.AddMinutes(-attRecInterval), "@Time_2", attRecord.Time.AddMinutes(attRecInterval) });
OleDbDataReader odRdr = odCom.ExecuteReader();
if (odRdr.Read())
{
odRdr.Close();
return true;
}
return false;
}
//not use
private DateTime GetWorkingDayByAttendanceRecord(AttendanceRecord attRecord, WorkingCalendar wCal)
{
List<Shift> shiftList = GetShiftListByWorkingCalendar(wCal.ID);
DateTime dRegularWorkingFrom = shiftList[0].From;
DateTime wFrom = new DateTime(attRecord.Time.Year, attRecord.Time.Month, attRecord.Time.Day, dRegularWorkingFrom.Hour,
dRegularWorkingFrom.Minute, dRegularWorkingFrom.Second).AddMinutes(-1 * wCal.EarliestBeforeEntry);
if (attRecord.Time.CompareTo(wFrom) == 1) //after earliest allowed entry
{
if (attRecord.Time.CompareTo(wFrom.AddDays(1)) != -1) //TODO how could this happen?
return wFrom.AddDays(1);
else
return wFrom;
}
else
{
if (Is1stday(attRecord))
return wFrom;
else
return wFrom.AddDays(-1);
}
}
public List<AttendanceLogRecord> GetAttendanceLogRecordList(int iCompany, int iDepartment, DateTime beginDate, DateTime endDate, int columnIndex, bool isOrderByAcs)
{
List<AttendanceReport> attendanceReports = GetAttendanceReport(iCompany, iDepartment, beginDate, endDate, columnIndex, isOrderByAcs);
if (attendanceReports.Count == 0)
return null;
List<string> lEmplNumbers = GetEmployeeNumberList(iCompany, iDepartment);
if (lEmplNumbers == null || lEmplNumbers.Count == 0)
return null;
string sEmplNumbers = string.Join(",", lEmplNumbers.ToArray());
OleDbCommand odCom = BuildSelectCmd("Employee", "EmployeeNumber, FirstName, LastName", "EmployeeNumber IN(" + sEmplNumbers + ") AND Active=TRUE");
OleDbDataReader odRdr = odCom.ExecuteReader();
List<Employee> employeeList = new List<Employee>();
Employee empl;
while (odRdr.Read())
{
empl = new Employee();
empl.EmployeeNumber = (int)odRdr["EmployeeNumber"];
empl.LastName = odRdr["LastName"].ToString();
empl.FirstName = odRdr["FirstName"].ToString();
employeeList.Add(empl);
}
odRdr.Close();
List<AttendanceLogRecord> attLogList = new List<AttendanceLogRecord>();
AttendanceLogRecord attLog = null;
List<AttendanceRecord> attRecordList = new List<AttendanceRecord>();
AttendanceRecord attRecord = null;
foreach (AttendanceReport attReport in attendanceReports)
{
string sAttendanceRecordIDs = attReport.AttendanceRecordIDList;
sAttendanceRecordIDs = sAttendanceRecordIDs.Replace("{", "").Replace("}", ",").Trim(',');
odCom = BuildSelectCmd("AttendanceRecord", "*", "ID IN(" + sAttendanceRecordIDs + ")");
odRdr = odCom.ExecuteReader();
attRecordList.Clear();
while (odRdr.Read())
{
attRecord = new AttendanceRecord();
attRecord.ID = (int)odRdr["ID"];
attRecord.EmployeeNumber = (int)odRdr["EmployeeNumber"];
attRecord.Note = odRdr["Note"].ToString();
attRecord.Time = (DateTime)odRdr["Time"];
attRecordList.Add(attRecord);
}
odRdr.Close();
attRecordList.Sort(delegate(AttendanceRecord e1, AttendanceRecord e2) { return e1.Time.CompareTo(e2.Time); });
int roundValue = GetConfig().RecordRoundingValue;
foreach (AttendanceRecord att in attRecordList)
{
att.Time = Util.RoundDateTime(att.Time, roundValue);
}
bool isCheckIn = true;
bool isFirst = true;
DateTime dDateLog = attRecordList[0].Time.Date;
foreach (AttendanceRecord att in attRecordList)
{
attLog = new AttendanceLogRecord();
if (isFirst)
{
attLog.EmployeeNumber = attReport.EmployeeNumber;
attLog.DateLog = attReport.WorkFrom.Date;
//TODO wrong number, total hours here is based on the in/out, not report
attLog.TotalHours = Math.Round(CalculateTotalHours(attRecordList), 2);
Employee employee = employeeList.Find(delegate(Employee e) { return e.EmployeeNumber == attReport.EmployeeNumber; });
attLog.EmployeeName = employee.LastName + ", " + employee.FirstName;
isFirst = false;
}
attLog.ID = att.ID;
attLog.TimeLog = (isCheckIn ? "In " : "Out ") + att.Time.ToString("HH:mm");
if (att.Time.Date.CompareTo(attReport.WorkFrom.Date) > 0)
attLog.TimeLog += " [" + att.Time.Date.ToShortDateString() + "]";
attLog.Note = att.Note;
attLogList.Add(attLog);
isCheckIn = !isCheckIn;
}
if (isCheckIn == false && dDateLog.Equals(DateTime.Now.Date) == false)
{
attLog = new AttendanceLogRecord();
//attLog.DateLog = attReport.WorkFrom.Date;
//attLog.EmployeeNumber = attReport.EmployeeNumber;
attLog.TimeLog = "OutMistakes";
attLogList.Add(attLog);
}
}
List<RoostedDayOff> roostedDayOffList = GetRoostedDayOffList();
Hashtable hasEmplName = new Hashtable();
if (roostedDayOffList.Count > 0)
{
AttendanceLogRecord attRc = null;
foreach (RoostedDayOff roostedDayOff in roostedDayOffList)
{
string employeeName = null;
if (hasEmplName.ContainsKey(roostedDayOff.EmployeeNumber))
{
employeeName = (string)hasEmplName[roostedDayOff.EmployeeNumber];
}
else
{
Employee employee = GetEmployeeByEmployeeNumber(roostedDayOff.EmployeeNumber);
if (employee == null)
continue;
employeeName = employee.LastName + ", " + employee.FirstName;
hasEmplName.Add(roostedDayOff.EmployeeNumber, employeeName );
}
attRc = new AttendanceLogRecord();
attRc.EmployeeNumber = roostedDayOff.EmployeeNumber;
attRc.EmployeeName = employeeName;
attRc.DateLog = roostedDayOff.Date;
attRc.TotalHours= roostedDayOff.TotalHours;
attRc.TimeLog = "Roosted day off";
attRc.Note = roostedDayOff.Note;
int indexRp = attLogList.FindIndex(0, delegate(AttendanceLogRecord e) { return e.EmployeeNumber == attRc.EmployeeNumber && e.DateLog.Date.CompareTo(attRc.DateLog.Date) == 1; });
if (indexRp < 0)
{
indexRp = attLogList.FindLastIndex(delegate(AttendanceLogRecord e) { return e.EmployeeNumber == attRc.EmployeeNumber; });
if (indexRp < 0)
{
indexRp = attLogList.FindIndex(delegate(AttendanceLogRecord e) { return e.EmployeeNumber > attRc.EmployeeNumber; });
if (indexRp < 0)
{
indexRp = attLogList.Count;
}
}
else
indexRp++;
}
int indexRp_1 = 0;
while (true)
{
if (indexRp > attLogList.Count - 1)
break;
indexRp_1 = attLogList.FindIndex(indexRp, 1, delegate(AttendanceLogRecord e) { return e.DateLog.Equals(DateTime.MinValue); });
if (indexRp_1 < 1)
break;
indexRp++;
}
attLogList.Insert(indexRp, attRc);
}
hasEmplName.Clear();
roostedDayOffList.Clear();
}
return attLogList;
}
private void AddRecord()
{
try
{
_startPoint = DateTime.Now.Ticks;
SetState(1);
int toBeAdded = (int)nudAdd.Value;
Invoke(new InitProgressCallBack(InitProgress), new object[] { toBeAdded });
Invoke(new SetTextCallBack(SetText), new object[] { txtProgress, "" });
Invoke(new SetTextCallBack(SetText), new object[] { txtDuration, "" });
int added = 0;
for (int i = 0; i < toBeAdded; i++)
{
AttendanceRecord attRecord = new AttendanceRecord();
attRecord.EmployeeNumber = GetRandomEmployeeNumber();
attRecord.Time = GetRandomTime();
attRecord.ID = _dtCtrl.AddAttendanceRecord(attRecord);
if (attRecord.ID > 0)
{
added++;
_attRecordList.Add(attRecord);
}
string textProgress = "Adding: " + (i + 1) + "/" + toBeAdded + " (Added: " + added + ")";
Invoke(new AddProgressCallBack(AddProgress), new object[] { 1 });
Invoke(new SetTextCallBack(SetText), new object[] { txtProgress, textProgress });
Invoke(new SetTextCallBack(SetText), new object[] { txtDuration, GetDuration() });
}
//Invoke(new SetTextCallBack(AddText), new object[] { txtProgress, "Calculating" });
//_dtCtrl.CalculateAttendanceRecord();
//_dtCtrl.RefreshConnection();
//Invoke(new SetTextCallBack(AddText), new object[] { txtProgress, "DONE" });
Invoke(new SetTextCallBack(SetText), new object[] { txtDuration, GetDuration() });
MessageBox.Show(added + " records have been added");
}
catch (Exception ex)
{
MessageBox.Show("[" + ex.Message + "]" + ex.StackTrace);
}
SetState(0);
}
private void CalculateWorkingDayByAttendanceRecord(ref DateTime dWorkingFrom, ref DateTime dWorkingTo, AttendanceRecord attRecord, WorkingCalendar wCal, List<Shift> shiftList)
{
if (shiftList.Count == 1)
{
DateTime dRegularWorkingFrom = shiftList[0].From;
DateTime dRegularWorkingTo = shiftList[0].To;
dWorkingFrom = new DateTime(attRecord.Time.Year, attRecord.Time.Month, attRecord.Time.Day, dRegularWorkingFrom.Hour,
dRegularWorkingFrom.Minute, dRegularWorkingFrom.Second).AddMinutes(-1 * wCal.EarliestBeforeEntry);
if (attRecord.Time.CompareTo(dWorkingFrom) == 1) //after earliest allowed entry
{
if (attRecord.Time.CompareTo(dWorkingFrom.AddDays(1)) != -1) //TODO how could this happen?
dWorkingFrom = dWorkingFrom.AddDays(1);
}
else
{
if (!Is1stday(attRecord))
dWorkingFrom = dWorkingFrom.AddDays(-1);
}
dWorkingTo = dWorkingFrom.Date.AddHours(dRegularWorkingTo.Hour).AddMinutes(dRegularWorkingTo.Minute + wCal.LastestAfterExit + wCal.EarliestBeforeEntry);
if (dWorkingFrom.CompareTo(dWorkingTo) == 1)
dWorkingTo.AddDays(1);
}
else
{
DateTime dRegularWorkingFrom, dRegularWorkingTo;
bool overDay = true;
for (int i = 0; i < shiftList.Count; i++)
{
dRegularWorkingFrom = shiftList[i].From;
dRegularWorkingTo = shiftList[i].To;
double distanceMinute = TimeSpan.FromTicks(dRegularWorkingTo.Ticks - dRegularWorkingFrom.Ticks).TotalMinutes;
dWorkingFrom = new DateTime(attRecord.Time.Year, attRecord.Time.Month, attRecord.Time.Day, dRegularWorkingFrom.Hour,
dRegularWorkingFrom.Minute, dRegularWorkingFrom.Second).AddMinutes(-1 * wCal.EarliestBeforeEntry);
dWorkingTo = dWorkingFrom.AddMinutes(distanceMinute + wCal.LastestAfterExit + wCal.EarliestBeforeEntry);
if (dWorkingFrom.CompareTo(attRecord.Time) != 1 && dWorkingTo.CompareTo(attRecord.Time) != -1)
{
overDay = false;
break;
}
}
if (overDay)
{
for (int i = 0; i < shiftList.Count; i++)
{
dRegularWorkingFrom = shiftList[i].From;
dRegularWorkingTo = shiftList[i].To;
double distanceMinute = TimeSpan.FromTicks(dRegularWorkingTo.Ticks - dRegularWorkingFrom.Ticks).TotalMinutes;
dWorkingFrom = new DateTime(attRecord.Time.Year, attRecord.Time.Month, attRecord.Time.Day, dRegularWorkingFrom.Hour,
dRegularWorkingFrom.Minute, dRegularWorkingFrom.Second).AddMinutes(-1 * wCal.EarliestBeforeEntry);
dWorkingFrom = dWorkingFrom.AddDays(-1);
dWorkingTo = dWorkingFrom.AddMinutes(distanceMinute + wCal.LastestAfterExit + wCal.EarliestBeforeEntry);
if (dWorkingFrom.CompareTo(attRecord.Time) != 1 && dWorkingTo.CompareTo(attRecord.Time) != -1)
{
break;
}
}
}
}
}
private void AddTestData()
{
_dtCtrl.BeginTransaction();
try
{
Invoke(new SetTextCallBack(SetText), new object[] { txtProgress, "Adding ..." });
//add test company
Company com = new Company();
com.Name = DateTime.Now.Ticks.ToString();
com.ID = _dtCtrl.AddCompany(com);
//add test department
Department dep = new Department();
dep.CompanyID = com.ID;
dep.Name = DateTime.Now.Ticks.ToString();
dep.SupDepartmentID = 0; //root
dep.ID = _dtCtrl.AddDepartment(dep);
//add test working calendar
WorkingCalendar wCal = new WorkingCalendar();
wCal.Name = DateTime.Now.Ticks.ToString();
wCal.RegularWorkingFrom = new DateTime(2000, 2, 2, 9, 0, 0);
wCal.RegularWorkingTo = new DateTime(2000, 2, 2, 18, 0, 0);
wCal.WorkOnMonday = true;
wCal.WorkOnTuesday = true;
wCal.WorkOnWednesday = true;
wCal.WorkOnThursday = true;
wCal.WorkOnFriday = true;
wCal.GraceForwardToEntry = 30;
wCal.GraceBackwardToExit = 30;
wCal.EarliestBeforeEntry = 60;
wCal.LastestAfterExit = 180;
List<Break> breakList = new List<Break>();
Break break1 = new Break();
break1.From = new DateTime(2000, 2, 2, 12, 0, 0);
break1.To = new DateTime(2000, 2, 2, 13, 0, 0);
break1.Name = "break1";
break1.Paid = true;
breakList.Add(break1);
List<Holiday> holidayList = new List<Holiday>();
PaymentRate workingDayPaymentRate = new PaymentRate();
workingDayPaymentRate.NumberOfRegularHours = 8;
workingDayPaymentRate.RegularRate = 100;
workingDayPaymentRate.NumberOfOvertime1 = 8;
workingDayPaymentRate.OvertimeRate1 = 200;
PaymentRate nonWorkingDayPaymentRate = workingDayPaymentRate;
PaymentRate holidayPaymentRate = workingDayPaymentRate;
PayPeriod payPeriod = new PayPeriod();
payPeriod.CustomPeriod = 5;
payPeriod.PayPeriodTypeID = 5; //custom
payPeriod.StartFrom = new DateTime(2010, 1, 1);
wCal.ID = _dtCtrl.AddWorkingCalendar(wCal, breakList, holidayList, workingDayPaymentRate, nonWorkingDayPaymentRate, holidayPaymentRate, payPeriod);
//add test employee
Employee emp = new Employee();
emp.Active = true;
emp.ActiveFrom = DateTime.Today;
emp.ActiveTo = DateTime.Today.AddDays(1);
emp.Address = DateTime.Now.Ticks.ToString();
emp.Birthday = DateTime.Today.AddYears(-20);
emp.DepartmentID = dep.ID;
emp.EmployeeNumber = 0;
emp.FirstName = DateTime.Now.Ticks.ToString();
emp.JobDescription = DateTime.Now.Ticks.ToString();
emp.HiredDate = DateTime.Today;
emp.LeftDate = DateTime.Today.AddYears(1);
emp.LastName = DateTime.Now.Ticks.ToString();
emp.PhoneNumber = DateTime.Now.Ticks.ToString();
emp.WorkingCalendarID = wCal.ID;
emp.PayrollNumber = _dtCtrl.AddEmployee(emp, new List<Terminal>());
//add test att records
//att1 : expected totalHours: 9
AttendanceRecord att11 = new AttendanceRecord();
att11.EmployeeNumber = emp.EmployeeNumber;
att11.Time = new DateTime(2010, 1, 1, 9, 0, 0);
att11.ID = _dtCtrl.AddAttendanceRecord(att11);
AttendanceRecord att12 = new AttendanceRecord();
att12.EmployeeNumber = emp.EmployeeNumber;
att12.Time = new DateTime(2010, 1, 1, 18, 0, 0);
att12.ID = _dtCtrl.AddAttendanceRecord(att12);
AttendanceRecord att13 = new AttendanceRecord();
att13.EmployeeNumber = emp.EmployeeNumber;
att13.Time = new DateTime(2010, 1, 1, 12, 0, 0);
att13.ID = _dtCtrl.AddAttendanceRecord(att13);
AttendanceRecord att14 = new AttendanceRecord();
att14.EmployeeNumber = emp.EmployeeNumber;
att14.Time = new DateTime(2010, 1, 1, 13, 0, 0);
att14.ID = _dtCtrl.AddAttendanceRecord(att14);
//att2 : expected totalHours: 9
AttendanceRecord att21 = new AttendanceRecord();
att21.EmployeeNumber = emp.EmployeeNumber;
att21.Time = new DateTime(2010, 1, 2, 8, 45, 0);
att21.ID = _dtCtrl.AddAttendanceRecord(att21);
AttendanceRecord att22 = new AttendanceRecord();
att22.EmployeeNumber = emp.EmployeeNumber;
att22.Time = new DateTime(2010, 1, 2, 18, 15, 0);
att22.ID = _dtCtrl.AddAttendanceRecord(att22);
//att3 : expected totalHours: 9.75
AttendanceRecord att31 = new AttendanceRecord();
att31.EmployeeNumber = emp.EmployeeNumber;
att31.Time = new DateTime(2010, 1, 3, 8, 15, 0);
att31.ID = _dtCtrl.AddAttendanceRecord(att31);
AttendanceRecord att32 = new AttendanceRecord();
att32.EmployeeNumber = emp.EmployeeNumber;
att32.Time = new DateTime(2010, 1, 3, 18, 0, 0);
att32.ID = _dtCtrl.AddAttendanceRecord(att32);
//att4 : expected totalHours: 0 + out mistake alert
AttendanceRecord att41 = new AttendanceRecord();
att41.EmployeeNumber = emp.EmployeeNumber;
att41.Time = new DateTime(2010, 1, 4, 7, 00, 0);
att41.ID = _dtCtrl.AddAttendanceRecord(att41);
AttendanceRecord att42 = new AttendanceRecord();
att42.EmployeeNumber = emp.EmployeeNumber;
att42.Time = new DateTime(2010, 1, 4, 18, 0, 0);
att42.ID = _dtCtrl.AddAttendanceRecord(att42);
//att5 : expected totalHours: 8.8x
AttendanceRecord att51 = new AttendanceRecord();
att51.EmployeeNumber = emp.EmployeeNumber;
att51.Time = new DateTime(2010, 1, 5, 9, 06, 0);
att51.ID = _dtCtrl.AddAttendanceRecord(att51);
AttendanceRecord att52 = new AttendanceRecord();
att52.EmployeeNumber = emp.EmployeeNumber;
att52.Time = new DateTime(2010, 1, 5, 18, 2, 0);
att52.ID = _dtCtrl.AddAttendanceRecord(att52);
//att6 : expected totalHours: 8.5
AttendanceRecord att61 = new AttendanceRecord();
att61.EmployeeNumber = emp.EmployeeNumber;
att61.Time = new DateTime(2010, 1, 6, 9, 0, 0);
att61.ID = _dtCtrl.AddAttendanceRecord(att61);
AttendanceRecord att62 = new AttendanceRecord();
att62.EmployeeNumber = emp.EmployeeNumber;
att62.Time = new DateTime(2010, 1, 6, 18, 0, 0);
att62.ID = _dtCtrl.AddAttendanceRecord(att62);
AttendanceRecord att63 = new AttendanceRecord();
att63.EmployeeNumber = emp.EmployeeNumber;
att63.Time = new DateTime(2010, 1, 6, 12, 30, 0);
att63.ID = _dtCtrl.AddAttendanceRecord(att63);
AttendanceRecord att64 = new AttendanceRecord();
att64.EmployeeNumber = emp.EmployeeNumber;
att64.Time = new DateTime(2010, 1, 6, 13, 30, 0);
att64.ID = _dtCtrl.AddAttendanceRecord(att64);
_dtCtrl.CommitTransaction();
Invoke(new SetTextCallBack(SetText), new object[] { txtProgress, "Adding Complete." });
}
catch (Exception ex)
{
_dtCtrl.RollbackTransaction();
Invoke(new AddTextCallBack(SetText), new object[] { txtProgress, "Error: " + ex.Message });
}
}
public List<AttendanceRecord> GetAttendanceRecord(Terminal terminal, DateTime dtFrom, DateTime dtTo)
{
string devInfo = GetDeviceInfoStr(terminal);
string command = "GetRecord(start_time=\"" + ConvertToDateTimeString(dtFrom) + "\" end_time=\"" + ConvertToDateTimeString(dtTo) + "\")";
IntPtr result = IntPtr.Zero;
HwDev_Execute(devInfo, command, ref result);
List<AttendanceRecord> attRecordList = new List<AttendanceRecord>();
string keyName = "time=\"";
int iFrom, iTo;
string resultString = Marshal.PtrToStringAnsi(result);
while ((iFrom = resultString.IndexOf(keyName)) >= 0)
{
if (iFrom > resultString.IndexOf(")") && resultString.IndexOf(")") >= 0)
break;
iTo = resultString.IndexOf(keyName, iFrom + 1);
if(iTo < 0)
iTo = resultString.IndexOf(")", iFrom + 1);
if (iTo < 0)
throw new Exception();
string attString = resultString.Substring(iFrom, iTo - iFrom);
AttendanceRecord attRecord = new AttendanceRecord();
attRecord.Time = Convert.ToDateTime(GetValue(attString, "time"));
attRecord.EmployeeNumber = Convert.ToInt32(GetValue(attString, "id"));
attRecordList.Add(attRecord);
resultString = resultString.Substring(iTo);
}
return attRecordList;
}
private void AddTestOneShiftFlexi(ref IDataController _dtCtrl)
{
#region add test company
Company com = new Company();
com.Name = DateTime.Now.Ticks.ToString();
com.ID = _dtCtrl.AddCompany(com);
#endregion
#region add test department
Department dep = new Department();
dep.CompanyID = com.ID;
dep.Name = DateTime.Now.Ticks.ToString();
dep.SupDepartmentID = 0; //root
dep.ID = _dtCtrl.AddDepartment(dep);
#endregion
#region add test working calendar
List<Break> breakList = new List<Break>();
Break break1 = new Break();
break1.From = new DateTime(2000, 2, 2, 12, 0, 0);
break1.To = new DateTime(2000, 2, 2, 13, 0, 0);
break1.Name = "break1";
break1.Paid = true;
breakList.Add(break1);
List<Holiday> holidayList = new List<Holiday>();
PaymentRate workingDayPaymentRate = new PaymentRate();
workingDayPaymentRate.NumberOfRegularHours = 7;
workingDayPaymentRate.RegularRate = 100;
workingDayPaymentRate.NumberOfOvertime1 = 8;
workingDayPaymentRate.OvertimeRate1 = 200;
PaymentRate nonWorkingDayPaymentRate = workingDayPaymentRate;
PaymentRate holidayPaymentRate = workingDayPaymentRate;
PayPeriod payPeriod = new PayPeriod();
payPeriod.CustomPeriod = 5;
payPeriod.PayPeriodTypeID = 5; //custom
payPeriod.StartFrom = new DateTime(2010, 1, 1);
WorkingCalendar wCal = new WorkingCalendar();
wCal.Name = DateTime.Now.Ticks.ToString();
wCal.ApplyFlexiHours = true;
wCal.FlexiHours = 40;
wCal.WeekStartsOn = 3; //Thursday
List<Shift> shiftList = new List<Shift>();
Shift shift1 = new Shift();
shift1.From = new DateTime(2000, 2, 2, 9, 0, 0);
shift1.To = new DateTime(2000, 2, 2, 18, 0, 0);
shiftList.Add(shift1);
wCal.WorkOnMonday = true;
wCal.WorkOnTuesday = true;
wCal.WorkOnWednesday = true;
wCal.WorkOnThursday = true;
wCal.WorkOnFriday = true;
wCal.GraceForwardToEntry = 30;
wCal.GraceBackwardToExit = 30;
wCal.EarliestBeforeEntry = 60;
wCal.LastestAfterExit = 180;
wCal.ID = _dtCtrl.AddWorkingCalendar(wCal, shiftList, new List<Break>(), holidayList, workingDayPaymentRate, nonWorkingDayPaymentRate, holidayPaymentRate, payPeriod);
#endregion
#region add test employee
Employee emp = new Employee();
emp.Active = true;
emp.ActiveFrom = DateTime.Today;
emp.ActiveTo = DateTime.Today.AddDays(1);
emp.Address = DateTime.Now.Ticks.ToString();
emp.Birthday = DateTime.Today.AddYears(-20);
emp.DepartmentID = dep.ID;
emp.EmployeeNumber = 0;
emp.FirstName = DateTime.Now.Ticks.ToString();
emp.JobDescription = DateTime.Now.Ticks.ToString();
emp.HiredDate = DateTime.Today;
emp.LeftDate = DateTime.Today.AddYears(1);
emp.LastName = DateTime.Now.Ticks.ToString();
emp.PhoneNumber = DateTime.Now.Ticks.ToString();
emp.WorkingCalendarID = wCal.ID;
emp.PayrollNumber = _dtCtrl.AddEmployee(emp, new List<Terminal>());
#endregion
#region add test att records 2
//att7 : expected regHour: 10
AttendanceRecord att71 = new AttendanceRecord();
att71.EmployeeNumber = emp.EmployeeNumber;
att71.Time = new DateTime(2010, 1, 7, 9, 0, 0);
att71.ID = _dtCtrl.AddAttendanceRecord(att71);
AttendanceRecord att72 = new AttendanceRecord();
att72.EmployeeNumber = emp.EmployeeNumber;
att72.Time = new DateTime(2010, 1, 7, 19, 0, 0);
att72.ID = _dtCtrl.AddAttendanceRecord(att72);
//att8 : expected regHour: 8
AttendanceRecord att81 = new AttendanceRecord();
att81.EmployeeNumber = emp.EmployeeNumber;
att81.Time = new DateTime(2010, 1, 8, 9, 0, 0);
att81.ID = _dtCtrl.AddAttendanceRecord(att81);
AttendanceRecord att82 = new AttendanceRecord();
att82.EmployeeNumber = emp.EmployeeNumber;
att82.Time = new DateTime(2010, 1, 8, 17, 0, 0);
att82.ID = _dtCtrl.AddAttendanceRecord(att82);
//att9 : expected regHour: 8
AttendanceRecord att91 = new AttendanceRecord();
att91.EmployeeNumber = emp.EmployeeNumber;
att91.Time = new DateTime(2010, 1, 9, 9, 0, 0);
att91.ID = _dtCtrl.AddAttendanceRecord(att91);
AttendanceRecord att92 = new AttendanceRecord();
att92.EmployeeNumber = emp.EmployeeNumber;
att92.Time = new DateTime(2010, 1, 9, 17, 0, 0);
att92.ID = _dtCtrl.AddAttendanceRecord(att92);
//att10 : expected regHour: 8
AttendanceRecord att101 = new AttendanceRecord();
att101.EmployeeNumber = emp.EmployeeNumber;
att101.Time = new DateTime(2010, 1, 10, 9, 00, 0);
att101.ID = _dtCtrl.AddAttendanceRecord(att101);
AttendanceRecord att102 = new AttendanceRecord();
att102.EmployeeNumber = emp.EmployeeNumber;
att102.Time = new DateTime(2010, 1, 10, 17, 0, 0);
att102.ID = _dtCtrl.AddAttendanceRecord(att102);
//att11 : expected regHour: 4
AttendanceRecord att111 = new AttendanceRecord();
att111.EmployeeNumber = emp.EmployeeNumber;
att111.Time = new DateTime(2010, 1, 11, 9, 0, 0);
att111.ID = _dtCtrl.AddAttendanceRecord(att111);
AttendanceRecord att112 = new AttendanceRecord();
att112.EmployeeNumber = emp.EmployeeNumber;
att112.Time = new DateTime(2010, 1, 11, 13, 0, 0);
att112.ID = _dtCtrl.AddAttendanceRecord(att112);
//att12 : expected regHour: 2 overHour: 2
AttendanceRecord att121 = new AttendanceRecord();
att121.EmployeeNumber = emp.EmployeeNumber;
att121.Time = new DateTime(2010, 1, 12, 9, 0, 0);
att121.ID = _dtCtrl.AddAttendanceRecord(att121);
AttendanceRecord att122 = new AttendanceRecord();
att122.EmployeeNumber = emp.EmployeeNumber;
att122.Time = new DateTime(2010, 1, 12, 13, 0, 0);
att122.ID = _dtCtrl.AddAttendanceRecord(att122);
//att13 : expected overHour: 2
AttendanceRecord att131 = new AttendanceRecord();
att131.EmployeeNumber = emp.EmployeeNumber;
att131.Time = new DateTime(2010, 1, 13, 9, 0, 0);
att131.ID = _dtCtrl.AddAttendanceRecord(att131);
AttendanceRecord att132 = new AttendanceRecord();
att132.EmployeeNumber = emp.EmployeeNumber;
att132.Time = new DateTime(2010, 1, 13, 11, 0, 0);
att132.ID = _dtCtrl.AddAttendanceRecord(att132);
#endregion
}
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);
}
}
public AttendanceRecord GetAttendanceRecord(int id)
{
OleDbCommand odCom = BuildSelectCmd("AttendanceRecord", "*", "ID=@ID", new object[] { "@ID", id });
OleDbDataReader odRdr = odCom.ExecuteReader();
if (odRdr.Read())
{
AttendanceRecord attRecord = new AttendanceRecord();
attRecord.ID = (int)odRdr["ID"];
attRecord.EmployeeNumber = (int)odRdr["EmployeeNumber"];
attRecord.Note = odRdr["Note"].ToString();
//attRecord.PhotoData = odRdr["PhotoData"].ToString();
attRecord.Time = (DateTime)odRdr["Time"];
odRdr.Close();
return attRecord;
}
return null;
}
private PaymentRate GetPaymentRateByAttendanceRecord(AttendanceRecord attRecord)
{
int iEmployeeNumber = attRecord.EmployeeNumber;
WorkingCalendar workingCalendar = GetWorkingCalendarByEmployee(iEmployeeNumber);
int iWorkingCalendarID = (int)workingCalendar.ID;
int iDayTypeID = 2;
switch (attRecord.Time.DayOfWeek)
{
case DayOfWeek.Friday:
if (workingCalendar.WorkOnFriday)
iDayTypeID = 1;
break;
case DayOfWeek.Monday:
if (workingCalendar.WorkOnMonday)
iDayTypeID = 1;
break;
case DayOfWeek.Saturday:
if (workingCalendar.WorkOnSaturday)
iDayTypeID = 1;
break;
case DayOfWeek.Sunday:
if (workingCalendar.WorkOnSunday)
iDayTypeID = 1;
break;
case DayOfWeek.Thursday:
if (workingCalendar.WorkOnThursday)
iDayTypeID = 1;
break;
case DayOfWeek.Tuesday:
if (workingCalendar.WorkOnTuesday)
iDayTypeID = 1;
break;
case DayOfWeek.Wednesday:
if (workingCalendar.WorkOnWednesday)
iDayTypeID = 1;
break;
}
OleDbCommand odCom = BuildSelectCmd("Holiday", "*", "WorkingCalendarID=@ID", new object[] { "@ID", iWorkingCalendarID });
OleDbDataReader odRdr = odCom.ExecuteReader();
while (odRdr.Read())
{
DateTime holiday = (DateTime)odRdr["Date"];
if (attRecord.Time.Month == holiday.Month && attRecord.Time.Day == holiday.Day)
{
iDayTypeID = 3;
odRdr.Close();
break;
}
}
if (!odRdr.IsClosed) odRdr.Close();
odCom = BuildSelectCmd("PaymentRate", "*", "WorkingCalendarID=@WID AND DayTypeID=@DID", new object[] { "@WID", iWorkingCalendarID, "DID", iDayTypeID });
odRdr = odCom.ExecuteReader();
PaymentRate paymentRate = null;
if (odRdr.Read())
{
paymentRate = new PaymentRate();
paymentRate.DayTypeID = (int)odRdr["DayTypeID"];
paymentRate.ID = (int)odRdr["ID"];
paymentRate.NumberOfOvertime1 = (double)odRdr["NumberOfOvertime1"];
paymentRate.NumberOfOvertime2 = (double)odRdr["NumberOfOvertime2"];
paymentRate.NumberOfOvertime3 = (double)odRdr["NumberOfOvertime3"];
paymentRate.NumberOfOvertime4 = (double)odRdr["NumberOfOvertime4"];
paymentRate.NumberOfRegularHours = (double)odRdr["NumberOfRegularHours"];
paymentRate.OvertimeRate1 = (double)odRdr["OvertimeRate1"];
paymentRate.OvertimeRate2 = (double)odRdr["OvertimeRate2"];
paymentRate.OvertimeRate3 = (double)odRdr["OvertimeRate3"];
paymentRate.OvertimeRate4 = (double)odRdr["OvertimeRate4"];
paymentRate.RegularRate = (double)odRdr["RegularRate"];
paymentRate.WorkingCalendarID = (int)odRdr["WorkingCalendarID"];
}
odRdr.Close();
return paymentRate;
}
public List<AttendanceRecord> GetAttendanceRecordList()
{
OleDbCommand odCom = BuildSelectCmd("AttendanceRecord", "*", null);
OleDbDataReader odRdr = odCom.ExecuteReader();
List<AttendanceRecord> attendanceRecordList = new List<AttendanceRecord>();
AttendanceRecord attendanceRecord = null;
while (odRdr.Read())
{
attendanceRecord = new AttendanceRecord();
attendanceRecord.ID = Convert.ToInt32(odRdr["ID"]);
attendanceRecord.EmployeeNumber = Convert.ToInt32(odRdr["EmployeeNumber"]);
attendanceRecord.Time = Convert.ToDateTime(odRdr["Time"]);
//attendanceRecord.CheckIn = Convert.ToBoolean(odRdr["CheckIn"]);
//attendanceRecord.PhotoData = odRdr["PhotoData"].ToString();
attendanceRecord.Note = odRdr["Note"].ToString();
attendanceRecordList.Add(attendanceRecord);
}
odRdr.Close();
return attendanceRecordList;
}
private bool Is1stday(AttendanceRecord attRecord)
{
OleDbCommand odCom = BuildSelectCmd("AttendanceRecord", "COUNT(*) AS NumRc", "EmployeeNumber=@Empl",
new object[] { "@Empl", attRecord.EmployeeNumber });
OleDbDataReader odRdr = odCom.ExecuteReader();
if (odRdr.Read())
{
object o = odRdr["NumRc"]; //TODO get 1 when there's no att records.
odRdr.Close();
if (o == null || (int)o == 0)
return true;
}
return false;
}
public int AddAttendanceRecord(AttendanceRecord attRecord)
{
if (attRecord == null)
return -1;
if (IsInvalidAttendanceRecord(attRecord, false))
return 1; //TODO why not return -1 here?
int employeeNumber = attRecord.EmployeeNumber;
int attRecordID = 0;
if (attRecord.Note == null) attRecord.Note = "";
OleDbCommand odCom = BuildInsertCmd("AttendanceRecord",
new string[] { "EmployeeNumber", "Note", "Time" },
new object[] { attRecord.EmployeeNumber, attRecord.Note, attRecord.Time }
);
if (ExecuteNonQuery(odCom) == 1)
{
odCom.CommandText = "SELECT @@IDENTITY";
attRecordID = Convert.ToInt32(odCom.ExecuteScalar().ToString());
}
else
return -1;
attRecord.ID = attRecordID;
//TODO transaction here
AddUpdateAttendaceReport(attRecord, null);
return attRecordID;
}
public List<AttendanceLogRecord> GetAttendanceLogRecordList(int iCompany, int iDepartment, DateTime beginDate, DateTime endDate, int columnIndex, bool isOrderByAcs)
{
List<AttendanceReport> attendanceReports = GetAttendanceReport(iCompany, iDepartment, beginDate, endDate, columnIndex, isOrderByAcs);
if (attendanceReports.Count == 0)
return null;
List<string> lEmplNumbers = GetEmployeeNumberList(iCompany, iDepartment);
if (lEmplNumbers == null || lEmplNumbers.Count == 0)
return null;
string sEmplNumbers = string.Join(",", lEmplNumbers.ToArray());
OleDbCommand odCom = BuildSelectCmd("Employee", "EmployeeNumber, FirstName, LastName, PayrollNumber, JobDescription", "EmployeeNumber IN(" + sEmplNumbers + ") AND Active=TRUE");
OleDbDataAdapter odApt = new OleDbDataAdapter(odCom);
//TODO why don't use a List<Employee> here?
DataTable dtEmpl = new DataTable();
odApt.Fill(dtEmpl);
List<AttendanceLogRecord> attLogList = new List<AttendanceLogRecord>();
AttendanceLogRecord attLog = null;
List<AttendanceRecord> attRecordList = new List<AttendanceRecord>();
AttendanceRecord attRecord = null;
foreach (AttendanceReport attReport in attendanceReports)
{
string sAttendanceRecordIDs = attReport.AttendanceRecordIDList;
sAttendanceRecordIDs = sAttendanceRecordIDs.Replace("{", "").Replace("}", ",").Trim(',');
//TODO List<AttendanceRecord> GetAttendanceRecordByAttendanceReport(int attendanceReportID, bool orderByTime)
odCom = BuildSelectCmd("AttendanceRecord", "*", "ID IN(" + sAttendanceRecordIDs + ")");
OleDbDataReader odRdr = odCom.ExecuteReader();
attRecordList.Clear();
while (odRdr.Read())
{
attRecord = new AttendanceRecord();
attRecord.ID = (int)odRdr["ID"];
attRecord.EmployeeNumber = (int)odRdr["EmployeeNumber"];
attRecord.Note = odRdr["Note"].ToString();
//attRecord.PhotoData = odRdr["PhotoData"].ToString();
attRecord.Time = (DateTime)odRdr["Time"];
attRecordList.Add(attRecord);
}
odRdr.Close();
attRecordList.Sort(delegate(AttendanceRecord e1, AttendanceRecord e2) { return e1.Time.CompareTo(e2.Time); });
int roundValue = GetConfig().RecordRoundingValue;
foreach (AttendanceRecord att in attRecordList)
{
att.Time = Util.RoundDateTime(att.Time, roundValue);
}
bool isCheckIn = true;
bool isFirst = true;
DateTime dDateLog = attRecordList[0].Time.Date;
foreach (AttendanceRecord att in attRecordList)
{
attLog = new AttendanceLogRecord();
if (isFirst)
{
attLog.EmployeeNumber = attReport.EmployeeNumber;
attLog.DateLog = attReport.WorkFrom.Date;
//TODO wrong number, total hours here is based on the in/out, not report
//attLog.TotalHours = attReport.RegularHour + attReport.OvertimeHour1 + attReport.OvertimeHour2 + attReport.OvertimeHour3 + attReport.OvertimeHour4;
attLog.TotalHours = Math.Round(CalculateTotalHours(attRecordList), 2);
DataRow[] rdEmpl = dtEmpl.Select("EmployeeNumber=" + attReport.EmployeeNumber);
if (rdEmpl.Length > 0)
attLog.EmployeeName = rdEmpl[0]["LastName"] + ", " + rdEmpl[0]["FirstName"];
isFirst = false;
}
attLog.ID = att.ID;
attLog.TimeLog = (isCheckIn ? "In " : "Out ") + att.Time.ToString("HH:mm");
if (att.Time.Date.CompareTo(attReport.WorkFrom.Date) > 0)
attLog.TimeLog += " [" + att.Time.Date.ToShortDateString() + "]";
attLog.Note = att.Note;
attLogList.Add(attLog);
isCheckIn = !isCheckIn;
}
if (isCheckIn == false && dDateLog.Equals(DateTime.Now.Date) == false)
{
attLog = new AttendanceLogRecord();
attLog.TimeLog = "OutMistakes";
attLogList.Add(attLog);
}
}
return attLogList;
}
private AttendanceRecord GetAttRecordUserInput()
{
AttendanceRecord attRecord = new AttendanceRecord();
int employeeNumber = 0;
try
{
employeeNumber = Convert.ToInt32(cbxEmployeeNumber.SelectedValue);
if (employeeNumber <= 0)
throw new Exception();
}
catch
{
errProviders.SetError(cbxEmployeeNumber, "Invalid Employee number.");
return null;
}
DateTime dAttDate = (DateTime)dtpAttDate.Value;
DateTime dAttTime = (DateTime)dtpAttTime.Value;
dAttDate = new DateTime(dAttDate.Year, dAttDate.Month, dAttDate.Day, dAttTime.Hour, dAttTime.Minute, dAttTime.Second);
string sNote = txtNote.Text;
attRecord.EmployeeNumber = employeeNumber;
attRecord.Note = sNote;
attRecord.Time = dAttDate;
return attRecord;
}
