public static IEnumerable <dynamic> GetGroups()
{
return(Website.WithDatabase((db) =>
{
var rows = db.Query("SELECT * FROM Groups WHERE IsPresent='1'");
// Join with Members to get creator
return Website.ExpandoFromTable(rows).Select((r) => {
r.Creator = Website.ExpandoFromRow(db.QuerySingle("SELECT * FROM Members WHERE UUN=@0", r.CreatorUUN));
return r;
});
}));
}
public static IEnumerable <dynamic> GetParts(string pieceID, string orderBy = "Instrument")
{
int.Parse(pieceID); // Validate input
return(Website.WithDatabase((db) =>
{
var Parts = Website.ExpandoFromTable(db.Query(
"SELECT * FROM Parts WHERE Piece=@0 AND IsPresent='1' ORDER BY " + orderBy, pieceID));
foreach (var part in Parts)
{
part.Piece = db.QuerySingle("SELECT * FROM Pieces WHERE ID=@0", part.Piece);
}
return Parts;
}));
}
public static IEnumerable <dynamic> GetAll()
{
return(Website.WithDatabase((db) =>
{
// Order by votes
var rows = db.Query(@"SELECT Suggestions.Suggestion, Title, [Group] FROM Suggestions
JOIN Endorsements2 ON Suggestions.Suggestion=Endorsements2.Suggestion
WHERE Suggestions.Active='1'
GROUP BY Suggestions.Suggestion, Title, [Group]
ORDER BY count(Suggestions.Suggestion) DESC");
return Website.ExpandoFromTable(rows).Select((r) => {
r.Group = db.QuerySingle("SELECT * FROM Groups WHERE ID=@0", r.Group); return r;
});
}));
}
public static dynamic GetCurrentLoans(string studentID)
{
// Open the database & update the user
return(Website.WithDatabase((db) =>
{
// Perform the query
var Loans = Website.ExpandoFromTable(db.Query("SELECT * FROM Loans WHERE Member=@0 AND Returned='0'", studentID));
// Query for relations
foreach (var row in Loans)
{
row.Part = Website.ExpandoFromRow(db.QuerySingle("SELECT * FROM Parts WHERE ID=@0", row.Part));
row.Part.Piece = db.QuerySingle("SELECT * FROM Pieces WHERE ID=@0", row.Part.Piece);
}
return Loans;
}));
}