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

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

namespace LYB.DAL
{

    // DOCUMENTLIST 
    // Holds information about the DocumentList
    public class DocumentList
    {
        private int _DocumentListID;
        private int _GroupID;
        private string _GroupName;
        private int _HeadingID;
        private string _HeadingText;
        private int _CompositionID;
        private string _CompositionName;
        private int _SortOrder;

        public DocumentList()
        {
        }

        #region PROPERTIES
        public int DocumentListID
        {
            get { return _DocumentListID; }
            set { _DocumentListID = value; }
        }
        public int GroupID
        {
            get { return _GroupID; }
            set { _GroupID = value; }
        }
        public string GroupName
        {
            get { return _GroupName; }
            set { _GroupName = value; }
        }
        public int HeadingID
        {
            get { return _HeadingID; }
            set { _HeadingID = value; }
        }
        public string HeadingText
        {
            get { return _HeadingText; }
            set { _HeadingText = value; }
        }
        public int CompositionID
        {
            get { return _CompositionID; }
            set { _CompositionID = value; }
        }
        public string CompositionName
        {
            get { return _CompositionName; }
            set { _CompositionName = value; }
        }
        public int SortOrder
        {
            get { return _SortOrder; }
            set { _SortOrder = value; }
        }
        #endregion

    }
    public class NoDocumentList : DocumentList
    {
    }
    // DocumentListCOLLECTION
    // Collection of DocumentList objects
    public class DocumentListCollection : List<DocumentList>
    {
    }
    // DocumentListS--customer gateway
    // Behavior of the DocumentList
    public class DocumentLists
    {
        public static string ConnectionString
        {
            get { return ConfigurationManager.ConnectionStrings["MyLocalSqlServer"].ConnectionString; }
        }

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

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

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

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

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

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

            return coll;
        }
        #endregion
        #region METHOD: Save
        // Save 
        public static void Save(int DocumentListID, int GroupID, int HeadingID, int CompositionID, int SortOrder)
        {
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                SqlCommand cmd;
                if (HeadingID == 0 && CompositionID != 0)
                {
                    cmd = new SqlCommand(DocumentListCommands.cmdSave1, conn);
                    cmd.Parameters.AddWithValue("@DocumentListid", DocumentListID);
                    cmd.Parameters.AddWithValue("@GroupID", GroupID);
                    cmd.Parameters.AddWithValue("@sortorder", SortOrder);
                    cmd.Parameters.AddWithValue("@compositionid", CompositionID);
                    conn.Open();
                    cmd.ExecuteNonQuery();
                    conn.Close();
                    throw new System.Exception("Refresh DocumentLists");
                }
                else
                {
                    if (CompositionID == 0 && HeadingID != 0)
                    {
                        cmd = new SqlCommand(DocumentListCommands.cmdSave2, conn);
                        cmd.Parameters.AddWithValue("@DocumentListid", DocumentListID);
                        cmd.Parameters.AddWithValue("@GroupID", GroupID);
                        cmd.Parameters.AddWithValue("@sortorder", SortOrder);
                        cmd.Parameters.AddWithValue("@headingid", HeadingID);
                        conn.Open();
                        cmd.ExecuteNonQuery();
                        conn.Close();
                        throw new System.Exception("Refresh DocumentLists");
                    }
                }
            }
        }

        #endregion


        #region METHOD: Add
        // Add 
        public static void Add(int DocumentListID, int GroupID, int HeadingID, int CompositionID, int SortOrder)
        {
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                try
                {
                    SqlCommand cmd;
                    if (HeadingID == 0 && CompositionID != 0)
                    {
                        cmd = new SqlCommand(DocumentListCommands.cmdInsert1, conn);
                        cmd.Parameters.AddWithValue("@compositionid", CompositionID);
                        cmd.Parameters.AddWithValue("@DocumentListid", GetNextDocumentListID());
                        cmd.Parameters.AddWithValue("@GroupID", GroupID);
                        cmd.Parameters.AddWithValue("@sortorder", SortOrder);
                        conn.Open();
                        cmd.ExecuteNonQuery();
                        conn.Close();
                        throw new System.Exception("Refresh DocumentLists");
                    }
                    else
                    {
                        if (CompositionID == 0 && HeadingID != 0)
                        {
                            cmd = new SqlCommand(DocumentListCommands.cmdInsert2, conn);
                            cmd.Parameters.AddWithValue("@headingid", HeadingID);
                            cmd.Parameters.AddWithValue("@DocumentListid", GetNextDocumentListID());
                            cmd.Parameters.AddWithValue("@GroupID", GroupID);
                            cmd.Parameters.AddWithValue("@sortorder", SortOrder);
                            conn.Open();
                            cmd.ExecuteNonQuery();
                            conn.Close();
                            throw new System.Exception("Refresh DocumentLists");
                        }
                    }
                }
                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 DocumentListid)
        {
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(DocumentListCommands.cmdDelete, conn);
                cmd.Parameters.AddWithValue("@DocumentListid", DocumentListid);

                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
                throw new System.Exception("Refresh DocumentLists");
            }
        }
        public static void Delete(DocumentList DocumentList)
        {
            Delete(DocumentList.DocumentListID);
        }
        #endregion
        #region METHOD: GetNextDocumentListID

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

        }
        #endregion

    }
    // Helpers
    public static partial class HelperMethods
    {
        // GetDocumentList - used to fill the the GridView
        public static DocumentList GetDocumentList(SqlDataReader reader)
        {
            DocumentList DocumentList = new DocumentList();
            if (reader.IsClosed)
                reader.Read();

            try
            {
                DocumentList.DocumentListID = (int)reader["DocumentListid"];
                DocumentList.GroupName = reader["GroupName"].ToString();
                if (!reader.IsDBNull(1))
                    DocumentList.CompositionName = reader["CompositionName"].ToString();
                else
                    DocumentList.CompositionName = "";
                if (!reader.IsDBNull(2))
                    DocumentList.HeadingText = reader["headingtext"].ToString();
                else
                    DocumentList.HeadingText = "";
            }
            catch (InvalidOperationException)
            {
                // let pass
            }
            catch (Exception e)
            {
                throw new Exception("Fel vid GetDocumentList: " + e.Message);
            }

            return DocumentList;
        }
        // GetDocumentList - used to fill the the DetailsView
        public static DocumentList GetDocumentList(SqlDataReader reader, string all)
        {
            DocumentList DocumentList = new DocumentList();
            if (reader.IsClosed)
                reader.Read();

            try
            {
                DocumentList.GroupID = (int)reader["groupid"];
                DocumentList.DocumentListID = (int)reader["DocumentListid"];
                DocumentList.SortOrder = (int)reader["sortorder"];
                if (!reader.IsDBNull(2))
                    DocumentList.HeadingID = (int)reader["headingid"];
                else
                    DocumentList.HeadingID = 0;
                if (!reader.IsDBNull(3))
                    DocumentList.CompositionID = (int)reader["compositionid"];
                else
                    DocumentList.CompositionID = 0;
            }
            catch (InvalidOperationException)
            {
                // let pass
            }
            catch (Exception e)
            {
                throw new Exception("Fel vid GetDocumentList: " + e.Message);
            }

            return DocumentList;
        }

        // FillDocumentListList
        public static void FillDocumentListList(DocumentListCollection coll, SqlDataReader reader)
        {
            FillDocumentListList(coll, reader, -1, 0);
        }
        public static void FillDocumentListList(DocumentListCollection 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
                    {
                        DocumentList cust = HelperMethods.GetDocumentList(reader);
                        coll.Add(cust);
                    }
                }
                index++;
            }
        }

    }

}