// gets the directors name using the directors id
public static string FetchDirector(int id)
{
string sql = SqlProcedures.FetchDirector(id);
DataTable temp = Call(sql);
return(temp.Rows[0][0].ToString());
}
// gets the studio name using the studio id
public static string FetchProductionCompany(int id)
{
string sql = SqlProcedures.FetchProductionCompany(id);
DataTable temp = Call(sql);
return(temp.Rows[0][0].ToString());
}
// Get all genres that currently exist. This is used when a form is opened to populate the dropdown boxes.
public static List <string> RetrieveGenres()
{
DataTable temp = Call(SqlProcedures.RetrieveGenres());
List <string> genres = new List <string>();
for (int i = 0; i < temp.Rows.Count; i++)
{
genres.Add(temp.Rows[i][1].ToString());
}
return(genres);
}
// Edit the viewing with the 'viewingid'
public static bool ModifyViewing(int viewingid, int movieid, int customerid, DateTime viewedon)
{
try
{
Call(SqlProcedures.ModifyViewing(viewingid, movieid, customerid, viewedon));
return(true);
}
catch (Exception e)
{
MessageBox.Show(e.Message);
return(false);
}
}
/*
* // THIS METHOD IS NO LONGER USED AND IS INSTEAD IN THE REBUILD SCRIPT
*
* // This method runs all of the procedures in the procedures.txt file.
* public static void RerunAllProcedures()
* {
* using (StreamReader sr = new StreamReader("..\\..\\config\\procedures.txt"))
* {
* while (!sr.EndOfStream)
* {
* string line = sr.ReadLine();
* using (StreamReader sr2 = new StreamReader(line))
* {
* string sql = "";
* while (!sr2.EndOfStream)
* {
* string ln = sr2.ReadLine();
* if (ln.ToLower() == "go")
* {
* Run(sql, line);
* sql = "";
* }
* else
* {
* sql += ln + ' ';
* }
* }
* if (sql != "")
* Run(sql, line);
* }
* }
* }
*
* MessageBox.Show("All procedures successfully run");
* }
*/
// Add a movie to the database
public static bool AddMovie(string MovieName, int directorId, int studioId, string date, string rating, int length)
{
try
{
Call(SqlProcedures.AddMovie(MovieName, directorId, studioId, date, rating, length));
return(true);
}
catch (Exception e)
{
MessageBox.Show(e.Message);
return(false);
}
}
// Get all directors that currently exist. This is used when a form is opened to populate the dropdown boxes.
public static List <string> RetrieveDirectors()
{
DataTable temp = Call(SqlProcedures.RetrieveDirectors());
List <string> directors = new List <string>();
for (int i = 0; i < temp.Rows.Count; i++)
{
if (temp.Rows[i][1].ToString()[0] != '?')
{
directors.Add(temp.Rows[i][1].ToString());
}
}
return(directors);
}
// Edit the movie with 'movieid' with all of the other values
public static bool ModifyMovie(int MovieId, string MovieName, string director, string date, string studio, int length, string rating)
{
try
{
int directorId = GetDirector(director);
int studioId = GetStudio(studio);
Call(SqlProcedures.ModifyMovie(MovieId, MovieName, directorId, date, studioId, length, rating));
return(true);
}
catch (Exception e)
{
MessageBox.Show(e.Message);
return(false);
}
}
// Gets the viewing id based on other information to search by
public static int GetViewingId(string moviename, string customername, string categoryname, DateTime viewedon)
{
string sql = SqlProcedures.GetViewingsOnDateWithId(moviename, customername, categoryname, viewedon);
DataTable temp = Call(sql);
if (temp.Rows.Count > 0)
{
int id;
int.TryParse(temp.Rows[0][0].ToString(), out id);
return(id);
}
else
{
return(-1);
}
}
// Get a movie id by name
public static int GetMovieForViewing(string input)
{
string sql = SqlProcedures.GetMoviesForViewings(input);
DataTable temp = Call(sql);
if (temp.Rows.Count > 0)
{
int id;
int.TryParse(temp.Rows[0][0].ToString(), out id);
return(id);
}
else
{
return(-1);
}
}
// Find a customer by name and category
public static int SearchForCustomer(string customername, string categoryname)
{
string sql;
sql = SqlProcedures.GetCustomers(customername, categoryname);
DataTable temp = Call(sql);
if (temp.Rows.Count > 0)
{
int id;
int.TryParse(temp.Rows[0][0].ToString(), out id);
return(id);
}
else
{
return(-1);
}
}
// Search for a specific viewing with the given info
public static DataTable SearchForViewing(ViewingSearchType type, string moviename, string customername, string categoryname, DateTime viewedon)
{
string sql;
switch (type)
{
case ViewingSearchType.ExcludeDate:
{
sql = SqlProcedures.GetViewings(moviename, customername, categoryname);
return(Call(sql));
}
case ViewingSearchType.IncludeDate:
{
sql = SqlProcedures.GetViewingsOnDate(moviename, customername, categoryname, viewedon);
return(Call(sql));
}
}
return(null);
}
// Get a studios id by passing in a name
public static int GetStudio(string studio)
{
string sql = SqlProcedures.GetStudio(studio);
DataTable temp = Call(sql);
if (temp.Rows.Count == 0)
{
sql = SqlProcedures.CreateStudio(studio);
Run(sql, "Create Studio");
sql = SqlProcedures.GetStudio(studio);
temp = Call(sql);
}
if (temp.Rows.Count > 0)
{
int id;
int.TryParse(temp.Rows[0][0].ToString(), out id);
return(id);
}
else
{
return(-1);
}
}
// Gets the amount of money that every movie made and sorts them greatest to least
internal static object GetTopGrossingMovies()
{
string sql = SqlProcedures.GetTopGrossingMovies();
return(Call(sql));
}
// This function also searches for movies but is used to show them in a more user-friendly manner
public static DataTable SearchForMovieDisplay(MovieSearchType type, MoviesToShow moviesToShow, string input)
{
string sql;
if (input == "")
{
type = MovieSearchType.None;
}
if (moviesToShow == MoviesToShow.AllMovies)
{
switch (type)
{
case MovieSearchType.None:
{
sql = SqlProcedures.RetrieveMoviesDisplay();
return(Call(sql));
}
case MovieSearchType.MovieTitle:
{
sql = SqlProcedures.GetMoviesByTitleDisplay(input);
return(Call(sql));
}
case MovieSearchType.MovieGenre:
{
sql = SqlProcedures.GetMoviesByGenreDisplay(input);
return(Call(sql));
}
case MovieSearchType.MovieDirector:
{
sql = SqlProcedures.GetMoviesByDirectorDisplay(input);
return(Call(sql));
}
}
}
else
{
bool isRemoved = (moviesToShow == MoviesToShow.OnlyUnavailable);
switch (type)
{
case MovieSearchType.None:
{
sql = SqlProcedures.GetFilteredMoviesDisplay(isRemoved);
return(Call(sql));
}
case MovieSearchType.MovieTitle:
{
sql = SqlProcedures.GetFilteredMoviesByTitleDisplay(input, isRemoved);
return(Call(sql));
}
case MovieSearchType.MovieGenre:
{
sql = SqlProcedures.GetFilteredMoviesByGenreDisplay(input, isRemoved);
return(Call(sql));
}
case MovieSearchType.MovieDirector:
{
sql = SqlProcedures.GetFilteredMoviesByDirectorDisplay(input, isRemoved);
return(Call(sql));
}
}
}
return(null);
}
// Gets the highest customer count for a given day in a given month in a given year
internal static object GetHighestCustomerCountByDay(int day, int month, int year)
{
string sql = SqlProcedures.GetHighestCustomerCountByDay(day, month, year);
return(Call(sql));
}
// Gets the most popular agre group for each and every movie
internal static object GetMostPopularAgeGroupByMovie()
{
string sql = SqlProcedures.GetMostPopularAgeGroupByMovie();
return(Call(sql));
}
// Gets the total number of views for every movie and sorts them greatest to least
internal static object GetMostViewedMovies()
{
string sql = SqlProcedures.GetMostViewedMovies();
return(Call(sql));
}