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


namespace student
{
    public partial class MainForm : Form
    {
        dataBase data = new dataBase();
        OleDbDataAdapter couDataAdapter, stuDataAdapter, scoDataAdapter, scoDataAdapter1;
        Boolean couNewRecord = false;
        Boolean stuNewRecord = false;
        string sex;
        string couNumber, stuNumber, classes;
        public MainForm()
        {
            InitializeComponent();
            this.StartPosition = FormStartPosition.CenterScreen;
            stuDataGridView.ReadOnly = true;
            stuDataGridView.ReadOnly = true;
        }
        //獲取課程號
        private void GetCouNumber()
        {
            OleDbDataReader sdr;
            string couSql = "select couNumber from course";
            sdr = data.GetReader(couSql);
            couNumberComboBox.Items.Clear();

            while (sdr.Read())
            {
                couNumberComboBox.Items.Add(sdr.GetValue(0));
            }
            sdr.Close();
            data.Con.Close();
            if (couNumberComboBox.Items.Count > 0)
            {

            }
            couNumber = couNumberComboBox.Text;
            data.Con.Close();
        }
        //獲取課程名
        private void GetCouName()
        {
            OleDbDataReader sdr;
            string couSql = "select couName from course";
            sdr = data.GetReader(couSql);

            couComboBox.Items.Clear();
            comboBox1.Items.Clear();
            while (sdr.Read())
            {

                couComboBox.Items.Add(sdr.GetValue(0));
                comboBox1.Items.Add(sdr.GetValue(0));
            }
            sdr.Close();
            data.Con.Close();

            data.Con.Close();
        }
        //獲取班級
        private void GetStuClass(ComboBox combox1, ComboBox combox2)
        {
            OleDbDataReader sdr;
            string stuSql = "select distinct class from stuInfor where college = '" + combox1.Text + "'";
            combox2.Items.Clear();
            sdr = data.GetReader(stuSql);
            while (sdr.Read())
            {
                combox2.Items.Add(sdr.GetValue(0));

            }
            sdr.Close();
            data.Con.Close();
            if (combox2.Items.Count > 0)
            {
                combox2.SelectedIndex = 0;
            }
            data.Con.Close();

        }

        //獲取學生學院
        private void GetStuCollege()
        {
            OleDbDataReader sdr;
            string stuSql = "select distinct college from stuInfor";
            sdr = data.GetReader(stuSql);
            while (sdr.Read())
            {


            }
            sdr.Close();
            data.Con.Close();
            if (collegeComboBox.Items.Count > 0)
            {
            }
            data.Con.Close();

        }
        //獲取學生學號
        private void GetStuNumber(ComboBox s, ComboBox c, ComboBox n)
        {

            OleDbDataReader sdr;

            string stuSql = "select  stuNumber,stuName from stuInfor where college = '" + s.Text
                + "' and class = '" + c.Text + "'";
            n.Items.Clear();
            sdr = data.GetReader(stuSql);
            while (sdr.Read())
            {

                n.Items.Add(sdr.GetValue(0));
            }
            sdr.Close();
            data.Con.Close();
            if (n.Items.Count > 0)
            {
                n.SelectedIndex = 0;
                stuNumber = n.Text;
            }

            data.Con.Close();
        }




        private void GetStuScore2()
        {
            OleDbDataReader sdr;
            string sql = "select  S.score from stuScore S,course C where C.couName='" + couComboBox.Text + "' and S.stuNumber = '" + stuNumberComboBox.Text + "' and S.couNumber=C.couNumber";

            sdr = data.GetReader(sql);
            if (sdr.Read())
            {

                scoTextBox.Text = sdr["score"].ToString();

            }
            sdr.Close();
            data.Con.Close();
        }



        //綁定學生數據
        private void GetStudent()
        {
            DataSet stuDataSet = new DataSet();
            string stuSql = "select * from stuInfor where college = '" + collegeComboBox.Text + "'and class = '" + classComboBox.Text + "'";
            stuDataSet = data.getData(stuSql);
            stuDataAdapter = new OleDbDataAdapter(data.command);
            stuDataAdapter.Fill(stuDataSet, "stuInfor");
            stuBindingSource.DataSource = stuDataSet.Tables["stuInfor"];
            stuDataGridView.DataSource = stuBindingSource;
            data.Con.Close();
        }
        //綁定課程數據
        private void GetCourse()
        {
            DataSet couDataSet = new DataSet();
            string couSql = "select * from course";
            couDataSet = data.getData(couSql);
            couDataAdapter = new OleDbDataAdapter(data.command);
            couDataAdapter.Fill(couDataSet, "course");
            couBindingSource.DataSource = couDataSet.Tables["course"];
            couDataGridView.DataSource = couBindingSource;
            data.Con.Close();
        }
        //綁定成績數據
        private void GetScore()
        {
            DataSet scoDataSet = new DataSet();
            string scoSql = "select C.couName,S.score from stuScore S,course C where S.stuNumber = '" + stuNumberComboBox.Text + "' and  S.couNumber = C.couNumber";
            scoDataSet = data.getData(scoSql);
            scoDataAdapter = new OleDbDataAdapter(data.command);
            scoDataAdapter.Fill(scoDataSet, "stuScore");
            scoBindingSource.DataSource = scoDataSet.Tables["stuScore"];
            scoDataGridView.DataSource = scoBindingSource;
            data.Con.Close();
        }
        private void ManagerForm_Load(object sender, EventArgs e)
        {
            stuBindingNavigator.BindingSource = stuBindingSource;
            couBindingNavigator.BindingSource = couBindingSource;

            collegeComboBox.SelectedIndex = 0;

            GetStudent();
            GetCourse();
            GetCouNumber();

            GetScore();
            GetCouName();

        }


        //獲取課程號
        private void couNumberChanged(object sender, EventArgs e)
        {
            OleDbDataReader sdr;
            string sql = "select  couName from course where couNumber='" + couNumberComboBox.Text + "'";
            couNumber = couNumberComboBox.Text;
            sdr = data.GetReader(sql);
            if (sdr.Read())
            {
                couNameTextBox1.Text = sdr["couName"].ToString();

            }
            sdr.Close();
            data.Con.Close();
        }
        //創建新課程
        private void newCourse(object sender, EventArgs e)
        {
            couNumberComboBox.Text = "";
            couNameTextBox1.Text = "";
            couNewRecord = true;
            couNumberComboBox.DropDownStyle = ComboBoxStyle.DropDown;
        }
        private void addCourse(string stuNumber, string stuName, string couNumber)
        {
            string score = "";
            string sql = "insert into stuScore (stuNumber,stuName,couNumber,score) values(" +
                     "'" + stuNumber + "'," +
                     "'" + stuName + "'," +
                     "'" + couNumber + "'," +
                     "'" + score + "')";

            data.Execute(sql);
            // data.Con.Close();
        }
        //保存課程信息
        private void saveCourse(object sender, EventArgs e)
        {
            string sql;
            string sql1 = "update stuScore set couNumber = '" + couNumberComboBox.Text + "' where couNumber = '" + couNumber + "'";
            if (couNewRecord == true)
            {
                sql = "insert into course (couNumber,couName) values(" +
                     "'" + couNumberComboBox.Text + "'," +
                     "'" + couNameTextBox1.Text + "')";
            }
            else
            {
                sql = "update course set " +
                    " couNumber = '" + couNumberComboBox.Text + "'," +
                    " couName = '" + couNameTextBox1.Text + "' where couNumber = '" + couNumber + "'";


            }
            try
            {
                int rowAffected = data.ExecuteSql(sql);
                if (rowAffected == 1)
                {
                    int rowAffected1 = data.ExecuteSql(sql1);
                    couNumberComboBox.Items.Add(couNumberComboBox.Text);
                    GetCourse();
                }

            }
            catch (OleDbException ex)
            {
                MessageBox.Show("該課程的信息已存在!", "出現錯誤",
                    MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            if (couNewRecord == true)
            {

                OleDbDataReader sdr;
                string sql2 = "select  * from stuInfor";
                sdr = data.GetReader(sql2);
                while (sdr.Read())
                {
                    addScore(sdr["stuNumber"].ToString(), sdr["stuName"].ToString(), couNumberComboBox.Text);

                }
                sdr.Close();
                data.Con.Close();

                couNumberComboBox.DropDownStyle = ComboBoxStyle.DropDownList;
                couNewRecord = false;
                couNumberComboBox.SelectedIndex = couNumberComboBox.Items.Count - 1;

            }
            data.Con.Close();
            GetCouName();
            collegeComboBox.SelectedIndex = 1;

        }
        //刪除課程信息
        private void deleteCourse(object sender, EventArgs e)
        {
            string sql = "delete from course  where couNumber = '" + couNumberComboBox.Text + "'";
            string sql1 = "delete from stuScore  where couNumber = '" + couNumberComboBox.Text + "'";
            try
            {
                int rowAffected = data.ExecuteSql(sql);

                if (rowAffected == 1)
                {
                    int rowAffected1 = data.ExecuteSql(sql1);
                    couNumberComboBox.Items.Remove(couNumberComboBox.Text);
                    if (couNumberComboBox.Items.Count > 0)
                    {
                        couNumberComboBox.SelectedIndex = 0;
                    }
                    else
                    {
                        couNumberComboBox.Text = "";
                        couNameTextBox1.Text = "";
                    }
                    GetCourse();
                    GetCouNumber();
                    GetCouName();
                }

            }
            catch (OleDbException ex)
            {
                MessageBox.Show(ex.Message, "出現錯誤",
                    MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            data.Con.Close();
            collegeComboBox.SelectedIndex = 1;
        }

        /*查詢課程
        private void queryCourse(object sender, EventArgs e)
        {
            OleDbDataReader sdr;
            string sql = "select  * from course where couNumber='" + findCouToolStripTextBox.Text + "'";
            sdr = data.GetReader(sql);
            if (sdr.Read())
            {
                couNumberComboBox.Text = sdr["couNumber"].ToString();
            }
            else
            {
                MessageBox.Show("你所查找的課程信息不存在!", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            sdr.Close();
            data.Con.Close();
        }*/
        //學院變化時
        private void collegeChanged(object sender, EventArgs e)
        {
            classComboBox.DropDownStyle = ComboBoxStyle.DropDown;
            classComboBox.Text = "";
            stuNumberComboBox.DropDownStyle = ComboBoxStyle.DropDown;
            stuNumberComboBox.Text = "";           
            majorTextBox.Text = "";
            nameTextBox.Text = "";
            GetStuClass(collegeComboBox, classComboBox);
            classes = classComboBox.Text;


        }

        private void stuNumberChanged(object sender, EventArgs e)
        {
            OleDbDataReader sdr;
            string sql = "select  * from stuInfor where college='" + collegeComboBox.Text + "' and stuNumber = '" + stuNumberComboBox.Text + "'";
            stuNumber = stuNumberComboBox.Text;
            sdr = data.GetReader(sql);
            if (sdr.Read())
            {
                nameTextBox.Text = sdr["stuName"].ToString();
                majorTextBox.Text = sdr["major"].ToString();
            }
            sdr.Close();
            data.Con.Close();
            GetScore();
        }
        //添加學生
        private void newStudent(object sender, EventArgs e)
        {
            nameTextBox.Text = "";