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);
        }
예제 #10
0
        // *** 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);
        }
예제 #17
0
        // *** 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();
            }
        }