///Auther: Surajit Patra
///DataBase.cs
///surajitpatra@gmail.com, Mob: 8007369459

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

namespace RFIDAuthSystem
{
    class DataBase
    {
        private MySqlConnection connection;
        private string server;
        private string database;
        private string uid;
        private string password;

        public DataBase()
        {
            Initialize();
        }

        public void Initialize()
        {
            server = "localhost";
            database = "smartauthentication";
            uid = "root";
            password = "";
            string connectionString;
            connectionString = "SERVER=" + server + ";" + "DATABASE=" +
            database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";";

            connection = new MySqlConnection(connectionString);            
        }

        private bool OpenConnection()
        {
            try
            {
                connection.Open();
                return true;
            }
            catch (MySqlException ex)
            {
                switch (ex.Number)
                {
                    case 0:
                        MessageBox.Show("Cannot connect to server.  Contact administrator");
                        break;

                    case 1045:
                        MessageBox.Show("Invalid username/password, please try again");
                        break;
                }
                return false;
            }
        }

        private bool CloseConnection()
        {
            try
            {
                connection.Close();
                return true;
            }
            catch (MySqlException ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }
        }

        public bool Insert(string bookID, string bookName, string autherName, string empID)
        {
            string query = "INSERT INTO library_management (Book_ID, Book_Name, Author_Name, Employee_ID) VALUES ('"+bookID+"', '"+bookName+"', '"+autherName+"', '"+empID+"')";

            if (this.OpenConnection() == true)
            {
                MySqlCommand cmd = new MySqlCommand(query, connection);
                cmd.ExecuteNonQuery();
                this.CloseConnection();
            }
            return true;
        }

        public bool Delete(string empID)
        {
            string query = "DELETE FROM library_management WHERE Employee_ID = '" + empID + "'";

            if (this.OpenConnection() == true)
            {
                MySqlCommand cmd = new MySqlCommand(query, connection);
                cmd.ExecuteNonQuery();
                this.CloseConnection();
            }
            return true;
        }

        public bool CheckBookIssued(string value)
        {
            string query = "SELECT * FROM library_management WHERE Book_ID = '" + value + "'";
            if (this.OpenConnection() == true)
            {
                MySqlCommand cmd = new MySqlCommand(query, connection);
                MySqlDataReader dataReader = cmd.ExecuteReader();
                while (dataReader.Read())
                {
                    if (dataReader["Book_ID"] != DBNull.Value)
                    {
                        dataReader.Close();
                        this.CloseConnection();
                        return true;
                    }
                }
                dataReader.Close();
                this.CloseConnection();
            }            
            
            return false;
        }

        public List<string>[] Select(string value)
        {
            string query = "SELECT * FROM laptop_authentication WHERE Serial_Number = '" + value + "'";

            List<string>[] list = new List<string>[9];
            list[0] = new List<string>();
            list[1] = new List<string>();
            list[2] = new List<string>();
            list[3] = new List<string>();
            list[4] = new List<string>();
            list[5] = new List<string>();
            list[6] = new List<string>();
            list[7] = new List<string>();
            list[8] = new List<string>();

            if (this.OpenConnection() == true)
            {
                MySqlCommand cmd = new MySqlCommand(query, connection);
                MySqlDataReader dataReader = cmd.ExecuteReader();
                while (dataReader.Read())
                {
                    list[0].Add(dataReader["Serial_Number"] + "");
                    list[1].Add(dataReader["Employee_Name"] + "");
                    list[2].Add(dataReader["Salary_Code"] + "");
                    list[3].Add(dataReader["Project"] + "");
                    list[4].Add(dataReader["Designation"] + "");
                    list[5].Add(dataReader["Laptop_Detail"] + "");
                    list[6].Add(dataReader["Model"] + "");
                    list[7].Add(dataReader["Asset_Code"] + "");
                    list[8].Add(dataReader["Image"] + "");
                }

                dataReader.Close();
                this.CloseConnection();
                return list;
            }
            else
            {
                return list;
            }
        }
    }
}
