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 MySql.Data.MySqlClient;

namespace Exam18
{
    public partial class Form1 : Form
    {
        MySqlConnection connection;
        MySqlCommand command;
        String connectionString = "server=localhost;" +
                                    "database=mydb;" +
                                    "user=root;" +
                                    "password=524656bnm;";
        String commandString;

        public Form1()
        {
            InitializeComponent();
            connection = new MySqlConnection();
            connection.ConnectionString = connectionString;
            command = new MySqlCommand();
            command.Connection = connection;

            commandString = "select VacancyName, CompanyName from vacancy;";
            command.CommandText = commandString;
            connection.Open();
            MySqlDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {
                comboBox1.Items.Add(reader[0].ToString() + "|" + reader[1].ToString());

            }
            connection.Close();

        }

        private void Form1_Load(object sender, EventArgs e)
        {

        }

        private void button2_Click(object sender, EventArgs e)
        {
            if (comboBox1.Text != "")
            {
                String[] row;
                row = comboBox1.Text.Split('|');
                MessageBox.Show(row[0] + " " + row[1]);

                commandString = "select expirience from vacancy where VacancyName=@vacname, CompanyName=@compname;";
                command.CommandText = commandString;
                command.Parameters.Add("@vacname", MySqlDbType.VarChar, 45).Value = row[0];
                command.Parameters.Add("@compname", MySqlDbType.VarChar, 100).Value = row[1];
                int exp;
                connection.Open();
                MySqlDataReader reader = command.ExecuteReader();
                exp = Convert.ToInt32(reader[1]);

                command.Parameters.Clear();


            }

        }

        private void button1_Click(object sender, EventArgs e)
        {
            commandString = "select u.Name, u.Surname, u.Pathronymic, s.institution, s.DateOfStartStud, s.DateOfEndStud, s.DateOfPlace, p.name, s.Experience " +
                                "from user u join  summary s on (s.nUserId = u.nID) " +
                                "join positions p on (p.nID = s.Positions_nID) where s.dateofplace between @date1 and @date2;";
            command.CommandText = commandString;
            command.Parameters.Add("@date1", MySqlDbType.Date).Value = dateTimePicker1.Value;
            command.Parameters.Add("@date2", MySqlDbType.Date).Value = dateTimePicker2.Value;

            MySqlDataAdapter adapter = new MySqlDataAdapter(command);
            DataTable dt = new DataTable();
            adapter.Fill(dt);
            dataGridView1.DataSource = dt;
            connection.Close();
            command.Parameters.Clear();
        }
    }
}