private void populateUserList()
{
cbEmployee.Items.Clear();
SqlConnection conn = null;
SqlDataReader rdr = null;
try
{
conn = new SqlConnection(MovieListing.getConnString());
conn.Open();
SqlCommand cmd = new SqlCommand("sp_showAllUsers", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.ExecuteNonQuery();
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
id = (rdr["Employee_ID"].ToString());
name = (rdr["Name"].ToString());
idName = id + name;
cbEmployee.Items.Add(idName);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void btnRemove_Click(object sender, EventArgs e)
{
if (cbEmployee.SelectedIndex > 0)
{
DialogResult dialogResult = MessageBox.Show("Remove User " + cbEmployee.SelectedItem.ToString() + "!", "Confirm", MessageBoxButtons.YesNo);
if (dialogResult == DialogResult.Yes)
{
idName = new string(idName.Where(x => char.IsDigit(x)).ToArray());
try
{
SqlCommand cmd = new SqlCommand("sp_removeUser1", MovieListing.useConnection());
cmd.Parameters.Add(new SqlParameter("@employeeId", idName));
cmd.CommandType = CommandType.StoredProcedure;
cmd.ExecuteNonQuery();
MovieListing.useConnection().Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
populateUserList();
}
}
else
{
MessageBox.Show("Please select User.");
}
}
/* HELPER MTHOD TO INSERT IMAGE INTO THE DATABASE TABLE* {PASS IN IMAGE PATH}*/
private void getUsedSession()
{
// SqlConnection conn = null;
SqlDataReader rdr = null;
string unfixedTime;
string auditorium;
string fixedTime;
string usedTimeAuditorium;
try
{
//conn = new SqlConnection(connString);
// conn.Open();
SqlCommand cmd = new SqlCommand("sp_getUsedSession1", MovieListing.useConnection());
cmd.CommandType = CommandType.StoredProcedure;
cmd.ExecuteNonQuery();
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
auditorium = (rdr["Auditorium_ID"].ToString());
unfixedTime = (rdr["Time"].ToString());
fixedTime = unfixedTime.Substring(0, 5);
usedTimeAuditorium = auditorium + fixedTime;
sessions.Add(usedTimeAuditorium);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void addUser()
{
SqlConnection conn = null;
try
{
conn = new SqlConnection(MovieListing.getConnString());
SqlCommand cmd = new SqlCommand("sp_addUser2", conn);
cmd.CommandType = CommandType.StoredProcedure;
conn.Open();
cmd.Parameters.Add(new SqlParameter("@username", LoginName));
cmd.Parameters.Add(new SqlParameter("@password", password));
cmd.Parameters.Add(new SqlParameter("@userType", privelege));
cmd.Parameters.Add(new SqlParameter("@name", name));
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
}
public string login()
{
//SqlConnection conn = null;
SqlDataReader rdr = null;
string result = null;
try
{
//conn = new SqlConnection(MovieListing.getConnString());
// conn.Open();
SqlCommand cmd = new SqlCommand("sp_logIn4", MovieListing.useConnection());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@username", tbUserName.Text));
cmd.Parameters.Add(new SqlParameter("@password", tbPassword.Text));
var outParam = new SqlParameter("@result", SqlDbType.VarChar, -1);
var outParam2 = new SqlParameter("@result2", SqlDbType.VarChar, -1);
var outParam3 = new SqlParameter("@result3", SqlDbType.VarChar, -1);
var outParam4 = new SqlParameter("@result4", SqlDbType.VarChar, -1);
outParam.Direction = ParameterDirection.Output;
outParam2.Direction = ParameterDirection.Output;
outParam3.Direction = ParameterDirection.Output;
outParam4.Direction = ParameterDirection.Output;
cmd.Parameters.Add(outParam);
cmd.Parameters.Add(outParam2);
cmd.Parameters.Add(outParam3);
cmd.Parameters.Add(outParam4);
cmd.ExecuteNonQuery();
rdr = cmd.ExecuteReader();
if (cmd.Parameters["@result"].Value.ToString() == tbUserName.Text + " " + tbPassword.Text)
{
authenticate = true;
userName = cmd.Parameters["@result2"].Value.ToString();
userId = cmd.Parameters["@result3"].Value.ToString();
userPrivelege = cmd.Parameters["@result4"].Value.ToString();
}
else
{
MessageBox.Show("Error 001: Invalid Username Or Password");
authenticate = false;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
MovieListing.useConnection().Close();// Close Connection
return(result);
}
//decimal totalPrice;
// string quer = "select * from SeatPlan where SeatPlan_ID = ";
public SeatBooking()
{
InitializeComponent();
connect();
generateSeats();
displaySessionTime();
setTitle();
generateButtons();
picBoxLogo.Image = MovieListing.getImage();
// cmbSession.SelectedIndex = 0;
}
private void btnLogin_Click(object sender, EventArgs e)
{
login();
if (authenticate == true)
{
ActiveForm.Hide();
MovieListing ml = new MovieListing();
ml.ShowDialog();
}
}
public SeatBooking()
{
InitializeComponent();
getSession();
generateSeats();
sessionTimeAndScreen();
creatTicket();
generateButtons();
picBoxLogo.Image = MovieListing.getImage();
lblName.Text = Login.getUserName();
btnLogOf.BackgroundImage = Properties.Resources.logoff;
pictureBox2.BackgroundImage = Properties.Resources.PopcornLogo;
}
public void addSeatPlan()
{
using (MovieListing.useConnection())
{
SqlCommand cmd = new SqlCommand("Insert into SeatPLan(Auditorium_ID) values(@audit)", MovieListing.useConnection());
{
cmd.Parameters.AddWithValue("@audit", auditoriumID);
cmd.ExecuteNonQuery();
this.Close();
}
MovieListing.useConnection().Close();
}
}
public int getSeatPlanID()
{
using (MovieListing.useConnection())
{
SqlCommand cmd = new SqlCommand("Select Top 1 * from SeatPlan order by SeatPlan_ID desc", MovieListing.useConnection());
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
seatPlanID = Convert.ToInt32(reader["SeatPlan_ID"]);
}//End while
MovieListing.useConnection().Close();
}
return(seatPlanID);
}
public int getNumberOfSeats()
{
using (MovieListing.useConnection())
{
SqlCommand cmd = new SqlCommand("Select NumberOfSeats from Auditorium where Audtorium_ID =" + auditoriumID, MovieListing.useConnection());
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
numberOfSeats = Convert.ToInt32(reader["NumberOfSeats"]);
}//End while
MovieListing.useConnection().Close();
}
return(numberOfSeats);
}
/* METHODS TO GET DATA FROM DATABASE AND STORE IN THE INSTANCE VARIABLES*/
public int getMovie_ID()
{
using (MovieListing.useConnection())
{
SqlCommand cmd = new SqlCommand("Select Top 1 * from Movie order by Movie_ID desc", MovieListing.useConnection());
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
movie_ID = Convert.ToInt32(reader["Movie_ID"]);
}//End while
MovieListing.useConnection().Close();
}
insertPicture();
return(movie_ID);
}
private void updateSeats()
{
foreach (Seat i in seatList)
{
using (MovieListing.useConnection())
{
SqlCommand cmd = new SqlCommand("update seat set Reserved =@res where Seat_ID = @ID", MovieListing.useConnection());
{
cmd.Parameters.AddWithValue("@res", i.Reserved);
cmd.Parameters.AddWithValue("@id", i.ID);
cmd.ExecuteNonQuery();
MovieListing.useConnection().Close();;
}
}
}
}
public void insertPicture()
{
// SqlConnection conn = null;
// conn = new SqlConnection(connString);
try
{
SqlCommand cmd2 = new SqlCommand("sp_addMovie2", MovieListing.useConnection());
cmd2.CommandType = CommandType.StoredProcedure;
// conn.Open();
cmd2.Parameters.Add(new SqlParameter("@poster", imagePath));
cmd2.Parameters.Add(new SqlParameter("@id", movie_ID));
cmd2.ExecuteNonQuery();
MovieListing.useConnection().Close();
}
catch (Exception e) {
MessageBox.Show("Image could not be opened.");
}
}
public void generateSeats()
{
using (MovieListing.useConnection())
{
SqlCommand cmd = new SqlCommand("Select Seat.Seat_ID, Seat.Reserved, Seat.SeatPlan_ID, Seat.Number from(Session inner join Seat on Session.SeatPlan_ID =Seat.SeatPlan_ID and Session.Session_ID =" + MovieListing.SessionID + ")", MovieListing.useConnection());
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Seat seat = new Seat();
seat.ID = Convert.ToInt32(reader["Seat_ID"]);
seat.Reserved = Convert.ToBoolean(reader["Reserved"]);
seat.SeatPlanID = Convert.ToInt32(reader["SeatPlan_ID"]);
seat.SeatNumber = Convert.ToInt32(reader["Number"]);
seatList.Add(seat);
}//End while
MovieListing.useConnection().Close();
}
}
private void addUser()
{
try
{
SqlCommand cmd = new SqlCommand("sp_addUser2", MovieListing.useConnection());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@username", LoginName));
cmd.Parameters.Add(new SqlParameter("@password", password));
cmd.Parameters.Add(new SqlParameter("@userType", privelege));
cmd.Parameters.Add(new SqlParameter("@name", name));
cmd.ExecuteNonQuery();
MovieListing.useConnection().Close();
}
catch (Exception e)
{
MessageBox.Show("User Name Already Exist.");
}
}
// Connect to the database.
public void getSession()
{
using (MovieListing.useConnection())
{
SqlCommand cmd = new SqlCommand("Select * from Session where Session_ID = " + MovieListing.SessionID, MovieListing.useConnection());
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Session temp = new Session();
temp.SessionID = Convert.ToInt32(reader["Session_ID"]);
temp.Time = reader["Time"].ToString();
temp.MovieID = Convert.ToInt32(reader["Movie_ID"]);
temp.SeatPlan = Convert.ToInt32(reader["SeatPlan_ID"]);
temp.AuditoriumID = Convert.ToInt32(reader["Auditorium_ID"]);
sessionList.Add(temp);
}//End while
MovieListing.useConnection().Close();
//
}
}
public void addSeat()
{
for (int i = 0; i < getNumberOfSeats(); i++)
{
Seat seat = new Seat(seatPlanID, false, (i + 1));
try
{
SqlCommand cmd = new SqlCommand("sp_addSeat", MovieListing.useConnection());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@number", (i + 1)));
cmd.Parameters.Add(new SqlParameter("@reserved", false));
cmd.Parameters.Add(new SqlParameter("@planId", seatPlanID));
cmd.ExecuteNonQuery();
MovieListing.useConnection().Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
public void addSession(TimeSpan timeIn)
{
try
{
SqlCommand cmd = new SqlCommand("sp_addSession", MovieListing.useConnection());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@movieID", movie_ID));
cmd.Parameters.Add(new SqlParameter("@planID", seatPlanID));
cmd.Parameters.Add(new SqlParameter("@audit", auditoriumID));
cmd.Parameters.Add(new SqlParameter("@time", timeIn));
cmd.ExecuteNonQuery();
MovieListing.useConnection().Close();
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
}
/* NON QUERY SQL COMMANDS TO INSERT RECORDS INTO DATABASE TABLES BY PASSING VALUES FROM THE ADD MOVIE FORM*/
public void addMovie()
{
try
{
SqlCommand cmd = new SqlCommand("sp_addMovie6", MovieListing.useConnection());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@title", txtTitle.Text));
cmd.Parameters.Add(new SqlParameter("@genre", txtGenre.Text));
cmd.Parameters.Add(new SqlParameter("@runTime", txtRunTime.Text));
cmd.Parameters.Add(new SqlParameter("@ticketPrice", txtPrice.Text));
cmd.Parameters.Add(new SqlParameter("@timeShown1", time1));
cmd.Parameters.Add(new SqlParameter("@timeShown2", time2));
cmd.Parameters.Add(new SqlParameter("@timeShown3", time3));
cmd.ExecuteNonQuery();
MovieListing.useConnection().Close();
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
}
private void btnSubmit_Click_1(object sender, EventArgs e)
{
//SqlConnection conn = null;
SqlDataReader rdr = null;
if (txtTitle.Text != null && txtGenre.Text != null && txtRunTime.Text != null && txtPrice.Text != null &&
imagePath != null)
{
try
{
// conn = new SqlConnection(connectionString);
//conn.Open();
SqlCommand cmd = new SqlCommand("sp_editMovie1", MovieListing.useConnection());
cmd.Parameters.Add(new SqlParameter("@id", selectedMovieId));
cmd.Parameters.Add(new SqlParameter("@title", txtTitle.Text));
cmd.Parameters.Add(new SqlParameter("@genre", txtGenre.Text));
cmd.Parameters.Add(new SqlParameter("@runTime", txtRunTime.Text));
cmd.Parameters.Add(new SqlParameter("@ticketPrice", txtPrice.Text));
cmd.CommandType = CommandType.StoredProcedure;
cmd.ExecuteNonQuery();
rdr = cmd.ExecuteReader();
rdr.Close();
SqlCommand cmd2 = new SqlCommand("sp_addMovie2", MovieListing.useConnection());
cmd2.CommandType = CommandType.StoredProcedure;
cmd2.Parameters.Add(new SqlParameter("@poster", imagePath));
cmd2.Parameters.Add(new SqlParameter("@id", selectedMovieId));
cmd2.ExecuteNonQuery();
MovieListing.useConnection().Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
Close();
}
private void btnRemove_Click(object sender, EventArgs e)
{
idName = new string(idName.Where(x => char.IsDigit(x)).ToArray());
MessageBox.Show(idName);
SqlConnection conn = null;
try
{
conn = new SqlConnection(MovieListing.getConnString());
conn.Open();
SqlCommand cmd = new SqlCommand("sp_removeUser1", conn);
cmd.Parameters.Add(new SqlParameter("@employeeId", idName));
cmd.CommandType = CommandType.StoredProcedure;
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
populateUserList();
}
public void creatTicket()
{
using (MovieListing.useConnection())
{
SqlCommand cmd = new SqlCommand("Select * from Movie where Movie_ID = " + MovieListing.MovieID, MovieListing.useConnection());
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
movieTitle = reader["Title"].ToString();
runTime = reader["RunTime"].ToString();
price = Convert.ToDecimal(reader["Price"]);
}//End while
MovieListing.useConnection().Close();
foreach (Session i in sessionList)
{
lblScreen.Text = i.AuditoriumID.ToString();
lblDate.Text = today.ToShortDateString() + " " + i.Time;
}
}
lblTitle.Text = movieTitle;
lblRunTime.Text = runTime;
lblPrice.Text = price.ToString("F");
}
private void populateTextBoxes()
{
txtMovieID.ReadOnly = true;
tbPicturePath.ReadOnly = true;
SqlDataReader rdr = null;
string unfixedTime = null;
string fixedTime;
string unfixedCurrency = null;
string fixedCurrency;
try
{
SqlCommand cmd = new SqlCommand("sp_viewEditMovie", MovieListing.useConnection());
cmd.Parameters.Add(new SqlParameter("@id", selectedMovieId));
cmd.CommandType = CommandType.StoredProcedure;
cmd.ExecuteNonQuery();
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
txtTitle.Text = rdr["Title"].ToString();
txtGenre.Text = rdr["Genre"].ToString();
unfixedTime = rdr["RunTime"].ToString();
unfixedCurrency = rdr["Price"].ToString();
}
MovieListing.useConnection().Close();
fixedTime = unfixedTime.Substring(0, 8);
txtRunTime.Text = fixedTime;
fixedCurrency = unfixedCurrency.Substring(0, 2);
txtPrice.Text = fixedCurrency;
txtMovieID.Text = selectedMovieId;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}