public DataSet sqlSelect(string keyword)
{
string query = "";
switch (keyword)
{
//select all godmother ids and names
case "getGodmothers":
query = "Select ID,lastname + ', ' + firstname AS Name From FairyGodmother Where deleted =0";
break;
//select all cinderella ids and names
case "getCinderellas":
query = "Select ID,lastname + ', ' + firstname AS Name From Cinderella";
break;
case "getRandomGodmotherList":
query = "Select FairyGodmotherTimestamp.fairyGodmotherID 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='Available' Order By FairyGodmotherTimestamp.transID";
break;
case "getWaitingFairyGodmothersID":
query = "SELECT FairyGodmotherTimestamp.fairyGodmotherID, n.name 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='Available'";
break;
case "getWaitingCinderellasID":
query = "SELECT CinderellaTimestamp.cinderellaID, n.name FROM CinderellaTimestamp, (SELECT ID, lastname + ', ' + firstname AS NAME FROM Cinderella) n, (SELECT cinderellaID, MAX(TIMESTAMP) AS TIMESTAMP FROM CinderellaTimestamp GROUP BY cinderellaID) a WHERE a.cinderellaID= cinderellaTimestamp.cinderellaID AND a.timestamp= cinderellaTimestamp.timestamp AND n.ID= cinderellaTimestamp.cinderellaID AND cinderellaTimestamp.status='waiting'";
break;
//selects all the cinderella's statuses
case "getCinderellaStatuses":
query = "Select statusName, statusTxt From CinderellaStatus";
break;
case "getGodmotherStatuses":
query = "Select statusName, statusTxt From FairyGodmotherStatus";
break;
case "getShifts":
query = "Select (CONVERT(varchar,shiftStart,100) + ' -' + SUBSTRING(CONVERT(varchar, shiftEnd, 100), 13, 2) + ':' + SUBSTRING(CONVERT(varchar, shiftEnd, 100), 16, 2) + ''+ SUBSTRING(CONVERT(varchar, shiftEnd, 100), 18, 2)) as shiftTime, shiftID From AvailableFGShift";
//Need to only pull shift where date > than yesterday
//string where = "Where shiftDate > CAST(DateAdd(day, -1, getdate()) AS DATE";
//query += where;
break;
case "getlastGodMotherID":
query = "Select MAX(ID) FROM FairyGodmother";
break;
case "getCinderellaDetails":
query = "SELECT Cinderella.firstName, Cinderella.lastName, CinderellaTimestamp.status, Package.dressSize, Package.shoeSize, Package.dressAltered, Package.checkoutNotes FROM Cinderella, Package, FairyGodmother, CinderellaFGPairing, CinderellaTimestamp WHERE Cinderella.ID = Package.cinderellaID AND Cinderella.ID = CinderellaFGPairing.cinderellaID AND Cinderella.ID = CinderellaTimestamp.cinderellaID AND FairyGodmother.ID = CinderellaFGPairing.fairyGodmotherID AND (CinderellaTimestamp.status = 'Shopping' OR CinderellaTimestamp.status = 'Done Shopping' OR CinderellaTimestamp.status = 'Checked-Out')";
break;
case "getFairyGodmotherDetails":
query = "SELECT FairyGodmother.firstName, FairyGodmother.lastName FROM Cinderella, FairyGodmother, CinderellaFGPairing, CinderellaTimestamp WHERE Cinderella.ID = CinderellaFGPairing.cinderellaID AND Cinderella.ID = CinderellaTimestamp.cinderellaID AND FairyGodmother.ID = CinderellaFGPairing.fairyGodmotherID AND CinderellaTimestamp.status = 'Shopping'";
break;
case "exportData":
query = "Select * From Package";
break;
//gets the maxID from the Cinderella Table
case "getlastCinderellaID":
query = "SELECT MAX(ID) AS ID FROM CinderellaMGS.dbo.Cinderella";
break;
case "getDate":
query = "Select GetDate() as 'Current Date'";
break;
}
//Do not touch the code below this!
return database.getDataSet(query, "tableName");
}
