using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
using MySql.Data.Types;

namespace QC_Paper_Defect
{
    public partial class MenuUser : Form
    {
        MySqlConnection connection;
        MySqlCommand myComm = new MySqlCommand();

        public MenuUser()
        {
            InitializeComponent();
            connection = new MySqlConnection("server=localhost;user id=root;password=;database=ta;");

            //Get all items in datatable
            DataTable DTItems = GetAllItems();

            //Fill grid with items
            dataGridView1.DataSource = DTItems;



        }
        DataTable GetAllItems()
        {
            try
            {
                //prepare query to get all records from items table
                string query = "select * from tb_user";
                //prepare adapter to run query

                MySqlDataAdapter adapter = new MySqlDataAdapter(query, connection);
                DataSet DS = new DataSet();
                //get query results in dataset
                adapter.Fill(DS);
                //return datatable with all records
                return DS.Tables[0];

            }
            catch (Exception ex)
            {
                //MessageBox.Show(ex.Message);
                Console.WriteLine(ex.Message);
            }
            return null;
        }

        private void btn_tmbh_Click(object sender, EventArgs e)
        {
            connection.Open();
            myComm.Connection = connection;
            string SQL = "INSERT INTO tb_user (user_id,name,password,alamat,no_tlp,jabatan,level) VALUES ('"+tf_user.Text+"','"+tf_name.Text+"','"+tf_pass.Text+"','"+tf_alamat.Text+"','"+tf_tlp.Text+"','"+tf_jabatan.Text+"','"+tf_level.Text+"');";
            myComm.CommandText = SQL;

            try
            {
                myComm.ExecuteNonQuery();
                dataGridView1.DataSource = GetAllItems();
                // scrip Auto scroll ini trh mana???
                dataGridView1.FirstDisplayedCell = dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells[0];
                //MessageBox.Show("Added", "MySQL says");
                Console.WriteLine("Added: " + tf_user.Text + "," + tf_name.Text + "," + tf_pass.Text + "," + tf_alamat.Text + "," + tf_tlp.Text + "," + tf_jabatan.Text + "," + tf_level );
            }
            catch (MySqlException ex)
            {
                //MessageBox.Show("Unable to add because: " + ex.Message , "MySQL says");
                Console.WriteLine("Unable to add because: " + ex.Message);
            }
            connection.Close();

        }

        private void Simpan_Click(object sender, EventArgs e)
        {
            connection.Open();
            myComm.Connection = connection;
            string SQL = "INSERT INTO tb_user (user_id,name,password,alamat,no_tlp,jabatan,level) VALUES ('" + tf_user.Text + "','" + tf_name.Text + "','" + tf_pass.Text + "','" + tf_alamat.Text + "','" + tf_tlp.Text + "','" + tf_jabatan.Text + "','" + tf_level.Text + "');";
            myComm.CommandText = SQL;

            try
            {
                myComm.ExecuteNonQuery();
                dataGridView1.DataSource = GetAllItems();
                //MessageBox.Show("Added", "MySQL says");
                Console.WriteLine("Added: " + tf_user.Text + "," + tf_name.Text + "," + tf_pass.Text + "," + tf_alamat.Text + "," + tf_tlp.Text + "," + tf_jabatan.Text + "," + tf_level);
            }
            catch (MySqlException ex)
            {
                //MessageBox.Show("Unable to add because: " + ex.Message , "MySQL says");
                Console.WriteLine("Unable to add because: " + ex.Message);
            }
            connection.Close();
        }

        private void btn_batal_Click(object sender, EventArgs e)
        {
            Close();
        }

        private void btn_hps_Click(object sender, EventArgs e)
        {
            connection.Open();
            myComm.Connection = connection;
            string SQL = "DELETE from tb_user WHERE id_user='"+ tf_user.Text +"';";
            connection.Close();
        }

        private void btn_ubah_Click(object sender, EventArgs e)
        {
            connection.Open();
            myComm.Connection = connection;
            string SQL = "UPDATE tb_user SET name = '" + tf_name.Text + "' WHERE id_user = '" + tf_user.Text + "';";
            connection.Close();

            string selectedItem = dataGridView1.SelectedRows[0].Cells[1].Value.ToString();
            tf_user.Text = selectedItem;
        }

        private void dataGridView1_Sorted(object sender, EventArgs e)
        {
            this.dataGridView1.FirstDisplayedCell = this.dataGridView1.CurrentCell;
        }

    }
}