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; })); }