using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace Reservtion
{
    public partial class Rooms : Form
    {
        RConection r;
        DataSet ds = new DataSet();
        static DataTable dt2;
        Boolean x = false;

        private void DataGridViewIntialColumnHeaders()
        {
            dataGridView1.Columns["RoomNum"].HeaderText = "شماره اتاق";
            dataGridView1.Columns["Quantity"].Visible = false;
            dataGridView1.Columns["Number"].Visible = false;
            dataGridView1.Columns["Estate"].HeaderText = "وضعیت";
            dataGridView1.Columns["TotalPrice"].HeaderText = "هزینه";
        }

        private void Tabs1()
        {
            foreach (DataRow row in ds.Tables[0].Rows)
                checkedListBox1.Items.Add(row[0].ToString());

            for (int i = 0; i < checkedListBox1.Items.Count; i++)
                checkedListBox1.SetItemChecked(i, true);
        }

        private void Tabs2()
        {
            foreach (DataRow row in ds.Tables[0].Rows)
                listBox2.Items.Add(row[0].ToString());

            listBox2.SelectedIndex = 0;
        }

        private void Tabs3()
        {
            foreach (DataRow row in ds.Tables[0].Rows)
                listBox4.Items.Add(row[0].ToString());

            comboBox1.Text = comboBox1.Items[0].ToString();
            foreach (DataRow row in ds.Tables[4].Rows)
                comboBox1.Items.Add(row[0].ToString());
            label5.Text = ds.Tables[5].Rows[0][0].ToString() + "ريال";
        }

        public Rooms(RConection r1)
        {
            InitializeComponent();
            r = r1;
            radioButton1.Checked = true;
            try
            {                                               //0                                     //1                          //2                    //3                  //4                       //5
                r.cmd.CommandText = "SELECT Property FROM Property; SELECT Property,Item FROM PropertyItems; SELECT * FROM RoomPropertis; SELECT * FROM Room; SELECT * FROM Estates; SELECT * FROM ReservPrice";
                DataTable dt = new DataTable();
                r.da.Fill(ds);
                Tabs1();
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message + "\n" + ex.ErrorCode.ToString());
            }
            catch(System.Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            textBox1.SelectAll();
        }

        private void button4_Click(object sender, EventArgs e)
        {
            try
            {
                if (textBox1.Text != "")
                {
                    DataRow row;
                    row = ds.Tables[3].Select("Number=" + textBox1.Text)[0];
                    textBox2.Text = "وضعیت: " + row["Estate"] + "  هزینه:  " + row["TotalPrice"] + "ريال  \n  ";

                    for (int i = 0; i < checkedListBox1.Items.Count; i++)
                    {

                        if (checkedListBox1.GetItemCheckState(i) == CheckState.Checked)
                        {
                            textBox2.Text += checkedListBox1.Items[i] + ": ";
                            string s = "RoomNum=" + textBox1.Text + " AND Property='" + checkedListBox1.Items[i] + "'";
                            int c = ds.Tables[2].Select(s).Length;
                            if (c == 1)
                                textBox2.Text += ds.Tables[2].Select(s)[0]["Item"].ToString() + "  \n  ";
                            else
                                textBox2.Text += "ندارد  \n  ";
                        }
                    }
                }
                else
                    MessageBox.Show("فیلد شماره اتاق خالی است", "جستجوی اتاق", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);

            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message + "\n" + ex.ErrorCode.ToString());
            }
            catch(SystemException ex)
            {
                if (ex.Message == "Index was outside the bounds of the array.")
                    MessageBox.Show("اتاقی به شماره " + textBox1.Text + " یافت نشد", "جستجوی اتاق", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
                else
                MessageBox.Show(ex.Message + "\n" + ex.Source.ToString());
                //textBox2.Text = ex.Message;
            }
            textBox1.SelectAll();
        }

        private void textBox1_KeyPress(object sender, KeyPressEventArgs e)
        {
            if (!((int)e.KeyChar >= 48 && (int)e.KeyChar <= 57 || (int)e.KeyChar==8))
                e.Handled = true;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            for (int i = 0; i < checkedListBox1.Items.Count; i++)
                checkedListBox1.SetItemChecked(i, true);
            textBox1.Focus();
            textBox1.SelectAll();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            for (int i = 0; i < checkedListBox1.Items.Count; i++)
                checkedListBox1.SetItemChecked(i, false);
            textBox1.Focus();
            textBox1.SelectAll();
        }

        private void tabControl1_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (tabControl1.SelectedTab == tabPage1)
                this.AcceptButton = button4;
            else if (tabControl1.SelectedTab == tabPage2)
            {
                Tabs2();
                this.AcceptButton = button3; 
            }

            else if (tabControl1.SelectedTab == tabPage3)
            {
                try
                {
                    button10.PerformClick();
                    Tabs3();
                }
                catch (SqlException ex)
                {
                    MessageBox.Show(ex.Message+"\n"+ex.ErrorCode);
                }
                catch (SystemException ex)
                {
                    MessageBox.Show(ex.Message);
                }
                textBox4.SelectAll();
            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            if (listBox1.Items.Count != 0)
            {
                string[,] v = new string[listBox1.Items.Count, 2];
                int i = 0;
                string s = "";
                for (int j = 0; j < listBox1.Items.Count; j++)
                {
                    s = listBox1.Items[j].ToString();
                    while (s[i++] != ':') ;
                    v[j, 0] = s.Substring(0, i - 1);
                    v[j, 1] = s.Substring(i + 1);
                    i = 0;
                }
                
                s = "";
                for (i = 0; i < listBox1.Items.Count; i++)
                {
                    s += "(Property=N'" + v[i, 0] + "' AND Item=N'" + v[i, 1] + "')";
                    if (i < listBox1.Items.Count - 1)
                        s += " OR ";
                }

                //dt2 = new DataTable();
                r.cmd.CommandText = "SELECT  RoomNum, Count(RoomNum) as 'Quantity' INTO #Pri FROM RoomPropertis WHERE" + s + " Group By(RoomNum) Order By(Quantity)DESC; select * from #Pri Left outer join Room on #Pri.RoomNum=Room.Number Order by(Quantity)DESC; Drop Table #Pri";
                r.da.Fill(ds,"tv");
                //DataRow[] rows = new DataRow[ds.Tables["tv"].Rows.Count];
                //rows = dt2.Select("Quantity=2");
                dataGridView1.DataSource = ds.Tables["tv"];
                if(dataGridView1.Columns["RoomNum"].HeaderText != "شماره اتاق")
                    DataGridViewIntialColumnHeaders();
            }
            else
            {
                dataGridView1.Columns.Clear();
                MessageBox.Show("لیست جستجو خالی است", "جستجوی اتاق", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
            }
        }

        private void listBox2_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                listBox3.Items.Clear();
                foreach (DataRow row in ds.Tables[1].Select("Property='" + listBox2.SelectedItem + "'"))
                    listBox3.Items.Add(row["Item"]);
                listBox3.SelectedIndex = 0;
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message + ex.ErrorCode);
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message + ex.Source);
            }

        }

        private void listBox3_DoubleClick(object sender, EventArgs e)
        {
            int i;
            for (i = 0; i < listBox1.Items.Count && ! listBox1.Items[i].ToString().StartsWith(listBox2.SelectedItem.ToString()); i++) ;
            if (i == listBox1.Items.Count)
                listBox1.Items.Add(listBox2.SelectedItem + ": " + listBox3.SelectedItem);

            else
                MessageBox.Show("این مورد قبلاً انتخاب شده است", "انتخاب ویژگی", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1);
        }

        private void button5_Click(object sender, EventArgs e)
        {
            while (listBox1.SelectedIndex >= 0)
            listBox1.Items.Remove(listBox1.SelectedItem);
        }

        private void dataGridView1_Click(object sender, EventArgs e)
        {
            DataRow[] rows;
            rows = ds.Tables[2].Select("RoomNum=" + dataGridView1.CurrentRow.Cells[2].Value.ToString());
            textBox3.Text = "";
            foreach (DataRow row in rows)
                textBox3.Text += row["Property"] + ": " + row["Item"] + "  \n  ";
        }

        private void radioButton1_CheckedChanged(object sender, EventArgs e)
        {
            dataGridView1.DataSource = ds.Tables["tv"];
        }

        private void radioButton2_CheckedChanged(object sender, EventArgs e)
        {
            dataGridView1.Rows[0].Visible = false;
        }

        private void button6_Click(object sender, EventArgs e)
        {
            if (textBox1.Text == "")
                MessageBox.Show("اتاقی انتخاب نشده!!!", "حذف اتاق", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1);
            else
            {
                if (MessageBox.Show("آیا مطمئن هستید", "حذف اتاق", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) == DialogResult.Yes)
                {
                    try
                    {
                        r.cmd.CommandText = "DELETE FROM RoomPropertis WHERE RoomNum=" + textBox1.Text + "; " + "DELETE FROM Room WHERE Number=" + textBox1.Text + ";";
                        r.conn.Open();
                        r.cmd.ExecuteNonQuery();
                        ds.Clear();
                        r.cmd.CommandText = "SELECT Property FROM Property; SELECT Property,Item FROM PropertyItems; SELECT * FROM RoomPropertis; SELECT * FROM Room";
                        r.da.Fill(ds);
                    }
                    catch (SqlException ex)
                    {
                        MessageBox.Show(ex.Message + "\n" + ex.ErrorCode.ToString());
                    }
                    finally
                    {
                        r.conn.Close();
                    }
                    textBox2.Text = "";
                }
            }
            textBox1.SelectAll();
        }

        private void button7_Click(object sender, EventArgs e)
        {
            //new Room.EditRoom(r, ds, textBox1.Text).ShowDialog();
        }

        private void textBox4_KeyPress(object sender, KeyPressEventArgs e)
        {
            if (!((int)e.KeyChar >= 48 && (int)e.KeyChar <= 57 || (int)e.KeyChar == 8))
                e.Handled = true;
        }

        private void tabPage3_Click(object sender, EventArgs e)
        {

        }

        private void listBox4_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                listBox5.Items.Clear();
                foreach (DataRow row in ds.Tables[1].Select("Property='" + listBox4.SelectedItem + "'"))
                    listBox5.Items.Add(row["Item"]);
                listBox5.SelectedIndex = 0;
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message + ex.ErrorCode);
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message + ex.Source);
            }
        }

        private void listBox5_DoubleClick(object sender, EventArgs e)
        {
            int i;
            for (i = 0; i < listBox6.Items.Count && !listBox6.Items[i].ToString().StartsWith(listBox4.SelectedItem.ToString()); i++) ;
            if (i == listBox6.Items.Count)
            {
                listBox6.Items.Add(listBox4.SelectedItem + ": " + listBox5.SelectedItem);
            }
            else
                MessageBox.Show("این مورد قبلاً انتخاب شده است", "انتخاب ویژگی", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1);
        }

        private void button8_Click(object sender, EventArgs e)
        {
            try
            {
                Boolean x = false;
                if (textBox4.Text != "")
                {
                    if (comboBox1.Text != comboBox1.Items[0].ToString())
                    {
                        if (MessageBox.Show("از اطلاعات اتاق برای ثبت اطمینان دارید", "اضافه کردن اتاق جدید", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) == DialogResult.Yes)
                        {
                            x = true;
                            r.cmd.CommandText = "INSERT INTO Room (Number,Estate) VALUES(" + textBox4.Text + ",N'" + comboBox1.Text + "')";
                            r.conn.Open();
                            r.cmd.ExecuteNonQuery();
                            r.conn.Close();
                            if (listBox6.Items.Count > 0)
                            {
                                string[,] v = new string[listBox6.Items.Count, 2];
                                int i = 0;
                                string s = "";
                                for (int j = 0; j < listBox6.Items.Count; j++)
                                {
                                    s = listBox6.Items[j].ToString();
                                    while (s[i++] != ':') ;
                                    v[j, 0] = s.Substring(0, i - 1);
                                    v[j, 1] = s.Substring(i + 1);
                                    i = 0;
                                }
                                r.conn.Open();
                                for (int j = 0; j < listBox6.Items.Count; j++)
                                {
                                    s = "INSERT INTO RoomPropertis (RoomNum,Property,Item) VALUES(";
                                    s += textBox4.Text + ",N'" + v[j, 0] + "',N'" + v[j, 1] + "')";
                                    r.cmd.CommandText = s;
                                    r.cmd.ExecuteNonQuery();
                                }
                                r.conn.Close();
                            }
                            button10.PerformClick();
                            comboBox1.Text = comboBox1.Items[0].ToString();
                            listBox4.SelectedIndex = 0;
                            listBox5.SelectedIndex = 0;
                            textBox4.SelectAll();
                        }
                    }
                    else
                        MessageBox.Show("وضعیت فعلی اتاق را انتخاب کنید", "اضافه کردن اتاق جدید", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1);
                }
                else
                    MessageBox.Show("فیلد شماره اتاق خالی است", "اضافه کردن اتاق جدید", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1);


                if (x && listBox6.Items.Count > 0)
                    MessageBox.Show("اتاق و ویژگی های اتاق ثبت شد", "اضافه کردن اتاق جدید", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1);
                else if (x)
                    MessageBox.Show("اتاق ثبت شد", "اضافه کردن اتاق جدید", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1);
                x = false;
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message + "\n" + ex.ErrorCode);
            }
            catch (SystemException ex)
            {
                MessageBox.Show(ex.Message + "/n" + ex.Source);
            }
        }

        private void comboBox1_KeyPress(object sender, KeyPressEventArgs e)
        {
            e.Handled = true;
        }

        private void button9_Click(object sender, EventArgs e)
        {
            while (listBox6.SelectedIndex >= 0)
                listBox6.Items.Remove(listBox6.SelectedItem);
        }

        private void button10_Click(object sender, EventArgs e)
        {
            r.cmd.CommandText = "select max(Number) from Room";
            r.conn.Open();
            int NM = ((int)r.cmd.ExecuteScalar()) + 1;
            textBox4.Text = NM.ToString();
            r.conn.Close();
        }

        private void button11_Click(object sender, EventArgs e)
        {
            MessageBox.Show("اتاق انتخاب شده برای مشتری جدید پزیرش شود", "شروع عملیات پزیرش", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2);
            //new NewTraveler().Show();
        }

        private void Rooms_Activated(object sender, EventArgs e)
        {
            textBox2.Text += "sdfsdfsdfsdfsd";
        }

        private void tabPage1_Click(object sender, EventArgs e)
        {

        }

        private void checkedListBox1_SelectedIndexChanged(object sender, EventArgs e)
        {

        }
    }
}