public static string GetMusicReviews_Builder(SQL_Review_REQUEST including, SQL_Review_REQUEST excluding) { string SQLRequest = ""; return(SQLRequest); }
public List <Review_Reference> getReviews(SQL_Review_REQUEST request) { List <string[]> results = Query(SqlStatements.GetMusicReviews_Builder(request)); List <Review_Reference> reviews = new List <Review_Reference>(); //Each row will be a review build them here and return them. foreach (string[] row in results) { reviews.Add(new Review_Reference(row)); } return(reviews); }
//Get the SQL statement corresponding with a set of included strings public static string GetMusicReviews_Builder(SQL_Review_REQUEST request) { StringBuilder SQLRequest = new StringBuilder(); bool needsAnd = false; bool somethingAdded = false; //Returns "Review Reference" not a review; SQLRequest.Append("Select Title,Release_Date,Review_Date,Album,Artist,Rating From MusicReview Where "); if (!request.Artist.Equals("")) { somethingAdded = true; SQLRequest.Append("Artist = '" + request.Artist + "'"); needsAnd = true; } if (!request.Album.Equals("")) { somethingAdded = true; if (needsAnd) { SQLRequest.Append(" AND Album = '" + request.Album + "'"); } else { SQLRequest.Append("Album = '" + request.Album + "'"); } } //Check Comparative Statements ComparatorAdd("Rating", '>', request.RatingMax); ComparatorAdd("Rating", '<', request.RatingMin); ComparatorAdd("Release_Date", '>', request.ReleaseYearMax); ComparatorAdd("Release_Date", '<', request.ReleaseYearMin); ComparatorAdd("Review_Date", '>', request.ReviewYearMax); ComparatorAdd("Review_Date", '<', request.ReviewYearMin); string join_tags = "Select ID from MusicReview inner join ReviewTags on ID = ReviewID inner join Tags on ReviewTags.TagID = Tags.TagID where Name in"; //Add the Included Tags if (!AllNull(request.Included_Tags)) { somethingAdded = true; if (needsAnd) { SQLRequest.Append(" AND ID in (" + join_tags); } else { SQLRequest.Append("ID in (" + join_tags); } foreach (List <TagObject> Request in request.Included_Tags.Where(n => n != null)) { SQLRequest.Append("("); foreach (TagObject Tag in Request) { SQLRequest.Append("'" + Tag.Name + "',"); } SQLRequest.Remove(SQLRequest.Length - 1, 1); SQLRequest.Append(") group by ID having count(ID) = " + Request.Count + " union " + join_tags); } SQLRequest.Remove(SQLRequest.Length - (join_tags.Length + 6), (join_tags.Length + 6)); needsAnd = true; SQLRequest.Append(")"); } //Exclude the excluded tags if (!AllNull(request.Excluded_Tags)) { somethingAdded = true; if (needsAnd) { SQLRequest.Append(" AND ID not in (" + join_tags); } else { SQLRequest.Append("ID not in (" + join_tags); } //Select ID from(MusicReview inner join ReviewTags on ID = ReviewID inner join Tags on ReviewTags.TagID = Tags.TagID) where Name in ('english', 'fake') group by ID having count(ID) = 2 foreach (List <TagObject> Request in request.Excluded_Tags.Where(n => n != null)) { SQLRequest.Append("("); foreach (TagObject Tag in Request) { SQLRequest.Append("'" + Tag.Name + "',"); } SQLRequest.Remove(SQLRequest.Length - 1, 1); SQLRequest.Append(") group by ID having count(ID) = " + Request.Count + " union " + join_tags); } SQLRequest.Remove(SQLRequest.Length - (join_tags.Length + 6), (join_tags.Length + 6)); needsAnd = true; SQLRequest.Append(")"); } void ComparatorAdd(string field, char symbol, int value) { if (value >= 0) { somethingAdded = true; addAndRequest(field + ' ' + symbol + ' ' + value); needsAnd = true; } } void addAndRequest(string Request) { if (needsAnd) { SQLRequest.Append(" AND " + Request); } else { SQLRequest.Append(Request); } } if (somethingAdded == false) { return("Select Title,Release_Date,Review_Date,Album,Artist,Rating From MusicReview"); } return(SQLRequest.ToString()); }