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); } }