/// <summary>
/// Get a list of all categories sorted by name
/// </summary>
/// <returns>List with categories</returns>
public List<Category> GetCategories()
{
using (MediathekEntities context = new MediathekEntities())
{
var cat = from c in context.Categories
orderby c.Name
select c;
return cat.ToList();
}
}
/// <summary>
/// Get a category by it's ID
/// </summary>
/// <param name="catId">The ID of the category</param>
/// <returns>Category</returns>
public Category GetCategoryById(int catId)
{
using (MediathekEntities context = new MediathekEntities())
{
var cat = from c in context.Categories
where c.CategoryId == catId
select c;
return (Category)cat.First();
}
}
/// <summary>
/// Get a categories by for a given parent category ID ID
/// </summary>
/// <param name="parentCatId">The ID of the parent category</param>
/// <returns>List of categories</returns>
public List<Category> GetCategoriesByParentId(int parentCatId)
{
using (MediathekEntities context = new MediathekEntities())
{
var cats = context.Categories
.Include("ParentCategory")
.Where(c => c.ParentCategory.CategoryId == parentCatId);
return cats.ToList();
}
}
/// <summary>
/// Get all categories for a given media type.
/// Sorted alphabetically by category name
/// </summary>
/// <param name="mediaTypeId">The ID of the media type</param>
/// <returns>List with categories</returns>
public List<Category> GetCategoriesByMediaTypeId(int mediaTypeId)
{
using (MediathekEntities context = new MediathekEntities())
{
List<Category> catList = new List<Category>();
var mainCat = context.Categories.FirstOrDefault(c => c.CategoryId == mediaTypeId);
catList.Add(mainCat);
AddAllChildCategories(catList, mainCat.CategoryId);
catList.Sort();
return catList;
}
}
/// <summary>
/// Checks if a given media is available for renting.
/// This is the case when there are no open lendings and
/// when there is no other user who has the longest open reservation.
/// </summary>
/// <param name="mediaId">The ID of the media</param>
/// <param name="userId">The ID of the user</param>
/// <returns>True when media can be rent, else false.</returns>
public bool IsMediaAvailableForRenting(int mediaId, int userId)
{
using (MediathekEntities context = new MediathekEntities())
{
int? reservationUserId = GetUserIdWithOldestReservation(mediaId);
return (reservationUserId == null ||
reservationUserId == userId)
&&
((from mr in context.MediaRentings
where mr.MediaId == mediaId &&
mr.CheckInDate == null
select mr).Count() == 0);
}
}
/// <summary>
/// Mass rent media
/// </summary>
/// <param name="listMediaIds">List with media IDs that are being rented</param>
/// <param name="userId">The ID of the user who is the owner of the lendings</param>
/// <returns>Number of created entries</returns>
public int CreateLending(List<int> listMediaIds, int userId)
{
using (MediathekEntities context = new MediathekEntities())
{
int createCount = 0;
foreach (int mediaId in listMediaIds)
{
if (IsMediaAvailableForRenting(mediaId, userId))
{
MediaRenting mr = new MediaRenting();
mr.CheckOutDate = DateTime.Now;
mr.Media = context.MediaSet.FirstOrDefault(m => m.MediaId == mediaId);
mr.LeasingUser = context.Users.FirstOrDefault(u => u.UserId == userId);
// set the media state to "unavaiable"
mr.Media.MediaState = context.MediaStates.FirstOrDefault(ms => ms.MediaSateId == 2);
context.AddToMediaRentings(mr);
createCount++;
// if user had open reservation for the current media, we close the reservation
var res = from r in context.Reservations
where r.User.UserId == userId &&
r.Media.MediaId == mediaId &&
r.EndDate == null
select r;
foreach (Reservation reservation in res)
{
reservation.EndDate = DateTime.Now;
}
}
}
// save to DB
context.SaveChanges();
return createCount;
}
}
/// <summary>
/// Get all media a user has rented an not yet returned.
/// </summary>
/// <param name="userId">The ID of the user</param>
/// <returns>List with media</returns>
public List<MediaRenting> GetOpenLendingsByUser(int userId)
{
using (MediathekEntities context = new MediathekEntities())
{
var media = from m in context.MediaRentings.Include("Media.MediaType")
where m.UserId == userId &&
m.CheckInDate == null
orderby m.CheckOutDate
select m;
return media.ToList();
}
}
/// <summary>
/// Change the password of a user
/// </summary>
/// <param name="userId">The ID of the user</param>
/// <param name="oldPassword">The user's old password</param>
/// <param name="newPassword">The user's new password</param>
public void ChangeUserPassword(int userId, string oldPassword, string newPassword)
{
using (MediathekEntities context = new MediathekEntities())
{
try
{
var user = (from u in context.Users
where u.UserId == userId &&
u.Password.Equals(oldPassword)
select u).FirstOrDefault();
user.Password = newPassword;
context.SaveChanges();
}
catch (NullReferenceException)
{
throw new ConditionException(Localization.MsgErrorOldPasswordInvalid);
}
}
}
/// <summary>
/// Get the image of a specified media
/// </summary>
/// <param name="mediaId">The ID fo the media</param>
/// <param name="arr">The byte array</param>
/// <returns>Image as byte array</returns>
public void SaveMediaImage(int mediaId, byte[] arr)
{
using (MediathekEntities context = new MediathekEntities())
{
var media = context.MediaSet.FirstOrDefault(m => m.MediaId == mediaId);
media.Image = arr;
context.SaveChanges();
}
}
/// <summary>
/// Updates an existing MediaBook in the database
/// </summary>
/// <param name="mediaId">The ID of the media</param>
/// <param name="title">The title of the media</param>
/// <param name="description">The description</param>
/// <param name="author">The author</param>
/// <param name="publisher">The publisher</param>
/// <param name="publishingYear">The publishing year</param>
/// <param name="tag">The tag</param>
/// <param name="categoryId">The category ID</param>
public void UpdateMediaBook(int mediaId,
string title,
string description,
string author,
string publisher,
int? publishingYear,
string tag,
int categoryId)
{
using (MediathekEntities context = new MediathekEntities())
{
var media =
context.MediaSet.OfType<MediaBook>().FirstOrDefault(m => m.MediaId == mediaId);
media.Title = title;
media.Description = description;
media.Author = author;
media.Publisher = publisher;
media.PublishingYear = publishingYear;
media.Tag = tag;
media.Category = context.Categories.FirstOrDefault(c => c.CategoryId == categoryId);
context.SaveChanges();
}
}
/// <summary>
/// Get the ID of the media for the given reservation
/// </summary>
/// <param name="reservationId">The ID of the reservation</param>
/// <returns>The ID of the media</returns>
public int GetMediaIdFromReservation(int reservationId)
{
using (MediathekEntities context = new MediathekEntities())
{
var res = from r in context.Reservations.Include("Media")
where r.ReservationId == reservationId
select r;
return res.First().Media.MediaId;
}
}
/// <summary>
/// Check if a given user has an open reservation for a given media
/// </summary>
/// <param name="mediaId">The ID of the media</param>
/// <param name="userId">The ID of the user</param>
/// <returns>True, open reservation exists for the user. False, no open res. found.</returns>
public bool IsReservationOpen(int mediaId, int userId)
{
using (MediathekEntities context = new MediathekEntities())
{
var res = context.Reservations
.Include("Media").Include("User")
.Where(r => r.EndDate == null &&
r.Media.MediaId == mediaId &&
r.User.UserId == userId);
return res.Count() > 0;
}
}
/// <summary>
/// Mass creation of reservations for a given user ID.
/// The reservation limit is ignored.
/// If a reservation already exists, new new reservation will created.
/// </summary>
/// <param name="listMediaIds">List with media IDs</param>
/// <param name="userId">The user ID that gets the reservations</param>
public void CreateReservation(List<int> listMediaIds, int userId)
{
using (MediathekEntities context = new MediathekEntities())
{
foreach (int mediaId in listMediaIds)
{
if (!IsReservationOpen(mediaId, userId))
{
Reservation res = new Reservation();
res.CreationDate = DateTime.Now;
//res.Media = new Media() { MediaId = mediaId };
//res.User = new User() { UserId = userId };
res.Media = context.MediaSet.FirstOrDefault(m => m.MediaId == mediaId);
res.User = context.Users.FirstOrDefault(u => u.UserId == userId);
context.AddToReservations(res);
context.SaveChanges();
}
}
}
}
/// <summary>
/// Updates an existing MediaMusic in the database
/// </summary>
/// <param name="mediaId">The ID of the media</param>
/// <param name="title">The title of the media</param>
/// <param name="description">The description</param>
/// <param name="artist">The artist</param>
/// <param name="genre">The genre</param>
/// <param name="tracklist">The tracklist</param>
/// <param name="tag">The tag</param>
/// <param name="categoryId">The category ID</param>
public void UpdateMediaMusic(int mediaId,
string title,
string description,
string artist,
string genre,
string tracklist,
string tag,
int categoryId)
{
using (MediathekEntities context = new MediathekEntities())
{
var media =
context.MediaSet.OfType<MediaMusic>().FirstOrDefault(m => m.MediaId == mediaId);
media.Title = title;
media.Description = description;
media.Artist = artist;
media.Genre = genre;
media.Tracklist = tracklist;
media.Tag = tag;
media.Category = context.Categories.FirstOrDefault(c => c.CategoryId == categoryId);
context.SaveChanges();
}
}
/// <summary>
/// Create a new MediaMisc in the database
/// </summary>
/// <param name="title">The title of the media</param>
/// <param name="description">The description</param>
/// <param name="shortDescription">The short description</param>
/// <param name="tag">The tag</param>
/// <param name="categoryId">The category ID</param>
public void CreateMediaMisc(string title,
string description,
string shortDescription,
string tag,
int categoryId)
{
using (MediathekEntities context = new MediathekEntities())
{
MediaMisc media = new MediaMisc();
media.Title = title;
media.Description = description;
media.ShortDescription = shortDescription;
media.Tag = tag;
media.Category = context.Categories.FirstOrDefault(c => c.CategoryId == categoryId);
media.CreationDate = DateTime.Now;
// set media state to "available"
media.MediaState = context.MediaStates.FirstOrDefault(s => s.MediaSateId == 1);
// set media type
media.MediaType = context.MediaTypes.FirstOrDefault(mt => mt.MediaTypeId == 4);
context.AddToMediaSet(media);
context.SaveChanges();
}
}
/// <summary>
/// Get all lendings for a given user.
/// Ordered by check in date descending.
/// </summary>
/// <param name="userId">The ID of the user</param>
/// <returns>List of media lendings.</returns>
public List<MediaRenting> GetLendingsForUser(int userId)
{
using (MediathekEntities context = new MediathekEntities())
{
var rent = from mr in context.MediaRentings
where mr.UserId == userId
orderby mr.CheckInDate descending
select mr;
return rent.ToList();
}
}
/// <summary>
/// Updates an existing MediaBook in the database
/// </summary>
/// <param name="mediaId">The ID of the media</param>
/// <param name="title">The title of the media</param>
/// <param name="description">The description</param>
/// <param name="director">The director</param>
/// <param name="actors">Actors</param>
/// <param name="runtime">The runtime of the video</param>
/// <param name="tag">The tag</param>
/// <param name="categoryId">The category ID</param>
public void UpdateMediaVideo(int mediaId,
string title,
string description,
string director,
string actors,
int? runtime,
string tag,
int categoryId)
{
using (MediathekEntities context = new MediathekEntities())
{
var media =
context.MediaSet.OfType<MediaVideo>().FirstOrDefault(m => m.MediaId == mediaId);
media.Title = title;
media.Description = description;
media.Director = director;
media.Actors = actors;
media.Length = runtime;
media.Tag = tag;
media.Category = context.Categories.FirstOrDefault(c => c.CategoryId == categoryId);
context.SaveChanges();
}
}
/// <summary>
/// Create a new MediaBook in the database
/// </summary>
/// <param name="title">The title of the media</param>
/// <param name="description">The description</param>
/// <param name="director">The director</param>
/// <param name="actors">Actors</param>
/// <param name="runtime">The runtime of the video</param>
/// <param name="tag">The tag</param>
/// <param name="categoryId">The category ID</param>
public void CreateMediaVideo(string title,
string description,
string director,
string actors,
int? runtime,
string tag,
int categoryId)
{
using (MediathekEntities context = new MediathekEntities())
{
MediaVideo media = new MediaVideo();
media.Title = title;
media.Description = description;
media.Director = director;
media.Actors = actors;
media.Length = runtime;
media.Tag = tag;
media.Category = context.Categories.FirstOrDefault(c => c.CategoryId == categoryId);
media.CreationDate = DateTime.Now;
// set media state to "available"
media.MediaState = context.MediaStates.FirstOrDefault(s => s.MediaSateId == 1);
// set media type
media.MediaType = context.MediaTypes.FirstOrDefault(mt => mt.MediaTypeId == 3);
context.AddToMediaSet(media);
context.SaveChanges();
}
}
/// <summary>
/// Flags a media "order" as returned for a given user.
/// </summary>
/// <param name="userId">The ID of the user who has rented the media</param>
/// <param name="mediaId">The ID of the media</param>
/// <param name="checkOutDate">The checkout date of the media</param>
public void ReturnMedia(int userId, int mediaId, DateTime checkOutDate)
{
using (MediathekEntities context = new MediathekEntities())
{
var mr = (from m in context.MediaRentings.Include("Media")
where m.UserId == userId &&
m.MediaId == mediaId &&
m.CheckOutDate == checkOutDate
select m).FirstOrDefault();
if (mr != null)
{
// set checkin date to flag as returned
mr.CheckInDate = DateTime.Now;
// set the media's media state to available
mr.Media.MediaState = context.MediaStates.FirstOrDefault(ms => ms.MediaSateId == 1);
context.SaveChanges();
}
}
}
/// <summary>
/// Get a list with all closed reservations for a given user.
/// Ordered descending by end date (date when reservation has used)
/// </summary>
/// <param name="userId">The ID of the user</param>
/// <returns>List with reservations</returns>
public List<Reservation> GetClosedReservations(int userId)
{
using (MediathekEntities context = new MediathekEntities())
{
var res = context.Reservations
.Include("User")
.Where(r => r.EndDate != null &&
r.CreationDate != null &&
r.User.UserId == userId)
.OrderByDescending(r => r.EndDate);
return res.ToList();
}
}
/// <summary>
/// Updates an existing MediaMisc in the database
/// </summary>
/// <param name="mediaId">The ID of the media</param>
/// <param name="title">The title of the media</param>
/// <param name="description">The description</param>
/// <param name="shortDescription">The short description</param>
/// <param name="tag">The tag</param>
/// <param name="categoryId">The category ID</param>
public void UpdateMediaMisc(int mediaId,
string title,
string description,
string shortDescription,
string tag,
int categoryId)
{
using (MediathekEntities context = new MediathekEntities())
{
var media =
context.MediaSet.OfType<MediaMisc>().FirstOrDefault(m => m.MediaId == mediaId);
media.Title = title;
media.Description = description;
media.ShortDescription = shortDescription;
media.Tag = tag;
media.Category = context.Categories.FirstOrDefault(c => c.CategoryId == categoryId);
context.SaveChanges();
}
}
/// <summary>
/// Create a new MediaBook in the database
/// </summary>
/// <param name="title">The title of the media</param>
/// <param name="description">The description</param>
/// <param name="author">The author</param>
/// <param name="publisher">The publisher</param>
/// <param name="publishingYear">The publishing year</param>
/// <param name="tag">The tag</param>
/// <param name="categoryId">The category ID</param>
public void CreateMediaBook(string title,
string description,
string author,
string publisher,
int? publishingYear,
string tag,
int categoryId)
{
using (MediathekEntities context = new MediathekEntities())
{
MediaBook media = new MediaBook();
media.Title = title;
media.Description = description;
media.Author = author;
media.Publisher = publisher;
media.PublishingYear = publishingYear;
media.Tag = tag;
media.Category = context.Categories.FirstOrDefault(c => c.CategoryId == categoryId);
media.CreationDate = DateTime.Now;
// set media state to "available"
media.MediaState = context.MediaStates.FirstOrDefault(s => s.MediaSateId == 1);
// set media type
media.MediaType = context.MediaTypes.FirstOrDefault(mt => mt.MediaTypeId == 1);
context.AddToMediaSet(media);
context.SaveChanges();
}
}
/// <summary>
/// Get all open reservations.
/// Sorted by user name.
/// </summary>
/// <returns>Number of open reservations for the user.</returns>
public List<Reservation> GetOpenReservations()
{
using (MediathekEntities context = new MediathekEntities())
{
var res = from r in context.Reservations.Include("Media").Include("User")
where r.EndDate == null
orderby r.User.Surname
orderby r.User.Firstname
select r;
return res.ToList();
}
}
/// <summary>
/// Get media (general/misc) by it's ID.
/// Includes MediaState and Category.
/// </summary>
/// <param name="id">The ID of the media.</param>
/// <returns>Media item (general/misc)</returns>
public MediaMisc GetMediaMiscById(int id)
{
using (MediathekEntities context = new MediathekEntities())
{
var media = from m in context.MediaSet.OfType<MediaMisc>().Include("MediaState").Include("Category")
where m.MediaId == id
select m;
return media.First();
}
}
/// <summary>
/// Adds/inserts a new reservation for the given user for the given media.
/// </summary>
/// <param name="mediaId">The ID of the media</param>
/// <param name="userId">The ID of the user</param>
/// <exception cref="ConditionException">ConditionException thrown
/// when user has already to many open reservations</exception>
public void CreateReservation(int mediaId, int userId)
{
// check if max. reservation limit reached
string limitStr = GetSetting(Constants.SettingMaxOpenReservations);
int limit;
if (!int.TryParse(limitStr, out limit))
{
limit = 10; // set default
}
int openReservations = OpenReservations(userId);
if (openReservations < limit)
{
if (!IsReservationOpen(mediaId, userId))
{
using (MediathekEntities context = new MediathekEntities())
{
Reservation res = new Reservation();
res.CreationDate = DateTime.Now;
//res.Media = new Media() { MediaId = mediaId };
//res.User = new User() { UserId = userId };
res.Media = context.MediaSet.FirstOrDefault(m => m.MediaId == mediaId);
res.User = context.Users.FirstOrDefault(u => u.UserId == userId);
context.AddToReservations(res);
context.SaveChanges();
}
}
}
else
{
throw new ConditionException("Es existieren bereits zu viele offene Reservationen");
}
}
/// <summary>
/// Get media (no special type) by it's ID.
/// </summary>
/// <param name="mediaId">The ID of the media.</param>
/// <returns>Media item</returns>
public Media GetMediaById(int mediaId)
{
using (MediathekEntities context = new MediathekEntities())
{
var media = from m in context.MediaSet.OfType<Media>()
where m.MediaId == mediaId
select m;
return media.First();
}
}
/// <summary>
/// Delete a reservation by its ID.
/// </summary>
/// <param name="reservationId">The ID of the reservation</param>
public void DeleteReservation(int reservationId)
{
using (MediathekEntities context = new MediathekEntities())
{
var res = from r in context.Reservations
where r.ReservationId == reservationId
select r;
context.DeleteObject(res.First());
context.SaveChanges();
}
}
/// <summary>
/// Get the image of a specified media
/// </summary>
/// <param name="mediaId">The ID fo the media</param>
/// <returns>Image as byte array</returns>
public byte[] GetMediaImage(int mediaId)
{
using (MediathekEntities context = new MediathekEntities())
{
var media = from m in context.MediaSet
where m.MediaId == mediaId
select m;
return ((Media)media.First()).Image;
}
}
/// <summary>
/// Get the user ID of the oldest reservation for a given media.
/// </summary>
/// <param name="mediaId">The ID of the media</param>
/// <returns>User ID with oldest reservation, null returned when no user has a reservation</returns>
public int? GetUserIdWithOldestReservation(int mediaId)
{
using (MediathekEntities context = new MediathekEntities())
{
var res = from r in context.Reservations.Include("Media").Include("User")
where r.Media.MediaId == mediaId
orderby r.CreationDate
select r;
List<Reservation> reservations = res.ToList();
if (res == null || res.ToList().Count == 0)
{
return null;
}
else
{
return res.First().User.UserId; // get user id of oldest res
}
}
}
/// <summary>
/// Checks the login for a given user ID
/// </summary>
/// <param name="userId">The user ID</param>
/// <param name="password">The password associated with the user ID.</param>
/// <returns>True, when login found in database, else false.</returns>
public bool CheckUserLogin(string userId, string password)
{
if (userId == null) throw new ArgumentNullException("userId");
if (password == null) throw new ArgumentNullException("password");
int userIdInt = Convert.ToInt32(userId);
using (MediathekEntities context = new MediathekEntities())
{
var count = (from u in context.Users
where u.UserId == userIdInt &&
u.Password == password &&
!u.Deleted
select u).Count();
return count > 0;
}
}
