using Microsoft.VisualBasic;
using System;
using System.Data;
using System.Data.SqlClient;

namespace StrataFlix.Business
{

    /// <summary>
    /// This class contains queries that are used within the application as they do not belong
    /// to one specific business object but rather an application environment or gathering
    /// multiple result sets.
    /// </summary>
    /// <remarks></remarks>
	public sealed class ApplicationQueries
	{
		
		
		#region " Public Methods "
		
		/// <summary>
		/// Retrieves all of the recods that will be used within the Movie Maintenance editor
		/// </summary>
		/// <param name="moviePk"></param>
		/// <param name="includeMovieRecord"></param>
		/// <returns></returns>
		/// <remarks></remarks>
		public static SqlCommand MovieMaintenance_AllData(int moviePk, bool includeMovieRecord)
		{
			//-- Establish Locals
			SqlCommand cmd = new SqlCommand();
			
			//-- Determine if the movie record will be included
			if (includeMovieRecord)
			{
				cmd.CommandText = "SELECT Movies.* FROM Movies WHERE mv_pk = @moviePk;";
			}
			
			//-- Add the movie cast query
			cmd.CommandText += "SELECT MovieCast.*, People.pl_FirstName, People.pl_MiddleName, People.pl_LastName " + "FROM MovieCast LEFT JOIN People ON mc_pl_pk = pl_pk WHERE mc_mv_pk = @moviePk;";
			
			//-- Add the movie items query
			cmd.CommandText += "SELECT * FROM MovieItems WHERE mvi_mv_pk = @moviePk;";
			
			//-- Get the movie images
			cmd.CommandText += "SELECT * FROM MovieImages WHERE mi_mv_pk = @moviePk;";
			
			//-- Create the parms
			cmd.Parameters.AddWithValue("@moviePk", moviePk).SqlDbType = SqlDbType.Int;
			
			//-- Return the results
			return cmd;
		}
		
		/// <summary>
		/// Creates the query that will be used to retrieve the data for the people maintenance
		/// dialog.  This example shows how to use a stored procedure to retrieve the data
		/// versus the MovieMaintenance dialog that uses inline queries.
		/// </summary>
		/// <param name="peoplePk"></param>
		/// <param name="includePeopleRecord"></param>
		/// <returns></returns>
		/// <remarks></remarks>
		public static SqlCommand PeopleMaintenance_AllData(int peoplePk, bool includePeopleRecord)
		{
			//-- Establish Locals
			SqlCommand cmd = new SqlCommand("dbo.PeopleMaintenance_RetrieveAllData");
			
			//-- Create the parms
			cmd.Parameters.AddWithValue("@peoplePk", peoplePk).SqlDbType = SqlDbType.Int;
			cmd.Parameters.AddWithValue("@includePeople", includePeopleRecord).SqlDbType = SqlDbType.Bit;
			
			//-- Set the command type
			cmd.CommandType = CommandType.StoredProcedure;
			
			//-- Return results
			return cmd;
		}
		
		#endregion
		
	}
	
}
