private void loadMovies()
{
CategoryID a;
string SQL = "SELECT category.`category_id`,category.`name` FROM category";
try
{
cmd.CommandText = SQL;
reader = cmd.ExecuteReader();
ListMovies.Clear();
//lblEmployee.Text = "";
while (reader.Read())
{
a = new CategoryID(reader[0].ToString(),
//boroume omoios na grapsoume reader[1] bla bla
reader["name"].ToString());
ListMovies.Add(a);
}
cbxCategories.SelectedIndexChanged -= new EventHandler(cbxCategories_SelectedIndexChanged);
bind(cbxCategories);
cbxCategories.SelectedIndex = -1;
cbxCategories.Text = "Select Category ... ";
// lblEmployee.Text = "";
cbxCategories.SelectedIndexChanged += new EventHandler(cbxCategories_SelectedIndexChanged);
if (reader != null)
{
reader.Close();
}
}
catch (Exception ex) { MessageBox.Show(ex.Message); }
finally
{
//reader?.Close(); //if (reader != null) reader.Close();
}
}
private void ShowFilms()
{
int categoryID;
string sWHERE = string.Empty;
try
{
SQL = "SELECT" + Environment.NewLine +
"`film`.`film_id` AS `FID`," + Environment.NewLine +
"`film`.`title` AS `title`," + Environment.NewLine +
"`category`.`name` AS `category`," + Environment.NewLine +
"`film`.`length` AS `length`," + Environment.NewLine +
"`film`.`rating` AS `rating`," + Environment.NewLine +
"`film`.`rental_rate` AS `price`," + Environment.NewLine +
"film.`special_features` AS special_features," + Environment.NewLine +
"film.`rental_duration` AS rental_duration," + Environment.NewLine +
"film.`replacement_cost` AS replacement_cost," + Environment.NewLine +
"`film`.`description` AS `description`" + Environment.NewLine +
"FROM category" + Environment.NewLine +
"LEFT JOIN film_category ON category.`category_id`= film_category.`category_id`" + Environment.NewLine +
"LEFT JOIN film ON film_category.`film_id`= film.`film_id`" + Environment.NewLine;
sWHERE = " Where film.`title` LIKE " + My.Quote(txtMovieName.Text + "%");
if (cbxCategories.SelectedIndex != -1)
{
CategoryID a = (CategoryID)cbxCategories.SelectedItem;
categoryID = Convert.ToInt32(a.id);
sWHERE += " AND category.`category_id`=" + categoryID;
}
if (txtMovieName.Text != string.Empty || cbxCategories.SelectedIndex != -1)
{
SQL += Environment.NewLine + sWHERE;
}
cmd.CommandText = SQL;
reader = cmd.ExecuteReader();
tblMovies.Rows.Clear();
tblMovies.Load(reader);
dvgMovies.DataSource = tblMovies;
lblMovies.Text = "Movies Found: " + tblMovies.Rows.Count.ToString();
if (reader != null)
{
reader.Close();
}
}
catch (Exception ex) { MessageBox.Show(ex.ToString()); }
finally
{
//reader?.Close();
}
}
private void ShowPayments()
{
string from, to;
string sWHERE = string.Empty;
int storeID;
try
{
from = dtpFrom.Value.ToString("yyyy-MM-dd");
to = dtpTo.Value.ToString("yyyy-MM-dd");
SQL = "SELECT payment.`payment_id`,CONCAT(customer.`first_name`,' ', customer.`last_name`) AS Customer,customer.`email`,payment.`amount`,payment.`payment_date`, address.`address` AS StoreAddress" + Environment.NewLine +
"FROM payment" + Environment.NewLine +
"JOIN customer ON payment.`customer_id`=customer.`customer_id`" + Environment.NewLine +
"JOIN store ON customer.`store_id`=store.`store_id`" + Environment.NewLine +
"JOIN address ON store.`address_id`=address.`address_id`" + Environment.NewLine;
sWHERE = "WHERE payment.`payment_date` BETWEEN '" + from + "' AND '" + to + "'";
sWHERE += " AND customer.`last_name` LIKE '%" + txtCustomerLName.Text + "%'";
if (cbxStore.SelectedIndex != -1)
{
CategoryID a = (CategoryID)cbxStore.SelectedItem;
storeID = Convert.ToInt32(a.id);
sWHERE += " AND store.`store_id`=" + storeID;
}
SQL += sWHERE;
cmd.CommandText = SQL;
reader = cmd.ExecuteReader();
tblPayments.Rows.Clear();
tblPayments.Load(reader);
dvgPayments.DataSource = tblPayments;
lblPayments.Text = "Payments Found: " + tblPayments.Rows.Count.ToString();
if (reader != null)
{
reader.Close();
}
}
catch (Exception ex) { MessageBox.Show(ex.ToString()); }
finally
{
//reader?.Close();
}
}
private void loadStores()
{
CategoryID a;
string SQL = "SELECT store.`store_id`,address.`address`" + Environment.NewLine +
"FROM store" + Environment.NewLine +
"LEFT JOIN address ON store.`address_id`= address.`address_id`;";
try
{
cmd.CommandText = SQL;
reader = cmd.ExecuteReader();
storeList.Clear();
//lblEmployee.Text = "";
while (reader.Read())
{
a = new CategoryID(reader[0].ToString(),
//boroume omoios na grapsoume reader[1] bla bla
reader["address"].ToString());
storeList.Add(a);
}
cbxStore.SelectedIndexChanged -= new EventHandler(cbxStore_SelectedIndexChanged);
bind(cbxStore);
cbxStore.SelectedIndex = -1;
cbxStore.Text = "Select Store ... ";
// lblEmployee.Text = "";
cbxStore.SelectedIndexChanged += new EventHandler(cbxStore_SelectedIndexChanged);
if (reader != null)
{
reader.Close();
}
}
catch (Exception ex) { MessageBox.Show(ex.Message); }
finally
{
//reader?.Close(); //if (reader != null) reader.Close();
}
}