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