private void DeleteCustomer_Click(object sender, EventArgs e)
{
if (NetworkingHelpers.GetUserRentedMovies(CustomerRowID) > 0)
{
DisplayError("This Customer has rented movies!");
}
else
{
// Delete the customer from list
using (SqlConnection conn = new SqlConnection(NetworkingHelpers.HostConfig()))
{
// Open Connection
conn.Open();
string query = "DELETE FROM Customers WHERE CustomerID=@custID";
SqlCommand _cmd = new SqlCommand(query, conn);
_cmd.Parameters.AddWithValue("@custID", CustomerRowID);
_cmd.ExecuteNonQuery();
}
// Update the list
GetCustomersList();
ResetCustomersField();
}
}
private void CustomerUsername_TextChanged(object sender, EventArgs e)
{
// Verify Username in Real-time
using (SqlConnection conn = new SqlConnection(NetworkingHelpers.HostConfig()))
{
using (SqlCommand _cmd = new SqlCommand("SELECT * FROM Customers WHERE Username=@user", conn))
{
try
{
conn.Open();
_cmd.Parameters.AddWithValue("@user", CustomerUsername.Text.ToString());
object count = _cmd.ExecuteScalar();
if (count != null)
{
UsernameExists = true;
}
else
{
UsernameExists = false;
}
Error(UsernameExists.ToString());
}
catch (Exception err)
{
throw err;
}
}
}
}
private void ReturnMovie_Click(object sender, EventArgs e)
{
NetworkingHelpers.DeleteFromTable("Rented", "RentalID", RentalRawID.ToString());
using (SqlConnection conn = new SqlConnection(NetworkingHelpers.HostConfig()))
{
// Open Connection
conn.Open();
// Execute Query
using (SqlCommand _cmd = new SqlCommand("UPDATE Movies SET MovieCopies=@copies WHERE MovieID=@id", conn))
{
int copies = 0;
SqlCommand _getCopies = new SqlCommand("SELECT MovieCopies FROM Movies WHERE MovieID=@id", conn);
_getCopies.Parameters.AddWithValue("@id", MovieRowID);
SqlDataReader reader = _getCopies.ExecuteReader();
while (reader.Read())
{
copies = Convert.ToInt32(reader["MovieCopies"]);
}
reader.Close();
_cmd.Parameters.AddWithValue("@copies", copies + 1);
_cmd.Parameters.AddWithValue("@id", MovieRowID);
_cmd.ExecuteNonQuery();
}
// Update Tables to keep the table data in sync with database
GetMoviesList();
GetRentingList();
}
}
/*********************************************************************************/
// RENTED MOVIES TAB
private void IssueMovie_Click(object sender, EventArgs e)
{
if (CustomerRowID > 0 && MovieRowID > 0)
{
using (SqlConnection conn = new SqlConnection(NetworkingHelpers.HostConfig()))
{
// Open Connection
conn.Open();
// Only executes if there are copies of movies available
// NOTE: For Production Purpose - This is not a very good way to do this
if (MovieCopiesField.Value > 0)
{ // Update Movies table to decrease available copies count
using (SqlCommand _cmd = new SqlCommand("UPDATE Movies SET MovieCopies=@copies WHERE MovieID=@id", conn))
{
_cmd.Parameters.AddWithValue("@copies", Convert.ToInt32(MovieCopiesField.Value - 1));
_cmd.Parameters.AddWithValue("@id", MovieRowID);
_cmd.ExecuteNonQuery();
}
// Add Movie to Rented Movies table
using (SqlCommand _cmd = new SqlCommand("INSERT INTO Rented (CustomerID, Username, MovieID, RentFrom, RentTill) VALUES(@uid, @user, @id, @from, @till)", conn))
{
_cmd.Parameters.AddWithValue("@uid", CustomerRowID);
_cmd.Parameters.AddWithValue("@user", CustomerUsername.Text);
_cmd.Parameters.AddWithValue("@id", MovieRowID);
_cmd.Parameters.AddWithValue("@from", rentingPeriodStart.Value);
_cmd.Parameters.AddWithValue("@till", rentingPeriodEnd.Value);
_cmd.ExecuteNonQuery();
}
// Display Pop-up message to let user know total days the customer has rented movie for
TimeSpan time = rentingPeriodEnd.Value.Subtract(rentingPeriodStart.Value);
// This message will let user of the app know, which customer has hired which movie for how many days
DisplayError(CustomerUsername.Text + " has rented " + MovieTitleField.Text + " for " + Convert.ToInt32(time.TotalDays));
// Update the tables to keep data in sync with the database
GetMoviesList();
GetRentingList();
ResetRentingFields();
}
else
{
DisplayError("All copies of " + MovieTitleField.Text + " are rented!");
}
}
}
else
{
if (CustomerRowID <= 0)
{
DisplayError("Please Select the customer!");
}
else
{
DisplayError("Please Select the Movie!");
}
}
}
private void DeleteMovie_Click(object sender, EventArgs e)
{
if (NetworkingHelpers.GetMovieOnRent(MovieRowID) > 0)
{
DisplayError("Copy of this movie is Currently on Rent!");
}
else
{
NetworkingHelpers.DeleteFromTable("Movies", "MovieID", MovieRowID.ToString());
GetMoviesList();
}
}
private void Add_ModifyMovie_Click(object sender, EventArgs e)
{
if (MovieTitleField.Text == "" || MovieRentingCostField.Text == "")
{
MessageBox.Show("One or More fields are Empty!");
}
else
{
if (MovieAdd.Checked)
{
string columns = "(MovieTitle, MovieReleaseDate, MovieRatings, MovieCopies, MovieRatingCost, MovieGenre)";
string values = "(@title, @date, @ratings, @copies, @rentingCost, @genre)";
string[] fieldNames = { "@title", "@date", "@ratings", "@copies", "@rentingCost", "@genre" };
using (SqlConnection conn = new SqlConnection(NetworkingHelpers.HostConfig()))
{
// Open Connection
conn.Open();
SqlCommand _cmd = new SqlCommand("INSERT INTO Movies " + columns + " VALUES " + values, conn);
_cmd.Parameters.AddWithValue("@title", MovieTitleField.Text);
_cmd.Parameters.AddWithValue("@date", MovieRelease.Value);
_cmd.Parameters.AddWithValue("@ratings", MovieRatingsField.Value);
_cmd.Parameters.AddWithValue("@copies", MovieCopiesField.Value);
_cmd.Parameters.AddWithValue("@rentingCost", MovieRentingCostField.Text);
_cmd.Parameters.AddWithValue("@genre", MovieGenreField.SelectedItem.ToString());
_cmd.ExecuteNonQuery();
}
}
else
{
string values = "MovieTitle=@title, MovieReleaseDate=@date, MovieRatings=@ratings, MovieCopies=@copies, MovieRatingCost=@rentingCost, MovieGenre=@genre";
using (SqlConnection conn = new SqlConnection(NetworkingHelpers.HostConfig()))
{
// Open Connection
conn.Open();
SqlCommand _cmd = new SqlCommand("UPDATE Movies SET " + values + " WHERE MovieID=@id", conn);
_cmd.Parameters.AddWithValue("@title", MovieTitleField.Text);
_cmd.Parameters.AddWithValue("@date", MovieRelease.Value);
_cmd.Parameters.AddWithValue("@ratings", MovieRatingsField.Value);
_cmd.Parameters.AddWithValue("@copies", MovieCopiesField.Value);
_cmd.Parameters.AddWithValue("@rentingCost", MovieRentingCostField.Text);
_cmd.Parameters.AddWithValue("@genre", MovieGenreField.SelectedItem.ToString());
_cmd.Parameters.AddWithValue("@id", MovieRowID);
_cmd.ExecuteNonQuery();
}
}
GetMoviesList();
ResetMovieFields();
}
}
// Add to DataGridView
public static void AddToDataGridView(string table, DataGridView view, MetroFramework.Controls.MetroTile tile)
{
using (SqlConnection conn = new SqlConnection(NetworkingHelpers.HostConfig()))
{
// Establish Connection
conn.Open();
SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM " + table, conn);
DataTable dataTable = new DataTable();
dataTable.Clear();
adapter.Fill(dataTable);
view.AutoGenerateColumns = false;
view.DataSource = dataTable;
if (table == "Customers")
{
if (view.Rows.Count - 1 > 0)
{
tile.Text = (view.Rows.Count - 1).ToString() + " Registered Customer(s)";
}
else
{
tile.Text = "0 Registered Customers";
}
}
else if (table == "Movies")
{
if (view.Rows.Count - 1 > 0)
{
tile.Text = (view.Rows.Count - 1).ToString() + " Movie(s) in Store";
}
else
{
tile.Text = "0 Movies in Store";
}
}
else
{
if (view.Rows.Count - 1 > 0)
{
tile.Text = (view.Rows.Count - 1).ToString() + " Movie(s) Rented";
}
else
{
tile.Text = "0 Movies Rented";
}
}
}
}
// This function can be used to add items to any table
public static void AddToTable(string TableName, string columns, string values, string[] inputs, string[] fieldValues)
{
using (SqlConnection conn = new SqlConnection(NetworkingHelpers.HostConfig()))
{
// Open Connection
conn.Open();
SqlCommand _cmd = new SqlCommand("INSERT INTO " + TableName + columns + " VALUES " + values, conn);
for (int index = 0; index < inputs.Length; index++)
{
_cmd.Parameters.AddWithValue(inputs[index], fieldValues[index]);
}
_cmd.ExecuteNonQuery();
}
}
private void DeleteCustomer_Click(object sender, EventArgs e)
{
if (NetworkingHelpers.GetUserRentedMovies(CustomerRowID) > 0)
{
DisplayError("This Customer has rented movies!");
}
else
{
// Delete the customer from list
NetworkingHelpers.DeleteFromTable("Customers", "CustomerID", CustomerRowID.ToString());
// Update the list
GetCustomersList();
ResetCustomersField();
}
}
// Add to DataGridView
public static void AddToDataGridView(string table, DataGridView view, MetroFramework.Controls.MetroTile tile)
{
using (SqlConnection conn = new SqlConnection(NetworkingHelpers.HostConfig()))
{
// Establish Connection
conn.Open();
SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM " + table, conn);
DataTable dataTable = new DataTable();
dataTable.Clear();
adapter.Fill(dataTable);
view.AutoGenerateColumns = false;
view.DataSource = dataTable;
}
}
/*********************************************************************************/
// CUSTOMERS TAB
private void AddCustomer_Click(object sender, EventArgs e)
{
if (CustomerUsername.Text == "" || CustomerFirstName.Text == "" || CustomerLastName.Text == "" || CustomerAddress.Text == "" || CustomerPhone.Text == "")
{
MessageBox.Show("One or More fields are Empty!");
}
else
{
if (addNewCustomer.Checked)
{
if (!UsernameExists)
{
// Add new User Query
string[] fieldNames = { "@Username", "@FirstName", "@LastName", "@Address", "@PhoneNumber" };
string[] fieldValues = { CustomerUsername.Text.ToString(), CustomerFirstName.Text.ToString(), CustomerLastName.Text.ToString(), CustomerAddress.Text.ToString(), CustomerPhone.Text.ToString() };
// Function to add new entries to table
NetworkingHelpers.AddToTable("Customers", "(Username, FirstName, LastName, Address, PhoneNumber)", "(@Username, @FirstName, @LastName, @Address, @PhoneNumber)", fieldNames, fieldValues);
MessageBox.Show(CustomerUsername.Text + " has been added successfully!");
GetCustomersList();
}
else
{
DisplayError("Username Already Exists!");
}
}
else
{
// Update User Query
string[] fieldNames = { "@user", "@firstName", "@lastName", "@address", "@phone" };
string[] fieldValues = { CustomerUsername.Text, CustomerFirstName.Text, CustomerLastName.Text, CustomerAddress.Text, CustomerPhone.Text };
NetworkingHelpers.UpdateTable("Customers", "Username=@user, FirstName=@firstName, LastName=@lastName, Address=@address, PhoneNumber=@phone", "CustomerID=" + CustomerRowID.ToString(), fieldNames, fieldValues);
GetCustomersList();
}
ResetCustomersField();
}
}
private void DeleteMovie_Click(object sender, EventArgs e)
{
if (NetworkingHelpers.GetMovieOnRent(MovieRowID) > 0)
{
DisplayError("Copy of this movie is Currently on Rent!");
}
else
{
// Delete the customer from list
using (SqlConnection conn = new SqlConnection(NetworkingHelpers.HostConfig()))
{
// Open Connection
conn.Open();
string query = "DELETE FROM Movies WHERE MovieID=@movID";
SqlCommand _cmd = new SqlCommand(query, conn);
_cmd.Parameters.AddWithValue("@movID", MovieRowID);
_cmd.ExecuteNonQuery();
}
GetMoviesList();
}
}
private void GetCustomersList()
{
NetworkingHelpers.AddToDataGridView("Customers", dataGridView1, null);
}
private void GetRentingList()
{
NetworkingHelpers.AddToDataGridView("Rented", dataGridView2, RentedMoviesTile);
}
private void GetRentingList()
{
NetworkingHelpers.AddToDataGridView("Rented", dataGridView2, null);
}
/*********************************************************************************/
// CUSTOMERS TAB
private void AddCustomer_Click(object sender, EventArgs e)
{
if (CustomerUsername.Text == "" || CustomerFirstName.Text == "" || CustomerLastName.Text == "" || CustomerAddress.Text == "" || CustomerPhone.Text == "")
{
MessageBox.Show("One or More fields are Empty!");
}
else
{
if (addNewCustomer.Checked)
{
if (!UsernameExists)
{
// Add new User Query
string[] fieldNames = { "@Username", "@FirstName", "@LastName", "@Address", "@PhoneNumber" };
string[] fieldValues = { CustomerUsername.Text.ToString(), CustomerFirstName.Text.ToString(), CustomerLastName.Text.ToString(), CustomerAddress.Text.ToString(), CustomerPhone.Text.ToString() };
// Function to add new entries to table
using (SqlConnection conn = new SqlConnection(NetworkingHelpers.HostConfig()))
{
// Open Connection
conn.Open();
SqlCommand _cmd = new SqlCommand("INSERT INTO Customers (Username, FirstName, LastName, Address, PhoneNumber) VALUES (@Username, @FirstName, @LastName, @Address, @PhoneNumber)", conn);
for (int index = 0; index < fieldNames.Length; index++)
{
_cmd.Parameters.AddWithValue(fieldNames[index], fieldValues[index]);
}
_cmd.ExecuteNonQuery();
}
MessageBox.Show(CustomerUsername.Text + " has been added successfully!");
GetCustomersList();
}
else
{
DisplayError("Username Already Exists!");
}
}
else
{
// Update User Query
string[] fieldNames = { "@user", "@firstName", "@lastName", "@address", "@phone", "@cid" };
string[] fieldValues = { CustomerUsername.Text, CustomerFirstName.Text, CustomerLastName.Text, CustomerAddress.Text, CustomerPhone.Text, CustomerRowID.ToString() };
using (SqlConnection conn = new SqlConnection(NetworkingHelpers.HostConfig()))
{
// Open Connection
conn.Open();
string query = "UPDATE Customers SET Username=@user, FirstName=@firstName, LastName=@lastName, Address=@address, PhoneNumber=@phone WHERE CustomerID=@cid";
SqlCommand _cmd = new SqlCommand(query, conn);
for (int index = 0; index < fieldNames.Length; index++)
{
_cmd.Parameters.AddWithValue(fieldNames[index], fieldValues[index]);
}
_cmd.ExecuteNonQuery();
}
GetCustomersList();
}
ResetCustomersField();
}
}
/*********************************************************************************/
// MOVIES TAB
private void GetMoviesList()
{
NetworkingHelpers.AddToDataGridView("Movies", dataGridView3, null);
}
