public void UserDelete(int id)
{
BibliotekaEntities db = new BibliotekaEntities();
var query = (from x in db.User where x.UserId == id select x).SingleOrDefault();
query.IsActive = false;
db.SaveChanges();
}
public List<string> Types_Read()
{
BibliotekaEntities db = new BibliotekaEntities();
var query = (from x in db.DictBookGenre orderby x.BookGenreId select x.Name).ToList();
return query;
}
public void Destroy(BorrowDTO dBorrow)
{
BibliotekaEntities db = new BibliotekaEntities();
var query = (from x in db.Borrow where x.BorrowId == dBorrow.BorrowId select x).SingleOrDefault();
//db.Borrow.Remove(query);
query.IsReturned = true;
db.SaveChanges();
}
public IQueryable<ReportsHotUsersDTO> HotUsers_Read()
{
BibliotekaEntities db = new BibliotekaEntities();
var query = (from x in db.User orderby x.Borrow.Count select x)
.Select(u => new ReportsHotUsersDTO()
{
UserId = u.UserId,
FirstName = u.FirstName,
LastName = u.LastName,
FullName = u.FirstName + " " + u.LastName,
BorrowCount = u.Borrow.Count
});
return query;
}
public void Create(BookCreateDTO newBook)
{
BibliotekaEntities db = new BibliotekaEntities();
db.Book.Add(
new Book
{
Title = newBook.Title,
ReleaseDate = newBook.ReleaseDate,
ISBN = newBook.ISBN,
Count = newBook.Count,
Author = newBook.Author,
AddDate = DateTime.Now,
DictBookGenre = (from x in db.DictBookGenre where x.Name == newBook.DictBookGenre select x).SingleOrDefault()
});
db.SaveChanges();
}
public IQueryable<ReportsHotBooksDTO> HotBooks_Read()
{
BibliotekaEntities db = new BibliotekaEntities();
var query = (from x in db.Borrow select x)
.Select(book => new ReportsHotBooksDTO()
{
BookId = book.BookId,
Title = book.Book.Title,
DictBookGenre = book.Book.DictBookGenre.Name,
Author = book.Book.Author,
BorrowCount = book.Book.Borrow.Count,
FromDate = book.FromDate,
ToDate = book.ToDate
});
return query;
}
public void Create(BorrowCreateDTO newBorrow)
{
BibliotekaEntities db = new BibliotekaEntities();
foreach (var borrow in newBorrow.ChosenBooks)
{
db.Borrow.Add(
new Borrow
{
FromDate = DateTime.Now,
ToDate = newBorrow.ToDate,
User = (from x in db.User where x.UserId == newBorrow.Users select x).SingleOrDefault(),
Book = (from x in db.Book where x.BookId == borrow select x).SingleOrDefault()
});
db.SaveChanges();
}
}
public void UserCreate(UserCreateDTO nUser)
{
BibliotekaEntities db = new BibliotekaEntities();
db.User.Add(
new User
{
FirstName = nUser.FirstName,
LastName = nUser.LastName,
Email = nUser.Email,
Phone = nUser.Phone,
BirthDate = nUser.BirthDate,
IsActive = nUser.IsActive,
AddDate = DateTime.Now
});
db.SaveChanges();
}
public IQueryable<BorrowActiveDTO> ActiveBooks()
{
BibliotekaEntities db = new BibliotekaEntities();
var query =
(
from x
in db.Borrow
where x.IsReturned == false
select x
)
.Select(book => new BorrowActiveDTO()
{
BorrowId = book.BorrowId,
BookId = book.BookId,
Title = book.Book.Title,
Author = book.Book.Author,
FullName = book.User.FirstName + " " + book.User.LastName
});
return query;
}
public IQueryable<UserDTO> ActiveUsers()
{
BibliotekaEntities db = new BibliotekaEntities();
var query =
(
from x
in db.User
where x.Borrow.All(y => y.IsReturned) == false
select x
)
.Select(user => new UserDTO()
{
FirstName = user.FirstName,
Email = user.Email,
LastName = user.LastName,
UserId = user.UserId,
HasAllBookReturned = user.Borrow.All(x => x.IsReturned)
});
return query;
}
public UserDTO UserDetails(int id)
{
BibliotekaEntities db = new BibliotekaEntities();
var query = (from x in db.User where x.UserId == id select x)
.Select(u => new
{
UserId = u.UserId,
FirstName = u.FirstName,
LastName = u.LastName,
BirthDate = u.BirthDate,
Email = u.Email,
Phone = u.Phone,
AddDate = u.AddDate,
ModifiedDate = u.ModifiedDate,
IsActive = u.IsActive,
Borrows = u.Borrow.Select(b => new
{
b.Book.Title,
b.FromDate,
b.ToDate,
b.IsReturned
})
});
var user = query.SingleOrDefault();
var activeBorrows = new List<BorrowDTO>();
var borrowHistory = new List<BorrowDTO>();
foreach (var x in user.Borrows)
{
if (x.IsReturned)
borrowHistory.Add(new BorrowDTO
{
FromDate = x.FromDate,
IsReturned = x.IsReturned,
Title = x.Title,
ToDate = x.ToDate
});
else
activeBorrows.Add(new BorrowDTO
{
FromDate = x.FromDate,
IsReturned = x.IsReturned,
Title = x.Title,
ToDate = x.ToDate
});
}
return new UserDTO
{
UserId = user.UserId,
FirstName = user.FirstName,
LastName = user.LastName,
BirthDate = user.BirthDate,
Email = user.Email,
Phone = user.Phone,
AddDate = user.AddDate,
ModifiedDate = user.ModifiedDate,
IsActive = user.IsActive,
Borrows = activeBorrows,
History = borrowHistory,
};
}
public UserDTO Details(int id)
{
BibliotekaEntities db = new BibliotekaEntities();
var query = (from x in db.User where x.UserId == id select x)
.Select(u => new
{
Borrows = u.Borrow.Select(b => new
{
b.Book.Title,
b.FromDate,
b.ToDate,
b.IsReturned,
b.BorrowId,
b.UserId,
})
});
var user = query.SingleOrDefault();
var activeBorrows = new List<BorrowDTO>();
foreach (var x in user.Borrows)
{
if (!x.IsReturned)
{
activeBorrows.Add(new BorrowDTO
{
BorrowId = x.BorrowId,
FromDate = x.FromDate,
IsReturned = x.IsReturned,
Title = x.Title,
ToDate = x.ToDate
});
}
}
return new UserDTO
{
Borrows = activeBorrows
};
}
public List<UserDTO> UserList()
{
BibliotekaEntities db = new BibliotekaEntities();
var listOfUsers = (from x in db.User
orderby x.UserId select x).ToList()
.Select(u => new UserDTO()
{
UserId = u.UserId,
FirstName = u.FirstName,
LastName = u.LastName,
BirthDate = u.BirthDate,
Email = u.Email,
Phone = u.Phone,
AddDate = u.AddDate,
ModifiedDate = u.ModifiedDate,
IsActive = u.IsActive,
HasAllBookReturned = u.Borrow.All(x => x.IsReturned),
});
var result = listOfUsers.ToList();
return result;
}
public UserEditDTO UserFind(int id)
{
BibliotekaEntities db = new BibliotekaEntities();
var foundUser = (from x in db.User where x.UserId == id select x )
.Select(u => new UserEditDTO()
{
FirstName = u.FirstName,
LastName = u.LastName,
BirthDate = u.BirthDate,
Email = u.Email,
Phone = u.Phone,
IsActive = u.IsActive,
});
return foundUser.SingleOrDefault();
}
public void UserEdit(UserEditDTO eUser, int id)
{
BibliotekaEntities db = new BibliotekaEntities();
var query = (from x in db.User where x.UserId == id select x).SingleOrDefault();
query.FirstName = eUser.FirstName;
query.LastName = eUser.LastName;
query.Email = eUser.Email;
query.Phone = eUser.Phone;
query.ModifiedDate = DateTime.Now;
query.IsActive = eUser.IsActive;
db.SaveChanges();
}
public void Edit(BookEditDTO eBook, int id)
{
BibliotekaEntities db = new BibliotekaEntities();
var query = (from x in db.Book where x.BookId == id select x).SingleOrDefault();
query.Author = eBook.Author;
query.Count = eBook.Count;
query.DictBookGenre = (from x in db.DictBookGenre where x.Name == eBook.DictBookGenre select x).SingleOrDefault();
query.ISBN = eBook.ISBN;
query.ModifiedDate = DateTime.Now;
query.ReleaseDate = eBook.ReleaseDate;
query.Title = eBook.Title;
db.SaveChanges();
}
public BookDTO Details(int id)
{
BibliotekaEntities db = new BibliotekaEntities();
var query = (from x in db.Book where x.BookId == id select x)
.Select(book => new
{
BookId = book.BookId,
Title = book.Title,
ReleaseDate = book.ReleaseDate,
ISBN = book.ISBN,
DictBookGenre = book.DictBookGenre,
Count = book.Count,
Author = book.Author,
AddDate = book.AddDate,
ModifiedDate = book.ModifiedDate,
Borrows = book.Borrow.Select(b => new
{
b.Book.Title,
b.FromDate,
b.ToDate,
b.IsReturned
})
});
var PickedBook = query.SingleOrDefault();
var activeBorrows = new List<BorrowDTO>();
var borrowHistory = new List<BorrowDTO>();
foreach (var x in PickedBook.Borrows)
{
if (x.IsReturned)
borrowHistory.Add(new BorrowDTO
{
FromDate = x.FromDate,
IsReturned = x.IsReturned,
Title = x.Title,
ToDate = x.ToDate
});
else
activeBorrows.Add(new BorrowDTO
{
FromDate = x.FromDate,
IsReturned = x.IsReturned,
Title = x.Title,
ToDate = x.ToDate
});
}
return new BookDTO
{
BookId = PickedBook.BookId,
Title = PickedBook.Title,
ReleaseDate = PickedBook.ReleaseDate,
ISBN = PickedBook.ISBN,
DictBookGenre = PickedBook.DictBookGenre.Name,
Count = PickedBook.Count,
Author = PickedBook.Author,
AddDate = PickedBook.AddDate,
ModifiedDate = PickedBook.ModifiedDate,
Borrows = activeBorrows,
History = borrowHistory,
};
}
public IQueryable<BookIndexDTO> List()
{
BibliotekaEntities db = new BibliotekaEntities();
var query = (from x in db.Book orderby x.BookId select x)
.Select(book => new BookIndexDTO()
{
BookId = book.BookId,
Title = book.Title,
ReleaseDate = book.ReleaseDate,
ISBN = book.ISBN,
DictBookGenre = (from y in db.DictBookGenre where book.BookGenereId == y.BookGenreId select y.Name).FirstOrDefault(),
Count = book.Count,
Author = book.Author,
AddDate = book.AddDate,
ModifiedDate = book.ModifiedDate
});
return query;
}
public IQueryable<DictBookGenreDTO> GetTypes()
{
BibliotekaEntities db = new BibliotekaEntities();
var query = (from x in db.DictBookGenre orderby x.BookGenreId select x)
.Select(type => new DictBookGenreDTO()
{
BookGenrekId = type.BookGenreId,
Name = type.Name
});
return query;
}
public BookEditDTO Find(int id)
{
BibliotekaEntities db = new BibliotekaEntities();
var query = (from x in db.Book where x.BookId == id select x)
.Select(found => new BookEditDTO()
{
Author = found.Author,
Count = found.Count,
DictBookGenre = found.DictBookGenre.Name,
ISBN = found.ISBN,
ReleaseDate = found.ReleaseDate,
Title = found.Title
});
return query.SingleOrDefault();
}
public void DestroyV2(UserDTO model)
{
BibliotekaEntities db = new BibliotekaEntities();
foreach (var x in model.books)
{
var query = (from y in db.Borrow where y.BorrowId == x select y).SingleOrDefault();
//db.Borrow.Where(y => y.BorrowId == x).SingleOrDefault().
query.IsReturned = true;
}
db.SaveChanges();
//db.Borrow.Remove(query);
}
public IQueryable<UserDTO> GetUsers()
{
BibliotekaEntities db = new BibliotekaEntities();
var query =
(
from x in db.User
orderby x.UserId
where x.IsActive == true
select x
)
.Select(u => new UserDTO()
{
UserId = u.UserId,
FirstName = u.FirstName,
LastName = u.LastName,
FullName = u.FirstName + " " +u.LastName
});
return query;
}
