public void PersistUserBook(UserBookExperience userBook)
{
string connectionString = string.Format("data source={0}", Path.Combine(ConfigurationManager.AppSettings["DBPath"], FILENAME));
using (SQLiteConnection myConnection = new SQLiteConnection(connectionString))
{
myConnection.Open();
using (SQLiteCommand myCommand = myConnection.CreateCommand())
{
if (userBook.Identifier == 0)
{
//INSERT
myCommand.CommandText = string.Format("INSERT INTO user_books(book, user, read, rating, date_read) values ({0},{1},{2},{3},'{4}')", userBook.BookIdentifier, userBook.UserId, Convert.ToInt32(userBook.Read), userBook.Rating, userBook.DateRead.ToString("yyyy-MM-dd hh:mm:ss", CultureInfo.InvariantCulture));
myCommand.ExecuteNonQuery();
myCommand.CommandText = "SELECT MAX(id) FROM user_books";
int id = Convert.ToInt32((long)myCommand.ExecuteScalar());
userBook.Identifier = id;
}
else
{
//UPDATE
myCommand.CommandText = string.Format("UPDATE user_books SET read={0}, rating={1}, date_read='{2}' WHERE id={3}", Convert.ToInt32(userBook.Read), userBook.Rating, userBook.DateRead.ToString("yyyy-MM-dd hh:mm:ss", CultureInfo.InvariantCulture), userBook.Identifier);
myCommand.ExecuteNonQuery();
}
}
}
}
private void UpdateBookInSession(Book theBook, UserBookExperience userXp)
{
Book theBookInSession = ((List <Book>)Session["Books"]).SingleOrDefault(x => x.ID == theBook.ID);
theBookInSession.Experiences.RemoveAll(x => x.UserId == userXp.UserId);
theBookInSession.Experiences.Add(userXp);
}
public static UserBookExperience GetUserBookFromBook(User theUser, Book theBook, List <UserBookExperience> allUserBooks)
{
UserBookExperience defaultResult = new UserBookExperience(theBook.ID, theUser.Identifier);
UserBookExperience theUserBook = allUserBooks.SingleOrDefault(x => x.BookIdentifier == theBook.ID && x.UserId == theUser.Identifier);
return(theUserBook ?? defaultResult);
}
public void SetBook(Book theBook)
{
CurrentBook = theBook;
//titre
mTitre.Text = theBook.Title;
//auteurs
mAuthors.Text = DisplayList(theBook.Authors, " & ");
//Tags
mTags.Text = DisplayList(theBook.Tags, ", ");
if (!string.IsNullOrEmpty(theBook.Serie))
{
mSerie.Text = theBook.Serie + " [" + theBook.SerieIndex + "]";
}
else
{
mSerie.Text = string.Empty;
}
//Read
mPath.Text = theBook.FullBookPath;
mSummary.Text = CleanText(theBook.Summary);
//mSummary.Text = theBook.Summary;
mLink.NavigateUrl = "ebooks/" + theBook.URL;
mImage.ImageUrl = "ebooks/" + theBook.CoverURL;
mAdded.Text = theBook.DateAdded.ToString("dd MMM yyyy");
mPages.Text = theBook.Pages.ToString();
UserBookExperience xp = theBook.Experiences.SingleOrDefault(x => x.UserId == CurrentUser.Identifier);
if (xp != null)
{
mEnvie.Text = xp.Rating.ToString();
}
else
{
mEnvie.Text = "0";
}
mRead.Text = theBook.HasBeenReadBy(CurrentUser) ? xp.DateRead.ToString("dd MMM yyyy") : "Non";
mbtnMarkAsRead.Text = theBook.HasBeenReadBy(CurrentUser) ? "Marquer" + Environment.NewLine + "comme non-lu" : "Marquer" + Environment.NewLine + "comme lu";
mbtnMarkAsRead.Visible = !CurrentUser.IsGuest;
Dictionary <string, string> dico = (Dictionary <string, string>)HttpContext.Current.Session["DicoLanguages"];
mLanguage.Text = dico.ContainsKey(theBook.Language) ? dico[theBook.Language] : theBook.Language;
}
protected void mbtnEnviePlus_Click(object sender, EventArgs e)
{
IBookManager bm = BookManagerFactory.GetBookManager();
UserBookExperience ube = CurrentBook.Experiences.SingleOrDefault(x => x.UserId == CurrentUser.Identifier) ?? new UserBookExperience(CurrentBook.ID, CurrentUser.Identifier);
ube.Rating = Math.Min(5, ube.Rating + 1);
if (ube.Identifier == 0)
{
CurrentBook.Experiences.Add(ube);
}
bm.PersistUserBook(ube);
UpdateBookInSession(CurrentBook, ube);
SetBook(CurrentBook);
}
protected void mbtnMarkAsRead_Click(object sender, EventArgs e)
{
IBookManager bm = BookManagerFactory.GetBookManager();
UserBookExperience ube = CurrentBook.Experiences.SingleOrDefault(x => x.UserId == CurrentUser.Identifier) ?? new UserBookExperience(CurrentBook.ID, CurrentUser.Identifier);
ube.Read = !ube.Read;
ube.DateRead = ube.Read ? DateTime.Now : DateTime.MinValue;
if (ube.Identifier == 0)
{
CurrentBook.Experiences.Add(ube);
}
bm.PersistUserBook(ube);
UpdateBookInSession(CurrentBook, ube);
SetBook(CurrentBook);
}
public List <UserBookExperience> GetAllUserBookExperiences()
{
List <UserBookExperience> result = new List <UserBookExperience>();
string connectionString = string.Format("data source={0}", Path.Combine(ConfigurationManager.AppSettings["DBPath"], FILENAME));
using (SQLiteConnection myConnection = new SQLiteConnection(connectionString))
{
myConnection.Open();
using (SQLiteCommand myCommand = myConnection.CreateCommand())
{
myCommand.CommandText = "SELECT id, book, user, read, rating, datetime(date_read) as date_read FROM user_books";
var reader = myCommand.ExecuteReader();
while (reader.Read())
{
int bookId = reader.GetInt32(reader.GetOrdinal("book"));
int userId = reader.GetInt32(reader.GetOrdinal("user"));
UserBookExperience ub = new UserBookExperience(bookId, userId);
ub.Identifier = reader.GetInt32(reader.GetOrdinal("id"));
ub.Read = Convert.ToBoolean(reader[reader.GetOrdinal("read")]);
ub.Rating = reader.GetInt32(reader.GetOrdinal("rating"));
try
{
ub.DateRead = DateTime.ParseExact(reader[reader.GetOrdinal("date_read")].ToString(), "yyyy-MM-dd hh:mm:ss", CultureInfo.InvariantCulture);
}
catch (FormatException)
{
ub.DateRead = DateTime.MinValue;
}
result.Add(ub);
}
}
}
return(result);
}
public void PersistUserBook(UserBookExperience userBook)
{
throw new NotImplementedException();
}
public List <Book> GetAllBooks()
{
CultureInfo provider = CultureInfo.InvariantCulture;
List <Book> bookList = new List <Book>();
string connectionString = string.Format("data source={0}", Path.Combine(ConfigurationManager.AppSettings["DBPath"], FILENAME));
string readColumnName = "custom_column_" + ConfigurationManager.AppSettings["ReadCustomColumnNumber"];
string pagesColumnName = "custom_column_" + ConfigurationManager.AppSettings["PagesCustomColumnNumber"];
using (SQLiteConnection myConnection = new SQLiteConnection(connectionString))
{
myConnection.Open();
using (SQLiteCommand myCommand = myConnection.CreateCommand())
{
//myCommand.CommandText = "select books.id,books.timestamp, books.title, books.series_index, series.name as serie, authors.name as author, books.path , tags.name as tag, ifnull(" + readColumnName + ".value,0) as read,ifnull(" + pagesColumnName + ".value,0) as pages, comments.text from books left join books_series_link on books_series_link.book = books.id left join series on books_series_link.series = series.id inner join books_authors_link on books_authors_link.book = books.id inner join authors on authors.id = books_authors_link.author inner join books_tags_link on books_tags_link.book = books.id inner join tags on tags.id = books_tags_link.tag left outer join " + readColumnName + " on " + readColumnName + ".book = books.id left join comments on books.id = comments.book left outer join " + pagesColumnName + " on " + pagesColumnName + ".book = books.id";
myCommand.CommandText = "select books.id,books.timestamp, books.title, books.series_index, series.name as serie, authors.name as author, books.path , tags.name as tag, ifnull(" + pagesColumnName + ".value,0) as pages, comments.text, ifnull(languages.lang_code,'fra') as language from books left join books_series_link on books_series_link.book = books.id left join series on books_series_link.series = series.id inner join books_authors_link on books_authors_link.book = books.id inner join authors on authors.id = books_authors_link.author left join books_tags_link on books_tags_link.book = books.id left join tags on tags.id = books_tags_link.tag left join comments on books.id = comments.book left outer join " + pagesColumnName + " on " + pagesColumnName + ".book = books.id INNER JOIN books_languages_link ON books_languages_link.book = books.id INNER JOIN languages on books_languages_link.lang_code = languages.id";
var reader = myCommand.ExecuteReader();
while (reader.Read())
{
int id = reader.GetInt32(reader.GetOrdinal("id"));
Book existingBook = bookList.SingleOrDefault(x => x.ID == id);
if (existingBook == null)
{
Book theBook = new Book();
theBook.Title = reader[reader.GetOrdinal("title")] as string ?? string.Empty;
theBook.ID = reader.GetInt32(reader.GetOrdinal("id"));
theBook.Serie = reader[reader.GetOrdinal("serie")] as string ?? string.Empty;
theBook.SerieIndex = (int)(reader[reader.GetOrdinal("series_index")] as double? ?? 0);
theBook.Path = (reader[reader.GetOrdinal("path")] as string ?? string.Empty) /*.Replace('/','\\')*/;
theBook.Authors.Add(reader[reader.GetOrdinal("author")] as string ?? string.Empty);
theBook.Tags.Add(reader[reader.GetOrdinal("tag")] as string ?? string.Empty);
//theBook.Read = Convert.ToBoolean(reader[reader.GetOrdinal("read")]);
theBook.Summary = reader[reader.GetOrdinal("text")] as string ?? string.Empty;
theBook.DateAdded = (DateTime)reader[reader.GetOrdinal("timestamp")]; //
theBook.Pages = reader.GetInt32(reader.GetOrdinal("pages"));
theBook.Language = reader[reader.GetOrdinal("language")] as string ?? string.Empty;
bookList.Add(theBook);
}
else
{
string author = reader[reader.GetOrdinal("author")] as string ?? string.Empty;
if (!existingBook.Authors.Contains(author))
{
existingBook.Authors.Add(author);
}
string tag = reader[reader.GetOrdinal("tag")] as string ?? string.Empty;
if (!existingBook.Tags.Contains(tag))
{
existingBook.Tags.Add(tag);
}
}
}
}
using (SQLiteCommand myCommand = myConnection.CreateCommand())
{
myCommand.CommandText = "SELECT id, book, user, read, rating, datetime(date_read) as date_read FROM user_books";
var reader = myCommand.ExecuteReader();
while (reader.Read())
{
int bookId = reader.GetInt32(reader.GetOrdinal("book"));
int userId = reader.GetInt32(reader.GetOrdinal("user"));
UserBookExperience ub = new UserBookExperience(bookId, userId);
ub.Identifier = reader.GetInt32(reader.GetOrdinal("id"));
ub.Read = Convert.ToBoolean(reader[reader.GetOrdinal("read")]);
ub.Rating = reader.GetInt32(reader.GetOrdinal("rating"));
try
{
ub.DateRead = DateTime.ParseExact(reader[reader.GetOrdinal("date_read")].ToString(), "yyyy-MM-dd hh:mm:ss", CultureInfo.InvariantCulture);
}
catch (FormatException)
{
ub.DateRead = DateTime.MinValue;
}
bookList.Single(x => x.ID == ub.BookIdentifier).Experiences.Add(ub);
}
}
}
return(bookList);
}
