public DataSet getGodmotherStatus(string statustocheck, string orderby, bool filterToday)
{
if (filterToday)
{
//What is todays date?? any takers??
string today = GlobalVar.getDate();
string query = "Select transID, FairyGodmotherTimestamp.fairyGodmotherID, n.Name, FairyGodmotherTimestamp.timestamp, FairyGodmotherTimestamp.status From FairyGodmotherTimestamp, (Select ID, lastname + ', ' + firstname AS Name FROM FairyGodmother) n, (Select fairyGodmotherID, max(timestamp) as TimeStamp FROM FairyGodmotherTimestamp Group BY fairyGodmotherID) a Where a.fairyGodmotherID= FairyGodmotherTimestamp.fairyGodmotherID and a.timestamp= FairyGodmotherTimestamp.timestamp and n.ID= fairyGodmotherTimestamp.fairyGodmotherID and FairyGodmotherTimestamp.status='";
query += statustocheck + "'";
query += "and fairyGodmotherTimestamp.fairyGodmotherID IN (";
query += "Select FairyGodmotherShift.fairyGodmotherID From FairyGodmotherShift INNER JOIN AvailableFGShift ON FairyGodmotherShift.shiftID = AvailableFGShift.shiftID Where (DATEDIFF(day, '" + today + "', AvailableFGShift.shiftStart)=0) and (DATEDIFF(hour, '" + today + "', AvailableFGShift.shiftStart) <2))";// and (DATEDIFF(day, '" + today + "', AvailableFGShift.shiftStart) >0))";
query += " Order By " + orderby;//transID";
return database.getDataSet(query, "tableName");
}
else
{
string query = "Select transID, FairyGodmotherTimestamp.fairyGodmotherID, n.Name, FairyGodmotherTimestamp.timestamp, FairyGodmotherTimestamp.status From FairyGodmotherTimestamp, (Select ID, lastname + ', ' + firstname AS Name FROM FairyGodmother) n, (Select fairyGodmotherID, max(timestamp) as TimeStamp FROM FairyGodmotherTimestamp Group BY fairyGodmotherID) a Where a.fairyGodmotherID= FairyGodmotherTimestamp.fairyGodmotherID and a.timestamp= FairyGodmotherTimestamp.timestamp and n.ID= fairyGodmotherTimestamp.fairyGodmotherID and FairyGodmotherTimestamp.status='";
query += statustocheck + "' Order By "+ orderby; //transID";
return database.getDataSet(query, "tableName");
}
}