//using System.Data.SqlClient;
using Big.Lito.Data.DataModel;
using Big.Lito.Data.Sql;

namespace Big.Lito.Data.DataAccess
{
    //using System;
    using System.Data;
    using System.Data.SqlClient;

    public class ProjectSQL : ConnectionSQL, IProjectSQL
    {
        /// <summary>
        /// Method to get all club members
        /// </summary>
        /// <returns>Data table</returns>
        public DataTable GetAllProjects()
        {
            DataTable dataTable = new DataTable();

            string connectionString = this.ConnectionString;
            SqlConnection sqlCon = new SqlConnection(connectionString);

            sqlCon.Open();
            //string commandString = "SELECT * FROM ProjectSettings";
            string commandString = Scripts.SqlGetAllProjects;

            SqlCommand sqlCmd = new SqlCommand(commandString, sqlCon);
            //SqlDataReader dr = sqlCmd.ExecuteReader();

            SqlDataAdapter sd = new SqlDataAdapter();
            sd.SelectCommand = sqlCmd;

            sd.Fill(dataTable);

            return dataTable;
        }

        /// <summary>
        /// Method to get all club members
        /// </summary>
        /// <returns>Data table</returns>
        public DataTable GetAllProjectsForGridView()
        {
            DataTable dataTable = new DataTable();

            string connectionString = this.ConnectionString;
            SqlConnection sqlCon = new SqlConnection(connectionString);

            sqlCon.Open();
            //string commandString = "SELECT * FROM ProjectSettings";
            string commandString = Scripts.SqlGetAllProjectsForGridView;

            SqlCommand sqlCmd = new SqlCommand(commandString, sqlCon);
            //SqlDataReader dr = sqlCmd.ExecuteReader();

            SqlDataAdapter sd = new SqlDataAdapter();
            sd.SelectCommand = sqlCmd;

            sd.Fill(dataTable);

            return dataTable;
        }

        /// <summary>
        /// Method to get club member by Id
        /// </summary>
        /// <param name="id">member id</param>
        /// <returns>Data row</returns>
        public DataRow GetProjectById(int id)
        {
            DataTable dataTable = new DataTable();
            DataRow dataRow;
            string connectionString = this.ConnectionString;

            using (SqlConnection sqlCon = new SqlConnection(connectionString))
            {
                using (SqlCommand comm = new SqlCommand())
                {
                    string cmdString = Scripts.sqlGetProjectById;

                    comm.Connection = sqlCon;
                    comm.CommandText = cmdString;
                    comm.Parameters.AddWithValue("@Id", id);
                   try
                    {
                        SqlDataAdapter sd = new SqlDataAdapter();
                        sd.SelectCommand = comm;
                        sd.Fill(dataTable);

                        //sqlCon.Open();
                        //comm.ExecuteNonQuery();
                    }
                    catch (SqlException e)
                    {
                        // do something with the exception
                        // don't hide it
                    }

                    // Get the datarow from the table
                    dataRow = dataTable.Rows.Count > 0 ? dataTable.Rows[0] : null;
                   
                }
            }

            return dataRow;
        }

        /// <summary>
        /// Method to get project by name
        /// </summary>
        /// <param name="name">project name</param>
        /// <returns>Data row</returns>
        public DataRow GetProjectByName(string name)
        {
            DataTable dataTable = new DataTable();
            DataRow dataRow;
            string connectionString = this.ConnectionString;

            using (SqlConnection sqlCon = new SqlConnection(connectionString))
            {
                using (SqlCommand comm = new SqlCommand())
                {
                    string cmdString = Scripts.sqlGetProjectByName;

                    comm.Connection = sqlCon;
                    comm.CommandText = cmdString;
                    comm.Parameters.AddWithValue("@ProjectName", name);
                    try
                    {
                        SqlDataAdapter sd = new SqlDataAdapter();
                        sd.SelectCommand = comm;
                        sd.Fill(dataTable);

                        //sqlCon.Open();
                        //comm.ExecuteNonQuery();
                    }
                    catch (SqlException e)
                    {
                        // do something with the exception
                        // don't hide it
                    }

                    // Get the datarow from the table
                    dataRow = dataTable.Rows.Count > 0 ? dataTable.Rows[0] : null;

                }
            }

            return dataRow;
        }

        /// <summary>
        /// Method to add new member
        /// </summary>
        /// <param name="project">club member model</param>
        /// <returns>true or false</returns>
        public bool AddProject(ProjectModel project)
        {
            string connectionString = this.ConnectionString;

            //DateTime dateTimeVariable = //some DateTime value, e.g. DateTime.Now;
            //SqlCommand cmd = new SqlCommand("INSERT INTO <table> (<column>) VALUES (@value)", connection);
            //cmd.Parameters.AddWithValue("@value", dateTimeVariable);
            //cmd.ExecuteNonQuery();

            using (SqlConnection sqlCon = new SqlConnection(connectionString))
            {
                using (SqlCommand comm = new SqlCommand())
                {
                    string cmdString = Scripts.SqlInsertProject;

                    comm.Connection = sqlCon;
                    comm.CommandText = cmdString;
                    comm.Parameters.AddWithValue("@ProjectName", project.ProjectName);
                    comm.Parameters.AddWithValue("@Author", project.Author);
                    comm.Parameters.AddWithValue("@Created", project.CreatedDate);
                    comm.Parameters.AddWithValue("@LastUpdated", project.UpdatedDate);
                    comm.Parameters.AddWithValue("@HomeDir", project.HomeDir);
                    try
                    {
                        sqlCon.Open();
                        comm.ExecuteNonQuery();
                    }
                    catch(SqlException e)
                    {
                        // do something with the exception
                        // don't hide it
                    }
                }
            }

            return true;
        }

        /// <summary>
        /// Method to update project
        /// </summary>
        /// <param name="project">project</param>
        /// <returns>true / false</returns>
        public bool UpdateProject(ProjectModel project)
        {
            string connectionString = this.ConnectionString;

            using (SqlConnection sqlCon = new SqlConnection(connectionString))
            {
                using (SqlCommand comm = new SqlCommand())
                {
                    string cmdString = Scripts.SqlUpdateProject;

                    comm.Connection = sqlCon;
                    comm.CommandText = cmdString;
                    comm.Parameters.AddWithValue("@Id", project.Id);
                    comm.Parameters.AddWithValue("@ProjectName", project.ProjectName);
                    comm.Parameters.AddWithValue("@Author", project.Author);
                    comm.Parameters.AddWithValue("@LastUpdated", project.UpdatedDate);
                    comm.Parameters.AddWithValue("@HomeDir", project.HomeDir);
                    try
                    {
                        sqlCon.Open();
                        comm.ExecuteNonQuery();
                    }
                    catch (SqlException e)
                    {
                        // do something with the exception
                        // don't hide it
                    }
                }
            }

            return true;
        }

        /// <summary>
        /// Method to update project
        /// </summary>
        /// <param name="project">project</param>
        /// <returns>true / false</returns>
        public bool UpdateProjectIniGen(ProjectModel project)
        {
            string connectionString = this.ConnectionString;

            using (SqlConnection sqlCon = new SqlConnection(connectionString))
            {
                using (SqlCommand comm = new SqlCommand())
                {
                    string cmdString = Scripts.SqlUpdateProjectIniGen;

                    comm.Connection = sqlCon;
                    comm.CommandText = cmdString;
                    comm.Parameters.AddWithValue("@Id", project.Id);
                    comm.Parameters.AddWithValue("@LastUpdated", project.UpdatedDate);
                    comm.Parameters.AddWithValue("@FrameWidth", project.FrameWidth);
                    comm.Parameters.AddWithValue("@FrameHeight", project.FrameHeight);
                    comm.Parameters.AddWithValue("@ResW", project.ResW);
                    comm.Parameters.AddWithValue("@ResH", project.ResH);
                    comm.Parameters.AddWithValue("@ObjName", project.ObjName);
                    comm.Parameters.AddWithValue("@ObjW", project.ObjWidth);
                    comm.Parameters.AddWithValue("@ObjH", project.ObjHeight);
                    comm.Parameters.AddWithValue("@NRows", project.NRows);
                    comm.Parameters.AddWithValue("@NCols", project.NCols);
                    comm.Parameters.AddWithValue("@CoX", project.CoX);
                    comm.Parameters.AddWithValue("@CoY", project.CoY);

                    try
                    {
                        sqlCon.Open();
                        comm.ExecuteNonQuery();
                    }
                    catch (SqlException e)
                    {
                        // do something with the exception
                        // don't hide it
                    }
                }
            }

            return true;
        }

        /// <summary>
        /// Method to delete a club member
        /// </summary>
        /// <param name="id">member id</param>
        /// <returns>true / false</returns>
        public bool DeleteProject(int id)
        {
            string connectionString = this.ConnectionString;

            using (SqlConnection sqlCon = new SqlConnection(connectionString))
            {
                using (SqlCommand comm = new SqlCommand())
                {
                    string cmdString = Scripts.SqlDeleteProject;

                    comm.Connection = sqlCon;
                    comm.CommandText = cmdString;
                    comm.Parameters.AddWithValue("@Id", id);
                    try
                    {
                        sqlCon.Open();
                        comm.ExecuteNonQuery();
                    }
                    catch (SqlException e)
                    {
                        // do something with the exception
                        // don't hide it
                    }
                }
            }

            return true;
        }

    }
}