public List <Salle> getListeSalles()
{
List <Salle> liste = null;
mySqlConn = new MySql_Connection();
string sql = "SELECT * FROM Salle;";
mySqlConn.openConnection();
MySqlDataReader dataReader = mySqlConn.selectCommande(sql);
try
{
if (dataReader != null)
{
liste = new List <Salle>();
}
while (dataReader.Read())
{
Salle v = new Salle();
v.id = dataReader.GetInt32(0);
v.nbPlaces = dataReader.GetInt32(1);
liste.Add(v);
}
}
finally
{
mySqlConn.closeConnection();
}
return(liste);
}
public Visiteur findVisiteurByEmail(string email)
{
Visiteur visiteur = null;
mySqlConn = new MySql_Connection();
string sql = "SELECT * FROM Visiteur WHERE email ='" + email.Replace("'", "''") + "';";
mySqlConn.openConnection();
MySqlDataReader dataReader = mySqlConn.selectCommande(sql);
try
{
if (dataReader.Read())
{
visiteur = new Visiteur();
visiteur.id = dataReader.GetInt32(0);
visiteur.nom = dataReader[1].ToString().ToUpper();
visiteur.prenom = char.ToUpper(dataReader[2].ToString().First()) + dataReader[2].ToString().Substring(1).ToLower();
visiteur.email = dataReader[3].ToString();
visiteur.motDePasse = dataReader[4].ToString();
}
}
finally
{
mySqlConn.closeConnection();
}
return(visiteur);
}
public List <string> getListeSeancesProjection()
{
List <string> liste = null;
mySqlConn = new MySql_Connection();
string sql = "SELECT distinct date FROM Visionnage_Salle where date >= CURDATE();";
mySqlConn.openConnection();
MySqlDataReader dataReader = mySqlConn.selectCommande(sql);
try
{
if (dataReader != null)
{
liste = new List <string>();
}
while (dataReader.Read())
{
string date = dataReader[0].ToString().Substring(0, 10);
liste.Add(date);
}
}
finally
{
mySqlConn.closeConnection();
}
return(liste);
}
public bool isPlacesDisponible(int id_film, string date, int id_salle)
{
bool is_existe = false;
string sql = "SELECT nbPlacesDispo FROM visionnage_salle WHERE date = '" + date + "' and id_visionnage = " + id_film + " and id_salle = " + id_salle;
mySqlConn = new MySql_Connection();
mySqlConn.openConnection();
MySqlDataReader dataReader = mySqlConn.selectCommande(sql);
try
{
if (dataReader.Read())
{
int count = dataReader.GetInt32(0);
if (count > 0)
{
is_existe = true;
}
}
}
finally
{
mySqlConn.closeConnection();
}
return(is_existe);
}
public bool isDejaInscrit(int id_film, int id_visiteur, string date)
{
bool is_existe = false;
string sql = "SELECT COUNT(*) FROM visionnage_visiteur WHERE date = '" + date + "' and id_visionnage = " + id_film + " and id_visiteur= " + id_visiteur;
mySqlConn = new MySql_Connection();
mySqlConn.openConnection();
MySqlDataReader dataReader = mySqlConn.selectCommande(sql);
try
{
if (dataReader.Read())
{
int count = dataReader.GetInt32(0);
if (count > 0)
{
is_existe = true;
}
}
}
finally
{
mySqlConn.closeConnection();
}
return(is_existe);
}
public Admin findAdminByEmail(string email)
{
Admin admin = null;
mySqlConn = new MySql_Connection();
string sql = "SELECT * FROM admin WHERE email ='" + email.Replace("'", "''") + "';";
mySqlConn.openConnection();
MySqlDataReader dataReader = mySqlConn.selectCommande(sql);
try
{
if (dataReader.Read())
{
admin = new Admin();
admin.id = dataReader.GetInt32(0);
admin.nom = dataReader[1].ToString().ToUpper();
admin.prenom = char.ToUpper(dataReader[2].ToString().First()) + dataReader[2].ToString().Substring(1).ToLower();
admin.email = dataReader[3].ToString();
admin.motDePasse = dataReader[4].ToString();
}
}
finally
{
mySqlConn.closeConnection();
}
return(admin);
}
public Salle findSalleById(int id)
{
Salle salle = null;
mySqlConn = new MySql_Connection();
string sql = "SELECT * FROM salle WHERE id = " + id;
mySqlConn.openConnection();
MySqlDataReader dataReader = mySqlConn.selectCommande(sql);
try
{
if (dataReader.Read())
{
salle = new Salle();
salle.id = dataReader.GetInt32(0);
salle.nbPlaces = dataReader.GetInt32(1);
}
}
finally
{
mySqlConn.closeConnection();
}
return(salle);
}
public bool ajouterIscriptionVisionnage(int id_film, int id_visiteur, string date_seance, int salle)
{
mySqlConn = new MySql_Connection();
int count = 0;
string sql = "INSERT INTO Visionnage_visiteur VALUES (" + id_film + "," + id_visiteur + ", '" + date_seance + "');";
string sql2 = "update visionnage_salle set nbPlacesDispo = nbPlacesDispo-1 where id_visionnage = " + id_film + " and date = '" + date_seance + "' and id_salle = " + salle + ";";
mySqlConn.openConnection();
count = mySqlConn.executeCommande(sql);
if (count > 0)
{
count = mySqlConn.executeCommande(sql2);
if (count > 0)
{
mySqlConn.closeConnection();
return(true);
}
else
{
mySqlConn.closeConnection();
return(false);
}
}
else
{
mySqlConn.closeConnection();
return(false);
}
}
public bool isSalleExiste(int id_salle)
{
bool is_existe = false;
string sql = "SELECT COUNT(*) FROM salle WHERE id = " + id_salle;
mySqlConn = new MySql_Connection();
mySqlConn.openConnection();
MySqlDataReader dataReader = mySqlConn.selectCommande(sql);
try
{
if (dataReader.Read())
{
int count = dataReader.GetInt32(0);
if (count > 0)
{
is_existe = true;
}
}
}
finally
{
mySqlConn.closeConnection();
}
return(is_existe);
}
// *** IMPORTANT !! ***
//It is super werid that combo box selection is string, and by default the SelectedItem is "__blank__", which may cause the unexpected error
// *** Very Important !!!!!!! ***
public MainWindow()
{
InitializeComponent();
mydb = new MySql_Connection();
State_cb.ItemsSource = mydb.SQLSELECTExec("SELECT DISTINCT state FROM censusdata ORDER BY state; ", "state");
State_cb.SelectionChanged += State_cb_SelectionChanged;
city_lb.SelectionChanged += City_lb_SelectionChanged;
zip_lb.SelectionChanged += Zip_lb_SelectionChanged;
}
/* Visiteur */
public List <string> getVisionnagesActuels(string att)
{
List <string> liste = null;
mySqlConn = new MySql_Connection();
string sql = "SELECT * FROM Visionnage v, visionnage_salle vv where v.id = vv.id_visionnage " +
"and vv.date>=CURRENT_DATE and (v.nom like '%" + att + "%' or v.realisateur like '%" + att + "%' or v.producteur like '%" + att + "%') " +
"ORDER BY vv.date;";
mySqlConn.openConnection();
MySqlDataReader dataReader = mySqlConn.selectCommande(sql);
try
{
if (dataReader != null)
{
liste = new List <string>();
}
while (dataReader.Read())
{
//id nom realisateur producteur type is_3d is_original id_visionnage id_salle date horaire nbPlacesDispo
string s;
s = "-> No film: " + dataReader.GetInt32(0) + ", Titre: " + dataReader.GetString(1) + "\n";
s += " Par: " + dataReader.GetString(2) + ", Prod: " + dataReader.GetString(3) + "\n";
s += " ( " + dataReader.GetString(4);
if (dataReader.GetBoolean(5))
{
s += ", 3D:OUI";
}
else
{
s += ", 3D:NON";
}
if (dataReader.GetBoolean(6))
{
s += ", Original:OUI )\n";
}
else
{
s += ", Original:NON )\n";
}
s += " Séance: " + dataReader.GetDateTime(9).ToShortDateString() + " à " + dataReader.GetString(10) + "\n";
s += " Salle: " + dataReader.GetInt32(8) + " ( Places disponibles: " + dataReader.GetInt32(11) + " )";
liste.Add(s);
}
}
finally
{
mySqlConn.closeConnection();
}
return(liste);
}
public bool creerCompteAdmin(Admin e)
{
mySqlConn = new MySql_Connection();
int count = 0;
string sql = "INSERT INTO Admin VALUES (NULL, '" + e.nom + "', '" + e.prenom + "', '" + e.email + "', '" + e.motDePasse + "');";
mySqlConn.openConnection();
count = mySqlConn.executeCommande(sql);
mySqlConn.closeConnection();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool ajouterVisionnage(Visionnage x)
{
mySqlConn = new MySql_Connection();
int count = 0;
string sql = "INSERT INTO Visionnage VALUES (NULL, '" + x.nom + "', '" + x.realisateur + "', '" + x.producteur + "', '" + x.type + "', " + x.is_3d + ", " + x.is_original + ");";
mySqlConn.openConnection();
count = mySqlConn.executeCommande(sql);
mySqlConn.closeConnection();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool creerSeanceProjection(Visionnage_Salle v_s)
{
mySqlConn = new MySql_Connection();
int count = 0;
string sql = "INSERT INTO Visionnage_salle VALUES (" + v_s.id_visionnage + ", " + v_s.id_salle + ", '" + v_s.date + "', '" + v_s.horaire + "', " + v_s.nbPlacesDispo + ");";
mySqlConn.openConnection();
count = mySqlConn.executeCommande(sql);
mySqlConn.closeConnection();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool isVisiteurExiste(string email, string password)
{
bool is_existe = false;
mySqlConn = new MySql_Connection();
mySqlConn.openConnection();
StringBuilder md5_password = new StringBuilder();
using (MD5 md5 = MD5.Create())
{
byte[] hash = md5.ComputeHash(Encoding.UTF8.GetBytes(password));
for (int i = 0; i < hash.Length; i++)
{
md5_password.Append(hash[i].ToString("x2"));
}
}
string sql = "SELECT COUNT(*) FROM Visiteur WHERE email ='" + email.Replace("'", "''") + "' AND password='******'", "''") + "'";
MySqlDataReader dataReader = mySqlConn.selectCommande(sql);
try
{
if (dataReader.Read())
{
int count = dataReader.GetInt32(0);
if (count == 1)
{
is_existe = true;
}
}
}
finally
{
mySqlConn.closeConnection();
}
return(is_existe);
}
public List <Visionnage> getListeFilms()
{
List <Visionnage> liste = null;
mySqlConn = new MySql_Connection();
string sql = "SELECT * FROM Visionnage;";
mySqlConn.openConnection();
MySqlDataReader dataReader = mySqlConn.selectCommande(sql);
try
{
if (dataReader != null)
{
liste = new List <Visionnage>();
}
while (dataReader.Read())
{
Visionnage v = new Visionnage();
v.id = dataReader.GetInt32(0);
v.nom = dataReader.GetString(1);
v.realisateur = dataReader.GetString(2);
v.producteur = dataReader.GetString(3);
v.type = dataReader.GetString(4);
v.is_3d = dataReader.GetBoolean(5);
v.is_original = dataReader.GetBoolean(6);
liste.Add(v);
}
}
finally
{
mySqlConn.closeConnection();
}
return(liste);
}
// *** IMPORTANT !! ***
//It is super weird that combo box selection is string, and by default the SelectedItem is "__blank__", which may cause the unexpected error
// *** Very Important !!!!!!! ***
public MainWindow()
{
InitializeComponent();
List <string> val = new List <string>();
val.Add("21plus");
val.Add("allages");
cons.Add("Ages_Allowed", val);
val = new List <string>();
val.Add("none");
val.Add("full_bar");
val.Add("beer_and_wine");
cons.Add("Alcohol", val);
val = new List <string>();
val.Add("casual");
val.Add("dressy");
val.Add("formal");
cons.Add("Attire", val);
val = new List <string>();
val.Add("yes_free");
val.Add("no");
val.Add("yes_corkage");
cons.Add("BYOBCorkage", val);
val = new List <string>();
val.Add("average");
val.Add("loud");
val.Add("very_loud");
val.Add("quiet");
cons.Add("Noise_Level", val);
val = new List <string>();
val.Add("no");
val.Add("free");
val.Add("paid");
cons.Add("Wi_Fi", val);
tabControl.SelectedIndex = 1;
/**** Sync btw tab0 and tab1 ****/
//tb2Categories.ItemsSource = Categories.Items;
//tb2State_cb.ItemsSource = State_cb.Items;
/* ************ tab0 *********************** */
mydb = new MySql_Connection();
State_cb.ItemsSource = mydb.SQLSELECTExec("SELECT DISTINCT state FROM censusdata ORDER BY state; ", "state");
State_cb.SelectionChanged += State_cb_SelectionChanged;
State_cb.SelectionChanged += enableUpdate;
city_lb.SelectionChanged += City_lb_SelectionChanged;
zip_lb.SelectionChanged += Zip_lb_SelectionChanged;
mydb2 = new MySql_ConnectionTwo();
//Categories.ItemsSource = onTest();
Categories.ItemsSource = mydb2.SQLSELECTExec("select distinct category from category order by category;");
Categories.SelectionChanged += onSelectedCat;
Categories.SelectionChanged += enableUpdate;
// Categories.SelectedItemChanged += onModifySel;
//Selected_Categories.DataContextChanged +=(s,e)=> { MessageBox.Show("S"); };
Selected_Categories.SelectionChanged += (sender, e) => { Selected_Categories.Items.Remove(Selected_Categories.SelectedItem); };
Selected_Categories.SelectionChanged += enableUpdate;
/**********************************************************************/
/******************** tab 1 ********************/
tb2State_cb.ItemsSource = mydb2.SQLSELECTExec("SELECT DISTINCT state FROM business ORDER BY state;", "state");
tb2Categories.ItemsSource = Categories.Items;
tb2State_cb.SelectionChanged += tb2State_cb_SelectionChanged;
tb2city_lb.SelectionChanged += tb2City_lb_SelectionChanged;
tb2Categories.SelectionChanged += tb2onSelectedCat;
tb2Categories.SelectionChanged += enableSearch;
tb2Categories.SelectionChanged += getAttrs;
tb2Selected_Categories.SelectionChanged += (sender, e) => { tb2Selected_Categories.Items.Remove(tb2Selected_Categories.SelectedItem); };
tb2Selected_Categories.SelectionChanged += enableSearch;
tb2Selected_Categories.SelectionChanged += getAttrs;
tb2Selected_Categories.SelectionChanged += AttrSetVals;
NoState.Checked += NoState_Checked;
NoState.Unchecked += NoState_Checked;
//tb2State_cb.IsEnabled = false;
//tb2city_lb.IsEnabled = false;
//tb2zip_lb.IsEnabled = false;
/************************************************************************/
for (int i = 0; i < 6; i++)
{
MinRat.Items.Add(i);
MaxRat.Items.Add(i);
}
for (int i = 0; i <= Int32.Parse(mydb2.SQLSELECTExec("select max(review_count) from business;")[0]); i++)
{
MinRev.Items.Add(i);
MaxRev.Items.Add(i);
}
val = new List <string>();
for (int i = 0; i <= Int32.Parse(mydb2.SQLSELECTExec("select max(val) from attributes where attr='price_range';")[0]); i++)
{
val.Add(i.ToString());
}
cons.Add("Price_Range", val);
//US states translate btw full and abbr.
using (TextFieldParser data = new TextFieldParser("../../usstate.txt"))
{
data.TextFieldType = FieldType.Delimited;
data.SetDelimiters(" ");
while (!data.EndOfData)
{
var array = data.ReadFields();
string abbreviation = array[0];
string state_name = null;
if (array.Length > 2)
{
for (int i = 1; i < array.Length; i++)
{
if (i != array.Length - 1)
{
state_name += array[i] + " ";
}
else
{
state_name += array[i];
}
}
}
else
{
state_name = array[1];
}
all_state.Add(state_name, abbreviation);
all_state.Add(abbreviation, abbreviation);
}
//data.Dispose();
}
}
