using System;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Collections.Specialized;
using LYB.DAL.Helpers;

// HEADINGS 
// A class to hold headings data and a static class for the behavior according to the 
// Table Data Gateway pattern

namespace LYB.DAL
{
    // HEADING
    // Holds information about the heading
    public class Heading
    {
        private int _HeadingID;
        private string _HeadingText;
        private string _HeadingFontFamily;
        private Int16 _HeadingFontSize;
        public Heading()
        {
        }
        #region PROPERTIES
        public int HeadingID
        {
            get { return _HeadingID; }
            set { _HeadingID = value; }
        }
        public string HeadingText
        {
            get { return _HeadingText; }
            set { _HeadingText = value; }
        }
        public string HeadingFontFamily
        {
            get { return _HeadingFontFamily; }
            set { _HeadingFontFamily = value; }
        }
        public short HeadingFontSize
        {
            get { return _HeadingFontSize; }
            set { _HeadingFontSize = value; }
        }
        #endregion
	}
    public class NoHeading : Heading
    {
    }
    // HeadingCOLLECTION
    // Collection of Heading objects
    public class HeadingCollection : List<Heading>
    {
    }
    // HeadingS--customer gateway
    // Behavior of the Heading
    public class Headings
    {
        public static string ConnectionString
        {
            get { return ConfigurationManager.ConnectionStrings["MyLocalSqlServer"].ConnectionString; }
        }

        #region METHOD: Load
        // Load
        public static Heading Load(string id)
        {
            if (String.IsNullOrEmpty(id))
                return null; // new NoHeading();  

            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(HeadingCommands.cmdLoad, conn);
                cmd.Parameters.AddWithValue("@Headingid", id);

                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                reader.Read();
                Heading Heading = HelperMethods.GetHeading(reader, "all");
                reader.Close();
                conn.Close();

                return Heading;
            }
        }
        #endregion
        #region METHOD: LoadAll
        // LoadAll
        public static HeadingCollection LoadAll()
        {
            return LoadAll(-1, 0);
        }
        public static HeadingCollection LoadAll(int totalRows, int firstRow)
        {
            return LoadAll(totalRows, firstRow, "");
        }
        public static HeadingCollection LoadAll(int totalRows, int firstRow, string sortExpression)
        {
            HeadingCollection coll = new HeadingCollection();

            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(HeadingCommands.cmdLoadAll, conn);
                if (!String.IsNullOrEmpty(sortExpression))
                    cmd.CommandText += " ORDER BY " + sortExpression;

                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                HelperMethods.FillHeadingList(coll, reader, totalRows, firstRow);
                reader.Close();
                conn.Close();
            }

            return coll;
        }
        #endregion
        #region METHOD: Save
        // Save 
        public static void Save(int Headingid, string HeadingText, string HeadingFontFamily, short HeadingFontSize)
        {
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(HeadingCommands.cmdSave, conn);
                cmd.Parameters.AddWithValue("@Headingid", Headingid);
                cmd.Parameters.AddWithValue("@HeadingText", HeadingText);
                if (!(HeadingFontFamily == null))
                    cmd.Parameters.AddWithValue("@headingfontfamily", HeadingFontFamily);
                else
                    cmd.Parameters.AddWithValue("@headingfontfamily", "");
                if (HeadingFontSize != 0)
                    cmd.Parameters.AddWithValue("@headingfontsize", HeadingFontSize);
                else
                    cmd.Parameters.AddWithValue("@headingfontsize", 0);

                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
                throw new System.Exception("Refresh Headings");
            }
        }

        #endregion


        #region METHOD: Add
        // Add 
        public static void Add(int Headingid, string HeadingText, string HeadingFontFamily, short HeadingFontSize)
        {
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(HeadingCommands.cmdInsert, conn);
                cmd.Parameters.AddWithValue("@Headingid", GetNextHeadingID());
                cmd.Parameters.AddWithValue("@HeadingText", HeadingText);
                if (!(HeadingFontFamily == null))
                    cmd.Parameters.AddWithValue("@headingfontfamily", HeadingFontFamily);
                else
                    cmd.Parameters.AddWithValue("@headingfontfamily", "");
                if (HeadingFontSize != 0)
                    cmd.Parameters.AddWithValue("@headingfontsize", HeadingFontSize);
                else
                    cmd.Parameters.AddWithValue("@headingfontsize", 0);

                try
                {
                    conn.Open();
                    cmd.ExecuteNonQuery();
                    conn.Close();
                    throw new System.Exception("Refresh Headings");
                }
                catch (Exception e)
                {
                    if (e.Message.Contains("duplicate key"))
                    {
                        if (e.Message.Contains("PK"))
                            throw new System.Exception("ID finns redan. Sparades inte! " + e.Message);
                        else
                        {
                            throw new System.Exception("Namn finns redan. Sparades inte! " + e.Message);
                        }
                    }
                    else
                    {
                        if (e.Message.Contains("Refresh"))
                        {
                            throw new System.Exception(e.Message);
                        }
                        else
                        {
                            throw new System.Exception("Fel vid insert: " + e.Message);
                        }
                    }
                }
            }
        }

        #endregion


        #region METHOD: Delete
        // Delete 
        public static void Delete(int Headingid)
        {
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(HeadingCommands.cmdDelete, conn);
                cmd.Parameters.AddWithValue("@Headingid", Headingid);

                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
                throw new System.Exception("Refresh Headings");
            }
        }
        public static void Delete(Heading Heading)
        {
            Delete(Heading.HeadingID);
        }
        #endregion
        #region METHOD: GetNextHeadingID

        private static int GetNextHeadingID()
        {
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(HeadingCommands.cmdGetNextHeadingID, conn);
                int NextHeadingID = 9999;
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    NextHeadingID = (int)reader["highestid"] + 1;
                }
                conn.Close();
                return NextHeadingID;
            }

        }
        #endregion

    }
    // Helpers
    public static partial class HelperMethods
    {
        // GetHeading
        public static Heading GetHeading(SqlDataReader reader)
        {
            Heading Heading = new Heading();
            if (reader.IsClosed)
                reader.Read();

            try
            {
                Heading.HeadingID = (int)reader["Headingid"];
                Heading.HeadingText = reader["HeadingText"].ToString();
            }
            catch (InvalidOperationException)
            {
                // let pass
            }

            return Heading;
        }
        // GetHeading
        public static Heading GetHeading(SqlDataReader reader, string all)
        {
            Heading Heading = new Heading();
            if (reader.IsClosed)
                reader.Read();

            try
            {
                Heading.HeadingID = (int)reader["Headingid"];
                Heading.HeadingText = reader["HeadingText"].ToString();
                if (!reader.IsDBNull(1))
                    Heading.HeadingFontFamily = reader["HeadingFontFamily"].ToString();
                else
                    Heading.HeadingFontFamily = "";
                if (!reader.IsDBNull(2))
                    Heading.HeadingFontSize = (short)reader["HeadingFontSize"];
                else
                    Heading.HeadingFontSize = 0;
            }
            catch (InvalidOperationException)
            {
                // let pass
            }

            return Heading;
        }

        // FillHeadingList
        public static void FillHeadingList(HeadingCollection coll, SqlDataReader reader)
        {
            FillHeadingList(coll, reader, -1, 0);
        }
        public static void FillHeadingList(HeadingCollection coll, SqlDataReader reader, int totalRows, int firstRow)
        {
            int index = 0;
            bool readMore = true;

            while (reader.Read())
            {
                if (index >= firstRow && readMore)
                {
                    if (coll.Count >= totalRows && totalRows > 0)
                        readMore = false;
                    else
                    {
                        Heading cust = HelperMethods.GetHeading(reader);
                        coll.Add(cust);
                    }
                }
                index++;
            }
        }

    }

}