public DataSet getCinderellas(bool isShopping, bool isDoneShopping, bool isCheckedOut, string orderby)
{
//this is for checkout, so that i can pull data separately based on which statuses are desired
if (isShopping == false && isDoneShopping == false && isCheckedOut == false)
{
return null;
}
else
{
string query = "SELECT DISTINCT CinderellaTimestamp.transID, Cinderella.ID, Cinderella.firstName, Cinderella.lastName, CN.NAME, FairyGodmother.ID, FGM.fgID, FairyGodmother.firstName, FairyGodmother.lastName, FGLastN.fgLastName, FGFirstN.fgFirstName, CinderellaTimestamp.status, Package.cinderellaID, Package.shoeSize, Package.dressSize, Package.dressAltered, Package.whenAvailable FROM CinderellaTimestamp, (SELECT ID, lastname + ', ' + firstname AS NAME FROM Cinderella) CN, (SELECT ID, lastName AS fgLastName FROM FairyGodmother) FGLastN, (SELECT ID, firstName AS fgFirstName FROM FairyGodmother) FGFirstN, (SELECT ID AS fgID FROM FairyGodmother) FGM, Cinderella JOIN CinderellaFGPairing ON Cinderella.ID=CinderellaFGPairing.cinderellaID JOIN FairyGodmother ON FairyGodmother.ID=CinderellaFGPairing.fairyGodmotherID LEFT OUTER JOIN Package ON CinderellaFGPairing.cinderellaID=Package.cinderellaID WHERE CN.ID=CinderellaTimestamp.cinderellaID AND FGFirstN.ID=FairyGodmother.ID AND FGLastN.ID=FairyGodmother.ID AND FGM.fgID =FairyGodmother.ID AND transID IN (SELECT MAX(transID) FROM CinderellaTimestamp WHERE CN.ID = CinderellaTimestamp.cinderellaID) AND Cinderella.ID=CinderellaTimestamp.cinderellaID AND (";
if (isShopping == true)
{
query += "cinderellaTimestamp.status='Shopping'";
if (isDoneShopping == false && isCheckedOut == false)
{
query += ")";
}
}
if (isDoneShopping == true)
{
if (isShopping == false)
{
query += "cinderellaTimestamp.status='Done Shopping'";
}
else
{
query += " OR cinderellaTimestamp.status='Done Shopping'";
}
if (isCheckedOut == false)
{
query += ")";
}
}
if (isCheckedOut == true)
{
if (isShopping == false && isDoneShopping == false)
{
query += "cinderellaTimestamp.status='Checked-Out')";
}
else
{
query += " OR cinderellaTimestamp.status='Checked-Out')";
}
}
query += " ORDER BY " + orderby;
return database.getDataSet(query, "tableName");
}
}