using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using Maser.Feanor.Model;
using System.Data.SqlClient;
using System.Collections.ObjectModel;






namespace Maser.Feanor.Biz
{
    public enum ProjectStatus
    {
        InProgress,
        Finished,
        FinishedLastMonth,
        FinishedLastWeek,
        None
    }


    public class Projects
    {
        public Projects() { }

        public Project GetByPK(Int32 Project_PK)
        {            
            string query = String.Format("Select * from Projects where PK = {0}", Project_PK);
            try
            {
                return GetByQuery(query);
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public List<Project> GetAllActive()
        {
            string query = String.Format("Select * from Projects WHERE Stop < '2000-01-01' order by Project ASC"); // PROJECT active if stop-date is in past (stop date == null (not set))
            try
            {
                return GetListByQuery(query);
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public List<Project> GetAllFinished()
        {
            string query = String.Format("Select * from Projects WHERE Stop > '2000-01-01' order by Project ASC"); // PROJECT finished if stop-date != null (so stop-date is set)
            try
            {
                return GetListByQuery(query);
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public List<Project> GetAllFinishedLastMonth()
        {
            string query = String.Format("SELECT * FROM Projects WHERE Stop > DATEADD(MONTH, -1, GETDATE()) ORDER BY Project ASC;");
            try
            {
                return GetListByQuery(query);
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public List<Project> GetAllFinishedLastWeek()
        {
            string query = String.Format("SELECT * FROM Projects WHERE Stop > DATEADD(WEEK, -1, GETDATE()) ORDER BY Project ASC;");
            try
            {
                return GetListByQuery(query);
            }
            catch (Exception e)
            {
                throw e;
            }
        }


        public List<Project> GetAll()
        {
            string query = String.Format("Select * from Projects order by Project ASC");
            try
            {
                return GetListByQuery(query);
            }
            catch (Exception e)
            {
                throw e;
            }
        }


        public List<Project> GetByMIDS(Int32 Project)
        {
            string query = String.Format("Select * from Projects where Project = {0}", Project);
            try
            {
                return GetListByQuery(query);
            }
            catch (Exception e)
            {
                throw e;
            }
        }
        

        public int Add(Project p)
        {
            string query = "INSERT INTO Projects ";
            query += "(Chamber, Start, Stop, Project, ProjectDescription, SubProject, SubProjectDescription, Step, StepDescription, Customer)";
            query += " VALUES ";
            query += String.Format("('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}'); ", p.Chamber, p.Start.FormatSQL(), p.Stop.FormatSQL(), p.ProjectID, p.ProjectDescription, p.SubProject, p.SubProjectDescription, p.Step, p.StepDescription, p.Customer);
            query += "SELECT SCOPE_IDENTITY();";
            try
            {
                SqlConnection myConnection = new SqlConnection(Properties.Settings.Default.FeanorConnStr);
                myConnection.Open();
                SqlCommand myCommand = new SqlCommand(query, myConnection);
                return Int32.Parse(myCommand.ExecuteScalar().ToString());
            }
            catch (Exception e)
            {
                throw e;
            }
        }



        public Boolean Modify(Project p)
        {
            string query = "UPDATE Projects SET ";
            query += String.Format("Chamber='{0}',", p.Chamber);
            query += String.Format("Start='{0}',", p.Start.FormatSQL());
            query += String.Format("Stop='{0}',", p.Stop.FormatSQL());
            query += String.Format("Project='{0}',", p.ProjectID);
            query += String.Format("ProjectDescription='{0}',", p.ProjectDescription);
            query += String.Format("SubProject='{0}',", p.SubProject);
            query += String.Format("SubProjectDescription='{0}',", p.SubProjectDescription);
            query += String.Format("Step='{0}',", p.Step);
            query += String.Format("StepDescription='{0}',", p.StepDescription);
            query += String.Format("Customer='{0}'", p.Customer);
            query += String.Format(" WHERE PK={0};", p.PK);

            try
            {
                SqlConnection myConnection = new SqlConnection(Properties.Settings.Default.FeanorConnStr);
                myConnection.Open();
                SqlCommand myCommand = new SqlCommand(query, myConnection);
                myCommand.ExecuteNonQuery();
                return true;
            }
            catch (Exception e)
            {
                throw e;
            }
        }


        private Project GetByQuery(string query)
        {
            Project result = null;
            SqlDataReader myReader = null;
            SqlConnection myConnection = new SqlConnection(Properties.Settings.Default.FeanorConnStr);

            try
            {
                  myConnection.Open();

                SqlCommand myCommand = new SqlCommand(query, myConnection);
                myReader = myCommand.ExecuteReader();
                if (myReader.HasRows)
                {
                    myReader.Read();
                    result = new Project();
                    result.PK = myReader.GetInt32(0);
                    result.Chamber = myReader.GetInt32(1);
                    result.Start = myReader.GetDateTime(2);
                    result.Stop = myReader.GetDateTime(3);
                    result.ProjectID = myReader.GetInt32(4);
                    result.ProjectDescription = myReader.GetString(5);
                    result.SubProject = myReader.GetInt32(6);
                    result.SubProjectDescription = myReader.GetString(7);
                    result.Step = myReader.GetInt32(8);
                    result.StepDescription = myReader.GetString(9);
                    result.Customer = myReader.GetString(10);

                    if (result.Stop < new DateTime(2000, 1, 1)) 
                      result.Stop = null;
                }
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                myReader.Close();
                myConnection.Close();
                /* 
                https://msdn.microsoft.com/en-us/library/haa3afyz(v=vs.110).aspx
                Do not call Close or Dispose on a Connection, a DataReader, or any other managed object in the Finalize method of your class. 
                In a finalizer, only release unmanaged resources that your class owns directly. 
                If your class does not own any unmanaged resources, do not include a Finalize method in your class definition. 
                For more information, see Garbage Collection.
                */
            }
            return result;
        }

        private List<Project> GetListByQuery(string query)
        {
            List<Project> result = new List<Project>();
            SqlDataReader myReader = null;
            SqlConnection myConnection = new SqlConnection(Properties.Settings.Default.FeanorConnStr);

            try
            {
                myConnection.Open();

                SqlCommand myCommand = new SqlCommand(query, myConnection);
                myReader = myCommand.ExecuteReader();
                if (myReader.HasRows)
                {
                    while (myReader.Read())
                    {
                        Project p = new Project();
                        p.PK = myReader.GetInt32(0);
                        p.Chamber = myReader.GetInt32(1);
                        p.Start = myReader.GetDateTime(2);
                        p.Stop = myReader.GetDateTime(3);
                        
                        p.ProjectID = myReader.GetInt32(4);
                        p.ProjectDescription = myReader.GetString(5);
                        p.SubProject = myReader.GetInt32(6);
                        p.SubProjectDescription = myReader.GetString(7);
                        p.Step = myReader.GetInt32(8);
                        p.StepDescription = myReader.GetString(9);
                        p.Customer = myReader.GetString(10);

                        if (p.Stop < new DateTime(2000, 1, 1)) 
                            p.Stop = null;

                            result.Add(p);
                    }
                }               
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                myReader.Close();
                myConnection.Close(); // Martijn
                /* 
                https://msdn.microsoft.com/en-us/library/haa3afyz(v=vs.110).aspx
                Do not call Close or Dispose on a Connection, a DataReader, or any other managed object in the Finalize method of your class. 
                In a finalizer, only release unmanaged resources that your class owns directly. 
                If your class does not own any unmanaged resources, do not include a Finalize method in your class definition. 
                For more information, see Garbage Collection.
                */
            }
            return result;
        }


       

    }



}