public bool DeletePublisher(Publisher publisher) { if (string.IsNullOrEmpty(publisher.Token)) { return(false); } int personId = TokenManager.TokenDictionaryHolder[publisher.Token]; if (personId < 0) { return(false); } Person person = new ApiAccountController().GetPerson(personId); if (person.PersonType < PersonType.Librarian) { return(false); } using (var con = new SqlConnection(DB.ConnectionString)) { con.Open(); DB.ExecuteNonQuery(con, "UPDATE Book SET PublisherId = NULL WHERE PublisherId = @pid", new KeyValuePair <string, object>("pid", publisher.PublisherId)); DB.ExecuteNonQuery(con, "DELETE FROM Publisher WHERE PublisherId = @pid", new KeyValuePair <string, object>("pid", publisher.PublisherId)); } return(true); }
public bool Checkout(Reseravtion reseravtion) { string token = reseravtion.Token; if (string.IsNullOrEmpty(token)) { return(false); } int personId = TokenManager.TokenDictionaryHolder[token]; if (personId < 0) { return(false); } var person = new ApiAccountController().GetPerson(personId); if (person.PersonType < PersonType.Librarian) { return(false); } using (var con = new SqlConnection(DB.ConnectionString)) { con.Open(); // If Is already picked up, make it done, else make it pickedUp DB.ExecuteNonQuery(con, "UPDATE Reservation SET IsDone = (CASE WHEN IsPickedUp = 0 THEN 0 ELSE 1 END), IsPickedUp = 1 " + "WHERE ReservationId = @rid", new KeyValuePair <string, object>("rid", reseravtion.ReservationId)); } return(true); }
public int AddAuthor(Author author) { if (string.IsNullOrEmpty(author.Token)) { return(-1); } int personId = TokenManager.TokenDictionaryHolder[author.Token]; if (personId < 0) { return(-1); } Person person = new ApiAccountController().GetPerson(personId); if (person.PersonType < PersonType.Librarian) { return(-1); } if (string.IsNullOrEmpty(author.AuthorName)) { return(-1); } using (var con = new SqlConnection(DB.ConnectionString)) { con.Open(); int auId = DB.ExecuteInsertQuery(con, "INSERT INTO Author (AuthorName) VALUES (@auname)", new KeyValuePair <string, object>("auname", author.AuthorName)); return(auId); } }
public List <Reseravtion> ReservationSearch(string token, bool today, string key = "") { if (string.IsNullOrEmpty(token)) { return(null); } int personId = TokenManager.TokenDictionaryHolder[token]; if (personId < 0) { return(null); } var person = new ApiAccountController().GetPerson(personId); if (person.PersonType < PersonType.Librarian) { return(null); } if (key == null) { key = ""; } List <Reseravtion> reseravtions = new List <Reseravtion>(); using (var con = new SqlConnection(DB.ConnectionString)) { con.Open(); using (var reader = DB.ExecuteQuery(con, string.Format("SELECT Reservation.*, PersonInfo.Username, Book.Price * Reservation.Quantity AS Price, Book.BookTitle, " + "(CASE WHEN IsDone = 1 THEN 0 WHEN IsPickedUp = 1 AND GETDATE() > ReturnDate THEN 3 WHEN IsPickedUp = 1 THEN 2 ELSE 1 END) AS Status " + "FROM Reservation JOIN PersonInfo ON PersonInfo.PersonId = Reservation.PersonId " + "JOIN Book ON Reservation.BookId = Book.BookId WHERE IsDone = 0 AND (Username LIKE CONCAT('%', @key ,'%')" + "OR BookTitle LIKE CONCAT('%', @key, '%')) AND (CAST(PickupDate AS DATE) = CAST(GETDATE() AS DATE) OR CAST(ReturnDate AS DATE) = CAST(GETDATE() AS DATE) OR {0})" + "ORDER BY PickupDate, OrderDate", today ? "1=0" : "1=1"), new KeyValuePair <string, object>("key", key))) { while (reader.Read()) { reseravtions.Add(new Reseravtion { ReservationId = Convert.ToInt32(reader["ReservationId"]), PersonId = Convert.ToInt32(reader["PersonId"]), BookId = Convert.ToInt32(reader["BookId"]), PickupDate = Convert.ToDateTime(reader["PickupDate"]).ToString("MM/dd/yyyy"), ReturnDate = Convert.ToDateTime(reader["ReturnDate"]).ToString("MM/dd/yyyy"), Quantity = Convert.ToInt32(reader["Quantity"]), IsPickedUp = Convert.ToBoolean(reader["IsPickedUp"]), IsDone = Convert.ToBoolean(reader["IsDone"]), Username = reader["Username"].ToString(), BookTitle = reader["BookTitle"]?.ToString(), Status = (ReservationType)Convert.ToInt32(reader["Status"]), Price = Convert.ToDecimal(reader["Price"]) }); } } } return(reseravtions); }
public new ActionResult Profile() { ViewBag.Title = "Profile"; var controller = new ApiAccountController(); string token = model.Token; Person person = controller.GetPerson(TokenManager.TokenDictionaryHolder[token]); person.Token = model.Token; person.TokenPersonType = model.TokenPersonType; return(View(person)); }
public bool Delete(Book book) { // making sure someone who has permission is doing this string token = book.Token; if (string.IsNullOrEmpty(token)) { return(false); } int personId = TokenManager.TokenDictionaryHolder[token]; if (personId < 0) { return(false); } Person person = new ApiAccountController().GetPerson(personId); if (person.PersonType < PersonType.Librarian) { return(false); } using (var con = new SqlConnection(DB.ConnectionString)) { con.Open(); string imagePath; var bidparam = new KeyValuePair <string, object>("bid", book.BookId); using (var reader = DB.ExecuteQuery(con, "SELECT * FROM Book WHERE BookId = @bid", bidparam)) { if (reader.Read()) { imagePath = reader["ThumbnailImage"]?.ToString(); } else { return(false); } } if (!string.IsNullOrEmpty(imagePath)) { // delete the thumbnail image var fileInfo = new FileInfo(HttpContext.Current.Server.MapPath(imagePath)); fileInfo.Delete(); } // any foreign key references should be also deleted DB.ExecuteNonQuery(con, "DELETE FROM Reservation WHERE BookId = @bid", bidparam); DB.ExecuteNonQuery(con, "DELETE FROM Book WHERE BookId = @bid", bidparam); } return(true); }
public bool AddBook([FromUri] Book book) { if (string.IsNullOrEmpty(book.Token)) { return(false); } int personId = TokenManager.TokenDictionaryHolder[book.Token]; if (personId < 0) { return(false); } Person person = new ApiAccountController().GetPerson(personId); if (person.PersonType < PersonType.Librarian) { return(false); } if (string.IsNullOrEmpty(book.BookTitle) || string.IsNullOrEmpty(book.PublishingDate)) { return(false); } int bookId; using (var con = new SqlConnection(DB.ConnectionString)) { con.Open(); bookId = DB.ExecuteInsertQuery(con, "INSERT INTO Book (BookTitle, BookDescription, AuthorId, PublisherId, Price, " + "PublishingDate) VALUES (@btitle, @bdesc, @aid, @pid, @price, @pubDate)", new KeyValuePair <string, object>("btitle", book.BookTitle), new KeyValuePair <string, object>("bdesc", StringOrDBNull(book.BookDescription)), new KeyValuePair <string, object>("aid", PositiveOrDBNull(book.AuthorId)), new KeyValuePair <string, object>("pid", PositiveOrDBNull(book.PublisherId)), new KeyValuePair <string, object>("price", book.Price), new KeyValuePair <string, object>("pubDate", StringOrDBNull(book.PublishingDate))); } if (HttpContext.Current.Request.Files.Count > 0) { HttpPostedFile file = HttpContext.Current.Request.Files[0]; UpdateImageImpl(file, bookId); } return(true); }
public ActionResult Signup(string username, string password, string verifyPassword) { ViewBag.Title = "Signup"; if (string.IsNullOrEmpty(username) && string.IsNullOrEmpty(password) && string.IsNullOrEmpty(verifyPassword)) { return(Signup()); } Person user = new ApiAccountController().Signup(username, password, verifyPassword); if (user.Error) { return(View(user)); } Session["token"] = user.Token; return(RedirectToAction("Index", "Home")); }
public bool UpdateImage([FromUri] string token, [FromUri] int bookId) { int personId = TokenManager.TokenDictionaryHolder[token]; if (personId < 0) { return(false); } var person = new ApiAccountController().GetPerson(personId); if (person.PersonType < PersonType.Librarian) { return(false); } HttpPostedFile file = HttpContext.Current.Request.Files[0]; return(UpdateImageImpl(file, bookId)); }
public List <Person> GetPersons(string token) { List <Person> retVal = new List <Person>(); if (string.IsNullOrEmpty(token)) { return(null); } int personId = TokenManager.TokenDictionaryHolder[token]; if (personId < 0) { return(null); } var person = new ApiAccountController().GetPerson(personId); if (person.PersonType < PersonType.Admin) { return(null); } using (SqlConnection con = new SqlConnection(DB.ConnectionString)) { con.Open(); using (var reader = DB.ExecuteQuery(con, "SELECT * FROM PersonInfo " + "WHERE PersonInfo.PersonId != @pid", new KeyValuePair <string, object>("pid", personId))) { while (reader.Read()) { retVal.Add(new Person { PersonId = Convert.ToInt32(reader["PersonId"]), Username = reader["Username"]?.ToString(), PersonType = (PersonType)Convert.ToInt32(reader["PersonType"]) }); } } } return(retVal); }
public bool DeletePerson(Person user) { string token = user.Token; if (string.IsNullOrEmpty(token)) { return(false); } int personId = TokenManager.TokenDictionaryHolder[token]; if (personId < 0) { return(false); } var person = new ApiAccountController().GetPerson(personId); if (person.PersonType < PersonType.Admin) { return(false); } using (var con = new SqlConnection(DB.ConnectionString)) { con.Open(); var pidparam = new KeyValuePair <string, object>("pid", user.PersonId); int exists = DB.ExecuteScalar(con, "SELECT COUNT(*) FROM Maintainer WHERE PersonId = @pid", pidparam); if (exists > 0) { return(false); } DB.ExecuteNonQuery(con, "UPDATE Person SET Deleted = 1 WHERE PersonId = @pid", pidparam); } return(true); }
public ActionResult Login(string username, string password) { ViewBag.Title = "Login"; if (string.IsNullOrEmpty(username) && string.IsNullOrEmpty(password)) { return(Login()); } ApiAccountController apiController = new ApiAccountController(); string token = apiController.Login(username, password); if (string.IsNullOrEmpty(token)) { Session.Remove("token"); return(View(new Person { Error = true, Username = username, Token = model.Token })); } else { Session["token"] = token; if (Session["redirect"] != null) { string redirect = Session["redirect"].ToString(); Session.Remove("redirect"); return(Redirect(redirect)); } else { return(RedirectToAction("Index", "Home")); } } }
public Person AddPerson([FromBody] Person librarian) { if (string.IsNullOrEmpty(librarian.Token)) { return(null); } int personId = TokenManager.TokenDictionaryHolder[librarian.Token]; if (personId < 0) { return(null); } var person = new ApiAccountController().GetPerson(personId); if (person.PersonType < PersonType.Admin) { return(null); } if (string.IsNullOrEmpty(librarian.Username) || string.IsNullOrEmpty(librarian.UserPassword)) { return(null); } if (librarian.Username.Length < 4 || librarian.UserPassword.Length < 6) { return(null); } using (var con = new SqlConnection(DB.ConnectionString)) { con.Open(); int exists = DB.ExecuteScalar(con, "SELECT COUNT(*) FROM Person WHERE Username = @username", new KeyValuePair <string, object>("username", librarian.Username)); if (exists > 0) { return(null); } if (librarian.PersonType == PersonType.Librarian) { librarian.PersonId = DB.ExecuteInsertQuery(con, "INSERT INTO Person (Username , UserPassword) VALUES" + " (@lname, @lpass) ", new KeyValuePair <string, object>("lname", librarian.Username), new KeyValuePair <string, object>("lpass", OneWayEncrpyt(librarian.UserPassword))); DB.ExecuteNonQuery(con, "INSERT INTO Librarian (PersonId) VALUES (@pid)", new KeyValuePair <string, object>("pid", librarian.PersonId)); } else if (librarian.PersonType == PersonType.Proffessor) { librarian.PersonId = DB.ExecuteInsertQuery(con, "INSERT INTO Person (Username , UserPassword) VALUES" + " (@pname, @Ppass) ", new KeyValuePair <string, object>("pname", librarian.Username), new KeyValuePair <string, object>("Ppass", OneWayEncrpyt(librarian.UserPassword))); DB.ExecuteNonQuery(con, "INSERT INTO Professor (PersonId) VALUES (@pid)", new KeyValuePair <string, object>("pid", librarian.PersonId)); } else if (librarian.PersonType == PersonType.Admin) { librarian.PersonId = DB.ExecuteInsertQuery(con, "INSERT INTO Person (Username , UserPassword) VALUES" + " (@pname, @Ppass) ", new KeyValuePair <string, object>("pname", librarian.Username), new KeyValuePair <string, object>("Ppass", OneWayEncrpyt(librarian.UserPassword))); DB.ExecuteNonQuery(con, "INSERT INTO Librarian (PersonId) VALUES (@pid)", new KeyValuePair <string, object>("pid", librarian.PersonId)); DB.ExecuteNonQuery(con, "INSERT INTO Maintainer (PersonId) VALUES (@pid)", new KeyValuePair <string, object>("pid", librarian.PersonId)); } } return(librarian); }
private string TokenGenerator() { return(ApiAccountController.TokenGenerator()); }
public bool UpdateBook([FromBody] Book book) { if (string.IsNullOrEmpty(book.Token)) { return(false); } int personId = TokenManager.TokenDictionaryHolder[book.Token]; if (personId < 0) { return(false); } Person person = new ApiAccountController().GetPerson(personId); if (person.PersonType < PersonType.Librarian) { return(false); } using (var con = new SqlConnection(DB.ConnectionString)) { con.Open(); int count = DB.ExecuteScalar(con, "SELECT COUNT(BookId) FROM Book WHERE BookId = @bid", new KeyValuePair <string, object>("bid", book.BookId)); if (count < 1) { return(false); } string[] queryParts = new string[] { book.BookDescription != null ? "BookDescription = @bdesc" : "", book.AuthorId != null ? "AuthorId = @aid" : "", book.PublisherId != null ? "PublisherId = @pid" : "", book.BookTitle != null ? "BookTitle = @btitle" : "", book.PublishingDate != null ? "PublishingDate = @pubDate" : "", book.Quantity != null ? "Quantity = @quantity" : "", book.Price != null ? "Price = @price" : "" }; string queryBuilder = ""; bool flag = true; // queryBuilder = "...SET A = @a, B = @b, ..." foreach (var str in queryParts) { if (!string.IsNullOrEmpty(str)) { if (flag) { queryBuilder += str; flag = false; } else { queryBuilder += ", " + str; } } } if (string.IsNullOrEmpty(queryBuilder)) { return(false); } DB.ExecuteNonQuery(con, "UPDATE Book SET " + queryBuilder + " WHERE BookId = @bid " + "--@aid --@pid --@quantity --@bdesc --@btitle --@pubDate --@price", new KeyValuePair <string, object>("bid", book.BookId), new KeyValuePair <string, object>("aid", PositiveOrDBNull(book.AuthorId)), new KeyValuePair <string, object>("pid", PositiveOrDBNull(book.PublisherId)), new KeyValuePair <string, object>("quantity", book.Quantity ?? -1), new KeyValuePair <string, object>("bdesc", book.BookDescription ?? ""), new KeyValuePair <string, object>("btitle", book.BookTitle ?? ""), new KeyValuePair <string, object>("price", PositiveOrDBNull(book.Price)), new KeyValuePair <string, object>("pubDate", book.PublishingDate ?? "")); } return(true); }