private void BindWorkCalendar()
{
List<WorkingCalendar> workingCalendarList = _dtCtrl.GetWorkingCalendarList();
WorkingCalendar workingCalendar = new WorkingCalendar();
workingCalendar.ID = -1;
workingCalendar.Name = "All Working Calendar";
workingCalendarList.Insert(0, workingCalendar);
cbxWorkingCalendar.DataSource = workingCalendarList;
}
private void AddPayPeriod()
{
payPeriod.CustomPeriod = 5;
payPeriod.PayPeriodTypeID = 5; //custom
payPeriod.StartFrom = DateTime.Today;
payPeriod.ID = _dtCtrl.AddPayPeriod(payPeriod);
wCal = _dtCtrl.GetWorkingCalendarList()[0];
originalPayPeriodID = wCal.PayPeriodID;
wCal.PayPeriodID = payPeriod.ID;
_dtCtrl.UpdateWorkingCalendar(wCal);
}
private int GetShiftNumber(DateTime dDateLog, WorkingCalendar wCal, List<Shift> shiftList, bool overDay)
{
int shiftNumber = 1;
DateTime dRegularWorkingFrom, dRegularWorkingTo;
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;
DateTime dWorkingFrom = new DateTime(dDateLog.Year, dDateLog.Month, dDateLog.Day, dRegularWorkingFrom.Hour,
dRegularWorkingFrom.Minute, dRegularWorkingFrom.Second).AddMinutes(-1 * wCal.EarliestBeforeEntry);
if (overDay)
dWorkingFrom = dWorkingFrom.AddDays(-1);
DateTime dWorkingTo = dWorkingFrom.AddMinutes(distanceMinute + wCal.LastestAfterExit + wCal.EarliestBeforeEntry);
if (dWorkingFrom.CompareTo(dDateLog) != 1 && dWorkingTo.CompareTo(dDateLog) == 1)
{
shiftNumber = i + 1;
overDay = true;
break;
}
}
if (!overDay)
GetShiftNumber(dDateLog, wCal, shiftList, true);
return shiftNumber;
}
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;
}
}
}
}
}
public List<WorkingCalendar> GetWorkingCalendarList()
{
OleDbCommand odCom = BuildSelectCmd("WorkingCalendar", "*", null);
OleDbDataReader odRdr = odCom.ExecuteReader();
List<WorkingCalendar> workingCalendarList = new List<WorkingCalendar>();
WorkingCalendar workingCalendar = null;
while (odRdr.Read())
{
workingCalendar = new WorkingCalendar();
workingCalendar.ID = (int)odRdr["ID"];
workingCalendar.Name = odRdr["Name"].ToString();
workingCalendar.WorkOnMonday = Convert.ToBoolean(odRdr["WorkOnMonday"]);
workingCalendar.WorkOnTuesday = Convert.ToBoolean(odRdr["WorkOnTuesday"]);
workingCalendar.WorkOnWednesday = Convert.ToBoolean(odRdr["WorkOnWednesday"]);
workingCalendar.WorkOnThursday = Convert.ToBoolean(odRdr["WorkOnThursday"]);
workingCalendar.WorkOnFriday = Convert.ToBoolean(odRdr["WorkOnFriday"]);
workingCalendar.WorkOnSaturday = Convert.ToBoolean(odRdr["WorkOnSaturday"]);
workingCalendar.WorkOnSunday = Convert.ToBoolean(odRdr["WorkOnSunday"]);
workingCalendar.PayPeriodID = (int)odRdr["PayPeriodID"];
workingCalendar.GraceForwardToEntry = (int)odRdr["GraceForwardToEntry"];
workingCalendar.GraceBackwardToExit = (int)odRdr["GraceBackwardToExit"];
workingCalendar.EarliestBeforeEntry = (int)odRdr["EarliestBeforeEntry"];
workingCalendar.LastestAfterExit = (int)odRdr["LastestAfterExit"];
workingCalendar.ApplyFlexiHours = Convert.ToBoolean(odRdr["ApplyFlexiHours"]);
workingCalendar.FlexiHours = (int)odRdr["FlexiHours"];
workingCalendar.WeekStartsOn = (int)odRdr["WeekStartsOn"];
workingCalendarList.Add(workingCalendar);
}
odRdr.Close();
return workingCalendarList;
}
private int AddWorkingCalendar(WorkingCalendar workingCalendar)
{
OleDbCommand odCom1 = BuildInsertCmd("WorkingCalendar",
new string[] { "Name"
,"WorkOnMonday"
,"WorkOnTuesday"
,"WorkOnWednesday"
,"WorkOnThursday"
,"WorkOnFriday"
,"WorkOnSaturday"
,"WorkOnSunday"
,"RegularWorkingFrom"
,"RegularWorkingTo"
,"PayPeriodID"
,"GraceForwardToEntry"
,"GraceBackwardToExit"
,"EarliestBeforeEntry"
,"LastestAfterExit"
},
new object[] { workingCalendar.Name
,workingCalendar.WorkOnMonday
,workingCalendar.WorkOnTuesday
,workingCalendar.WorkOnWednesday
,workingCalendar.WorkOnThursday
,workingCalendar.WorkOnFriday
,workingCalendar.WorkOnSaturday
,workingCalendar.WorkOnSunday
,workingCalendar.RegularWorkingFrom
,workingCalendar.RegularWorkingTo
,workingCalendar.PayPeriodID
,workingCalendar.GraceForwardToEntry
,workingCalendar.GraceBackwardToExit
,workingCalendar.EarliestBeforeEntry
,workingCalendar.LastestAfterExit
}
);
if (odCom1.ExecuteNonQuery() == 1)
{
odCom1.CommandText = "SELECT @@IDENTITY";
return Convert.ToInt16(odCom1.ExecuteScalar().ToString());
}
return -1;
}
public bool UpdateWorkingCalendar(WorkingCalendar workingCalendar)
{
OleDbCommand odCom1 = BuildUpdateCmd("WorkingCalendar",
new string[] { "Name"
,"WorkOnMonday"
,"WorkOnTuesday"
,"WorkOnWednesday"
,"WorkOnThursday"
,"WorkOnFriday"
,"WorkOnSaturday"
,"WorkOnSunday"
,"RegularWorkingFrom"
,"RegularWorkingTo"
,"PayPeriodID"
,"GraceForwardToEntry"
,"GraceBackwardToExit"
,"EarliestBeforeEntry"
,"LastestAfterExit"
},
new object[] { workingCalendar.Name
,workingCalendar.WorkOnMonday
,workingCalendar.WorkOnTuesday
,workingCalendar.WorkOnWednesday
,workingCalendar.WorkOnThursday
,workingCalendar.WorkOnFriday
,workingCalendar.WorkOnSaturday
,workingCalendar.WorkOnSunday
,workingCalendar.RegularWorkingFrom
,workingCalendar.RegularWorkingTo
,workingCalendar.PayPeriodID
,workingCalendar.GraceForwardToEntry
,workingCalendar.GraceBackwardToExit
,workingCalendar.EarliestBeforeEntry
,workingCalendar.LastestAfterExit
},
"ID=@ID", new object[] { "@ID", workingCalendar.ID }
);
return odCom1.ExecuteNonQuery() > 0 ? true : false;
}
public List<WorkingCalendar> GetWorkingCalendarList()
{
OleDbCommand odCom = BuildSelectCmd("WorkingCalendar", "*", null);
OleDbDataReader odRdr = odCom.ExecuteReader();
List<WorkingCalendar> workingCalendarList = new List<WorkingCalendar>();
WorkingCalendar workingCalendar = null;
while (odRdr.Read())
{
workingCalendar = new WorkingCalendar();
workingCalendar.ID = (int)odRdr["ID"];
workingCalendar.Name = odRdr["Name"].ToString();
workingCalendar.WorkOnMonday = Convert.ToBoolean(odRdr["WorkOnMonday"]);
workingCalendar.WorkOnTuesday = Convert.ToBoolean(odRdr["WorkOnTuesday"]);
workingCalendar.WorkOnWednesday = Convert.ToBoolean(odRdr["WorkOnWednesday"]);
workingCalendar.WorkOnThursday = Convert.ToBoolean(odRdr["WorkOnThursday"]);
workingCalendar.WorkOnFriday = Convert.ToBoolean(odRdr["WorkOnFriday"]);
workingCalendar.WorkOnSaturday = Convert.ToBoolean(odRdr["WorkOnSaturday"]);
workingCalendar.WorkOnSunday = Convert.ToBoolean(odRdr["WorkOnSunday"]);
workingCalendar.RegularWorkingFrom = odRdr["RegularWorkingFrom"].GetType() != typeof(DBNull) ? Convert.ToDateTime(odRdr["RegularWorkingFrom"]) : Config.MinDate;
workingCalendar.RegularWorkingTo = odRdr["RegularWorkingTo"].GetType() != typeof(DBNull) ? Convert.ToDateTime(odRdr["RegularWorkingTo"]) : Config.MinDate;
workingCalendar.PayPeriodID = (int)odRdr["PayPeriodID"];
workingCalendar.GraceForwardToEntry = (int)odRdr["GraceForwardToEntry"];
workingCalendar.GraceBackwardToExit = (int)odRdr["GraceBackwardToExit"];
workingCalendar.EarliestBeforeEntry = (int)odRdr["EarliestBeforeEntry"];
workingCalendar.LastestAfterExit = (int)odRdr["LastestAfterExit"];
workingCalendarList.Add(workingCalendar);
}
odRdr.Close();
return workingCalendarList;
}
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 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
}
private void SetWorkingCalendarProperties(ref WorkingCalendar workingCalendar, ref List<Shift> shiftList, ref List<Break> breakList, ref List<Holiday> holidayList, ref PaymentRate workDayPaymentRate, ref PaymentRate nonWorkDayPaymentRate, ref PaymentRate holidayPaymentRate, ref PayPeriod payPeriod)
{
workingCalendar.Name = txtName.Text;
#region Flexi Hours
workingCalendar.ApplyFlexiHours = chbApplyFlexiHours.Checked;
if (workingCalendar.ApplyFlexiHours)
{
workingCalendar.FlexiHours = (int)nudFlexiHours.Value;
workingCalendar.WeekStartsOn = cbxWeekStartsOn.SelectedIndex;
}
#endregion
#region Get Working Days
workingCalendar.WorkOnMonday = chbMonday.Checked;
workingCalendar.WorkOnTuesday = chbTuesday.Checked;
workingCalendar.WorkOnWednesday = chbWednesday.Checked;
workingCalendar.WorkOnThursday = chbThursday.Checked;
workingCalendar.WorkOnFriday = chbFriday.Checked;
workingCalendar.WorkOnSaturday = chbSaturday.Checked;
workingCalendar.WorkOnSunday = chbSunday.Checked;
workingCalendar.GraceForwardToEntry = (int)nudGraceForwardToEntry.Value;
workingCalendar.GraceBackwardToExit = (int)nudGraceBackwardToExit.Value;
workingCalendar.EarliestBeforeEntry = (int)nudEarliestBeforeEntry.Value;
workingCalendar.LastestAfterExit = (int)nudLastestAfterExit.Value;
#endregion
#region Shift
if (rbtOneShift.Checked)
{
Shift shift = new Shift();
shift.From = dtpRegularWorkFrom.Value;
shift.To = dtpRegularWorkTo.Value;
shiftList.Add(shift);
}
else //multi-shifts
{
shiftList = _shiftList;
}
#endregion
#region Get Break Times
if (chbBreak1.Checked)
{
Break break1 = new Break();
break1.Name = txtBreakName1.Text;
break1.From = dtpBreakFrom1.Value;
break1.To = dtpBreakTo1.Value;
break1.Paid = chbBreakPaid1.Checked;
breakList.Add(break1);
}
if (chbBreak2.Checked)
{
Break break2 = new Break();
break2.Name = txtBreakName2.Text;
break2.From = dtpBreakFrom2.Value;
break2.To = dtpBreakTo2.Value;
break2.Paid = chbBreakPaid2.Checked;
breakList.Add(break2);
}
if (chbBreak3.Checked)
{
Break break3 = new Break();
break3.Name = txtBreakName3.Text;
break3.From = dtpBreakFrom3.Value;
break3.To = dtpBreakTo3.Value;
break3.Paid = chbBreakPaid3.Checked;
breakList.Add(break3);
}
#endregion
#region Get Payment Rates
if (workingCalendar.ApplyFlexiHours)
{
workDayPaymentRate.NumberOfRegularHours = (int)nudFlexiHourRegularHour.Value;
workDayPaymentRate.NumberOfOvertime1 = (int)nudFlexiHourOvertimeHour1.Value;
workDayPaymentRate.NumberOfOvertime2 = (int)nudFlexiHourOvertimeHour2.Value;
workDayPaymentRate.NumberOfOvertime3 = (int)nudFlexiHourOvertimeHour3.Value;
workDayPaymentRate.NumberOfOvertime4 = (int)nudFlexiHourOvertimeHour4.Value;
workDayPaymentRate.RegularRate = ((Rate)cbxFlexiHourWorkingDayRegularRate.SelectedItem).Value;
workDayPaymentRate.OvertimeRate1 = ((Rate)cbxHolidayOvertimeRate1.SelectedItem).Value;
workDayPaymentRate.OvertimeRate2 = ((Rate)cbxHolidayOvertimeRate2.SelectedItem).Value;
workDayPaymentRate.OvertimeRate3 = ((Rate)cbxHolidayOvertimeRate3.SelectedItem).Value;
workDayPaymentRate.OvertimeRate4 = ((Rate)cbxHolidayOvertimeRate4.SelectedItem).Value;
nonWorkDayPaymentRate.RegularRate = ((Rate)cbxFlexiHourNonWorkingDayRegularRate.SelectedItem).Value;
holidayPaymentRate.RegularRate = ((Rate)cbxFlexiHourHolidayRegularRate.SelectedItem).Value;
}
else
{
workDayPaymentRate.NumberOfRegularHours = (int)nudWorkDayRegularHour.Value;
workDayPaymentRate.NumberOfOvertime1 = (int)nudWorkDayOvertimeHour1.Value;
workDayPaymentRate.NumberOfOvertime2 = (int)nudWorkDayOvertimeHour2.Value;
workDayPaymentRate.NumberOfOvertime3 = (int)nudWorkDayOvertimeHour3.Value;
workDayPaymentRate.NumberOfOvertime4 = (int)nudWorkDayOvertimeHour4.Value;
workDayPaymentRate.RegularRate = ((Rate)cbxWorkDayRegularRate.SelectedItem).Value;
workDayPaymentRate.OvertimeRate1 = ((Rate)cbxWorkDayOvertimeRate1.SelectedItem).Value;
workDayPaymentRate.OvertimeRate2 = ((Rate)cbxWorkDayOvertimeRate2.SelectedItem).Value;
workDayPaymentRate.OvertimeRate3 = ((Rate)cbxWorkDayOvertimeRate3.SelectedItem).Value;
workDayPaymentRate.OvertimeRate4 = ((Rate)cbxWorkDayOvertimeRate4.SelectedItem).Value;
nonWorkDayPaymentRate.NumberOfRegularHours = (int)nudNonWorkDayRegularHour.Value;
nonWorkDayPaymentRate.NumberOfOvertime1 = (int)nudNonWorkDayOvertimeHour1.Value;
nonWorkDayPaymentRate.NumberOfOvertime2 = (int)nudNonWorkDayOvertimeHour2.Value;
nonWorkDayPaymentRate.NumberOfOvertime3 = (int)nudNonWorkDayOvertimeHour3.Value;
nonWorkDayPaymentRate.NumberOfOvertime4 = (int)nudNonWorkDayOvertimeHour4.Value;
nonWorkDayPaymentRate.RegularRate = ((Rate)cbxNonWorkDayRegularRate.SelectedItem).Value;
nonWorkDayPaymentRate.OvertimeRate1 = ((Rate)cbxNonWorkDayOvertimeRate1.SelectedItem).Value;
nonWorkDayPaymentRate.OvertimeRate2 = ((Rate)cbxNonWorkDayOvertimeRate2.SelectedItem).Value;
nonWorkDayPaymentRate.OvertimeRate3 = ((Rate)cbxNonWorkDayOvertimeRate3.SelectedItem).Value;
nonWorkDayPaymentRate.OvertimeRate4 = ((Rate)cbxNonWorkDayOvertimeRate4.SelectedItem).Value;
holidayPaymentRate.NumberOfRegularHours = (int)nudHolidayRegularHour.Value;
holidayPaymentRate.NumberOfOvertime1 = (int)nudHolidayOvertimeHour1.Value;
holidayPaymentRate.NumberOfOvertime2 = (int)nudHolidayOvertimeHour2.Value;
holidayPaymentRate.NumberOfOvertime3 = (int)nudHolidayOvertimeHour3.Value;
holidayPaymentRate.NumberOfOvertime4 = (int)nudHolidayOvertimeHour4.Value;
holidayPaymentRate.RegularRate = ((Rate)cbxHolidayRegularRate.SelectedItem).Value;
holidayPaymentRate.OvertimeRate1 = ((Rate)cbxHolidayOvertimeRate1.SelectedItem).Value;
holidayPaymentRate.OvertimeRate2 = ((Rate)cbxHolidayOvertimeRate2.SelectedItem).Value;
holidayPaymentRate.OvertimeRate3 = ((Rate)cbxHolidayOvertimeRate3.SelectedItem).Value;
holidayPaymentRate.OvertimeRate4 = ((Rate)cbxHolidayOvertimeRate4.SelectedItem).Value;
}
#endregion
#region Get Holidays
holidayList = _holidayList;
#endregion
#region Get Pay Period
if (rbtPayPeriodCustom.Checked)//custom Pay Period
{
payPeriod.PayPeriodTypeID = 5;
payPeriod.CustomPeriod = (int)nudCustomPayPeriod.Value;
}
else
{
if (rbtPayPeriodWeekly.Checked)
payPeriod.PayPeriodTypeID = 1;
else if (rbtPayPeriodBiweekly.Checked)
payPeriod.PayPeriodTypeID = 2;
else if (rbtPayPeriodMonthly.Checked)
payPeriod.PayPeriodTypeID = 3;
else if (rbtPayPeriodHalfmonthly.Checked)
payPeriod.PayPeriodTypeID = 4;
}
payPeriod.StartFrom = dtpPayPeriodStartFrom.Value;
#endregion
}
private void AddUpdateWorkingCalendar()
{
#region Validate inputs
if (ValidateName() == false)
{
tabAddUpdateWorkingCalendar.SelectedTab = tabPage1;
return;
}
if (ValidateWorkingHour() == false)
{
tabAddUpdateWorkingCalendar.SelectedTab = tabPage2;
return;
}
if (ValidateBreaks() == false)
{
tabAddUpdateWorkingCalendar.SelectedTab = tabPage3;
return;
}
if (ValidateWorkingDayRate() == false)
{
tabAddUpdateWorkingCalendar.SelectedTab = tabPage4;
return;
}
if (ValidateNonWorkingDayRate() == false)
{
tabAddUpdateWorkingCalendar.SelectedTab = tabPage4;
return;
}
if (ValidateHolidayRate() == false)
{
tabAddUpdateWorkingCalendar.SelectedTab = tabPage4;
return;
}
if (ValidateHoliday() == false)
{
tabAddUpdateWorkingCalendar.SelectedTab = tabPage5;
return;
}
if (ValidatePayPeriod() == false)
{
return;
}
#endregion
#region Create object and set properties
WorkingCalendar workingCalendar = null;
List<Shift> shiftList = new List<Shift>();
List<Break> breakList = new List<Break>();
List<Holiday> holidayList = new List<Holiday>();
PaymentRate workingDayPaymentRate = new PaymentRate();
PaymentRate nonWorkingDayPaymentRate = new PaymentRate();
PaymentRate holidayPaymentRate = new PaymentRate();
PayPeriod payPeriod = new PayPeriod();
if (_workingCalendarID < 0) //add
{
workingCalendar = new WorkingCalendar();
}
else //update
{
workingCalendar = _dtCtrl.GetWorkingCalendar(_workingCalendarID);
if (workingCalendar == null)
{
throw new NullReferenceException();
}
}
SetWorkingCalendarProperties(ref workingCalendar, ref shiftList, ref breakList, ref holidayList, ref workingDayPaymentRate, ref nonWorkingDayPaymentRate, ref holidayPaymentRate, ref payPeriod);
#endregion
#region insert/update in database
if (_workingCalendarID < 0) //add
{
int workingCalendarID = _dtCtrl.AddWorkingCalendar(workingCalendar, shiftList, breakList, holidayList, workingDayPaymentRate, nonWorkingDayPaymentRate, holidayPaymentRate, payPeriod);
if (workingCalendarID < 0)
{
throw new Exception("Working Calendar could not be added.");
}
MessageBox.Show("Working Calendar added.");
this.Close();
}
else //update
{
bool result = _dtCtrl.UpdateWorkingCalendar(workingCalendar, shiftList, breakList, holidayList, workingDayPaymentRate, nonWorkingDayPaymentRate, holidayPaymentRate, payPeriod);
if (result != true)
{
throw new Exception("Working Calendar could not be updated.");
}
MessageBox.Show("Working Calendar updated.");
this.Close();
}
#endregion
}
private void AddEmployee()
{
//com.Name = DateTime.Now.Ticks.ToString();
//com.ID = _dtCtrl.AddCompany(com);
//dep.Name = DateTime.Now.Ticks.ToString();
//dep.CompanyID = com.ID;
//dep.SupDepartmentID = 0;
//dep.ID = _dtCtrl.AddDepartment(dep);
//wCal.Name = DateTime.Now.Ticks.ToString();
//wCal.RegularWorkingFrom = DateTime.Today;
//wCal.RegularWorkingTo = DateTime.Today;
//List<Break> breakList = new List<Break>();
//List<Holiday> holidayList = new List<Holiday>();
//PaymentRate workingDayPaymentRate = new PaymentRate();
//PaymentRate nonWorkingDayPaymentRate = new PaymentRate();
//PaymentRate holidayPaymentRate = new PaymentRate();
//PayPeriod payPeriod = new PayPeriod();
//payPeriod.CustomPeriod = 5;
//payPeriod.PayPeriodTypeID = 5; //custom
//payPeriod.StartFrom = DateTime.Today;
//wCal.ID = _dtCtrl.AddWorkingCalendar(wCal, breakList, holidayList, workingDayPaymentRate, nonWorkingDayPaymentRate, holidayPaymentRate, payPeriod);
dep = _dtCtrl.GetDepartmentList()[0];
com = _dtCtrl.GetCompany(dep.CompanyID);
wCal = _dtCtrl.GetWorkingCalendarList()[0];
ter = _dtCtrl.GetTerminalList()[0];
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.FaceData1 = "";
emp.FaceData2 = "";
emp.FaceData3 = "";
emp.FaceData4 = "";
emp.FaceData5 = "";
emp.FaceData6 = "";
emp.FaceData7 = "";
emp.FaceData8 = "";
emp.FaceData9 = "";
emp.FaceData10 = "";
emp.FaceData11 = "";
emp.FaceData12 = "";
emp.FaceData13 = "";
emp.FaceData14 = "";
emp.FaceData15 = "";
emp.FaceData16 = "";
emp.FaceData17 = "";
emp.FaceData18 = "";
emp.PayrollNumber = _dtCtrl.AddEmployee(emp, new List<Terminal>(){ter});
}
//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);
}
}
private String GetWorkOnStr(WorkingCalendar workingCalendar)
{
string workOnStr = "";
if (workingCalendar.WorkOnMonday)
workOnStr += "Mon";
if (workingCalendar.WorkOnTuesday)
workOnStr += "Tue";
if (workingCalendar.WorkOnWednesday)
workOnStr += "Wed";
if (workingCalendar.WorkOnThursday)
workOnStr += "Thu";
if (workingCalendar.WorkOnFriday)
workOnStr += "Fri";
if (workingCalendar.WorkOnSaturday)
workOnStr += "Sat";
if (workingCalendar.WorkOnSunday)
workOnStr += "Sun";
return workOnStr;
}
public WorkingCalendar GetWorkingCalendarByEmployee(int employeeNumber)
{
OleDbCommand odCom = BuildSelectCmd("WorkingCalendar", "*",
"ID=(Select TOP 1 WorkingCalendarID From Employee Where EmployeeNumber=@ID AND Active=TRUE)", new object[] { "@ID", employeeNumber });
OleDbDataReader odRdr = odCom.ExecuteReader();
WorkingCalendar workingCalendar = null;
if (odRdr.Read())
{
workingCalendar = new WorkingCalendar();
workingCalendar.ID = (int)odRdr["ID"];
workingCalendar.Name = odRdr["Name"].ToString();
workingCalendar.WorkOnMonday = Convert.ToBoolean(odRdr["WorkOnMonday"]);
workingCalendar.WorkOnTuesday = Convert.ToBoolean(odRdr["WorkOnTuesday"]);
workingCalendar.WorkOnWednesday = Convert.ToBoolean(odRdr["WorkOnWednesday"]);
workingCalendar.WorkOnThursday = Convert.ToBoolean(odRdr["WorkOnThursday"]);
workingCalendar.WorkOnFriday = Convert.ToBoolean(odRdr["WorkOnFriday"]);
workingCalendar.WorkOnSaturday = Convert.ToBoolean(odRdr["WorkOnSaturday"]);
workingCalendar.WorkOnSunday = Convert.ToBoolean(odRdr["WorkOnSunday"]);
workingCalendar.RegularWorkingFrom = odRdr["RegularWorkingFrom"].GetType() != typeof(DBNull) ? Convert.ToDateTime(odRdr["RegularWorkingFrom"]) : Config.MinDate;
workingCalendar.RegularWorkingTo = odRdr["RegularWorkingTo"].GetType() != typeof(DBNull) ? Convert.ToDateTime(odRdr["RegularWorkingTo"]) : Config.MinDate;
workingCalendar.PayPeriodID = (int)odRdr["PayPeriodID"];
workingCalendar.GraceForwardToEntry = (int)odRdr["GraceForwardToEntry"];
workingCalendar.GraceBackwardToExit = (int)odRdr["GraceBackwardToExit"];
workingCalendar.EarliestBeforeEntry = (int)odRdr["EarliestBeforeEntry"];
workingCalendar.LastestAfterExit = (int)odRdr["LastestAfterExit"];
}
odRdr.Close();
return workingCalendar;
}
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 bool UpdateWorkingCalendar(WorkingCalendar workingCalendar, List<Break> breakList, List<Holiday> holidayList, PaymentRate workingDayPaymentRate, PaymentRate nonWorkingDayPaymentRate, PaymentRate holidayPaymentRate, PayPeriod payPeriod)
{
BeginTransaction();
try
{
//update pay period
PayPeriod oldPayPeriod = GetPayPeriod(workingCalendar.PayPeriodID);
if (ComparePayPeriods(payPeriod, oldPayPeriod) == true)
{
payPeriod = oldPayPeriod;
}
else
{
payPeriod.ID = AddPayPeriod(payPeriod);
}
if (payPeriod.ID < 0)
throw new NullReferenceException();
workingCalendar.PayPeriodID = payPeriod.ID;
if (UpdateWorkingCalendar(workingCalendar) == false)
throw new NullReferenceException();
//update breaks
foreach (Break _break in GetBreakListByWorkingCalendar(workingCalendar.ID))
{
if (DeleteBreak(_break.ID) == false)
throw new NullReferenceException();
}
foreach (Break _break in breakList)
{
_break.WorkingCalendarID = workingCalendar.ID;
if (AddBreak(_break) < 0)
throw new NullReferenceException();
}
//add holidays
foreach (Holiday holiday in GetHolidayListByWorkingCalendar(workingCalendar.ID))
{
if (DeleteHoliday(holiday.ID) == false)
throw new NullReferenceException();
}
foreach (Holiday holiday in holidayList)
{
holiday.WorkingCalendarID = workingCalendar.ID;
if (AddHoliday(holiday) < 0)
throw new NullReferenceException();
}
//update payment rates
workingDayPaymentRate.ID = GetWorkingDayPaymentRateByWorkingCalendar(workingCalendar.ID).ID;
workingDayPaymentRate.DayTypeID = 1; //working day
workingDayPaymentRate.WorkingCalendarID = workingCalendar.ID;
if (UpdatePaymentRate(workingDayPaymentRate) == false)
throw new NullReferenceException();
nonWorkingDayPaymentRate.ID = GetNonWorkingDayPaymentRateByWorkingCalendar(workingCalendar.ID).ID;
nonWorkingDayPaymentRate.DayTypeID = 2; //non working day
nonWorkingDayPaymentRate.WorkingCalendarID = workingCalendar.ID;
if (UpdatePaymentRate(nonWorkingDayPaymentRate) == false)
throw new NullReferenceException();
holidayPaymentRate.ID = GetHolidayPaymentRateByWorkingCalendar(workingCalendar.ID).ID;
holidayPaymentRate.DayTypeID = 3; //holiday
holidayPaymentRate.WorkingCalendarID = workingCalendar.ID;
if (UpdatePaymentRate(holidayPaymentRate) == false)
throw new NullReferenceException();
CommitTransaction();
}
catch (Exception)
{
RollbackTransaction();
return false;
}
return true;
}
private void BindWorkingCalendar(int workingCalendarID)
{
_workingCalendar = _dtCtrl.GetWorkingCalendar(workingCalendarID);
if (_workingCalendar == null)
{
MessageBox.Show("Working Calendar not found or has been deleted.");
this.Close();
}
lblWorkFrom.Text = GetTimeString(_workingCalendar.RegularWorkingFrom);
lblWorkTo.Text = GetTimeString(_workingCalendar.RegularWorkingTo);
//set payment rate
PaymentRate workDayPaymentRate = _dtCtrl.GetWorkingDayPaymentRateByWorkingCalendar(workingCalendarID);
if (workDayPaymentRate == null)
{
//TODO
}
else
{
ucWorkingDayPaymentRate.SetPaymentRate(workDayPaymentRate);
}
PaymentRate nonWorkDayPaymentRate = _dtCtrl.GetNonWorkingDayPaymentRateByWorkingCalendar(workingCalendarID);
if (nonWorkDayPaymentRate == null)
{
//TODO
}
else
{
ucNonWorkingDayPaymentRate.SetPaymentRate(nonWorkDayPaymentRate);
}
PaymentRate holidayRate = _dtCtrl.GetHolidayPaymentRateByWorkingCalendar(workingCalendarID);
if (holidayRate == null)
{
//TODO
}
else
{
ucHolidayPaymentRate.SetPaymentRate(holidayRate);
}
//set break
List<Break> breakList = _dtCtrl.GetBreakListByWorkingCalendar(workingCalendarID);
if (breakList.Count >= 1)
{
lblBreak1.Text = GetTimeString(breakList[0].From) + " - " + GetTimeString(breakList[0].To);
}
else
{
lblBreak1.Text = "";
}
if (breakList.Count >= 2)
{
lblBreak2.Text = GetTimeString(breakList[1].From) + " - " + GetTimeString(breakList[0].To);
}
else
{
lblBreak2.Text = "";
}
if (breakList.Count >= 3)
{
lblBreak3.Text = GetTimeString(breakList[2].From) + " - " + GetTimeString(breakList[0].To);
}
else
{
lblBreak3.Text = "";
}
//set pay period
PayPeriod payPeriod = _dtCtrl.GetPayPeriod(_workingCalendar.PayPeriodID);
if (payPeriod == null)
{
//TODO
}
else
{
PayPeriodType payPeriodType = _dtCtrl.GetPayPeriodType(payPeriod.PayPeriodTypeID);
if (payPeriodType == null)
{
//TODO
}
else if (payPeriodType.ID == 5) //custom
{
lblPayPeriod.Text = "Every " + payPeriod.CustomPeriod + " day(s)";
}
else
{
lblPayPeriod.Text = payPeriodType.Name;
}
lblPayPeriodStartFrom.Text = payPeriod.StartFrom.ToShortDateString();
//set calendar
_holidayList = _dtCtrl.GetHolidayListByWorkingCalendar(workingCalendarID);
FormatCalendar(DateTime.Today.Year, DateTime.Today.Month);
}
}
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 WorkingCalendar GetWorkingCalendarByEmployee(int employeeNumber)
{
OleDbCommand odCom = BuildSelectCmd("WorkingCalendar", "*",
"ID=(Select TOP 1 WorkingCalendarID From Employee Where EmployeeNumber=@ID AND Active=TRUE)", new object[] { "@ID", employeeNumber });
OleDbDataReader odRdr = odCom.ExecuteReader();
WorkingCalendar workingCalendar = null;
if (odRdr.Read())
{
workingCalendar = new WorkingCalendar();
workingCalendar.ID = (int)odRdr["ID"];
workingCalendar.Name = odRdr["Name"].ToString();
workingCalendar.WorkOnMonday = Convert.ToBoolean(odRdr["WorkOnMonday"]);
workingCalendar.WorkOnTuesday = Convert.ToBoolean(odRdr["WorkOnTuesday"]);
workingCalendar.WorkOnWednesday = Convert.ToBoolean(odRdr["WorkOnWednesday"]);
workingCalendar.WorkOnThursday = Convert.ToBoolean(odRdr["WorkOnThursday"]);
workingCalendar.WorkOnFriday = Convert.ToBoolean(odRdr["WorkOnFriday"]);
workingCalendar.WorkOnSaturday = Convert.ToBoolean(odRdr["WorkOnSaturday"]);
workingCalendar.WorkOnSunday = Convert.ToBoolean(odRdr["WorkOnSunday"]);
workingCalendar.PayPeriodID = (int)odRdr["PayPeriodID"];
workingCalendar.GraceForwardToEntry = (int)odRdr["GraceForwardToEntry"];
workingCalendar.GraceBackwardToExit = (int)odRdr["GraceBackwardToExit"];
workingCalendar.EarliestBeforeEntry = (int)odRdr["EarliestBeforeEntry"];
workingCalendar.LastestAfterExit = (int)odRdr["LastestAfterExit"];
workingCalendar.ApplyFlexiHours = Convert.ToBoolean(odRdr["ApplyFlexiHours"]);
workingCalendar.FlexiHours = (int)odRdr["FlexiHours"];
workingCalendar.WeekStartsOn = (int)odRdr["WeekStartsOn"];
}
odRdr.Close();
return workingCalendar;
}
public int AddWorkingCalendar(WorkingCalendar workingCalendar, List<Break> breakList, List<Holiday> holidayList, PaymentRate workingDayPaymentRate, PaymentRate nonWorkingDayPaymentRate, PaymentRate holidayPaymentRate, PayPeriod payPeriod)
{
BeginTransaction();
try
{
//add pay period
payPeriod.ID = AddPayPeriod(payPeriod);
if (payPeriod.ID < 0)
throw new NullReferenceException();
workingCalendar.PayPeriodID = payPeriod.ID;
workingCalendar.ID = AddWorkingCalendar(workingCalendar);
if (workingCalendar.ID < 0)
throw new NullReferenceException();
//add breaks
foreach (Break _break in breakList)
{
_break.WorkingCalendarID = workingCalendar.ID;
if (AddBreak(_break) < 0)
throw new NullReferenceException();
}
//add holidays
foreach (Holiday holiday in holidayList)
{
holiday.WorkingCalendarID = workingCalendar.ID;
if (AddHoliday(holiday) < 0)
throw new NullReferenceException();
}
//add payment rates
workingDayPaymentRate.DayTypeID = 1; //working day
workingDayPaymentRate.WorkingCalendarID = workingCalendar.ID;
if (AddPaymentRate(workingDayPaymentRate) < 0)
throw new NullReferenceException();
nonWorkingDayPaymentRate.DayTypeID = 2; //non working day
nonWorkingDayPaymentRate.WorkingCalendarID = workingCalendar.ID;
if (AddPaymentRate(nonWorkingDayPaymentRate) < 0)
throw new NullReferenceException();
holidayPaymentRate.DayTypeID = 3; //holiday
holidayPaymentRate.WorkingCalendarID = workingCalendar.ID;
if (AddPaymentRate(holidayPaymentRate) < 0)
throw new NullReferenceException();
CommitTransaction();
}
catch (Exception)
{
RollbackTransaction();
return -1;
}
return workingCalendar.ID;
}
private void BindWorkingCalendar(int workingCalendarID)
{
_workingCalendar = _dtCtrl.GetWorkingCalendar(workingCalendarID);
if (_workingCalendar == null)
{
MessageBox.Show("Working Calendar not found or has been deleted.");
this.Close();
}
//set shift
List<Shift> shiftList = _dtCtrl.GetShiftListByWorkingCalendar(workingCalendarID);
if (shiftList.Count == 0)
{
MessageBox.Show("Invalid Working Calendar.");
this.Close();
}
else
{
lblNumberOfShift.Text = shiftList.Count.ToString();
}
lblWorkFrom.Text = GetTimeString(shiftList[0].From);
lblWorkTo.Text = GetTimeString(shiftList[shiftList.Count - 1].To);
//set payment rate
PaymentRate workDayPaymentRate = _dtCtrl.GetWorkingDayPaymentRateByWorkingCalendar(workingCalendarID);
if (workDayPaymentRate == null)
{
//TODO
}
else
{
ucWorkingDayPaymentRate.SetPaymentRate(workDayPaymentRate);
}
if (_workingCalendar.ApplyFlexiHours == false)
{
PaymentRate nonWorkDayPaymentRate = _dtCtrl.GetNonWorkingDayPaymentRateByWorkingCalendar(workingCalendarID);
if (nonWorkDayPaymentRate == null)
{
//TODO
}
else
{
ucNonWorkingDayPaymentRate.SetPaymentRate(nonWorkDayPaymentRate);
}
PaymentRate holidayRate = _dtCtrl.GetHolidayPaymentRateByWorkingCalendar(workingCalendarID);
if (holidayRate == null)
{
//TODO
}
else
{
ucHolidayPaymentRate.SetPaymentRate(holidayRate);
}
}
else
{
tabControl1.TabPages.Remove(tpgNonWorkingDayPaymentRate);
tabControl1.TabPages.Remove(tpgHolidayPaymentRate);
tpgWorkingDayPaymentRate.Text = "Flexi Hours Payment Rate";
}
//set break
List<Break> breakList = _dtCtrl.GetBreakListByWorkingCalendar(workingCalendarID);
if (breakList.Count >= 1)
{
lblBreak1.Text = GetTimeString(breakList[0].From) + " - " + GetTimeString(breakList[0].To);
}
else
{
lblBreak1.Text = "";
}
if (breakList.Count >= 2)
{
lblBreak2.Text = GetTimeString(breakList[1].From) + " - " + GetTimeString(breakList[0].To);
}
else
{
lblBreak2.Text = "";
}
if (breakList.Count >= 3)
{
lblBreak3.Text = GetTimeString(breakList[2].From) + " - " + GetTimeString(breakList[0].To);
}
else
{
lblBreak3.Text = "";
}
//set pay period
PayPeriod payPeriod = _dtCtrl.GetPayPeriod(_workingCalendar.PayPeriodID);
if (payPeriod == null)
{
//TODO
}
else
{
PayPeriodType payPeriodType = _dtCtrl.GetPayPeriodType(payPeriod.PayPeriodTypeID);
if (payPeriodType == null)
{
//TODO
}
else if (payPeriodType.ID == 5) //custom
{
lblPayPeriod.Text = "Every " + payPeriod.CustomPeriod + " day(s)";
}
else
{
lblPayPeriod.Text = payPeriodType.Name;
}
lblPayPeriodStartFrom.Text = payPeriod.StartFrom.ToShortDateString();
//set calendar
_holidayList = _dtCtrl.GetHolidayListByWorkingCalendar(workingCalendarID);
FormatCalendar(DateTime.Today.Year, DateTime.Today.Month);
}
}