public int ReturnMediaAction(int mediaId, int uid, DateTime returnDate)
{
using (SqlConnection connection = new SqlConnection(CONNECTION_STRING))
{
try
{
// Define the SQL
String query = "SELECT UID, MediaID, BorrowDate, ReturnDate FROM TabBorrow WHERE (UID = @userID) AND (MediaID = @mediaID)";
SqlDataAdapter DataAdapter = new SqlDataAdapter();
DataSet UserDataSet = new DataSet();
//call the estisatemedReturndata function
DateTime DateMightReturn = EstimatedReturnDate(mediaId, uid);
// for each day late, user would have to pay $2
int PenaltyFee = 2 * (DateTime.Today - DateMightReturn.Date).Days;
if (DateTime.Today == DateMightReturn.Date)
{
PenaltyFee = 0;
}
connection.Open();
DataAdapter.SelectCommand = new SqlCommand(query, connection);
DataAdapter.SelectCommand.Parameters.Add("@MediaID", SqlDbType.Int).Value = mediaId;
DataAdapter.SelectCommand.Parameters.Add("@UID", SqlDbType.Int).Value = uid;
int RowCount = DataAdapter.Fill(UserDataSet, "returnList");
foreach (DataRow row in UserDataSet.Tables["returnList"].Rows)
{
ModelMediaDTO newMedia = new ModelMediaDTO(int.Parse(row[0].ToString()), int.Parse(row[1].ToString()), Convert.ToDateTime(row[2].ToString()), Convert.ToDateTime(row[3].ToString()));
}
// after a media has been returned, add it to returned media admin's list
// then delete it from borrow record;
return(PenaltyFee);
}
catch (Exception exception)
{
Console.WriteLine("Error: " + exception.Message);
return(0);
}
}
}
public List <MediaDTO> MediaSearcFunction(MediaDTO media)
{
// call the model reference
iSearchModel SearchEngine = new SearchMediaImpl();
// create a list of media
// all the variables will be taken from Controller DTOs
List <MediaDTO> ListOfSearch = new List <MediaDTO>();
ModelMediaDTO mediaDTOfromModel = new ModelMediaDTO(media.MediaID, media.Title, media.Genre, media.Director, media.Language, media.PublishYear, media.Budget);
List <ModelMediaDTO> ListOfMediaFromModel = SearchEngine.MediaSearch(mediaDTOfromModel);
foreach (ModelMediaDTO modelMedia in ListOfMediaFromModel)
{
ListOfSearch.Add(new MediaDTO(modelMedia.MediaID, modelMedia.Title, modelMedia.Genre, modelMedia.Director, modelMedia.Language, modelMedia.PublishYear, modelMedia.Budget));
}
return(ListOfSearch);
}
public List <ModelMediaDTO> MediaSearch(ModelMediaDTO modelMedia)
{
// 1. Create a connection
SqlConnection dbConnection = new SqlConnection(CONNECTION_STRING);
try
{
List <ModelMediaDTO> MediaSearchList = new List <ModelMediaDTO>();
//create query
String query = "SELECT * " +
"FROM TabMedia " +
"INNER JOIN TabDirector ON TabMedia.Director = TabDirector.DID " +
"INNER JOIN TabGenre ON TabMedia.Genre = TabGenre.GID " +
"INNER JOIN TabLanguage ON TabMedia.Language = TabLanguage.LID " +
"WHERE (TabMedia.Title LIKE '%' + @title + '%' OR @title = '') " +
"AND(TabGenre.GenreName LIKE '%' + @genre + '%' OR @genre = '') " +
"AND(TabDirector.DirectorName LIKE '%' + @dName + '%' OR @dName = '') " +
"AND(TabMedia.PublishYear = @year OR @year = 0) " +
"AND(TabLanguage.LanguageName LIKE '%' + @lang + '%' OR @lang = '')";
//Create new adapter
SqlDataAdapter DataAdapter = new SqlDataAdapter();
//Create empty dataset
Dataset UserDataset = new Dataset();
dbConnection.Open();
//insert insert command into the adapter
DataAdapter.SelectCommand = new SqlCommand(query, dbConnection);
DataAdapter.SelectCommand.Parameters.Add("@title", SqlDbType.VarChar).Value = modelMedia.Title;
DataAdapter.SelectCommand.Parameters.Add("@genre", SqlDbType.VarChar).Value = modelMedia.Genre;
DataAdapter.SelectCommand.Parameters.Add("@dName", SqlDbType.VarChar).Value = modelMedia.Director;
DataAdapter.SelectCommand.Parameters.Add("@year", SqlDbType.Int).Value = modelMedia.PublishYear;
DataAdapter.SelectCommand.Parameters.Add("@lang", SqlDbType.VarChar).Value = modelMedia.Language;
int RowCount = DataAdapter.Fill(UserDataset, "mediaSearch");
// if there's a row successfully inserted, return true and vice versa
if (RowCount > 0)
{
foreach (DataRow row in UserDataset.Tables["mediaSearch"].Rows)
{
MediaSearchList.Add(new ModelMediaDTO(int.Parse(row[0].ToString()), row[1].ToString(), row[10].ToString(), row[8].ToString(), row[12].ToString(), int.Parse(row[5].ToString()), decimal.Parse(row[6].ToString())));
}
return(MediaSearchList);
}
else
{
List <ModelMediaDTO> ListOfMedia = new List <ModelMediaDTO>();
Console.WriteLine("Errr while validation user: "******"Errr while validation user: " + ex.Message);
return(browseMedia);
}
}
