//Databases III Project
//by Xarhs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Collections;
using System.Data.OleDb;
using MySql.Data.MySqlClient;

namespace Cars
{
    public partial class Form1 : Form
    {
        public string strAccessConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=NEWCarRentals.mdb";
        string MyConString = "SERVER=localhost;" +
                "DATABASE=CarRental;" +
                "UID=root;" +
                "PASSWORD=;";
        public ArrayList AllCars;
        public ArrayList SelectedUsers;
        public CarCharacteristics tmp;
        public DeletedUsers du;
        public int Available = 0;
        public int ClientsNum = 0;
        public enum RentedClicked
        {
            _Clicked,
            _NotClicked,
        }
        
        public Form1()
        {
            InitializeComponent();
            RentedButtonState = RentedClicked._NotClicked;
        }

        RentedClicked RentedButtonState = new RentedClicked();

        private void OnFormLoad(object sender, EventArgs e)
        {
            LoadDatabase();
        }

        private void LoadDatabase()
        {
            string strSelect = @"SELECT Brands.Brand, Models.Model, Models.Category, Models.Availability,
                                 Models.Rented
                                    FROM Brands
                                        INNER JOIN Models
                                            ON Brands.BrandID = Models.BrandID";

            try
            {
                DataSet myDataSet = new DataSet();
                MySqlConnection mySqlConn = new MySqlConnection(MyConString);
                MySqlDataAdapter mySqlAdapter = new MySqlDataAdapter(strSelect, mySqlConn);
                mySqlAdapter.Fill(myDataSet, "Brands");
                
                AllCars = new ArrayList();

                DataRowCollection dra = myDataSet.Tables["Brands"].Rows;
                foreach (DataRow dr in dra)
                {
                    tmp = new CarCharacteristics();
                    tmp.Brand = dr[0].ToString();
                    tmp.Model = dr[1].ToString();
                    tmp.Category = dr[2].ToString();
                    tmp.Availability = System.Convert.ToInt32(dr[3]);
                    tmp.Rented = System.Convert.ToInt32(dr[4]);
                    AllCars.Add(tmp);
                }
               
                for (int i = 0; i < AllCars.Count; i++)
                {
                    tmp = (CarCharacteristics)AllCars[i];
                    if(this.BrandName.Items.Contains(tmp.Brand) == false)
                        this.BrandName.Items.Add(tmp.Brand);
                }
                BrandName.SelectedIndex = -1;
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: " + ex.Message.ToString());
            }
        }

        private void BrandName_SelectedIndexChanged(object sender, EventArgs e)
        {
            ModelsName.Items.Clear();
            ModelsName.Sorted = true;
            CategoryRent.Items.Clear();

            foreach (CarCharacteristics a in AllCars)
            {
                if (BrandName.SelectedItem.ToString() == a.Brand)
                {
                      ModelsName.Items.Add(a.Model);
                }
            }
            ModelsName.SelectedIndex = 0;
        }

        private void RentButton_Click(object sender, EventArgs e)
        {
            if (BrandName.SelectedIndex == -1)
                BrandName.Focus();
            else if (ModelsName.SelectedIndex == -1)
                ModelsName.Focus();
            else if (CategoryRent.SelectedIndex == -1)
                CategoryRent.Focus();
            else if (NameTextBox.Text.Length == 0)
                NameTextBox.Focus();
            else if (LastnameTextBox.Text.Length == 0)
                LastnameTextBox.Focus();
            else if(PhoneTextBox.Text.Length == 0)
                PhoneTextBox.Focus();
            else
            {
                foreach (CarCharacteristics a in AllCars)
                {
                    
                    if (BrandName.SelectedItem.ToString() == a.Brand &&
                        ModelsName.SelectedItem.ToString() == a.Model &&
                        CategoryRent.SelectedItem.ToString() == a.Category)
                    {
                        if (a.Rented < a.Availability)
                        {
                            a.Name[a.count] = NameTextBox.Text;
                            a.Lastname[a.count] = LastnameTextBox.Text;
                            a.phone[a.count] = PhoneTextBox.Text;
                            a.count++;
                            a.Added = true;
                            a.Rented++;
                            Available = a.Availability - a.Rented;
                            AvailableTextBox.Text = Available.ToString();
                            RentedButtonState = RentedClicked._Clicked;
                            ClientsNum++;
                            ClientNumLabel.Text = ClientsNum.ToString() + " Clients";
                            
                        }
                        else
                            MessageBox.Show("Not available");
                    }
                }
            }
        }

        private void UpdateButton_Click(object sender, EventArgs e)
        {
            if (RentedButtonState == RentedClicked._Clicked)
            {
                UpdateDatabase();
            }
        }

        private void UpdateDatabase()
        {
            try
            {
                MySqlConnection mySqlConn = new MySqlConnection(MyConString);
                MySqlCommand UPDATECommand = new MySqlCommand();
                MySqlCommand INSERTCommand = new MySqlCommand();
                UPDATECommand.Connection = mySqlConn;
                INSERTCommand.Connection = mySqlConn;
                mySqlConn.Open();

                foreach (CarCharacteristics a in AllCars)
                {
                    for (int i = 0; i < a.count; i++)
                    {
                        if (a.Added && a.Name[i] != null)
                        {
                            INSERTCommand.CommandText = @"INSERT INTO Customers 
                                                     ( Name, Lastname, Phone, ModelID )
                           SELECT '" + a.Name[i] + "', '" + a.Lastname[i] + "', '" + a.phone[i] + "',"
                                      + " ModelID FROM Models WHERE Model ='" + a.Model + "'";
                            INSERTCommand.ExecuteNonQuery();

                            UPDATECommand.CommandText = @"UPDATE Models 
                                              SET Models.Rented=" + a.Rented +
                                                   " WHERE Models.BrandID=(SELECT BrandID From Brands WHERE Brand='" + a.Brand + "')"
                                                     + " AND Models.Model='" + a.Model + "'"
                                                      + " AND Category='" + a.Category + "'";
                            UPDATECommand.ExecuteNonQuery();
                        }
                    }
                }
                mySqlConn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: " + ex.Message.ToString());
            }
            System.Array.Clear(tmp.Name, 0, tmp.Name.Length);
            System.Array.Clear(tmp.Lastname, 0, tmp.Lastname.Length);
            System.Array.Clear(tmp.phone, 0, tmp.phone.Length);
        }

        private void AddButton_Click(object sender, EventArgs e)
        {
            bool Cancel = false;
            bool Show = false;
            bool found = false;
            DialogResult result;

            if (BrandTextBox.Text.Length == 0)
                BrandTextBox.Focus();
            else if (ModelTextBox.Text.Length == 0)
                ModelTextBox.Focus();
            else if (CategoryAdd.SelectedIndex == -1)
                CategoryAdd.Focus();
            else if (CountTextBox.Text.Length == 0)
                CountTextBox.Focus();
            else
            {
                try
                {
                    foreach (CarCharacteristics a in AllCars)
                    {
                        
                        if (a.Brand == BrandTextBox.Text || a.Model == ModelTextBox.Text)
                        {
                            found = true;
                            if (a.Brand == BrandTextBox.Text && a.Model == ModelTextBox.Text)
                            {
                                Show = true;
                                Cancel = true;
                                break;
                            }
                        }
                    }

                    if (Show && found)
                    {
                        MessageBox.Show(BrandTextBox.Text + " " + ModelTextBox.Text + " already exist");
                    }
                    else if (!Show && found)
                    {
                        //Show = false;
                        result = MessageBox.Show("Brand or Model already exist, Are you sure?", "Confirm", MessageBoxButtons.YesNo);
                        if (result == DialogResult.No)
                            Cancel = true;
                    }
                    if (found && !Cancel)
                    {
                        string InsertModelCommand = @"INSERT INTO Models 
                            ( Model, Category, Availability, Rented, BrandID)
                                SELECT '" + ModelTextBox.Text + "','" + CategoryAdd.SelectedItem.ToString() + "'," + Convert.ToInt32(CountTextBox.Text) + ",0,"
                                  + "BrandID FROM Brands WHERE Brand='" + BrandTextBox.Text + "'";

                        InsertModel(InsertModelCommand);
                    }
                    if(!found && !Cancel)
                    {
                        string InsertBrand = @"INSERT INTO Brands (Brand) VALUES
                                                ('" + BrandTextBox.Text + "');";
                        string InsertModel = @"INSERT INTO Models 
                                ( Model, Category, Availability, Rented, BrandID )
                                  SELECT '" + ModelTextBox.Text + "','" + CategoryAdd.SelectedItem.ToString() + " '," + Convert.ToInt32(CountTextBox.Text) + ",0,"
                                    + "BrandID From Brands WHERE Brand='" + BrandTextBox.Text + "'";

                        InsertBrandAndModel(InsertBrand, InsertModel);
                    }
                    
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Error: " + ex.Message.ToString());
                }
            }
        }

        private void InsertBrandAndModel(string strBrand,string strModel)
        {
            try
            {
                MySqlConnection myConn = new MySqlConnection(MyConString);
                MySqlCommand InsertBrandCommand = new MySqlCommand();
                MySqlCommand InsertModelCommand = new MySqlCommand();
                InsertBrandCommand.Connection = myConn;
                InsertModelCommand.Connection = myConn;

                InsertBrandCommand.CommandText = strBrand;


                InsertModelCommand.CommandText = strModel;

                myConn.Open();
                InsertBrandCommand.ExecuteNonQuery();
                InsertModelCommand.ExecuteNonQuery();
                myConn.Close();
            }
            catch (Exception)
            {

            }
        }

        private void InsertModel(string strCommand)
        {
            try
            {
                MySqlConnection mySqlConn = new MySqlConnection(MyConString);
                MySqlCommand command = new MySqlCommand();
                command.Connection = mySqlConn;
                command.CommandText = strCommand;
                mySqlConn.Open();
                command.ExecuteNonQuery();
                mySqlConn.Close();
            }
            catch (Exception)
            {

            }
        }

        private void RefreshButton_Click(object sender, EventArgs e)
        {
            BrandName.Items.Clear();
            BrandName.Text = "";
            ModelsName.Items.Clear();
            ModelsName.Text = "";
            CategoryRent.Items.Clear();
            CategoryRent.Text = "";
            AvailableTextBox.Clear();
            NameTextBox.Clear();
            LastnameTextBox.Clear();
            PhoneTextBox.Clear();
            LoadDatabase();
        }

        private void ModelsName_SelectedIndexChanged(object sender, EventArgs e)
        {
            CategoryRent.Sorted = true;
            CategoryRent.Items.Clear();

            foreach (CarCharacteristics a in AllCars)
            {
                if (BrandName.SelectedItem.ToString() == a.Brand &&
                    ModelsName.SelectedItem.ToString() == a.Model)
                {
                    CategoryRent.Items.Add(a.Category);
                    CategoryRent.SelectedItem=a.Category;
                }
            }
        }

        private void CategoryRent_SelectedIndexChanged(object sender, EventArgs e)
        {
            foreach (CarCharacteristics a in AllCars)
            {
                if (BrandName.SelectedItem.ToString() == a.Brand &&
                    ModelsName.SelectedItem.ToString() == a.Model &&
                    CategoryRent.SelectedItem.ToString() == a.Category)
                {
                    Available = a.Availability - a.Rented;
                    AvailableTextBox.Text = Available.ToString();
                }
            }
        }
        private void SearchButton_Click(object sender, EventArgs e)
        {
            string strSelect = "";

            if (SearchNameTextBox.Text.Length == 0)
            {
                strSelect = @"SELECT Customers.Lastname, Customers.Name, Customers.Phone, 
                                     Brands.Brand, Models.Model, Models.Category  
                                        FROM Brands,Models,Customers
                                            WHERE Brands.BrandID = Models.BrandID
                                            AND Models.ModelID = Customers.ModelID
                                            AND Customers.Lastname = '" + SearchLastnameTextBox.Text + "'";
                Search(strSelect);
            }
            else if (SearchLastnameTextBox.Text.Length == 0)
            {
                strSelect = @"SELECT Customers.Lastname, Customers.Name, Customers.Phone, 
                                     Brands.Brand, Models.Model, Models.Category  
                                        FROM Brands,Models,Customers
                                            WHERE Brands.BrandID = Models.BrandID
                                            AND Models.ModelID = Customers.ModelID
                                            AND Customers.Name = '" + SearchNameTextBox.Text + "'";

                Search(strSelect);
            }
            else
            {
                strSelect = @"SELECT Customers.Lastname, Customers.Name, Customers.Phone, 
                                     Brands.Brand, Models.Model, Models.Category  
                                        FROM Brands,Models,Customers
                                            WHERE Brands.BrandID = Models.BrandID
                                            AND Models.ModelID = Customers.ModelID
                                            AND Customers.Name = '" + SearchNameTextBox.Text + "'"
                                         + "AND Customers.Lastname = '" + SearchLastnameTextBox.Text + "'";
                Search(strSelect);
            }
        }

        private void Search(string strSelect)
        {
            try
            {
                DataSet myDataSet = new DataSet();
                MySqlConnection mySqlConn = null;
                mySqlConn = new MySqlConnection(MyConString);
                MySqlDataAdapter mySqlAdapter = new MySqlDataAdapter(strSelect, mySqlConn);
                mySqlAdapter.Fill(myDataSet, "Brands");
                SearchDataGrid.DataSource = myDataSet;
                SearchDataGrid.DataMember = "Brands";
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: " + ex.Message.ToString());
            }
        }

        private void tabPage1_Enter(object sender, EventArgs e)
        {
            SelectedUsers = new ArrayList();
        }

        private void SearchDataGrid_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            du = new DeletedUsers();
            du.Lastname = SearchDataGrid.Rows[e.RowIndex].Cells[0].FormattedValue.ToString();
            du.Name = SearchDataGrid.Rows[e.RowIndex].Cells[1].FormattedValue.ToString();
            SelectedUsers.Add(du);
        }

        private void SearchDataGrid_CellLeave(object sender, DataGridViewCellEventArgs e)
        {
            foreach (DeletedUsers str in SelectedUsers)
            {
                if (str.Lastname == SearchDataGrid.Rows[e.RowIndex].Cells[0].FormattedValue.ToString() ||
                    str.Name == SearchDataGrid.Rows[e.RowIndex].Cells[1].FormattedValue.ToString())
                {
                    SelectedUsers.Remove(str.Lastname);
                    SelectedUsers.Remove(str.Name);
                }
            }
        }

        private void DeleteButton_Click(object sender, EventArgs e)
        {
            if (SelectedUsers.Count == 0)
                MessageBox.Show("Nothing to delete!");
            else
            {
                foreach (DeletedUsers str in SelectedUsers)
                {
                    MessageBox.Show("Name: " + str.Name + Environment.NewLine + "Lastname: " + str.Lastname + Environment.NewLine
                        + "DELETED!");
                }
            }
        }
        void Delete()
        {
            try
            {

            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: " + ex.Message.ToString());
            }
        }
    }
}