/*
 * Nithisaran Suksom
 * RERU
 * 29 September 2020
 */
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 book
{
    public partial class Form1 : Form
    {
        public static string connector = "server=localhost;Database=book2;User ID=root;password=";
        public Form1()
        {
            InitializeComponent();
        }


        public void loadBookData()
        {
            string query = "SELECT * FROM book_Details";
            MySqlConnection myConn = new MySqlConnection(connector);
            MySqlDataAdapter myAdap = new MySqlDataAdapter();
            MySqlCommand myComm = new MySqlCommand(query, myConn);
            myAdap.SelectCommand = myComm;
            DataTable dTable = new DataTable();
            myAdap.Fill(dTable);
            dataGridView1.DataSource = dTable;
            dataGridView1.Columns[0].HeaderText = "ID";
            dataGridView1.Columns[1].HeaderText = "ชื่อหนังสือ";
            dataGridView1.Columns[2].HeaderText = "ผู้เเต่ง";
            dataGridView1.Columns[3].HeaderText = "ราคา";
            dataGridView1.Columns[4].HeaderText = "ประเภทหนังสือ";

        }
        public void loadBookTypeData()
        {
            string query = "SELECT * FROM book_type";
            MySqlConnection myConn = new MySqlConnection(connector);
            MySqlDataAdapter myAdap = new MySqlDataAdapter();
            MySqlCommand myComm = new MySqlCommand(query, myConn);
            myAdap.SelectCommand = myComm;
            MySqlDataAdapter adapter;
            DataTable table = new DataTable();
            adapter = new MySqlDataAdapter("SELECT * FROM book_type", connector);
            adapter.Fill(table);
            listBox1.DataSource = table;
            listBox1.DisplayMember = "book_Type_Name";
            listBox1.ValueMember = "book_Type_ID";
            DataTable dTable = new DataTable();
            myAdap.Fill(dTable);
            dataGridView2.DataSource = dTable;
            dataGridView2.Columns[0].HeaderText = "ID";
            dataGridView2.Columns[1].HeaderText = "ชื่อประเภทหนังสือ";
            dataGridView2.Refresh();
            dataGridView2.Update();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            loadBookTypeData();
            loadBookData();
            this.dataGridView1.RefreshEdit();
            this.dataGridView2.RefreshEdit();
        }


        private void button1_Click_1(object sender, EventArgs e)
        {
            MySqlConnection myConn = new MySqlConnection(connector);
            string text = listBox1.GetItemText(listBox1.SelectedValue);
            string query = $"INSERT INTO book_details(book_Details_Name,book_Details_Author,book_Details_Price,book_Type_ID) VALUES ('{textBox2.Text}','{textBox3.Text}','{maskedTextBox1.Text}',{text})";
            myConn.Open();
            MySqlCommand myCommand = new MySqlCommand(query, myConn);
            myCommand.ExecuteNonQuery();
            MessageBox.Show("Save Data");
            myConn.Close();
            this.dataGridView2.RefreshEdit();
        }

        private void button2_Click_1(object sender, EventArgs e)
        {
            string query = $"INSERT INTO book_type (book_Type_Name) VALUES ('{textBox1.Text}')";
            MySqlConnection myConn = new MySqlConnection(connector);
            myConn.Open();
            MySqlCommand myCommand = new MySqlCommand(query, myConn);
            myCommand.ExecuteNonQuery();
            MessageBox.Show("Save Data");
            myConn.Close();
            this.dataGridView2.RefreshEdit();
        }

        private void button3_Click_1(object sender, EventArgs e)
        {
            loadBookTypeData();
            loadBookData();
            this.dataGridView1.RefreshEdit();
            this.dataGridView2.RefreshEdit();
        }
    }
}