public List<User> GetUsers()
{
List<User> users = null;
var context = new CityLibraryEntities();
try
{
users = (
from u in context.Users
orderby u.LastName
select u
).ToList();
}
catch (Exception ex)
{
Log.Error(ex.Message, ex);
throw;
}
finally
{
context.Dispose();
}
return users;
}
public string DeleteUser(int id)
{
string err = null;
var context = new CityLibraryEntities();
try
{
var currentUser = context.Users.SingleOrDefault(u => u.UserID == id);
if (currentUser != null)
{
currentUser.IsActive = false;
context.SaveChanges();
}
}
catch (Exception ex)
{
Log.Error(ex.Message, ex);
err = ex.Message;
}
return err;
}
public string CreateBook(Book book)
{
string err = null;
var context = new CityLibraryEntities();
try
{
context.Books.Add(book);
context.SaveChanges();
}
catch (Exception ex)
{
Log.Error(ex.Message, ex);
err = ex.Message;
}
return err;
}
public string CreateUser(User user)
{
string err = null;
var context = new CityLibraryEntities();
try
{
// throw new Exception("aaaaaaaaaaaaa");
context.Users.Add(user);
context.SaveChanges();
}
catch (Exception ex)
{
Log.Error(ex.Message, ex);
err = ex.Message;
}
return err;
}
// do ulepszenia
public bool UserHasBooks(int id)
{
List<Borrow> notReturnedBorrows = null;
var context = new CityLibraryEntities();
try
{
notReturnedBorrows = (
from b in context.Borrows
where ( b.UserId == id && b.IsReturned == false)
orderby b.BookId
select b
).ToList();
}
catch (Exception ex)
{
Log.Error(ex.Message, ex);
return false;
}
finally
{
context.Dispose();
}
if (notReturnedBorrows.Count > 0)
{
return true;
}
else
{
return false;
}
}
public string UpdateUser(User user)
{
string err = null;
var context = new CityLibraryEntities();
try
{
var currentUser = context.Users.SingleOrDefault(u => u.UserID == user.UserID);
if (currentUser != null)
{
currentUser.FirstName = user.FirstName;
currentUser.LastName = user.LastName;
context.SaveChanges();
}
}
catch (Exception ex)
{
Log.Error(ex.Message, ex);
err = ex.Message;
}
return err;
}
public string UpdateBook(Book book)
{
string err = null;
var context = new CityLibraryEntities();
try
{
var currentBook = context.Books.SingleOrDefault(b => b.BookId == book.BookId);
AutoMapper.Mapper.CreateMap<Book, Book>();
if (currentBook != null)
{
currentBook = AutoMapper.Mapper.Map<Book, Book>(book);
context.SaveChanges();
}
}
catch (Exception ex)
{
Log.Error(ex.Message, ex);
err = ex.Message;
}
return err;
}
public List<User> GetUsers()
{
List<User> users = null;
var context = new CityLibraryEntities();
try
{
var query = (
from u in context.Users
where u.IsActive
orderby u.LastName
select u
).Include(u => u.Borrows);
//var sql = ((System.Data.Entity.Infrastructure.DbQuery<User>)query).ToString();
users = query.ToList<User>();
}
catch (Exception ex)
{
Log.Error(ex.Message, ex);
throw;
}
finally
{
context.Dispose();
}
return users;
}
public List<Borrow> GetBorrows(SearchCriteria[] searchCriteria, SortOrder[] sort, int page, int pageSize, out int count)
{
var context = new CityLibraryEntities();
List<Borrow> borrows = null;
try
{
count = context.Borrows.Count();
// Linq
IQueryable<Borrow> query = context.Borrows;
// Search
if (searchCriteria != null && searchCriteria.Length > 0)
{
foreach (var sc in searchCriteria)
{
//query = query.Where(b => b.Author == sc.Value)
}
}
query = query.Where(b => b.IsReturned == false);
// Sorting
if (sort != null && sort.Length > 0)
{
//foreach (SortOrder sc in sort)
//{
//}
}
else
{
query = query.OrderByDescending(b => b.BorrowId);
}
query = query.Include(u => u.Book).Include(u => u.User);
// Pagging
query = query.Skip(page * pageSize).Take(pageSize);
// Retrieve from database
borrows = query.ToList();
}
catch (Exception ex)
{
Log.Error(ex.Message, ex);
throw;
}
finally
{
context.Dispose();
}
return borrows;
}
public List<global::CityLibrary.Core.Dao.DictBookGenre> GetGenres()
{
var context = new CityLibraryEntities();
List<DictBookGenre> genres = null;
try
{
// Linq
IQueryable<DictBookGenre> query = context.DictBookGenres;
// Sorting
query = query.OrderBy(b => b.BookGenreId);
// Pagging
//query = query.Skip(page*pageSize).Take(page);
// Retrieve from database
genres = query.ToList();
}
catch (Exception ex)
{
Log.Error(ex.Message, ex);
throw;
}
finally
{
context.Dispose();
}
return genres;
}
public List<DictBookGenre> GetGenreList()
{
var context = new CityLibraryEntities();
try
{
return context.DictBookGenres.ToList();
}
catch (Exception ex)
{
Log.Error(ex.Message, ex);
throw;
}
finally
{
context.Dispose();
}
}
public List<Book> GetBooks(SearchCriteria[] searchCriterias, SortOrder[] sortOrders, int page, int pageSize, out int count)
{
var context = new CityLibraryEntities();
List<Book> books = null;
try
{
count = context.Books.Count();
// Linq
IQueryable<Book> query = context.Books;
// Search
if (searchCriterias != null && searchCriterias.Length > 0)
{
foreach (var sc in searchCriterias)
{
//query = query.Where(b => b.Author == sc.Value)
}
}
// Sorting
if (sortOrders != null)
{
// logika
}
else
{
query = query.OrderBy(b => b.BookId);
}
// Pagging
//query = query.Skip(page*pageSize).Take(page);
// Retrieve from database
books = query.ToList();
}
catch (Exception ex)
{
Log.Error(ex.Message, ex);
throw;
}
finally
{
context.Dispose();
}
return books;
}
public bool RemoveBorrow(int borrowId)
{
var context = new CityLibraryEntities();
try
{
var borrow = context.Borrows.SingleOrDefault(b => b.BorrowId == borrowId);
if (borrow != null)
{
borrow.IsReturned = true;
context.SaveChanges();
}
return true;
}
catch (Exception ex)
{
Log.Error(ex.Message, ex);
return false;
}
finally
{
context.Dispose();
}
}
public bool OrderBooks(int userId, int[] booksToBorrow)
{
var context = new CityLibraryEntities();
try
{
foreach (var bookId in booksToBorrow)
{
var borrow = new Borrow
{
FromDate = DateTime.Today,
ToDate = DateTime.Today.AddDays(7),
IsReturned = false,
BookId = bookId,
UserId = userId
};
context.Borrows.Add(borrow);
}
context.SaveChanges();
return true;
}
catch (Exception ex)
{
Log.Error(ex.Message, ex);
return false;
}
finally
{
context.Dispose();
}
}
public User GetUser(int id)
{
var context = new CityLibraryEntities();
try
{
var user = context.Users
.Where(u => u.UserID == id)
.Include(u => u.Borrows).SingleOrDefault();
return user;
}
catch (Exception ex)
{
Log.Error(ex.Message, ex);
throw;
}
finally
{
context.Dispose();
}
}
public Book GetBook(int id)
{
var context = new CityLibraryEntities();
try
{
return context.Books.SingleOrDefault(b => b.BookId == id);
}
catch (Exception ex)
{
Log.Error(ex.Message, ex);
throw;
}
finally
{
context.Dispose();
}
}
public List<BookLite> GetBooksToBorrow()
{
var context = new CityLibraryEntities();
try
{
// Zapytanie składa się z dwóch połączonych
// 1. Pobiera listę książek które nigdy nie zostały wypżyczone (br.BorrowId is null) oraz tych które nie zostały zwrócone (br.IsReturned = 0)
// CASE WHEN br.IsReturned IS NULL THEN b.[Count] ELSE b.[Count] - Count(*) - Jeśli nigdy nie wypożyczona pobierz liczbę książek z właściwośći książki
// Jeśli jest chociaż jedna wypożyczona to zwróc różnicę (Dostępne - Wypożyczone)
// (Nie obsługuje przypadku gdy wszystkie zwrócone)
//(SELECT b.BookId, b.Title, br.IsReturned, CASE WHEN br.IsReturned IS NULL THEN b.[Count] ELSE b.[Count] - Count(*) END as DoWypozyczenia
//FROM
//Book b LEFT OUTER JOIN
//Borrow br ON b.BookId = br.BookId
//WHERE br.BorrowId is null OR br.IsReturned = 0
//GROUP BY b.BookId, b.Title, b.[Count], br.IsReturned
// 2. Przypadek gdy wszystkie książki o określonym BookId są zwócone
// Pobieram listę książek zwróconych (IsReturned = 1) oraz wstawiam liczbę książek z właściwości ksiazki Book.Count
//SELECT b.BookId, b.Title, br.IsReturned, b.[Count] as DoWypozyczenia
//FROM
//Book b LEFT OUTER JOIN
//Borrow br ON b.BookId = br.BookId
//WHERE br.IsReturned = 1
// 3. Grupowanie po BookId oraz Title
//SELECT tb.BookId, tb.Title, Min(tb.DoWypozyczenia) FROM
// ... SELECT_1 UNION SELECT_2 ...
//GROUP BY tb.BookId, tb.Title
//HAVING Min(tb.DoWypozyczenia) > 0
// Cały SQL
//SELECT tb.BookId, tb.Title, Min(tb.DoWypozyczenia) FROM
//(SELECT b.BookId, b.Title, br.IsReturned, CASE WHEN br.IsReturned IS NULL THEN b.[Count] ELSE b.[Count] - Count(*) END as DoWypozyczenia
//FROM
//Book b LEFT OUTER JOIN
//Borrow br ON b.BookId = br.BookId
//WHERE br.BorrowId is null OR br.IsReturned = 0
//GROUP BY b.BookId, b.Title, b.[Count], br.IsReturned
//UNION
//SELECT b.BookId, b.Title, br.IsReturned, b.[Count] as DoWypozyczenia
//FROM
//Book b LEFT OUTER JOIN
//Borrow br ON b.BookId = br.BookId
//WHERE br.IsReturned = 1
//GROUP BY b.BookId, b.Title, b.[Count], br.IsReturned) as tb
//GROUP BY tb.BookId, tb.Title
//HAVING Min(tb.DoWypozyczenia) > 0
// Wygenerowane przy pomocy http://www.sqltolinq.com/
// Dopracowane w http://www.linqpad.net/
var query = (
from tb in (
(
from b in context.Books
join br in context.Borrows on b.BookId equals br.BookId into br_join
from brj in br_join.DefaultIfEmpty()
where
brj == null ||
brj.IsReturned == false
group new {b, brj} by new
{
b.BookId,
b.Title,
b.Count,
brj.IsReturned
}
into g
select new
{
BookId = (Int32?) g.Key.BookId,
g.Key.Title,
IsReturned = (Boolean?) g.Key.IsReturned,
LeftCount = g.Key.IsReturned == null ? (Int64) g.Key.Count : (g.Key.Count - g.Count())
}
).Union(
from b in context.Books
join br in context.Borrows on b.BookId equals br.BookId into br_join
from brj in br_join.DefaultIfEmpty()
where
brj.IsReturned == true
group new {b, brj} by new
{
b.BookId,
b.Title,
b.Count,
brj.IsReturned
}
into g
select new
{
BookId = (Int32?) g.Key.BookId,
g.Key.Title,
IsReturned = (Boolean?) g.Key.IsReturned,
LeftCount = (Int64)g.Key.Count
}
)
)
group tb by new
{
tb.BookId,
tb.Title
}
into g
where g.Min(p => p.LeftCount) > 0
select new BookLite
{
BookId = g.Key.BookId.Value,
Title = g.Key.Title,
LeftCount = (int) g.Min(p => p.LeftCount)
});
//var sql = ((System.Data.Entity.Infrastructure.DbQuery<BookLite>)query).ToString();
return query.ToList();
}
catch (Exception ex)
{
Log.Error(ex.Message, ex);
throw;
}
finally
{
context.Dispose();
}
}
