/// <summary> /// Delete a rule. /// </summary> /// <param name="question">The question</param> /// <param name="table">The specified Table</param> public void DeleteRule(string question, Table table) { using (var db = new BingDBEntities()) { switch (table) { case Table.ApprovedRules: var apprule = (from r in db.ApprovedRules where r.Question == question select r).First(); db.ApprovedRules.Remove(apprule); break; case Table.RejectedRules: var rejrule = (from r in db.RejectedRules where r.Question == question select r).First(); db.RejectedRules.Remove(rejrule); break; case Table.PendingRules: var penrule = (from r in db.PendingRules where r.Question == question select r).First(); db.PendingRules.Remove(penrule); break; default: System.Diagnostics.Debug.WriteLine("Unknown table"); return; } db.SaveChanges(); } }
/// <summary> /// Takes in a question and returns the corresponding answer for that rule /// from the PendingRules table. /// </summary> /// <param name="question">The supplied question</param> /// <returns>The answer of the question</returns> public string GetAnswerFromPending(string question) { // Remove extra whitespace and punctuation from the question question = Regex.Replace(question, "\\s+", " ").Trim(); question = RemovePuncForQuery(question); List <string> wildCard; if ((wildCard = GetWildCard(question)).Count > 0) { Business.Data data = new Business.Data(); return(data.GetData(wildCard[1], wildCard[0], wildCard[2])); } else { using (var db = new BingDBEntities()) { var query = from r in db.PendingRules where r.Question.Replace("?", "").Replace(".", "").Replace(",", "").Replace("!", "").Replace("<", ""). Replace(">", "").Replace("/", "").Replace("\\", "").Replace(":", "").Replace(";", "").ToLower() == question.ToLower() select r.Answer; string result = query.FirstOrDefault(); if (result != null) { return(result); } } } return("Sorry, no result was found for that query"); }
/// <summary> /// Add data to the relevant tables in the database. /// </summary> /// <param name="MovieName">The movie name</param> /// <param name="Genre">The genre</param> /// <param name="Actors">The actors</param> /// <param name="CreatedBy">User who created this data</param> /// <returns>Error message if adding data fails</returns> public string DataAdd(string MovieName, string Genre, List <string> Actors, string CreatedBy) { if (MovieName == null || Genre == null || Actors == null) { return("Movie, Genre and Actors fields are required."); } else if (CheckDuplicate(MovieName, -1) != null) { return(CheckDuplicate(MovieName, -1)); } else { BingDBEntities db = new BingDBEntities(); db.Movies.Add(new Movies { MovieName = MovieName, CreatedBy = CreatedBy }); db.SaveChanges(); db.Genres.Add(new Genre { GenreType = Genre, MovieID = MovieNameToID(MovieName) }); foreach (string Actor in Actors) { db.Actors.Add(new Actor { MovieID = MovieNameToID(MovieName), ActorName = Actor }); } db.SaveChanges(); return(null); } }
/// <summary> /// Returns true if the movie data trying to be added is a duplicate movie. /// </summary> /// <param name="Movie">The movie title</param> /// <param name="MovieID">The movie ID</param> /// <returns>Error message if duplicate exists</returns> public string CheckDuplicate(string Movie, int MovieID) { List <int> Matches = new List <int>(); using (var db = new BingDBEntities()) { Matches = db.Movies.Where(q => q.MovieName == Movie).Select(q => q.MovieID).ToList(); } if (Matches.Count > 0) { int sameIDCount = 0; foreach (int match in Matches) { if (match == MovieID) { sameIDCount++; } } if (sameIDCount > 0) { return(null); } else { return("That movie already exists. Please edit the movie on the Data List screen."); } } return(null); }
public ApproverReport() { BingDBEntities db = new BingDBEntities(); EditorData = new List <List <string> >(); List <string> EditorsList = db.Database.SqlQuery <string>("SELECT Email from AspNetUsers WHERE Id IN (SELECT UserId from AspNetUserRoles WHERE RoleId = 'Editor')").ToList(); double AvgSuccessRate = 0; int Count = 0; foreach (string user in EditorsList) { double ApprovedRulesCount = db.ApprovedRules.Where(q => q.CreatedBy == user).ToList().Count; double RejectedRulesCount = db.RejectedRules.Where(q => q.CreatedBy == user).ToList().Count; double PendingRulesCount = db.PendingRules.Where(q => q.CreatedBy == user).ToList().Count; string SuccessRate = (ApprovedRulesCount / (ApprovedRulesCount + RejectedRulesCount) * 100).ToString("N0") + "%"; if (ApprovedRulesCount == 0 && RejectedRulesCount == 0) { SuccessRate = "-"; } else { AvgSuccessRate += int.Parse(SuccessRate.TrimEnd('%')); Count++; } EditorData.Add(new List <string> { user, ApprovedRulesCount.ToString("N0"), RejectedRulesCount.ToString("N0"), PendingRulesCount.ToString("N0"), SuccessRate }); } this.AvgSuccessRate = (AvgSuccessRate / Count).ToString("N0") + "%"; }
/// <summary> /// Constructor for the RulesList. /// </summary> public RulesList() { BingDBEntities db = new BingDBEntities(); PendingRulesList = db.PendingRules.ToList(); ApprovedRulesList = db.ApprovedRules.ToList(); RejectedRulesList = db.RejectedRules.ToList(); }
/// <summary> /// Get MovieID from the name of a movie. /// </summary> /// <param name="MovieName">Name of the movie</param> /// <returns>The ID of the movie</returns> public int MovieNameToID(string MovieName) { using (var db = new BingDBEntities()) { int Movie = db.Movies.Where(q => q.MovieName == MovieName).Select(q => q.MovieID).First(); return(Movie); } }
/// <summary> /// Print all rejected rules. /// </summary> /// <returns>A list of all rejected rules</returns> public List <RejectedRule> PrintRejectedRules() { using (var db = new BingDBEntities()) { var rules = from r in db.RejectedRules select r; return(rules.ToList()); } }
/// <summary> /// Print all pending rules. /// </summary> /// <returns>A list of all pending rules</returns> public List <PendingRule> PrintPendingRules() { using (var db = new BingDBEntities()) { var rules = from r in db.PendingRules select r; return(rules.ToList()); } }
/// <summary> /// Print all rejected rules of a specific User. /// </summary> /// <param name="user">The specified User</param> /// <returns>A list of all rejected rules of the specified User</returns> public List <RejectedRule> PrintUsersRejectedRules(string user) { using (var db = new BingDBEntities()) { var rules = from r in db.RejectedRules where r.RejectedBy == user select r; return(rules.ToList()); } }
/// <summary> /// Get the ID of an approved rule. /// </summary> /// <param name="question">The question</param> /// <returns>The ID of the approved rule</returns> public int GetApprovedID(string question) { using (var db = new BingDBEntities()) { var id = (from r in db.ApprovedRules where r.Question == question select r.RuleID).First(); return(id); } }
/// <summary> /// Search for a pending rule based on its ID. /// </summary> /// <param name="id">The ID of a pending rule</param> /// <returns>The pending rule</returns> public PendingRule SearchPendingRule(int id) { using (var db = new BingDBEntities()) { var penrule = (from r in db.PendingRules where r.RuleID == id select r).First(); return(penrule); } }
/// <summary> /// Search for an approved rule based on its ID. /// </summary> /// <param name="id">The ID of a approved rule</param> /// <returns>The approved rule</returns> public ApprovedRule SearchApprovedRule(int id) { using (var db = new BingDBEntities()) { var apprule = (from r in db.ApprovedRules where r.RuleID == id select r).First(); return(apprule); } }
/// <summary> /// Print all pending rules of a specific User. /// </summary> /// <param name="user">The specified User</param> /// <returns>A list of all pending rules of the specified User</returns> public List <PendingRule> PrintUsersPendingRules(string user) { using (var db = new BingDBEntities()) { var rules = from r in db.PendingRules where r.LastEditedBy == user select r; return(rules.ToList()); } }
/// <summary> /// Search for a rejected rule based on its ID. /// </summary> /// <param name="id">The ID of a rejected rule</param> /// <returns>The rejected rule</returns> public RejectedRule SearchRejectedRule(int id) { using (var db = new BingDBEntities()) { var rejrule = (from r in db.RejectedRules where r.RuleID == id select r).First(); return(rejrule); } }
/// <summary> /// Reject a rule. /// </summary> /// <param name="question">The question</param> /// <param name="user">The current User</param> /// <param name="createdBy">The User who created the rule</param> /// <param name="lastEditedBy">The last User who edited the rule</param> public void RejectRule(string question, string user, string createdBy, string lastEditedBy) { using (var db = new BingDBEntities()) { var penrule = (from r in db.PendingRules where r.Question == question select r).First(); AddRule(penrule.Question, penrule.Answer, user, createdBy, lastEditedBy, penrule.Lookup, Table.RejectedRules); db.PendingRules.Remove(penrule); db.SaveChanges(); } }
/// <summary> /// Delete the data /// </summary> /// <param name="MovieID">The movie ID</param> public void DeleteData(int MovieID) { BingDBEntities db = new BingDBEntities(); var MovieDB = db.Movies.Where(q => q.MovieID == MovieID).First(); var GenreDB = db.Genres.Where(q => q.MovieID == MovieID).First(); var ActorDB = db.Actors.Where(q => q.MovieID == MovieID).ToList(); db.Movies.Remove(MovieDB); db.Genres.Remove(GenreDB); foreach (Actor Actor in ActorDB) { db.Actors.Remove(Actor); } db.SaveChanges(); }
/// <summary> /// Constructor for the Editor's Report. /// </summary> /// <param name="Email">The email of the current Editor</param> public EditorReport(string Email) { BingDBEntities db = new BingDBEntities(); UsersApprovedRules = db.ApprovedRules.Where(q => q.CreatedBy == Email).ToList(); UsersApprovedRulesCount = db.ApprovedRules.Where(q => q.CreatedBy == Email).ToList().Count; UsersRejectedRulesCount = db.RejectedRules.Where(q => q.CreatedBy == Email).ToList().Count; if (UsersApprovedRulesCount == 0 && UsersRejectedRulesCount == 0) { ApprovalRate = "-"; } else { ApprovalRate = (UsersApprovedRulesCount / (UsersApprovedRulesCount + UsersRejectedRulesCount) * 100).ToString("N0") + "%"; } }
/// <summary> /// Create a complete DataList from a MovieID. /// </summary> /// <param name="MovieID">The movie ID</param> /// <returns>A data list of the Movie</returns> public DataList CreateDataList(int MovieID) { string MovieName; string Genre; List <string> Actors; using (var db = new BingDBEntities()) { MovieName = db.Movies.Where(q => q.MovieID == MovieID).Select(q => q.MovieName).First(); Genre = db.Genres.Where(q => q.MovieID == MovieID).Select(q => q.GenreType).ToList()[0]; Actors = db.Actors.Where(q => q.MovieID == MovieID).Select(q => q.ActorName).ToList(); } return(new DataList { MovieID = MovieID, MovieName = MovieName, Genre = Genre, Actors = Actors }); }
/// <summary> /// Constructor for the Rules Report. /// </summary> public RulesReport() { BingDBEntities db = new BingDBEntities(); ApprovedRulesList = db.ApprovedRules.ToList(); ApprovedCount = ApprovedRulesList.Count; RejectedCount = db.RejectedRules.ToList().Count; if (ApprovedCount == 0 && RejectedCount == 0) { SuccessRate = "-"; } else { SuccessRate = ((double)ApprovedCount / ((double)ApprovedCount + (double)RejectedCount) * 100).ToString("N0") + "%"; } }
/// <summary> /// Constructor of the Data. /// </summary> public Data() { BingDBEntities db = new BingDBEntities(); DataList = new List <DataList>(); List <int> Movies = db.Movies.Select(q => q.MovieID).ToList(); foreach (int Movie in Movies) { var MovieData = db.Movies.Where(q => q.MovieID == Movie).First(); string Genre = db.Genres.Where(q => q.MovieID == Movie).Select(q => q.GenreType).ToList().First(); List <string> Actors = db.Actors.Where(q => q.MovieID == Movie).Select(q => q.ActorName).ToList(); DataList.Add(new DataList { MovieID = Movie, MovieName = MovieData.MovieName, Genre = Genre, Actors = Actors, LastEditedBy = MovieData.LastEditedBy, CreatedBy = MovieData.CreatedBy }); } }
/// <summary> /// Check whether the question is already exists in the database /// </summary> /// <param name="question">The question supplied</param> /// <param name="RuleID"></param> /// <returns>True if the question already exists</returns> public bool CheckExisting(string question, int RuleID) { int ApprovedCheck; int RejectedCheck; int PendingCheck; using (var db = new BingDBEntities()) { var query = from r in db.ApprovedRules where r.Question.Replace("?", "").Replace(".", "").Replace(",", "").Replace("!", "").Replace("<", ""). Replace(">", "").Replace("/", "").Replace("\\", "").Replace(":", "").Replace(";", "").ToLower() == question.Replace("?", "").Replace(".", "").Replace(",", "").Replace("!", "").Replace("<", ""). Replace(">", "").Replace("/", "").Replace("\\", "").Replace(":", "").Replace(";", "").ToLower() select r.RuleID; ApprovedCheck = query.FirstOrDefault(); query = from r in db.RejectedRules where r.Question.Replace("?", "").Replace(".", "").Replace(",", "").Replace("!", "").Replace("<", ""). Replace(">", "").Replace("/", "").Replace("\\", "").Replace(":", "").Replace(";", "").ToLower() == question.Replace("?", "").Replace(".", "").Replace(",", "").Replace("!", "").Replace("<", ""). Replace(">", "").Replace("/", "").Replace("\\", "").Replace(":", "").Replace(";", "").ToLower() select r.RuleID; RejectedCheck = query.FirstOrDefault(); query = from r in db.PendingRules where r.Question.Replace("?", "").Replace(".", "").Replace(",", "").Replace("!", "").Replace("<", ""). Replace(">", "").Replace("/", "").Replace("\\", "").Replace(":", "").Replace(";", "").ToLower() == question.Replace("?", "").Replace(".", "").Replace(",", "").Replace("!", "").Replace("<", ""). Replace(">", "").Replace("/", "").Replace("\\", "").Replace(":", "").Replace(";", "").ToLower() select r.RuleID; PendingCheck = query.FirstOrDefault(); } if ((ApprovedCheck != 0 || RejectedCheck != 0 || PendingCheck != 0) && (ApprovedCheck != RuleID && RejectedCheck != RuleID && PendingCheck != RuleID)) { return(true); } else { return(false); } }
/// <summary> /// Handles data edits. Updates movie and genre details, deletes all existing actors and adds a new list of actors. /// </summary> /// <param name="MovieID">The movie ID</param> /// <param name="MovieName">The movie name</param> /// <param name="Genre">The movie genre</param> /// <param name="Actors">The actors of the movie</param> /// <param name="LastEditedBy">The last User who edits the data</param> /// <returns>Error message if editing fails</returns> public string EditData(int MovieID, string MovieName, string Genre, List <string> Actors, string LastEditedBy) { //ADD CHECK TO SEE IF MOVIE NAME ALREADY EXISTS if (MovieName == null || Genre == null || Actors == null) { return("Movie, Genre and Actors fields are required."); } else if (CheckDuplicate(MovieName, MovieID) != null) { return(CheckDuplicate(MovieName, MovieID)); } using (var db = new BingDBEntities()) { var MovieDB = db.Movies.Where(q => q.MovieID == MovieID).First(); var GenreDB = db.Genres.Where(q => q.MovieID == MovieID).First(); var ActorDB = db.Actors.Where(q => q.MovieID == MovieID).ToList(); MovieDB.MovieName = MovieName; MovieDB.LastEditedBy = LastEditedBy; GenreDB.GenreType = Genre; foreach (Actor Actor in ActorDB) { db.Actors.Remove(Actor); } db.SaveChanges(); foreach (string Actor in Actors) { db.Actors.Add(new Actor { MovieID = MovieID, ActorName = Actor }); } db.SaveChanges(); } return(null); }
/// <summary> /// Edit an existing rule. /// </summary> /// <param name="id">The ID of the rule</param> /// <param name="question">The new question</param> /// <param name="answer">The new answer</param> /// <param name="user">The current User</param> /// <param name="Lookup">The lookup table for data driven rule</param> /// <param name="table">The specified Table</param> /// <returns>Error message if editing a rule fails</returns> public string EditRule(int id, string question, string answer, string user, string Lookup, Table table) { // Returns false if either question or response is empty if (question == null || answer == null) { return("Question and Answer fields are required."); } // If it is data driven, make sure it is in the right format if (Lookup != null && !((answer == "{Movies}" || answer == "{Genres}" || answer == "{Actors}") && (question.Contains("{%}")))) { return("You are attempting to make a data driven rule. Ensure your question contains {%} and your answer is either {Movies}, {Genres} or {Actors}."); } using (var db = new BingDBEntities()) { question = question.TrimEnd(' '); if (CheckExisting(question, id)) { return("This question already exists, please use another."); } switch (table) { case Table.ApprovedRules: var apprule = (from r in db.ApprovedRules where r.RuleID == id select r).First(); apprule.Question = question; apprule.Answer = answer; apprule.LastEditedBy = user; apprule.Lookup = Lookup; break; case Table.RejectedRules: var rejrule = (from r in db.RejectedRules where r.RuleID == id select r).First(); rejrule.Question = question; rejrule.Answer = answer; rejrule.LastEditedBy = user; rejrule.Lookup = Lookup; break; case Table.PendingRules: var penrule = (from r in db.PendingRules where r.RuleID == id select r).First(); penrule.Question = question; penrule.Answer = answer; penrule.LastEditedBy = user; penrule.Lookup = Lookup; break; default: System.Diagnostics.Debug.WriteLine("Unknown table"); return("Sorry something went wrong. Try reload the page and try again."); } db.SaveChanges(); return(null); } }
/// <summary> /// Gets desired data from the data tables. /// </summary> /// <param name="LookupTable">Table to lookup the comparison for the Value</param> /// <param name="Value">The value to compare</param> /// <param name="AnswerTable">Table to get the relevant answers</param> /// <returns>The desired data</returns> public string GetData(string LookupTable, string Value, string AnswerTable) { using (var db = new BingDBEntities()) { List <int> Matches = new List <int>(); List <string> Answers = new List <string>(); switch (LookupTable) { case "Movies": Matches = db.Movies.Where(q => q.MovieName.ToLower() == Value.ToLower()).Select(q => q.MovieID).ToList(); break; case "Genres": Matches = db.Genres.Where(q => q.GenreType.ToLower() == Value.ToLower()).Select(q => q.MovieID).ToList(); break; case "Actors": Matches = db.Actors.Where(q => q.ActorName.ToLower() == Value.ToLower()).Select(q => q.MovieID).ToList(); break; } switch (AnswerTable) { case "{Movies}": foreach (int Match in Matches) { Answers.AddRange(db.Movies.Where(q => q.MovieID == Match).Select(q => q.MovieName).ToList()); } break; case "{Genres}": foreach (int Match in Matches) { Answers.AddRange(db.Genres.Where(q => q.MovieID == Match).Select(q => q.GenreType).ToList()); } break; case "{Actors}": foreach (int Match in Matches) { Answers.AddRange(db.Actors.Where(q => q.MovieID == Match).Select(q => q.ActorName).ToList()); } break; } if (Answers.Count == 0) { return("Sorry, no data was found for that query"); } else { string answer = ""; foreach (string ans in Answers) { answer = answer + ans + ", "; } return(answer.Substring(0, answer.Length - 2)); } } }
/// <summary> /// Return the wildcard of a given question if it meets any of data driven questions. /// </summary> /// <param name="askedQuestion">The supplied question</param> /// <returns>A list of best matches</returns> public List <string> GetWildCard(string askedQuestion) { // Get all data driven questions List <string> ApprovedQuestions = new List <string>(); using (var db = new BingDBEntities()) { ApprovedQuestions = (from r in db.ApprovedRules where r.Lookup != null select r.Question).ToList(); } // Create list of potential matches List <List <string> > Matches = new List <List <string> >(); foreach (string question in ApprovedQuestions) { string comparison = question.Replace("?", "").Replace(".", "").Replace(",", "").Replace("!", "").Replace("<", ""). Replace(">", "").Replace("/", "").Replace("\\", "").Replace(":", "").Replace(";", "").ToLower(); Tuple <string, string> SeparatedQuestion = GetQuestionPrefixSufix(comparison); if (askedQuestion.IndexOf(SeparatedQuestion.Item1, StringComparison.OrdinalIgnoreCase) >= 0 && askedQuestion.IndexOf(SeparatedQuestion.Item2, StringComparison.OrdinalIgnoreCase) >= 0) { string Answer; string Lookup; using (var db = new BingDBEntities()) { Answer = (from r in db.ApprovedRules where r.Question.Equals(question) select r.Answer).First(); Lookup = (from r in db.ApprovedRules where r.Question.Equals(question) select r.Lookup).First(); } Matches.Add(new List <string> { SeparatedQuestion.Item1, askedQuestion.Substring(SeparatedQuestion.Item1.Length, askedQuestion.Length - SeparatedQuestion.Item1.Length - SeparatedQuestion.Item2.Length), SeparatedQuestion.Item2, Lookup, Answer }); } } // Return best match int LargestPrefix = 0; int LargestSufix = 0; List <string> BestMatch = new List <string>(); foreach (List <string> match in Matches) { if (match[0].Length >= LargestPrefix && match[2].Length >= LargestSufix) { BestMatch.Clear(); BestMatch.Add(match[1]); BestMatch.Add(match[3]); BestMatch.Add(match[4]); LargestPrefix = match[0].Length; LargestSufix = match[2].Length; } } return(BestMatch); }
/// <summary> /// Add a new rule into the specified Table. /// </summary> /// <param name="question">The question</param> /// <param name="response">The answer to the question</param> /// <param name="user">The current User</param> /// <param name="createdBy">The User who created the rule</param> /// <param name="lastEditedBy">The last User who edited the rule</param> /// <param name="Lookup">The lookup table for data driven rule</param> /// <param name="table">The specified Table</param> /// <returns>Error message if adding a rule fails</returns> public string AddRule(string question, string response, string user, string createdBy, string lastEditedBy, string Lookup, Table table) { // Returns false if either question or response is empty if (question == null || response == null) { return("Question and Answer fields are required."); } // If it is data driven, make sure it is in the right format. if (Lookup != null && !((response == "{Movies}" || response == "{Genres}" || response == "{Actors}") && (question.Contains("{%}")))) { return("You are attempting to make a data driven rule. Ensure your question contains {%} and your answer is either {Movies}, {Genres} or {Actors}."); } // Remove extra whitespace from the question question = Regex.Replace(question, "\\s+", " ").Trim(); using (var db = new BingDBEntities()) { question = question.TrimEnd(' '); switch (table) { case Table.ApprovedRules: var apprule = new ApprovedRule { Question = question, Answer = response, ApprovedBy = user, LastEditedBy = lastEditedBy, CreatedBy = createdBy, Lookup = Lookup }; db.ApprovedRules.Add(apprule); break; case Table.RejectedRules: var rejrule = new RejectedRule { Question = question, Answer = response, RejectedBy = user, LastEditedBy = lastEditedBy, CreatedBy = createdBy, Lookup = Lookup }; db.RejectedRules.Add(rejrule); break; case Table.PendingRules: if (CheckExisting(question, -1)) { return("This question already exists, please use another."); } var penrule = new PendingRule { Question = question, Answer = response, LastEditedBy = user, CreatedBy = user, Lookup = Lookup }; db.PendingRules.Add(penrule); break; default: System.Diagnostics.Debug.WriteLine("Unknown table"); return("Sorry something went wrong. Try reload the page and try again."); } db.SaveChanges(); return(null); } }