/*
PROJECT C#
*/
using System;
using System.Collections.Generic;
using System.Drawing;
using System.Windows.Forms;
using System.Data.SqlClient;
using MySql.Data.MySqlClient;

namespace SQLFramework
{
    public partial class MainForm : Form
    {
        public MySqlConnection dbHandle = new MySqlConnection("server=localhost;user=root;database=sqlsharp;password=;");
        public int SelectedID{get;set;}
        public List<int> mark = new List<int>();
        public MainForm()
        {
            InitializeComponent();
            try{
                dbHandle.Open();
            }catch(Exception e){
                MessageBox.Show(e.Message,"Error",MessageBoxButtons.OK);
                Environment.Exit(0);
                return;
            }
            InitializeProgram();
        }
        public void InitializeProgram(){
            try{dbHandle.Open();}catch(Exception){}
            studentsBox.Items.Clear();
            MySqlCommand getCMD = new MySqlCommand("SELECT * FROM `students` ORDER BY `Id`",dbHandle);
            int i = 1;
            using(MySqlDataReader reader = getCMD.ExecuteReader()){
                while(reader.Read()){
                    if(SelectedID == 0)SelectedID = reader.GetInt32("Id");
                    string name = "["+i+"] "+reader.GetString("Name");i++;
                    studentsBox.Items.Add(name);
                }
                reader.Close();
            }
            if(studentsBox.Items.Count > 0)studentsBox.SelectedIndex = 0;
            UpdateMarks();
            dbHandle.Close();
        }
        void MainFormLoad(object sender, EventArgs e)
        {

        }
        void StudentsBoxSelectedIndexChanged(object sender, EventArgs e)
        {
            SelectedID = studentsBox.SelectedIndex+1;
            UpdateMarks();
        }
        void UpdateMarks(){
            try{dbHandle.Open();}catch(Exception){}
            MySqlCommand updCMD = new MySqlCommand("SELECT * FROM `students` WHERE `Id` = '"+SelectedID+"'",dbHandle);
            using(MySqlDataReader reader = updCMD.ExecuteReader()){
                reader.Read();
                mark.Clear();
                if(false == reader.HasRows){
                    markLabel.Text = "Ученик:\nНет данных\n\nОценки:\n\nНет данных";
                }else{
                    markLabel.Text = "Ученик:\n"+reader.GetString("Name")+"\n\nОценки:\n\n";
                    string marks = reader.GetString("Marks");
                    if(string.IsNullOrWhiteSpace(marks)){
                        markLabel.Text += "Оценок нет";
                    }else{
                        int i=0;
                        int summ=0;
                        foreach(char m in marks){
                            mark.Add((int)Char.GetNumericValue(m));
                            summ += (int)Char.GetNumericValue(m);
                            if(++i < marks.Length)markLabel.Text += m+"; ";
                            else markLabel.Text += m;
                        }
                        markLabel.Text += "\n\nСредний балл:\n\n";
                        float middle = ((float)summ/(float)i);

                        markLabel.Text += string.Format("{0:N1}",middle);
                    }
                }
                reader.Close();
            }
            dbHandle.Close();
        }
        void NewStudentBoxTextChanged(object sender, EventArgs e)
        {
        }
        void NewStudentButtonClick(object sender, EventArgs e)
        {
            if(newStudentBox.Text.Equals("Help")){
                MessageBox.Show("ClearTable - очистить таблицу учеников","Help",MessageBoxButtons.OK);
                newStudentBox.Text = string.Empty;
                return;
            }
            if(newStudentBox.Text.Equals("ClearTable")){
                try{dbHandle.Open();}catch(Exception){}
                MySqlCommand cmd = new MySqlCommand("TRUNCATE TABLE `students`",dbHandle);
                cmd.ExecuteNonQuery();
                MessageBox.Show("Таблица учеников очищена","Successful",MessageBoxButtons.OK);
                dbHandle.Close();
                InitializeProgram();
                newStudentBox.Text = string.Empty;
                return;
            }
            if(newStudentBox.Text.Length < 3){
                MessageBox.Show("Имя слишком короткое","Error",MessageBoxButtons.OK);
                newStudentBox.Text = string.Empty;
                return;
            }
            if(newStudentBox.Text.Length > 28){
                MessageBox.Show("Имя слишком длинное","Error",MessageBoxButtons.OK);
                newStudentBox.Text = string.Empty;
                return;
            }
            try{dbHandle.Open();}catch(Exception){}
            string query = "INSERT INTO `students` (`Name`) VALUES ('"+newStudentBox.Text+"')";
            MySqlCommand Ccmd = new MySqlCommand(query,dbHandle);
            Ccmd.ExecuteNonQuery();
            dbHandle.Close();
            InitializeProgram();
            newStudentBox.Text = string.Empty;
        }
        void MarkButtonClick(object sender, EventArgs e)
        {
            if(studentsBox.Items.Count <= 0){
                MessageBox.Show("Ученик не выбран","Error",MessageBoxButtons.OK);
                return;
            }
            string marks = string.Empty;
            foreach(int x in mark)
            {
                marks += x.ToString();
            }
            marks += markBox.Value.ToString();
            try{dbHandle.Open();}catch(Exception){}
            MySqlCommand cmd = new MySqlCommand("UPDATE `students` SET `Marks` = '"+marks+"' WHERE `Id` = '"+SelectedID+"'",dbHandle);
            cmd.ExecuteNonQuery();
            dbHandle.Close();
            UpdateMarks();
        }
        void markAddChanged(object sender, EventArgs e)
        {
            markButton.Text = "Выставить оценку '"+markBox.Value.ToString()+"'";
        }
    }
}