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


namespace DBMS_Project
{
    public partial class Form1 : Form
    {
        string sqlCommand;
        public Form1()
        {
            InitializeComponent();
            this.Size = new Size(1000, 600);
            tabPage1.Text = "Doctor";
            tabPage2.Text = "Division";
            tabPage3.Text = "Patient";
            tabPage4.Text = "Reservation";
            tabPage5.Text = "Shift Arrangement";
            tabPage6.Text = "Raw SQL Query";
        }

        private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
        {

        }

        private void webBrowser1_DocumentCompleted(object sender, WebBrowserDocumentCompletedEventArgs e)
        {

        }

        private void rdoFname_Lname_Shi_CheckedChanged(object sender, EventArgs e)
        {

        }

        private void btnQuery_Click(object sender, EventArgs e)
        {
            // SQL connection
            SqlConnection cn = new SqlConnection();
            cn.ConnectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;" +
                "AttachDbFilename=|DataDirectory|Dental_Center.mdf;" +
                 "Integrated Security=True";


            if (tabControl1.SelectedTab == tabPage1) // "Doctor" table query
            {
                if (rdoDid_Doc.Checked == false && rdoDivNo_Doc.Checked == false && rdoFname_Lname_Doc.Checked == false)
                {
                    MessageBox.Show("Please select a query type!!");
                }
                else
                {
                    DataSet ds = new DataSet();

                    if (rdoDid_Doc.Checked == true) // query with Did
                    {
                        sqlCommand = "SELECT * FROM Doctor WHERE DoctorID = " + txtQuery.Text;
                    }
                    else if (rdoDivNo_Doc.Checked == true) //query with DivNo
                    {
                        sqlCommand = "SELECT * FROM Doctor WHERE DivisionNo = " + txtQuery.Text;
                    }
                    else if (rdoFname_Lname_Doc.Checked == true) // query with fname_lname
                    {
                        // split text into fname and lname
                        string[] splitStr = txtQuery.Text.Split();
                        string fName = splitStr[0];
                        string lName = splitStr[1];

                        sqlCommand = "SELECT * FROM Doctor WHERE FName = " + "\'" + fName + "\'" + " AND LName = " + "\'" + lName + "\'";
                    }

                    SqlDataAdapter da = new SqlDataAdapter(sqlCommand, cn);
                    da.Fill(ds);
                    dataGridView1.DataSource = ds.Tables[0];
                }

            }
            else if (tabControl1.SelectedTab == tabPage2) // "Division" table query
            {
                DataSet ds = new DataSet();
                if (rdoDivNo_Div.Checked == false && rdoDivName_Div.Checked == false)
                {
                    MessageBox.Show("Please select a query type!!");
                }
                else
                {
                    if(rdoDivNo_Div.Checked == true) // query with DivNo
                    {
                        sqlCommand = "SELECT * FROM Division WHERE Division_no = " + txtQuery.Text;
                    }
                    else if(rdoDivName_Div.Checked == true)
                    {
                        sqlCommand = "SELECT * FROM Division WHERE DivisionName = " + "\'" + txtQuery.Text +"\'";
                    }

                    SqlDataAdapter da = new SqlDataAdapter(sqlCommand, cn);
                    da.Fill(ds);
                    dataGridView1.DataSource = ds.Tables[0];
                }
            }
            else if(tabControl1.SelectedTab == tabPage3) // "Patient" table query
            {
                DataSet ds = new DataSet();

                if (rdo_Pid_Pat.Checked == false && rdoDoc_inCharge_Pat.Checked == false && rdoDivNo_Pat.Checked == false && rdoFname_Lname_Pat.Checked == false && rdoPhonenum_Pat.Checked == false)
                {
                    MessageBox.Show("Please select a query type!!");
                }
                else
                {
                    if(rdo_Pid_Pat.Checked == true) // query with Pid
                    {
                        sqlCommand = "SELECT * FROM Patient WHERE PatientID = " + txtQuery.Text;
                        SqlDataAdapter da = new SqlDataAdapter(sqlCommand, cn);
                        da.Fill(ds);
                        dataGridView1.DataSource = ds.Tables[0];
                    }
                    else if(rdoDoc_inCharge_Pat.Checked == true) // query with doc_in_charge fname lname
                    {
                        // split text into fname and lname
                        string[] splitStr = txtQuery.Text.Split();
                        string fName = splitStr[0];
                        string lName = splitStr[1];

                        sqlCommand = "SELECT Patient.PatientID, Patient.FName, Patient.LName FROM Patient, Doctor WHERE Doctor.FName = " + "\'" + fName + "\'" + "AND Doctor.LName = " + "\'" + lName + "\'" +
                                        "AND Patient.Doctor_In_Charge_ID = Doctor.DoctorID";
                        SqlDataAdapter da = new SqlDataAdapter(sqlCommand, cn);
                        da.Fill(ds);
                        dataGridView1.DataSource = ds.Tables[0];

                    }
                    else if(rdoDivNo_Pat.Checked == true) // query with DivName
                    {
                        sqlCommand = "SELECT Patient.PatientID, Patient.FName, Patient.LName FROM Patient, Doctor, Division WHERE Doctor.DivisionNo = Division.Division_no AND Division.DivisionName = " + "\'" + txtQuery.Text + "\'" +
                            " AND Doctor.DoctorID = Patient.Doctor_In_Charge_ID";
                        SqlDataAdapter da = new SqlDataAdapter(sqlCommand, cn);
                        da.Fill(ds);
                        dataGridView1.DataSource = ds.Tables[0];
                    }
                    else if(rdoFname_Lname_Pat.Checked == true) // query with fname lname
                    {
                        // split text into fname and lname
                        string[] splitStr = txtQuery.Text.Split();
                        string fName = splitStr[0];
                        string lName = splitStr[1];

                        sqlCommand = "SELECT * FROM Patient WHERE FName = " + "\'" + fName + "\'" + "AND LName = " + "\'" + lName + "\'";
                        SqlDataAdapter da = new SqlDataAdapter(sqlCommand, cn);
                        da.Fill(ds);
                        dataGridView1.DataSource = ds.Tables[0];
                    }
                    else if(rdoPhonenum_Pat.Checked == true) //query with phonenum
                    {
                        sqlCommand = "SELECT * FROM Patient WHERE PhoneNum = " + "\'" + txtQuery.Text + "\'";
                        SqlDataAdapter da = new SqlDataAdapter(sqlCommand, cn);
                        da.Fill(ds);
                        dataGridView1.DataSource = ds.Tables[0];
                    }
                }

            }
            else if (tabControl1.SelectedTab == tabPage4) // "Reservation" Table query
            {
                DataSet ds = new DataSet();
                if(rdoFname_Lname_Res.Checked == false && rdoPhonenum_Res.Checked == false)
                {
                    MessageBox.Show("Please select a query type!!");
                }
                else
                {
                    if(rdoFname_Lname_Res.Checked == true) // query with fname lname
                    {
                        // split text into fname and lname
                        string[] splitStr = txtQuery.Text.Split();
                        string fName = splitStr[0];
                        string lName = splitStr[1];

                        sqlCommand = "SELECT Patient.FName, Patient.LName, Reservation.DateTime FROM Patient, Reservation WHERE Patient.PatientID = Reservation.PatientID AND Patient.FName = " + "\'" + fName + "\'" + "AND Patient.LName = " + "\'" + lName + "\'";
                        SqlDataAdapter da = new SqlDataAdapter(sqlCommand, cn);
                        da.Fill(ds);
                        dataGridView1.DataSource = ds.Tables[0];
                    }
                    else if(rdoPhonenum_Res.Checked == true)
                    {
                        sqlCommand = "SELECT Patient.FName, Patient.LName, Reservation.DateTime FROM Patient, Reservation WHERE Patient.PatientID = Reservation.PatientID AND Patient.PhoneNum = " + "\'" + txtQuery.Text +"\'";
                        SqlDataAdapter da = new SqlDataAdapter(sqlCommand, cn);
                        da.Fill(ds);
                        dataGridView1.DataSource = ds.Tables[0];
                    }
                }
            }
            else if(tabControl1.SelectedTab == tabPage5) // "Shift Arrange" Table query
            {
                DataSet ds = new DataSet();
                if(rdoFname_Lname_Shi.Checked == false && rdoDid_Shi.Checked == false && rdoSession_Shi.Checked == false)
                {
                    MessageBox.Show("Please select a query type!!");
                }
                else
                {
                    if(rdoFname_Lname_Shi.Checked == true) // query with fname lname
                    {
                        // split text into fname and lname
                        string[] splitStr = txtQuery.Text.Split();
                        string fName = splitStr[0];
                        string lName = splitStr[1];

                        sqlCommand = "SELECT Shift_Arrange.Session, Shift_Arrange.Date FROM Doctor, Shift_Arrange WHERE Doctor.DoctorID = Shift_Arrange.DoctorID AND Doctor.FName = " + "\'" + fName + "\'" + "AND Doctor.LName = " + "\'" + lName + "\'";
                        SqlDataAdapter da = new SqlDataAdapter(sqlCommand, cn);
                        da.Fill(ds);
                        dataGridView1.DataSource = ds.Tables[0];
                    }
                    else if(rdoDid_Shi.Checked == true) // query with doctor ID
                    {
                        sqlCommand = "SELECT Doctor.FName, Doctor.LName, Shift_Arrange.Session, Shift_Arrange.Date  FROM Shift_Arrange, Doctor WHERE Doctor.DoctorID = " + txtQuery.Text + " AND Doctor.DoctorID = Shift_Arrange.DoctorID";
                        SqlDataAdapter da = new SqlDataAdapter(sqlCommand, cn);
                        da.Fill(ds);
                        dataGridView1.DataSource = ds.Tables[0];
                    }
                    else if(rdoSession_Shi.Checked == true) // query with sessionDate
                    {
                        sqlCommand = "SELECT Doctor.FName, Doctor.LName, Shift_Arrange.Session, Shift_Arrange.Date  FROM Shift_Arrange, Doctor WHERE Date = " + "\'" + txtQuery.Text + "\'" + " AND Doctor.DoctorID = Shift_Arrange.DoctorID";
                        SqlDataAdapter da = new SqlDataAdapter(sqlCommand, cn);
                        da.Fill(ds);
                        dataGridView1.DataSource = ds.Tables[0];
                    }
                }

            }
            else if(tabControl1.SelectedTab == tabPage6) // SQL raw query
            {
                DataSet ds = new DataSet();
                sqlCommand = txtRawSQL.Text;
                SqlDataAdapter da = new SqlDataAdapter(sqlCommand, cn);
                da.Fill(ds);
                dataGridView1.DataSource = ds.Tables[0];
            }

        }
    }
}