Hi. I have a web form with a datagrid that displays employees who are on holiday. One of the datagrid columns shows the date of their last day of holiday, but what I really require is the Start Back at work date, i.e. if their last day of holiday is a Friday, then the Start Back date should be the following Monday (ignoring any weekends and public holiday dates).
I have a table which holds the public holiday dates (tblUKHolidays), but how do I build the datagrid to include a Start Back column. I’ve also tried building a c# method to calculate the Start Back date but I don’t know how to include this in the datagrid source.
Here is the datagrid source code without the method
private DataView CreateSource(string SortBy) {
SqlDataAdapter daGrid = new SqlDataAdapter(String.Format(@"SELECT qryFullName.FullName AS FullName, tblAbsence.dtmAbsStart AS StartDate, tblAbsence.dtmAbsEnd AS EndDate, tblAbsence.blnContactable AS Contactable, " +
"CASE " +
"WHEN tblAbsence.dtmAbsStart = '{0}' THEN tblAbsence.strAMPMStart " +
"WHEN tblAbsence.dtmAbsEnd = '{0}' AND tblAbsence.strAMPMEnd IS NOT NULL THEN tblAbsence.strAMPMEnd " +
"ELSE '' " +
"END AS AMPM " +
"FROM qryFullName INNER JOIN tblAbsence ON qryFullName.strLogonName = tblAbsence.strLogonName " +
"WHERE (tblAbsence.dtmAbsStart <= '{0}') AND (tblAbsence.dtmAbsEnd >= '{0}') AND tblAbsence.blnDeleted = 0", ActiveDate), HolData.TimeOffConn);
DataSet dsGrid = new DataSet();
daGrid.Fill(dsGrid,"Absence");
DataView vwBookings = dsGrid.Tables["Absence"].DefaultView;
vwBookings.Sort=SortBy;
return vwBookings;
}
Here is the method that I want to supply the Start Back date.
private DateTime StartBackDate(DateTime dtEndDate)
{
DateTime MyDate;
int MyDays = 0;
MyDate = dtEndDate.AddDays(1);
SqlDataAdapter daUKHolidays = new SqlDataAdapter("Select * from tblUKHolidays", HolData.TimeOffConn);
DataSet dsUKHolidays = new DataSet();
daUKHolidays.Fill(dsUKHolidays, "tblUKHolidays");
DataView dvUKHolidays = new DataView(dsUKHolidays.Tables["tblUKHolidays"]);
int rowIndex = dvUKHolidays.Find(MyDate);
while (MyDays < 1)
{
if ((MyDate.DayOfWeek.ToString() == "Monday")|(MyDate.DayOfWeek.ToString() == "Tuesday")|(MyDate.DayOfWeek.ToString() == "Wednesday")|(MyDate.DayOfWeek.ToString() == "Thursday")|(MyDate.DayOfWeek.ToString() == "Friday"))
{
if (rowIndex == -1)
{
MyDays = MyDays + 1;
}
}
if (MyDays >= 1)
break;
MyDate = MyDate.AddDays(1);;
}
return MyDate;
}