public void TestAddNewBorrowerToDB()
{
Borrower newBorrower = new Borrower()
{
IsOwner = false,
FirstName = "Pizza",
LastName = "King",
Email = "*****@*****.**",
Address = "1234 Pizza Blvd",
City = "New York City",
State = "NY",
Zipcode = "10001",
Phone = "855-911-1337"
};
var result = repo.AddNewBorrowerToDB(newBorrower).BorrowerId;
Assert.AreEqual(result, 10);
}
//Add a New Borrower to DB
public Borrower AddNewBorrowerToDB(Borrower newBorrower)
{
using (SqlConnection cn = new SqlConnection(Settings.ConnectionString))
{
var p = new DynamicParameters();
p.Add("IsOwner", newBorrower.IsOwner);
p.Add("FirstName", newBorrower.FirstName);
p.Add("LastName", newBorrower.LastName);
p.Add("Email", newBorrower.Email);
p.Add("StreetAddress", newBorrower.Address);
p.Add("City", newBorrower.City);
p.Add("State", newBorrower.State);
p.Add("Zipcode", newBorrower.Zipcode);
p.Add("Phone", newBorrower.Phone);
p.Add("BorrowerID", DbType.Int32, direction: ParameterDirection.Output);
cn.Execute("AddNewBorrowerToBorrowers", p, commandType: CommandType.StoredProcedure);
newBorrower.BorrowerId = p.Get<int>("BorrowerID");
}
return newBorrower;
}
//Retrieve Full Borrowers List with All DVD Rental Statuses in there
public List<Borrower> RetrieveFullBorrowersList()
{
List<Borrower> listOfBorrowers = new List<Borrower>();
using (SqlConnection cn = new SqlConnection(Settings.ConnectionString))
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText =
"select FirstName, LastName, BorrowerID, IsOwner, Email, StreetAddress, City," +
" State, Zipcode, Phone from Borrowers";
cmd.Connection = cn;
cn.Open();
//Error connection not establised
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
var newBorrower = new Borrower();
newBorrower.FirstName = dr["FirstName"].ToString();
newBorrower.LastName = dr["LastName"].ToString();
newBorrower.BorrowerId = int.Parse(dr["BorrowerID"].ToString());
newBorrower.IsOwner = bool.Parse(dr["IsOwner"].ToString());
newBorrower.Email = dr["Email"].ToString();
newBorrower.Address = dr["StreetAddress"].ToString();
newBorrower.City = dr["City"].ToString();
newBorrower.State = dr["State"].ToString();
newBorrower.Zipcode = dr["Zipcode"].ToString();
newBorrower.Phone = dr["Phone"].ToString();
listOfBorrowers.Add(newBorrower);
}
}
cn.Close();
//Add all BorrowerStatuses to each User
foreach (var b in listOfBorrowers)
{
cmd.CommandText = ("select bs.BorrowerStatusID, bs.BorrowerID, bs.DVDID, bs.DateBorrowed, bs.DateReturned," +
" b.FirstName, b.LastName, m.MovieID, m.MovieTitle" +
" from BorrowerStatuses bs" +
" LEFT JOIN Borrowers b on bs.BorrowerID = b.BorrowerID" +
" LEFT JOIN DVDs d on bs.DVDID=d.DVDID" +
" LEFT JOIN Movies m on d.MovieID=m.MovieID" +
" WHERE bs.BorrowerID = @BorrowerID");
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@BorrowerID", b.BorrowerId);
cmd.Connection = cn;
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
var newStatus = new Status();
newStatus.StatusId = int.Parse(dr["BorrowerStatusID"].ToString());
newStatus.Borrower.BorrowerId = int.Parse(dr["BorrowerID"].ToString());
newStatus.Borrower.FirstName = dr["FirstName"].ToString();
newStatus.Borrower.LastName = dr["LastName"].ToString();
newStatus.DVDId = int.Parse(dr["DVDID"].ToString());
newStatus.DateBorrowed = DateTime.Parse(dr["DateBorrowed"].ToString());
if (dr["DateReturned"] != DBNull.Value)
{
newStatus.DateReturned = DateTime.Parse(dr["DateReturned"].ToString());
}
newStatus.MovieId = int.Parse(dr["MovieID"].ToString());
newStatus.MovieTitle = dr["MovieTitle"].ToString();
b.ListOfStatuses.Add(newStatus);
}
}
b.MoviesCheckedOut = b.ListOfStatuses.Count(s => s.DateReturned == null);
cn.Close();
}
}
return listOfBorrowers;
}
//Return a list of Borrowers Names in the DB and their BorrowerID (smaller scale for select list item)
public List<Borrower> RetrieveSmallListOfBorrowers()
{
List<Borrower> listOfBorrowers = new List<Borrower>();
using (SqlConnection cn = new SqlConnection(Settings.ConnectionString))
{
SqlCommand cmd = new SqlCommand();
//Get Owner Name first!!
cmd.CommandText = "select FirstName, LastName, BorrowerID, IsOwner from Borrowers " +
"where IsOwner = 1";
cmd.Connection = cn;
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
var newBorrower = new Borrower();
newBorrower.FirstName = dr["FirstName"].ToString();
newBorrower.LastName = dr["LastName"].ToString();
newBorrower.BorrowerId = int.Parse(dr["BorrowerID"].ToString());
newBorrower.IsOwner = bool.Parse(dr["IsOwner"].ToString());
listOfBorrowers.Add(newBorrower);
}
}
cn.Close();
//Get Rest of Users and order by First Name
List<Borrower> nonOwnerBorrowers = new List<Borrower>();
cmd.CommandText = "select FirstName, LastName, BorrowerID, IsOwner from Borrowers " +
"where IsOwner = 0";
cmd.Connection = cn;
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
var newBorrower = new Borrower();
newBorrower.FirstName = dr["FirstName"].ToString();
newBorrower.LastName = dr["LastName"].ToString();
newBorrower.BorrowerId = int.Parse(dr["BorrowerID"].ToString());
newBorrower.IsOwner = bool.Parse(dr["IsOwner"].ToString());
nonOwnerBorrowers.Add(newBorrower);
}
}
cn.Close();
var ordered = nonOwnerBorrowers.OrderBy(b => b.FirstName);
listOfBorrowers.AddRange(ordered);
}
return listOfBorrowers;
}
public Status()
{
Borrower = new Borrower();
}
public AddBorrowerVM()
{
Borrower = new Borrower();
ListOfStates = new List<SelectListItem>()
{
new SelectListItem()
{
Text = "AK",
Value = "AK"
},
new SelectListItem()
{
Text = "AL",
Value = "AL"
},
new SelectListItem()
{
Text = "AR",
Value = "AR"
},
new SelectListItem()
{
Text = "AZ",
Value = "AZ"
},
new SelectListItem()
{
Text = "CA",
Value = "CA"
},
new SelectListItem()
{
Text = "CO",
Value = "CO"
},
new SelectListItem()
{
Text = "CT",
Value = "CT"
},
new SelectListItem()
{
Text = "DC",
Value = "DC"
},
new SelectListItem()
{
Text = "DE",
Value = "DE"
},
new SelectListItem()
{
Text = "FL",
Value = "FL"
},
new SelectListItem()
{
Text = "GA",
Value = "GA"
},
new SelectListItem()
{
Text = "HI",
Value = "HI"
},
new SelectListItem()
{
Text = "IA",
Value = "IA"
},
new SelectListItem()
{
Text = "ID",
Value = "ID"
},
new SelectListItem()
{
Text = "IL",
Value = "IL"
},
new SelectListItem()
{
Text = "IN",
Value = "IN"
},
new SelectListItem()
{
Text = "KS",
Value = "KS"
},
new SelectListItem()
{
Text = "KY",
Value = "KY"
},
new SelectListItem()
{
Text = "LA",
Value = "LA"
},
new SelectListItem()
{
Text = "MA",
Value = "MA"
},
new SelectListItem()
{
Text = "MD",
Value = "MD"
},
new SelectListItem()
{
Text = "ME",
Value = "ME"
},
new SelectListItem()
{
Text = "MI",
Value = "MI"
},
new SelectListItem()
{
Text = "MN",
Value = "MN"
},
new SelectListItem()
{
Text = "MO",
Value = "MO"
},
new SelectListItem()
{
Text = "MS",
Value = "MS"
},
new SelectListItem()
{
Text = "MT",
Value = "MT"
},
new SelectListItem()
{
Text = "NC",
Value = "NC"
},
new SelectListItem()
{
Text = "ND",
Value = "ND"
},
new SelectListItem()
{
Text = "NE",
Value = "NE"
},
new SelectListItem()
{
Text = "NY",
Value = "NY"
},
new SelectListItem()
{
Text = "OH",
Value = "OH"
},
new SelectListItem()
{
Text = "OK",
Value = "OK"
},
new SelectListItem()
{
Text = "OR",
Value = "OR"
},
new SelectListItem()
{
Text = "PA",
Value = "PA"
},
new SelectListItem()
{
Text = "RI",
Value = "RI"
},
new SelectListItem()
{
Text = "SC",
Value = "SC"
},
new SelectListItem()
{
Text = "SD",
Value = "SD"
},
new SelectListItem()
{
Text = "TN",
Value = "TN"
},
new SelectListItem()
{
Text = "TX",
Value = "TX"
},
new SelectListItem()
{
Text = "UT",
Value = "UT"
},
new SelectListItem()
{
Text = "VA",
Value = "VA"
},
new SelectListItem()
{
Text = "VT",
Value = "VT"
},
new SelectListItem()
{
Text = "WA",
Value = "WA"
},
new SelectListItem()
{
Text = "WI",
Value = "WI"
},
new SelectListItem()
{
Text = "WV",
Value = "WV"
},
new SelectListItem()
{
Text = "WY",
Value = "WY"
},
};
}
public RentalInfo()
{
Movie = new MovieInfo();
Borrower = new Borrower();
}
