using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace Project1
{
    /// <summary>
    /// The implementation of the UserDatabase Interface.
    /// Database connection and disconnection functions,
    /// and SQL queries are involved.
    /// </summary>
    public class UserDatabaseImp : UserDatabase
    {
        public UserDatabaseImp()
        {
            Console.WriteLine("UserDatabaseImp instance created.");
        }

        #region UserDatabase Members

        /// <summary>
        /// Add a customer record to the database
        /// </summary>
        /// <param name="cust">An object of Customer</param>
        public void add(Customer cust)
        {
            try
            {
                string database = "infybank.mdb";
                string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + database;
                OleDbConnection dbConnection = new OleDbConnection(connectionString);
                string query = "INSERT INTO Customer VALUES ('" + cust.sCustID + "', '" + cust.sUserID
                                + "', '" + cust.sPassword + "', '" + cust.sName + "', '" + cust.sGender
                                + "', '" + cust.dDob.ToShortDateString() + "', '" + cust.sAddress
                                + "', '" + cust.sCity + "', '" + cust.sState + "', " + cust.iPin
                                + ", " + cust.iTel + ", " + cust.iFax + ", '" + cust.sEmail + "')";
                OleDbCommand command = new OleDbCommand(query, dbConnection);
                dbConnection.Open();
                command.ExecuteNonQuery();
                Console.WriteLine("Customer added.");
                dbConnection.Close();

            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
        }

        /// <summary>
        /// Remove a customer record from the database
        /// </summary>
        /// <param name="cust">An object of Customer</param>
        public void remove(Customer cust)
        {
            try
            {
                string database = "infybank.mdb";
                string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + database;
                OleDbConnection dbConnection = new OleDbConnection(connectionString);
                string query = "DELETE FROM Customer WHERE CustomerID = '" + cust.sCustID + "'";
                OleDbCommand command = new OleDbCommand(query, dbConnection);
                dbConnection.Open();
                command.ExecuteNonQuery();
                Console.WriteLine("Customer deleted.");
                dbConnection.Close();

            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
        }

        /// <summary>
        /// Update a customer record in the database
        /// </summary>
        /// <param name="user">An object of Customer</param>
        public void update(Customer cust)
        {
            try
            {
                string database = "infybank.mdb";
                string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + database;
                OleDbConnection dbConnection = new OleDbConnection(connectionString);
                string query = "UPDATE Customer SET Password = '" + cust.sPassword + "', Gender = '"
                                + cust.sGender + "', DateOfBirth = '" + cust.dDob.ToShortDateString()
                                + "', Address = '" + cust.sAddress + "', City = '" + cust.sCity
                                + "', State = '" + cust.sState + "', Pin = " + cust.iPin
                                + ", Telephone = " + cust.iTel + ", Fax = " + cust.iFax
                                + ", Email = '" + cust.sEmail + "' WHERE CustomerID = '" + cust.sCustID + "'";
                OleDbCommand command = new OleDbCommand(query, dbConnection);
                dbConnection.Open();
                command.ExecuteNonQuery();
                Console.WriteLine("Customer updated.");
                dbConnection.Close();

            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
        }

        /// <summary>
        /// Search for a customer record in the database
        /// </summary>
        /// <param name="custID">The customer ID of a customer</param>
        public Customer search(string custID)
        {
            string cid = "";
            string uid = "";
            string pwd = "";
            string name = "";
            string gender = "";
            DateTime dob = new DateTime(0);
            string addr = "";
            string city = "";
            string state = "";
            string email = "";
            int pin = 0;
            int tel = 0;
            int fax = 0;
            int flag = 0;

            try
            {
                string database = "infybank.mdb";
                string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + database;
                OleDbConnection dbConnection = new OleDbConnection(connectionString);

                string query = "SELECT * FROM Customer WHERE CustomerID = '" + custID + "'";

                OleDbCommand command = new OleDbCommand(query, dbConnection);
                dbConnection.Open();

                OleDbDataReader reader = command.ExecuteReader();

                if (reader.Read())
                {
                    Console.WriteLine("Customer found.");

                    cid = reader.GetString(0).ToString();
                    uid = reader.GetString(1).ToString();
                    pwd = reader.GetString(2).ToString();
                    name = reader.GetString(3).ToString();
                    gender = reader.GetString(4).ToString();
                    dob = reader.GetDateTime(5);
                    addr = reader.GetString(6).ToString();
                    city = reader.GetString(7).ToString();
                    state = reader.GetString(8).ToString();
                    pin = reader.GetInt32(9);
                    tel = reader.GetInt32(10);
                    fax = reader.GetInt32(11);
                    email = reader.GetString(12).ToString();
                    flag = 1;
                }
                else
                {
                    flag = 0;
                }
                reader.Close();
                dbConnection.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            if (flag == 1)
                return new Customer(uid, pwd, cid, name, gender, dob, addr, city, state, pin, tel, fax, email);
            else
                return null;
        }

        #endregion

        public List<Customer> getAll()
        {
            List<Customer> custList = new List<Customer>();
            string cid = "";
            string uid = "";
            string pwd = "";
            string name = "";
            string gender = "";
            DateTime dob = new DateTime(0);
            string addr = "";
            string city = "";
            string state = "";
            string email = "";
            int pin = 0;
            int tel = 0;
            int fax = 0;
            //  int flag = 0;

            try
            {
                string database = "infybank.mdb";
                string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + database;
                OleDbConnection dbConnection = new OleDbConnection(connectionString);

                string query = "SELECT * FROM Customer";

                OleDbCommand command = new OleDbCommand(query, dbConnection);
                dbConnection.Open();

                OleDbDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    Console.WriteLine("Customer found.");

                    cid = reader.GetString(0).ToString();
                    uid = reader.GetString(1).ToString();
                    pwd = reader.GetString(2).ToString();
                    name = reader.GetString(3).ToString();
                    gender = reader.GetString(4).ToString();
                    dob = reader.GetDateTime(5);
                    addr = reader.GetString(6).ToString();
                    city = reader.GetString(7).ToString();
                    state = reader.GetString(8).ToString();
                    pin = reader.GetInt32(9);
                    tel = reader.GetInt32(10);
                    fax = reader.GetInt32(11);
                    email = reader.GetString(12).ToString();
                    //   flag = 1;

                    custList.Add(new Customer(uid, pwd, cid, name, gender, dob, addr, city,
                                        state, pin, tel, fax, email));
                }

                reader.Close();
                dbConnection.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            //    if (flag == 1)
            return custList;
            //    else
            //        return null;

        }



    }
}