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