public static Response.BaseResponse RemoveUser(User user)
{
string queryRemoveUser = "******";
Response.BaseResponse response = new Response.BaseResponse();
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
MySqlCommand mySqlCommandRemoveUser = new MySqlCommand(queryRemoveUser, conn);
mySqlCommandRemoveUser.Parameters.AddWithValue("@username", user.Username);
try
{
conn.Open();
int rowsAffected = mySqlCommandRemoveUser.ExecuteNonQuery();
if (rowsAffected != 1)
{
throw new Exception("Error removing from users table. Rows affected: " + rowsAffected);
}
response.Success = true;
}
catch (Exception e)
{
response.ErrorText = e.Message;
}
return(response);
}
}
public static Response.BaseResponse RemoveBook(Book book)
{
string queryRemoveBook = "DELETE FROM `bx-books` WHERE ISBN = @ISBN";
Response.BaseResponse response = new Response.BaseResponse();
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
MySqlCommand mySqlCommandRemoveBook = new MySqlCommand(queryRemoveBook, conn);
mySqlCommandRemoveBook.Parameters.AddWithValue("@ISBN", book.ISBN);
try
{
conn.Open();
int rowsAffected = mySqlCommandRemoveBook.ExecuteNonQuery();
if (rowsAffected != 1)
{
throw new Exception("Error removing from books table. Rows affected: " + rowsAffected);
}
response.Success = true;
}
catch (Exception e)
{
response.ErrorText = e.Message;
}
}
return(response);
}
public static Response.BaseResponse UpdateVote(string userID, string bookISBN, int vote)
{
Response.BaseResponse response = new Response.BaseResponse();
string queryUpdateVote = "UPDATE `bx-book-ratings` SET `Book-Rating` = @Rating WHERE ISBN = @ISBN AND `User-ID` = @UserID";
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
MySqlCommand mySqlCommandUpdateVote = new MySqlCommand(queryUpdateVote, conn);
mySqlCommandUpdateVote.Parameters.AddWithValue("@UserID", userID);
mySqlCommandUpdateVote.Parameters.AddWithValue("@ISBN", bookISBN);
mySqlCommandUpdateVote.Parameters.AddWithValue("@Rating", vote);
try
{
conn.Open();
//Calculate weight
int rowsAffected = mySqlCommandUpdateVote.ExecuteNonQuery();
if (rowsAffected != 1)
{
throw new Exception("Error updating the vote. Rows affected: " + rowsAffected);
}
response.Success = true;
}
catch (Exception e)
{
response.ErrorText = e.Message;
}
return(response);
}
}
public static Response.BaseResponse AddVote(string userID, string bookISBN, int vote)
{
Response.BaseResponse response = new Response.BaseResponse();
string queryAddVote = "INSERT INTO `bx-book-ratings`(`User-ID`, `ISBN`, `Book-Rating`) VALUES (@UserID,@ISBN,@Rating)";
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
MySqlCommand mySqlCommandAddVote = new MySqlCommand(queryAddVote, conn);
mySqlCommandAddVote.Parameters.AddWithValue("@UserID", userID);
mySqlCommandAddVote.Parameters.AddWithValue("@ISBN", bookISBN);
mySqlCommandAddVote.Parameters.AddWithValue("@Rating", vote);
try
{
conn.Open();
//Calculate weight
int rowsAffected = mySqlCommandAddVote.ExecuteNonQuery();
if (rowsAffected != 1)
{
throw new Exception("Error inserting the vote. Rows affected: " + rowsAffected);
}
response.Success = true;
}
catch (Exception e)
{
response.ErrorText = e.Message;
}
return(response);
}
}
private void btnRegister_Click(object sender, RoutedEventArgs e)
{
if (String.IsNullOrWhiteSpace(txtUser.Text) || String.IsNullOrWhiteSpace(txtState.Text) || String.IsNullOrWhiteSpace(txtCountry.Text) ||
String.IsNullOrWhiteSpace(txtCity.Text) || String.IsNullOrWhiteSpace(txtAge.Text) || String.IsNullOrWhiteSpace(txtPass.Password))
{
MessageBox.Show("Please enter valid info.", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
return;
}
User user = new User()
{
Username = txtUser.Text,
Age = UInt32.Parse(txtAge.Text),
Location = String.Join(",", txtCity.Text, txtState.Text, txtCountry.Text),
IsAdmin = chkIsAdmin.IsChecked ?? false
};
Response.BaseResponse success = new Response.BaseResponse()
{
Success = true
};
FullBookVoteWindow fullBookVoteWindow = new FullBookVoteWindow(userVotes);
if (userVotes.Count < 10)
{
fullBookVoteWindow.ShowDialog();
}
if (fullBookVoteWindow.DialogResult == true || userVotes.Count >= 10)
{
var register = SqlHandler.AddUser(user, txtPass.Password);
if (register.Success)
{
foreach (var item in userVotes)
{
SqlHandler.AddVote(register.Content.ToString(), item.Content.ISBN, item.Rating);
}
}
else
{
success.Success = false;
success.ErrorText = register.ErrorText;
}
}
else
{
success.Success = false;
success.ErrorText = "Voting cancelled by user.";
}
if (success.Success)
{
MessageBox.Show("User has been added successfully", "Success", MessageBoxButton.OK, MessageBoxImage.Information);
this.Close();
}
else
{
MessageBox.Show("There was an error while registering!\n\n" + success.ErrorText, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
public static Response.BaseResponse AddBook(Book book)
{
Response.BaseResponse response = new Response.BaseResponse();
string queryAddNewBook = "INSERT INTO `bx-books`(`ISBN`,`Book-Title`,`Book-Author`,`Year-Of-Publication`,`Publisher`,`Image-URL-S`,`Image-URL-M`,`Image-URL-L`,`AddedDate`) VALUES (@ISBN,@BookTitle,@BookAuthor,@YearOfPublication,@Publisher,@ImageURI_S,@ImageURI_M,@ImageURI_L,@AddedDate)";
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
MySqlCommand mySqlCommandAddBook = new MySqlCommand(queryAddNewBook, conn);
mySqlCommandAddBook.Parameters.AddWithValue("@ISBN", book.ISBN);
mySqlCommandAddBook.Parameters.AddWithValue("@BookTitle", book.BookTitle);
mySqlCommandAddBook.Parameters.AddWithValue("@BookAuthor", book.BookAuthor);
mySqlCommandAddBook.Parameters.AddWithValue("@YearOfPublication", book.YearOfPublication);
mySqlCommandAddBook.Parameters.AddWithValue("@Publisher", book.Publisher);
mySqlCommandAddBook.Parameters.AddWithValue("@ImageURI_S", book.ImageURI_S);
mySqlCommandAddBook.Parameters.AddWithValue("@ImageURI_M", book.ImageURI_M);
mySqlCommandAddBook.Parameters.AddWithValue("@ImageURI_L", book.ImageURI_L);
var now = DateTime.Now;
mySqlCommandAddBook.Parameters.AddWithValue("@AddedDate", now.ToString("yyyy-MM-dd HH:mm:ss"));
try
{
conn.Open();
int rowsAffected = mySqlCommandAddBook.ExecuteNonQuery();
if (rowsAffected != 1)
{
throw new Exception("Error inserting into books table. Rows affected: " + rowsAffected);
}
response.Success = true;
}
catch (Exception e)
{
response.ErrorText = e.Message;
}
}
return(response);
}
internal static Response.BaseResponse UpdateAllWeights(BackgroundWorker bgw)
{
Response.BaseResponse response = new Response.BaseResponse();
string queryUpdate = "UPDATE `bx-books` SET `RatingWeight` = @Weight WHERE ISBN = @ISBN";
string queryGetSum = "SELECT SUM(`Book-Rating`), COUNT(`Book-Rating`) FROM `bx-book-ratings` WHERE ISBN = @ISBN";
var responseC = GetTotalAverageVotes();
double C = 0.0;
if (responseC.Success)
{
C = responseC.Content;
}
else
{
response.ErrorText += responseC.ErrorText + ".\n";
}
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
try
{
var allBooksResponse = GetAllBooks();
conn.Open();
if (allBooksResponse.Success)
{
int bookCount = allBooksResponse.Content.Count, index = 0;
foreach (Book item in allBooksResponse.Content)
{
MySqlCommand commandUpdate = new MySqlCommand(queryUpdate, conn);
commandUpdate.Parameters.AddWithValue("@ISBN", item.ISBN);
MySqlCommand commandGetSum = new MySqlCommand(queryGetSum, conn);
commandGetSum.Parameters.AddWithValue("@ISBN", item.ISBN);
int count = 0;
double sum = 0.0;
try
{
using (MySqlDataReader reader = commandGetSum.ExecuteReader())
{
if (reader.Read())
{
sum = GetSafeField <double>(reader, 0);
count = (int)GetSafeField <Int64>(reader, 1);
}
else
{
throw new Exception("Can't read count and sum values for book " + item.ISBN + ".");
}
}
}
catch (Exception e)
{
response.ErrorText += e.Message + " Error occured on update at " + item.ISBN + ".\n";
continue;
}
double weight;
if (count == 0)
{
weight = 0;
}
else
{
double avg = sum / count;
weight = CommonLibrary.GetWeightRate(count, avg, C, CommonLibrary.MinBookToCountVote);
}
commandUpdate.Parameters.AddWithValue("@Weight", weight);
int rowsAffected = commandUpdate.ExecuteNonQuery();
if (rowsAffected != 1)
{
throw new Exception("Error weight update. Rows affected: " + rowsAffected);
}
bgw.ReportProgress((100 * index++) / bookCount);
}
}
else
{
throw new Exception(allBooksResponse.ErrorText);
}
response.Success = true;
}
catch (Exception e)
{
response.ErrorText = e.Message;
}
}
return(response);
}
