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