/// <summary>
/// Gets the item identifier.
/// </summary>
/// <param name="itemTitle">The item title.</param>
/// <param name="itemType">Type of the item.</param>
/// <returns></returns>
public static int GetItemId(string itemTitle, string itemType)
{
using ( SQLiteConnection conn = new SQLiteConnection(Db.TableLocation) )
{
using ( SQLiteCommand cmd = new SQLiteCommand() )
{
cmd.Connection = conn;
conn.Open();
SqLiteHelper sh = new SqLiteHelper(cmd);
// Query table for item name
DataTable mediaData =
sh.Select(
"SELECT item.id FROM item " + "JOIN type ON type.id = item.typeId " +
"WHERE item.title = @title AND type.type = @type;",
new[]
{
new SQLiteParameter("@title", itemTitle),
new SQLiteParameter("@type", itemType)
});
// Return 0 if there are no rows from query
if ( mediaData.Rows.Count == 0 ) return 0;
// Otherwise return the Items ID
return Convert.ToInt32(mediaData.Rows[0].ItemArray.GetValue(0));
}
}
}
/// <summary>
/// Gets the theme from the users settings table
/// </summary>
public static void GetTheme()
{
// Get the users preferred theme from the database
using ( SQLiteConnection conn = new SQLiteConnection(Db.TableLocation) )
{
using ( SQLiteCommand cmd = new SQLiteCommand() )
{
// Setup database connection
cmd.Connection = conn;
conn.Open();
SqLiteHelper sh = new SqLiteHelper(cmd);
// Get users settings from database
DataTable dt = sh.Select("SELECT * FROM settings WHERE id = @userid;",
new[] { new SQLiteParameter("@userid", CurrentUserId) });
if ( dt.Rows.Count != 0 )
{
// Get users theme
string themeAccent = dt.Rows[0]["themeAccent"].ToString();
string themeBg = dt.Rows[0]["themeBG"].ToString();
// Apply theme
SetTheme(themeAccent, themeBg);
}
}
}
}
/// <summary>
/// Gets the reviews and stores them in the Reviews property
/// </summary>
/// <param name="itemId">The item identifier.</param>
private void GetReviews(int itemId)
{
using ( SQLiteConnection conn = new SQLiteConnection(Db.TableLocation) )
{
using ( SQLiteCommand cmd = new SQLiteCommand() )
{
// Connect to database
cmd.Connection = conn;
conn.Open();
SqLiteHelper sh = new SqLiteHelper(cmd);
// Store the database contents to the UserList datatable
Reviews =
sh.Select(
"SELECT review.id, user.username, progress.rating, review.date, review.review " +
"FROM review " +
"JOIN user ON user.ID = review.userId " +
"JOIN progress ON progress.UserId = review.userid AND progress.ItemId = review.itemid " +
"WHERE review.review <> '' AND review.itemId = @itemid ;",
new[] { new SQLiteParameter("@itemid", itemId) });
// Set property to true if item has reviews
int noOfReviews = Reviews.Rows.Count;
HasReviews = noOfReviews > 0;
}
}
}
/// <summary>
/// Gets the font from the users settings table
/// </summary>
public static void GetFont()
{
using ( SQLiteConnection conn = new SQLiteConnection(Db.TableLocation) )
{
using ( SQLiteCommand cmd = new SQLiteCommand() )
{
// Setup database connection
cmd.Connection = conn;
conn.Open();
SqLiteHelper sh = new SqLiteHelper(cmd);
// Get users settings from database
DataTable dt = sh.Select("SELECT * FROM settings WHERE id = @userid;",
new[] { new SQLiteParameter("@userid", CurrentUserId) });
if ( dt.Rows.Count == 0 ) return;
// Get users theme
int fontSize = Convert.ToInt32(dt.Rows[0]["fontSize"]);
string fontType = dt.Rows[0]["fontType"].ToString();
UseFont(fontSize, fontType);
}
}
}
/// <summary>
/// Try to login to the users account
/// </summary>
private void TryLogin()
{
string inputUsername = TxtUsername.Text;
string inputPassword = TxtPassword.Password;
bool loginSuccess = false;
using ( SQLiteConnection conn = new SQLiteConnection(Db.TableLocation) )
{
using ( SQLiteCommand cmd = new SQLiteCommand() )
{
// Connect to database
cmd.Connection = conn;
conn.Open();
SqLiteHelper sh = new SqLiteHelper(cmd);
// Query database for user details
DataTable dt =
sh.Select(
"SELECT * FROM user " + "WHERE UPPER(username) = UPPER(@user) " +
"AND password = @pass;",
new[]
{
new SQLiteParameter("@user", inputUsername),
new SQLiteParameter("@pass", inputPassword)
});
// flag login as success and store user ID
if ( dt.Rows.Count != 0 )
{
loginSuccess = true;
User.CurrentUserId = Convert.ToInt32(dt.Rows[0]["id"]);
}
}
}
// If login successful start the MainWindow and close this
if ( loginSuccess )
{
MainWindow main = new MainWindow();
main.Show();
Close();
}
else Utilities.ShowMessage("Error", "Username or password is incorrect");
}
/// <summary>
/// Adds to database.
/// </summary>
/// <returns></returns>
public static int AddToDatabase()
{
using ( SQLiteConnection conn = new SQLiteConnection(Db.TableLocation) )
{
using ( SQLiteCommand cmd = new SQLiteCommand() )
{
cmd.Connection = conn;
conn.Open();
SqLiteHelper sh = new SqLiteHelper(cmd);
// Look for item in database and return its ID
DataTable dt =
sh.Select(
"SELECT item.id, item.title, type.type " + "FROM item " +
"JOIN Type ON item.typeId = Type.id " +
"WHERE UPPER(title) = UPPER(@title) AND " + "type = @type ;",
new[]
{
new SQLiteParameter("@title", SearchItem.Media.Title),
new SQLiteParameter("@type", SearchItem.Media.MediaType)
});
if ( dt.Rows.Count == 0 )
{
// Item doesn't exist in DB - Add it and get ID
Dictionary <string, object> newItemData = GetItemDetails();
sh.Insert("item", newItemData);
return Convert.ToInt32(sh.LastInsertRowId()); // ID of last insert
}
// Item exists - Get ID
int itemId = Convert.ToInt32(dt.Rows[0]["id"]);
Dictionary <string, object> newData = GetItemDetails();
sh.Update("item", newData, "id", itemId);
return itemId;
}
}
}
/// <summary>
/// Determines whether the specified item identifier has item.
/// </summary>
/// <param name="itemId">The item identifier.</param>
/// <returns></returns>
public static bool HasItem(int itemId)
{
using ( SQLiteConnection conn = new SQLiteConnection(Db.TableLocation) )
{
using ( SQLiteCommand cmd = new SQLiteCommand() )
{
cmd.Connection = conn;
conn.Open();
SqLiteHelper sh = new SqLiteHelper(cmd);
// Query table for item name
DataTable itemData =
sh.Select(
"SELECT item.id, item.title " + "FROM item " +
"JOIN progress ON item.id = progress.itemId " +
"WHERE item.id = @item AND progress.userId = @user;",
new[]
{
new SQLiteParameter("@item", itemId),
new SQLiteParameter("@user", User.CurrentUserId)
});
// Return false if no items queried, else it's true
if ( itemData.Rows.Count == 0 ) return false;
return true;
}
}
}
/// <summary>
/// Show the users media stats
/// </summary>
/// <param name="userid">The userid.</param>
private void ShowStats(int userid)
{
using ( SQLiteConnection conn = new SQLiteConnection(Db.TableLocation) )
{
using ( SQLiteCommand cmd = new SQLiteCommand() )
{
// Setup database
cmd.Connection = conn;
conn.Open();
SqLiteHelper sh = new SqLiteHelper(cmd);
// Query database for user details
DataTable dt =
sh.Select(
"SELECT type.type, ROUND(SUM(item.length), 2) 'Total Length', SUM(progress.Progress) 'Number Watched', " +
" ROUND(SUM(item.length * progress.Progress)/60.0/24.0, 2) 'Days Total' " +
"FROM progress " + "JOIN item ON progress.itemId = item.id " +
"JOIN type ON item.typeId = type.id " +
"WHERE progress.userId = @user AND progress.statusId IN (0, 1, 2) " +
"GROUP BY item.typeId", new[] { new SQLiteParameter("@user", userid) });
// If data is returned, display it
if ( dt.Rows.Count == 0 ) return;
float movieTime = 0, movieNumber = 0;
float seriesTime = 0, seriesNumber = 0;
float bookTime = 0, bookNumber = 0;
for ( int i = 0; i < dt.Rows.Count; i++ )
{
string type = dt.Rows[i].ItemArray[0].ToString();
// To parse the data without it being rounded
NumberFormatInfo decimalpoint = CultureInfo.InvariantCulture.NumberFormat;
// Parse the contents to appropriate variables
switch ( type )
{
case "Movie":
movieTime = float.Parse(dt.Rows[i].ItemArray[3].ToString(),
decimalpoint);
movieNumber = float.Parse(
dt.Rows[i].ItemArray[2].ToString(), decimalpoint);
break;
case "Series":
seriesTime = float.Parse(
dt.Rows[i].ItemArray[3].ToString(), decimalpoint);
seriesNumber =
float.Parse(dt.Rows[i].ItemArray[2].ToString(),
decimalpoint);
break;
case "Book":
bookTime = float.Parse(dt.Rows[i].ItemArray[3].ToString(),
decimalpoint);
bookNumber = float.Parse(
dt.Rows[i].ItemArray[2].ToString(), decimalpoint);
break;
}
}
// Display the contents
LblStatMovie.Content =
String.Format("{0} Movies Added, spending {1} days in total.", movieNumber,
movieTime);
LblStatSeries.Content =
String.Format("{0} Episodes Added, spending {1} days in total.",
seriesNumber, seriesTime);
LblStatBook.Content = String.Format("{0} Books Added, at {1} pages in total.",
bookNumber, bookTime);
}
}
}
/// <summary>
/// Get the users data and display it
/// </summary>
/// <param name="userid">The userid.</param>
private void ShowUser(int userid)
{
using ( SQLiteConnection conn = new SQLiteConnection(Db.TableLocation) )
{
using ( SQLiteCommand cmd = new SQLiteCommand() )
{
// Setup database
cmd.Connection = conn;
conn.Open();
SqLiteHelper sh = new SqLiteHelper(cmd);
// Query database for user details
DataTable dt = sh.Select("SELECT username, registerdate FROM user WHERE id = @user",
new[] { new SQLiteParameter("@user", userid) });
// If data is returned, display it
if ( dt.Rows.Count == 0 ) return;
LblUsername.Content = dt.Rows[0].ItemArray[0].ToString();
LblDateRegistered.Content = dt.Rows[0].ItemArray[1].ToString();
}
}
}
/// <summary>
/// Refreshes or set the table.
/// </summary>
public void RefreshTable()
{
using ( SQLiteConnection conn = new SQLiteConnection(Db.TableLocation) )
{
using ( SQLiteCommand cmd = new SQLiteCommand() )
{
// Connect to database
cmd.Connection = conn;
conn.Open();
SqLiteHelper sh = new SqLiteHelper(cmd);
// Store the database contents to the DtMovie DataTable
MediaDataTable =
sh.Select(
"select item.id, item.title, item.year, progress.rating, progress.progress 'itemprogress', status.id as 'statusid' " +
"from progress " + "join user on progress.userId = user.id " +
"JOIN item on progress.itemId = item.id " +
"JOIN status on progress.statusId = status.id " +
"JOIN type on item.typeId = type.id " +
"WHERE user.id = @user AND type.type = @type AND status.status = @status " +
"ORDER BY item.title ASC;",
new[]
{
new SQLiteParameter("@user", UserId),
new SQLiteParameter("@type", Type),
new SQLiteParameter("@status", Status)
});
DataGridItem.ItemsSource = MediaDataTable.DefaultView;
}
}
}
/// <summary>
/// Queries the database for list of users and stores it in the UserList property
/// </summary>
public static void GetUsers()
{
using ( SQLiteConnection conn = new SQLiteConnection(Db.TableLocation) )
{
using ( SQLiteCommand cmd = new SQLiteCommand() )
{
// Connect to database
cmd.Connection = conn;
conn.Open();
SqLiteHelper sh = new SqLiteHelper(cmd);
// Store the database contents to the UserList datatable
UserList =
sh.Select(
"SELECT id, username, registerdate " + "FROM user " +
"WHERE id != @currentuser;",
new[] { new SQLiteParameter("@currentuser", CurrentUserId) });
conn.Close();
}
}
}
/// <summary>
/// Method called when displaying a specified item
/// </summary>
public void GetLocalItemData()
{
// Get specified data from database
using ( SQLiteConnection conn = new SQLiteConnection(Db.TableLocation) )
{
using ( SQLiteCommand cmd = new SQLiteCommand() )
{
// Connect to database
cmd.Connection = conn;
conn.Open();
SqLiteHelper sh = new SqLiteHelper(cmd);
// Store the database contents to the DtMovie DataTable
DataTable itemData =
sh.Select(
"SELECT item.id, item.title, item.year, item.imdbrating, item.length, item.synopsis, " +
"item.posterUrl, type.type, " +
"item.trailerurl, item.agerating, item.genre, item.author " +
"FROM item " + "JOIN type ON item.typeId = type.id " +
"WHERE item.id = @item", new[] { new SQLiteParameter("@item", ItemId) });
SearchItem.Media = new Item(itemData);
// Show content and try to get trailer and reviews
ShowItem();
ShowTrailer();
ShowReviews();
BtnReview.Visibility = Visibility.Hidden;
if ( ManipulateItem.HasItem(ItemId) ) BtnReview.Visibility = Visibility.Visible;
}
}
}
/// <summary>
/// Saves media item details to database
/// </summary>
private void SaveMedia()
{
using ( SQLiteConnection conn = new SQLiteConnection(Db.TableLocation) )
{
using ( SQLiteCommand cmd = new SQLiteCommand() )
{
// Connect to database
cmd.Connection = conn;
conn.Open();
SqLiteHelper sh = new SqLiteHelper(cmd);
// Save item to database
ItemId = ManipulateItem.AddToDatabase();
#region Add item to list
// Check if user already has item
DataTable dt2 =
sh.Select(
"SELECT * FROM progress " +
"WHERE itemid = @itemId AND userid = @userId;",
new[]
{
new SQLiteParameter("@itemid", ItemId),
new SQLiteParameter("@userId", User.CurrentUserId)
});
if ( dt2.Rows.Count == 0 )
{
// User doesn't have item - Add it
AddToUsersList(ItemId);
// Refresh reviews
ShowReviews();
// Refresh Tables
( (MainWindow) Application.Current.MainWindow ).RefreshContent();
Utilities.ShowMessage("Complete", "Finished adding item to list");
}
else Utilities.ShowMessage("Woops", "You already have this item");
#endregion Add item to list
}
}
}
/// <summary>
/// Gets the top 10 ranked items from the database
/// </summary>
private void GetPopularItems()
{
using ( SQLiteConnection conn = new SQLiteConnection(Db.TableLocation) )
{
using ( SQLiteCommand cmd = new SQLiteCommand() )
{
// Connect to database
cmd.Connection = conn;
conn.Open();
SqLiteHelper sh = new SqLiteHelper(cmd);
// Store the database contents to the UserList datatable
PopularItemsDataTable =
sh.Select("SELECT item.id, item.title, item.year, item.genre, type.type " +
"FROM item " + "JOIN type ON type.id = item.typeId " +
"ORDER BY item.imdbRating DESC " + "LIMIT 10");
// Set item source so that DataGrid updates
DataGridPopularItems.ItemsSource = PopularItemsDataTable.DefaultView;
}
}
}
/// <summary>
/// Gets the recommendations.
/// </summary>
private void GetRecommendations()
{
using ( SQLiteConnection conn = new SQLiteConnection(Db.TableLocation) )
{
using ( SQLiteCommand cmd = new SQLiteCommand() )
{
// Connect to database
cmd.Connection = conn;
conn.Open();
SqLiteHelper sh = new SqLiteHelper(cmd);
// Store the database contents to the UserList datatable
RecommendationsDataTable =
sh.Select(
"SELECT recommendation.itemId, item.title, item.year, type.type, recommendation.date, user.username 'Sent by' " +
"FROM recommendation " + "JOIN user ON recommendation.fromId = user.id " +
"JOIN item ON recommendation.itemid = item.id " +
"JOIN type ON item.typeId = type.id " + "WHERE userid = @userid",
new[] { new SQLiteParameter("@userid", User.CurrentUserId) });
DataGridRecs.ItemsSource = RecommendationsDataTable.DefaultView;
// Set property to true if item has reviews
int noOfRecommendations = RecommendationsDataTable.Rows.Count;
HasRecommendations = noOfRecommendations > 0;
// If no reviews, hide table and show message
if ( !HasRecommendations )
{
DataGridRecs.Visibility = Visibility.Collapsed;
LblNoRecs.Visibility = Visibility.Visible;
}
}
}
}
/// <summary>
/// Try to register a users account
/// </summary>
private void TryRegister()
{
using ( SQLiteConnection conn = new SQLiteConnection(Db.TableLocation) )
{
using ( SQLiteCommand cmd = new SQLiteCommand() )
{
// Connect to database
cmd.Connection = conn;
conn.Open();
SqLiteHelper sh = new SqLiteHelper(cmd);
string inputUsername = TxtRegisterUsername.Text;
string inputPassword = TxtRegisterPassword1.Password;
string inputPasswordCheck = TxtRegisterPassword2.Password;
// Check that both passwords match and input isn't empty
if ( inputPassword != inputPasswordCheck || inputPassword == "" ||
inputPasswordCheck == "" || inputUsername == "" )
{
// if no match, or empty, stop
Utilities.ShowMessage("Error", "Passwords do not match!");
return;
}
// Check if username already exists
DataTable dataUserConfirm =
sh.Select("SELECT * FROM User WHERE UPPER(username) = UPPER(@user)",
new[] { new SQLiteParameter("@user", inputUsername) });
if ( dataUserConfirm.Rows.Count > 0 )
// A row getting returned would indicate the user exists
{
Utilities.ShowMessage("Error", "Username has been taken.");
return;
}
// Insert new user into table
Dictionary <string, object> userDict = new Dictionary <string, object>();
userDict["username"] = inputUsername;
userDict["password"] = inputPassword;
sh.Insert("user", userDict);
sh.Execute("INSERT INTO settings DEFAULT VALUES;");
Utilities.ShowMessage("Success", "Account created!");
}
}
}
