public void AddDVDTest()
{
var manager = new Manager();
var _dvds = manager.GetAllDVDs();
int count = _dvds.Count();
var dvd = new DVD
{
Title = "Test",
Director = "Test",
Studio = "Test",
Rating = MPAARating.NR,
Status = LendingStatus.InStock,
//Genre = Genres.Action,
UserRating = 4,
//ReleaseDate = DateTime.Parse("10-10-2010"),
Actors =
{
},
};
manager.AddDVD(dvd);
bool result = (_dvds.Count() == count + 1);
Assert.IsTrue(result);
}
public ActionResult DeleteDVD(DVD dvd)
{
var manager = new Manager();
manager.DeleteDVD(dvd.Id);
return RedirectToAction("Index", "Home");
}
public int AddDVD(DVD dvd)
{
int dvdid;
using (var connection = new SqlConnection(Settings.ConnectionString))
{
var command = new SqlCommand();
command.CommandText = "SP_AddDVD";
command.CommandType = CommandType.StoredProcedure;
var outputParam = new SqlParameter("@DVDID", SqlDbType.Int) {Direction = ParameterDirection.Output};
command.Connection = connection;
command.Parameters.AddWithValue("@Title", dvd.Title);
command.Parameters.AddWithValue("@ReleaseDate", dvd.ReleaseDate.Date.ToString("MM-dd-yyyy"));
command.Parameters.AddWithValue("@MPAA", dvd.MPAA);
command.Parameters.AddWithValue("@Director", dvd.Director);
command.Parameters.AddWithValue("@StudioID", dvd.StudioID);
command.Parameters.Add(outputParam);
connection.Open();
command.ExecuteNonQuery();
dvdid = (int) outputParam.Value;
}
return dvdid;
}
public AddDVDVM()
{
DvdToAdd = new DVD();
StudioNameSelectList = new List<SelectListItem>();
MPAASelectList = new List<SelectListItem>();
ActorSelectList = new List<SelectListItem>();
ActorSelectedValues = new List<int>();
}
public ActionResult AddDVD(DVD dvd)
{
var manager = new Manager();
manager.AddDVD(dvd);
dvd.CheckOutDate = DateTime.Now;
dvd.Status = LendingStatus.CheckedOut;
return RedirectToAction("Index", "Home");
}
public void CheckOutDVD(DVD dvd)
{
var selectedDVD = _dvds.FirstOrDefault(d => d.Id == dvd.Id);
selectedDVD.Id = dvd.Id;
selectedDVD.CheckOutDate = dvd.CheckOutDate;
selectedDVD.Borrower = dvd.Borrower;
selectedDVD.Status = dvd.Status;
}
public int AddDVD(DVD dvd)
{
int results = _dvds.Max(d => d.DVDId);
results++;
dvd.DVDId = results;
_dvds.Add(dvd);
return results;
}
public void AddDVD(DVD dvd)
{
if (GetAllDVDs().Count != 0)
{
dvd.Id = GetAllDVDs().LastOrDefault().Id + 1;
}
else
{
dvd.Id = 1;
}
_dvdRepo.AddDVD(dvd);
}
public void AddDVDTest()
{
var repo = new DVDMockRepo();
var dvd = new DVD()
{
Title = "Test",
ReleaseDate = new DateTime(2015, 10, 19),
MPAA = "R",
Director = "Mr. Test",
StudioID = 1
};
int dvdId = repo.AddDVD(dvd);
Assert.AreEqual(DVDMockRepo._dvds.Count, 4);
Assert.AreEqual(DVDMockRepo._dvds[3].Title, "Test");
}
public void AddDVDTestProduction()
{
var repo = new DVDRepo();
var dvd = new DVD()
{
Title = "Test",
ReleaseDate = new DateTime(2015, 10, 19),
MPAA = "R",
Director = "Mr. Test",
StudioID = 1
};
int dvdId = repo.AddDVD(dvd);
//change depending on your database
Assert.AreEqual(dvdId, 19);
}
public ActionResult SendNewMovieInfoToDB(AddDVDVM newDVDs)
{
if (ModelState.IsValid)
{
List<DVD> listOfDVDsAdded = new List<DVD>();
for (int i = 0; i < newDVDs.Quantity; i++)
{
var newDVD = new DVD();
newDVD.Movie = newDVDs.Movie;
newDVD.DVDType = newDVDs.DVDType;
listOfDVDsAdded.Add(_oops.AddMovieToDB(newDVD));
}
return View("SuccessfullyAddedNewMovie", listOfDVDsAdded);
}
else
{
return View("AddMovieByTMDBId", newDVDs);
}
}
public void TestAddNewDVDToDBViaTMDB()
{
//Adding The Avengers (2012) - TMDB# 24428
var newDVD = new DVD();
newDVD.Movie = repo.ReturnMovieInfoFromTMDB(24428);
newDVD.DVDType = "Blu-Ray";
var newDVDId = repo.AddNewDVDToDBViaTMDB(newDVD).DVDId;
Assert.AreEqual(newDVDId, 75);
}
//Adding a New DVD To the SQL Database (Checks if it already exists as well)
public DVD AddNewDVDToDBViaTMDB(DVD newDVD)
{
using (SqlConnection cn = new SqlConnection(Settings.ConnectionString))
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "select count(movies.movietmdbnum) from movies " +
"where movietmdbnum = @MovieTMDBNum";
cmd.Parameters.AddWithValue("@MovieTMDBNum", newDVD.Movie.MovieTMDBNum);
cmd.Connection = cn;
cn.Open();
int movieTMDBNumCount = int.Parse(cmd.ExecuteScalar().ToString());
cn.Close();
if (movieTMDBNumCount == 0)
{
cmd.CommandText = ("select count(studios.studioname) from studios " +
"where studioname = @StudioName");
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@StudioName", newDVD.Movie.Studio.StudioName);
cn.Open();
int studioCount = (int) cmd.ExecuteScalar();
//Adding Studio
if (studioCount == 0)
{
var p = new DynamicParameters();
p.Add("StudioName", newDVD.Movie.Studio.StudioName);
p.Add("StudioTMDBNum", newDVD.Movie.Studio.StudioTMDBNum);
p.Add("StudioID", DbType.Int32, direction: ParameterDirection.Output);
cn.Execute("AddNewStudioToStudios", p, commandType: CommandType.StoredProcedure);
newDVD.Movie.Studio.StudioId = p.Get<int>("StudioID");
}
else
{
cmd.CommandText = "select studioid from studios " +
"where studioname = @StudioName";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@StudioName", newDVD.Movie.Studio.StudioName);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
newDVD.Movie.Studio.StudioId = int.Parse(dr["StudioID"].ToString());
}
}
cn.Close();
//Adding Director
cmd.CommandText = "select count(directors.directorname) from directors " +
"where directorname = @DirectorName";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@DirectorName", newDVD.Movie.Director.DirectorName);
cn.Open();
int directorCount = (int) cmd.ExecuteScalar();
if (directorCount == 0)
{
var p = new DynamicParameters();
p.Add("DirectorName", newDVD.Movie.Director.DirectorName);
p.Add("DirectorTMDBNum", newDVD.Movie.Director.DirectorTMDBNum);
p.Add("DirectorID", DbType.Int32, direction: ParameterDirection.Output);
cn.Execute("AddNewDirectorToDirectors", p, commandType: CommandType.StoredProcedure);
newDVD.Movie.Director.DirectorId = p.Get<int>("DirectorID");
}
else
{
cmd.CommandText = "select directorid from directors " +
"where directorname = @DirectorName";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@DirectorName", newDVD.Movie.Director.DirectorName);
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
newDVD.Movie.Director.DirectorId = int.Parse(dr["DirectorID"].ToString());
}
}
}
cn.Close();
//Adding MovieInfo
cmd.CommandText = "select count(movies.movietitle) from movies " +
"where movietitle = @MovieTitle";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@MovieTitle", newDVD.Movie.MovieTitle);
cn.Open();
int movieTitleCount = (int) cmd.ExecuteScalar();
if (movieTitleCount == 0)
{
var p = new DynamicParameters();
p.Add("DirectorID", newDVD.Movie.Director.DirectorId);
p.Add("StudioID", newDVD.Movie.Studio.StudioId);
p.Add("MovieTitle", newDVD.Movie.MovieTitle);
p.Add("MovieTMDBNum", newDVD.Movie.MovieTMDBNum);
p.Add("Rating", newDVD.Movie.MpaaRating);
p.Add("ReleaseDate", newDVD.Movie.ReleaseDate);
p.Add("DurationInMin", newDVD.Movie.Duration);
p.Add("Synopsis", newDVD.Movie.Synopsis);
p.Add("PosterUrl", newDVD.Movie.PosterUrl);
p.Add("YouTubeTrailer", newDVD.Movie.YouTubeTrailer);
p.Add("InCollection", true);
p.Add("MovieID", DbType.Int32, direction: ParameterDirection.Output);
cn.Execute("AddNewMovieToMovies", p, commandType: CommandType.StoredProcedure);
newDVD.Movie.MovieId = p.Get<int>("MovieID");
}
cn.Close();
//Adding Actors
if (newDVD.Movie.MovieActors.Count != 0)
{
for (int i = 0; i < newDVD.Movie.MovieActors.Count; i++)
{
cmd.CommandText = "select count(actors.actorname) from actors " +
"where actorname = @ActorName";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@ActorName", newDVD.Movie.MovieActors[i].ActorName);
cn.Open();
int actorNameCount = (int) cmd.ExecuteScalar();
if (actorNameCount == 0)
{
var p = new DynamicParameters();
p.Add("ActorName", newDVD.Movie.MovieActors[i].ActorName);
p.Add("ActorTMDBNum", newDVD.Movie.MovieActors[i].ActorTMDBNum);
p.Add("ActorID", DbType.Int32, direction: ParameterDirection.Output);
cn.Execute("AddNewActorToActors", p, commandType: CommandType.StoredProcedure);
newDVD.Movie.MovieActors[i].ActorId = p.Get<int>("ActorID");
}
else
{
cmd.CommandText = "select actorid from actors " +
"where actorname = @ActorName";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@ActorName", newDVD.Movie.MovieActors[i].ActorName);
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
newDVD.Movie.MovieActors[i].ActorId = int.Parse(dr["ActorID"].ToString());
}
}
}
cn.Close();
}
foreach (var a in newDVD.Movie.MovieActors)
{
var p = new DynamicParameters();
p.Add("ActorID", a.ActorId);
p.Add("MovieID", newDVD.Movie.MovieId);
p.Add("CharacterName", a.CharacterName);
cn.Execute("AddNewActorMovieToActorsMovies", p, commandType: CommandType.StoredProcedure);
}
}
//Adding Genres
if (newDVD.Movie.Genres.Count != 0)
{
for (int i = 0; i < newDVD.Movie.Genres.Count; i++)
{
cmd.CommandText = "select count(genres.genrename) from genres " +
"where genrename = @GenreName";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@GenreName", newDVD.Movie.Genres[i].GenreName);
cn.Open();
int genresCount = (int) cmd.ExecuteScalar();
if (genresCount == 0)
{
var p = new DynamicParameters();
p.Add("GenreName", newDVD.Movie.Genres[i].GenreName);
p.Add("GenreID", DbType.Int32, direction: ParameterDirection.Output);
cn.Execute("AddNewGenreToGenres", p, commandType: CommandType.StoredProcedure);
newDVD.Movie.Genres[i].GenreId = p.Get<int>("GenreID");
}
else
{
cmd.CommandText = "select genreid from genres " +
"where genrename = @GenreName";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@GenreName", newDVD.Movie.Genres[i].GenreName);
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
newDVD.Movie.Genres[i].GenreId = int.Parse(dr["GenreID"].ToString());
}
}
}
cn.Close();
}
foreach (var g in newDVD.Movie.Genres)
{
var p = new DynamicParameters();
p.Add("GenreID", g.GenreId);
p.Add("MovieID", newDVD.Movie.MovieId);
cn.Execute("AddNewGenreMovieToGenresMovies", p, commandType: CommandType.StoredProcedure);
}
}
//Adding MovieAliases
if (newDVD.Movie.MovieAliases.Count != 0)
{
for (int i = 0; i < newDVD.Movie.MovieAliases.Count; i++)
{
var p = new DynamicParameters();
p.Add("MovieID", newDVD.Movie.MovieId);
p.Add("MovieAlias", newDVD.Movie.MovieAliases[i].MovieAliasTitle);
p.Add("MovieAliasID", DbType.Int32, direction: ParameterDirection.Output);
cn.Execute("AddNewMovieAliasToMovieAliases", p, commandType: CommandType.StoredProcedure);
newDVD.Movie.MovieAliases[i].MovieAliasId = p.Get<int>("MovieAliasID");
}
}
}
else
{
cmd.CommandText = "select movieid from movies " +
"where movietmdbnum = @MovieTMDBNum";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("MovieTMDBNum", newDVD.Movie.MovieTMDBNum);
cmd.Connection = cn;
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
newDVD.Movie.MovieId = int.Parse(dr["MovieID"].ToString());
}
}
cn.Close();
//Change InCollection to True
var pInColl = new DynamicParameters();
pInColl.Add("MovieID", newDVD.Movie.MovieId);
pInColl.Add("InCollection", true);
cn.Execute("ChangeInCollectionBoolean", pInColl, commandType: CommandType.StoredProcedure);
}
//Add DVD to Database
var pDVD = new DynamicParameters();
pDVD.Add("MovieID", newDVD.Movie.MovieId);
pDVD.Add("DVDType", newDVD.DVDType);
pDVD.Add("DVDID", DbType.Int32, direction: ParameterDirection.Output);
cn.Execute("AddNewDVDToDVDs", pDVD, commandType: CommandType.StoredProcedure);
newDVD.DVDId = pDVD.Get<int>("DVDID");
}
return newDVD;
}
//Retrieve Full Movie Info and DVDs based on MovieID from the SQL DB
public List<DVD> RetrieveFullDVDInfoFromDB(int movieId)
{
List<DVD> listOfDVDInfo = new List<DVD>();
using (var cn = new SqlConnection(Settings.ConnectionString))
{
var cmd = new SqlCommand();
Models.Movie movieInfo = new Models.Movie();
//Get Movie Info including Director & Studio By MovieId
cmd.CommandText = "GetMovieInfoByMovieId";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = cn;
cmd.Parameters.AddWithValue("@MovieID", movieId);
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
movieInfo.MovieId = int.Parse(dr["MovieID"].ToString());
movieInfo.MovieTitle = dr["MovieTitle"].ToString();
movieInfo.MovieTMDBNum = int.Parse(dr["MovieTMDBNum"].ToString());
movieInfo.ReleaseDate = DateTime.Parse(dr["ReleaseDate"].ToString());
movieInfo.MpaaRating = dr["Rating"].ToString();
movieInfo.Duration = int.Parse(dr["DurationInMin"].ToString());
if (dr["Synopsis"] != DBNull.Value)
{
movieInfo.Synopsis = dr["Synopsis"].ToString();
}
if (dr["PosterUrl"] != DBNull.Value)
{
movieInfo.PosterUrl = dr["PosterUrl"].ToString();
}
if (dr["YouTubeTrailer"] != DBNull.Value)
{
movieInfo.YouTubeTrailer = dr["YouTubeTrailer"].ToString();
}
movieInfo.Director.DirectorId = int.Parse(dr["DirectorID"].ToString());
movieInfo.Director.DirectorName = dr["DirectorName"].ToString();
if (dr["DirectorTMDBNum"] != DBNull.Value)
{
movieInfo.Director.DirectorTMDBNum = int.Parse(dr["DirectorTMDBNum"].ToString());
}
movieInfo.Studio.StudioId = int.Parse(dr["StudioID"].ToString());
movieInfo.Studio.StudioName = dr["StudioName"].ToString();
if (dr["StudioTMDBNum"] != DBNull.Value)
{
movieInfo.Studio.StudioTMDBNum = int.Parse(dr["StudioTMDBNum"].ToString());
}
}
}
cn.Close();
//Get Actors List for Movie by MovieID
cmd.CommandText = "GetActorsListByMovieID";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = cn;
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@MovieID", movieId);
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
if (dr["ActorID"] != DBNull.Value)
{
Actor newActor = new Actor();
newActor.ActorId = int.Parse(dr["ActorID"].ToString());
if (dr["ActorName"] != DBNull.Value)
{
newActor.ActorName = dr["ActorName"].ToString();
}
if (dr["ActorTMDBNum"] != DBNull.Value)
{
newActor.ActorTMDBNum = int.Parse(dr["ActorTMDBNum"].ToString());
}
if (dr["CharacterName"] != DBNull.Value)
{
newActor.CharacterName = dr["CharacterName"].ToString();
}
movieInfo.MovieActors.Add(newActor);
}
}
}
cn.Close();
//Get Genres List for Movie by MovieID
cmd.CommandText = "GetGenresListByMovieID";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = cn;
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@MovieID", movieId);
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
if (dr["GenreID"] != DBNull.Value)
{
Models.Genre newGenre = new Models.Genre();
newGenre.GenreId = int.Parse(dr["GenreID"].ToString());
if (dr["GenreName"] != DBNull.Value)
{
newGenre.GenreName = dr["GenreName"].ToString();
}
movieInfo.Genres.Add(newGenre);
}
}
}
cn.Close();
//Get Movie Aliases List for Movie by MovieID
cmd.CommandText = "GetMovieAliasesByMovieID";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = cn;
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@MovieID", movieId);
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
if (dr["MovieAliasID"] != DBNull.Value)
{
MovieAlias newMovieAlias = new MovieAlias();
newMovieAlias.MovieAliasId = int.Parse(dr["MovieAliasID"].ToString());
if (dr["MovieAlias"] != DBNull.Value)
{
newMovieAlias.MovieAliasTitle = dr["MovieAlias"].ToString();
}
movieInfo.MovieAliases.Add(newMovieAlias);
}
}
}
cn.Close();
//Get User Ratings for Movie by MovieID
cmd.CommandText = "GetUserRatingsByMovieID";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = cn;
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@MovieID", movieId);
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
if (dr["UserRatingID"] != DBNull.Value)
{
UserRating newUserRating = new UserRating();
newUserRating.UserRatingId = int.Parse(dr["UserRatingID"].ToString());
if (dr["MovieID"] != DBNull.Value)
{
newUserRating.MovieId = int.Parse(dr["MovieID"].ToString());
}
if (dr["BorrowerID"] != DBNull.Value)
{
newUserRating.BorrowerId = int.Parse(dr["BorrowerID"].ToString());
}
if (dr["Rating"] != DBNull.Value)
{
newUserRating.Rating = int.Parse(dr["Rating"].ToString());
}
if (dr["OwnerRating"] != DBNull.Value)
{
newUserRating.Owner = bool.Parse(dr["OwnerRating"].ToString());
}
if (dr["FirstName"] != DBNull.Value)
{
newUserRating.BorrowerName = (dr["FirstName"].ToString() + " " +
dr["LastName"].ToString());
}
movieInfo.UserRatings.Add(newUserRating);
}
}
}
cn.Close();
//Get User Notes for Movie by MovieID
cmd.CommandText = "GetUserNotesByMovieID";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = cn;
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@MovieID", movieId);
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
if (dr["UserNoteID"] != DBNull.Value)
{
UserNote newUserNote = new UserNote();
newUserNote.UserNoteId = int.Parse(dr["UserNoteID"].ToString());
if (dr["MovieID"] != DBNull.Value)
{
newUserNote.MovieId = int.Parse(dr["MovieID"].ToString());
}
if (dr["MovieTitle"] != DBNull.Value)
{
newUserNote.MovieTitle = dr["MovieTitle"].ToString();
}
if (dr["BorrowerID"] != DBNull.Value)
{
newUserNote.BorrowerId = int.Parse(dr["BorrowerID"].ToString());
}
if (dr["Note"] != DBNull.Value)
{
newUserNote.Note = dr["Note"].ToString();
}
if (dr["OwnerNote"] != DBNull.Value)
{
newUserNote.Owner = bool.Parse(dr["OwnerNote"].ToString());
}
if (dr["FirstName"] != DBNull.Value)
{
newUserNote.BorrowerName = (dr["FirstName"].ToString() + " " + dr["LastName"].ToString());
}
movieInfo.UserNotes.Add(newUserNote);
}
}
}
cn.Close();
//Get DVDID and DVDType Info by MovieID
cmd.CommandText = "GetDVDInfoByMovieID";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = cn;
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@MovieID", movieId);
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
if (dr["DVDID"] != DBNull.Value)
{
DVD newDVD = new DVD();
newDVD.DVDId = int.Parse(dr["DVDID"].ToString());
if (dr["DVDType"] != DBNull.Value)
{
newDVD.DVDType = dr["DVDType"].ToString();
}
newDVD.Movie = movieInfo;
listOfDVDInfo.Add(newDVD);
}
}
}
cn.Close();
//Get Borrower Statuses for a DVD by DVDID
foreach (var d in listOfDVDInfo)
{
cmd.CommandText = "GetBorrowerStatusesByDVDID";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = cn;
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@DVDID", d.DVDId);
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
if (dr["BorrowerStatusID"] != DBNull.Value)
{
Status newStatus = new Status();
newStatus.StatusId = int.Parse(dr["BorrowerStatusID"].ToString());
if (dr["BorrowerID"] != DBNull.Value)
{
newStatus.Borrower.BorrowerId = int.Parse(dr["BorrowerID"].ToString());
}
if (dr["DVDID"] != DBNull.Value)
{
newStatus.DVDId = int.Parse(dr["DVDID"].ToString());
}
if (dr["DateBorrowed"] != DBNull.Value)
{
newStatus.DateBorrowed = DateTime.Parse(dr["DateBorrowed"].ToString());
}
if (dr["DateReturned"] != DBNull.Value)
{
newStatus.DateReturned = DateTime.Parse(dr["DateReturned"].ToString());
}
if (dr["IsOwner"] != DBNull.Value)
{
newStatus.Borrower.IsOwner = bool.Parse(dr["IsOwner"].ToString());
}
if (dr["FirstName"] != DBNull.Value)
{
newStatus.Borrower.FirstName = dr["FirstName"].ToString();
}
if (dr["LastName"] != DBNull.Value)
{
newStatus.Borrower.LastName = dr["LastName"].ToString();
}
if (dr["Email"] != DBNull.Value)
{
newStatus.Borrower.Email = dr["Email"].ToString();
}
if (dr["StreetAddress"] != DBNull.Value)
{
newStatus.Borrower.Address = dr["StreetAddress"].ToString();
}
if (dr["City"] != DBNull.Value)
{
newStatus.Borrower.City = dr["City"].ToString();
}
if (dr["State"] != DBNull.Value)
{
newStatus.Borrower.State = dr["State"].ToString();
}
if (dr["Zipcode"] != DBNull.Value)
{
newStatus.Borrower.Zipcode = dr["Zipcode"].ToString();
}
if (dr["Phone"] != DBNull.Value)
{
newStatus.Borrower.Phone = dr["Phone"].ToString();
}
d.Statuses.Add(newStatus);
}
}
}
cn.Close();
}
}
return listOfDVDInfo;
}
//Remove DVD from Database via DVDID
public bool RemoveDVDFromDb(int dvdId)
{
var dvdToDelete = new DVD();
dvdToDelete.DVDId = dvdId;
//Get dvdToDelete info first to save as object
using (SqlConnection cn = new SqlConnection(Settings.ConnectionString))
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "select MovieID, DVDType from DVDs where DVDID=@DVDID";
cmd.Connection = cn;
cn.Open();
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("DVDID", dvdId);
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
dvdToDelete.Movie.MovieId = int.Parse(dr["MovieID"].ToString());
dvdToDelete.DVDType = dr["DVDType"].ToString();
}
}
cn.Close();
//Read all BorrowerStatuses and insert on to DeletedBorrowerStatus (extra feature)
//First Delete All BorrowerStatuses on that DVDID to eliminate FK
var p = new DynamicParameters();
p.Add("DVDID", dvdToDelete.DVDId);
cn.Execute("RemoveBorrowerStatusOnDVDID", p, commandType: CommandType.StoredProcedure);
//Add DVDInfo to DeletedDVDs table
var p1 = new DynamicParameters();
p1.Add("DVDID", dvdToDelete.DVDId);
p1.Add("MovieID", dvdToDelete.Movie.MovieId);
p1.Add("DVDType", dvdToDelete.DVDType);
cn.Execute("AddDVDtoDeletedDVDs", p1, commandType: CommandType.StoredProcedure);
//Delete DVDID from DVDs table
var p2 = new DynamicParameters();
p2.Add("DVDID", dvdToDelete.DVDId);
cn.Execute("RemoveDVDFromDVDs", p2, commandType: CommandType.StoredProcedure);
//Check if that is the last DVD copy of the Movie, and if yes, must mark InCollection as false in Movies
cmd.CommandText = "select count(DVDs.MovieID) from DVDs where MovieID=@MovieID";
cmd.Connection = cn;
cn.Open();
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("MovieID", dvdToDelete.Movie.MovieId);
int dvdsCount = int.Parse(cmd.ExecuteScalar().ToString());
if (dvdsCount == 0)
{
var pInColl = new DynamicParameters();
pInColl.Add("MovieID", dvdToDelete.Movie.MovieId);
pInColl.Add("InCollection", false);
cn.Execute("ChangeInCollectionBoolean", pInColl, commandType: CommandType.StoredProcedure);
return false;
}
else
{
return true;
}
}
}
//Retrieve partial DVDs List info from SQL DB (lighter weight; good for ViewDVDsStatuses
public List<DVD> RetrievePartialDVDsInfo(int movieId)
{
List<DVD> listOfDVDInfo = new List<DVD>();
using (var cn = new SqlConnection(Settings.ConnectionString))
{
var cmd = new SqlCommand();
Models.Movie movieInfo = new Models.Movie();
//Get Movie Info including Director & Studio By MovieId
cmd.CommandText = "GetMovieInfoByMovieId";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = cn;
cmd.Parameters.AddWithValue("@MovieID", movieId);
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
movieInfo.MovieId = int.Parse(dr["MovieID"].ToString());
movieInfo.MovieTitle = dr["MovieTitle"].ToString();
}
}
cn.Close();
//Get DVDID and DVDType Info by MovieID
cmd.CommandText = "GetDVDInfoByMovieID";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = cn;
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@MovieID", movieId);
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
if (dr["DVDID"] != DBNull.Value)
{
DVD newDVD = new DVD();
newDVD.DVDId = int.Parse(dr["DVDID"].ToString());
if (dr["DVDType"] != DBNull.Value)
{
newDVD.DVDType = dr["DVDType"].ToString();
}
newDVD.Movie = movieInfo;
listOfDVDInfo.Add(newDVD);
}
}
}
cn.Close();
//Get Borrower Statuses for a DVD by DVDID
foreach (var d in listOfDVDInfo)
{
cmd.CommandText = "GetBorrowerStatusesByDVDID";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = cn;
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@DVDID", d.DVDId);
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
if (dr["BorrowerStatusID"] != DBNull.Value)
{
Status newStatus = new Status();
newStatus.StatusId = int.Parse(dr["BorrowerStatusID"].ToString());
if (dr["BorrowerID"] != DBNull.Value)
{
newStatus.Borrower.BorrowerId = int.Parse(dr["BorrowerID"].ToString());
}
if (dr["DVDID"] != DBNull.Value)
{
newStatus.DVDId = int.Parse(dr["DVDID"].ToString());
}
if (dr["DateBorrowed"] != DBNull.Value)
{
newStatus.DateBorrowed = DateTime.Parse(dr["DateBorrowed"].ToString());
}
if (dr["DateReturned"] != DBNull.Value)
{
newStatus.DateReturned = DateTime.Parse(dr["DateReturned"].ToString());
}
if (dr["IsOwner"] != DBNull.Value)
{
newStatus.Borrower.IsOwner = bool.Parse(dr["IsOwner"].ToString());
}
if (dr["FirstName"] != DBNull.Value)
{
newStatus.Borrower.FirstName = dr["FirstName"].ToString();
}
if (dr["LastName"] != DBNull.Value)
{
newStatus.Borrower.LastName = dr["LastName"].ToString();
}
if (dr["Email"] != DBNull.Value)
{
newStatus.Borrower.Email = dr["Email"].ToString();
}
if (dr["StreetAddress"] != DBNull.Value)
{
newStatus.Borrower.Address = dr["StreetAddress"].ToString();
}
if (dr["City"] != DBNull.Value)
{
newStatus.Borrower.City = dr["City"].ToString();
}
if (dr["State"] != DBNull.Value)
{
newStatus.Borrower.State = dr["State"].ToString();
}
if (dr["Zipcode"] != DBNull.Value)
{
newStatus.Borrower.Zipcode = dr["Zipcode"].ToString();
}
if (dr["Phone"] != DBNull.Value)
{
newStatus.Borrower.Phone = dr["Phone"].ToString();
}
d.Statuses.Add(newStatus);
}
}
}
cn.Close();
}
}
return listOfDVDInfo;
}
public void CheckOutDVD(DVD dvd)
{
_dvdRepo.CheckOutDVD(dvd);
}
public void EditDVD(DVD dvd)
{
_dvdRepo.EditDVD(dvd);
}
public void EditDVD(DVD dvd)
{
var selectedDVD = _dvds.FirstOrDefault(d => d.Id == dvd.Id);
selectedDVD.Id = dvd.Id;
selectedDVD.Title = dvd.Title;
selectedDVD.Director = dvd.Director;
//selectedDVD.Genre = dvd.Genre;
selectedDVD.Status = dvd.Status;
selectedDVD.Borrower = dvd.Borrower;
selectedDVD.CheckOutDate = dvd.CheckOutDate;
selectedDVD.ReturnDate = dvd.ReturnDate;
selectedDVD.UserNotes = dvd.UserNotes;
}
public void ReturnDVD(DVD dvd)
{
_dvdRepo.ReturnDVD(dvd);
}
public DVD GetDVDById(int id)
{
var results = new DVD();
results = _dvds.FirstOrDefault(d => d.DVDId.Equals(id));
return results;
}
public DVDDetailVM()
{
Dvd = new DVD();
StudioDescription = "";
ActorNames = new List <string>();
}
public DvdBorrowerDetailVM()
{
Dvd = new DVD();
BorrowerDetails = new List <DVDBorrowerDetail>();
Borrowers = new List <Borrower>();
}
public DVD GetDVDById(int id)
{
var results = new DVD();
using (SqlConnection cn = new SqlConnection(Settings.ConnectionString))
{
var d = new DynamicParameters();
d.Add("DVDId", id);
results = cn.Query<DVD>("SELECT * FROM DVDs WHERE DVDId = @DVDId", d).FirstOrDefault();
}
return results;
}
public ActionResult ReturnDVD(DVD dvd)
{
var manager = new Manager();
dvd.ReturnDate = DateTime.Now;
dvd.Status = LendingStatus.InStock;
manager.ReturnDVD(dvd);
return RedirectToAction("Index", "Home");
}
public void ReturnDVD(DVD dvd)
{
var selectedDVD = _dvds.FirstOrDefault(d => d.Id == dvd.Id);
selectedDVD.Id = dvd.Id;
selectedDVD.ReturnDate = dvd.ReturnDate;
selectedDVD.UserNotes = dvd.UserNotes;
selectedDVD.Status = dvd.Status;
}
public DVDDetailVM()
{
Dvd = new DVD();
StudioDescription = "";
ActorNames = new List<string>();
}
public void AddDVD(DVD dvd)
{
_dvds.Add(dvd);
}