using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Web;

namespace digioz
{
    /// <summary>
    /// Summary description for CommentEntryManagement
    /// </summary>
    public class CommentEntryManagement
    {
        private static PortalSettings coPortalSettings;

        public CommentEntryManagement()
        { }

        public static List<CommentEntry> GetCommentPageCommentEntries(string psCommentEntryPostingPage)
        {
            coPortalSettings = PortalSettingsManagement.GetPortalSettings();
            List<CommentEntry> loCommentEntryList = new List<CommentEntry>();
            DateTime ldCommDate;
            string lsSQL;

            if (coPortalSettings.DBRelational == true)
            {
                IDataRelational loDB = DataFactory.GetDataObject(coPortalSettings);
                DataTable loCommentEntries;
                if (psCommentEntryPostingPage == "All")
                {
                    lsSQL = "EXEC spGetCommentPageCommentEntriesAll";
                }
                else
                {
                    string lsSubString = string.Empty;

                    if (psCommentEntryPostingPage.LastIndexOf("/") > 0)
                    {
                        lsSubString = psCommentEntryPostingPage.Substring(psCommentEntryPostingPage.LastIndexOf("/"), psCommentEntryPostingPage.Length - psCommentEntryPostingPage.LastIndexOf("/"));
                    }

                    lsSQL = "EXEC spGetCommentPageCommentEntries @commententry_posting_page=\""
                        + lsSubString + "\";";
                }

                try
                {
                    loDB.openConnection();
                    loCommentEntries = loDB.QueryDBDataset(lsSQL);

                    foreach (DataRow loDR in loCommentEntries.Rows)
                    {
                        //If comment date/time is null, set it to a dummy date.
                        if (loDR["commententry_dateTime"].Equals(System.DBNull.Value))
                        {
                            ldCommDate = Convert.ToDateTime("01/01/1900");
                        }
                        else
                        {
                            ldCommDate = Convert.ToDateTime(loDR["commententry_dateTime"]);
                        }

                        loCommentEntryList.Add(new CommentEntry(Convert.ToInt32(loDR["commententry_id"]),
                            Convert.ToString(loDR["commententry_comment"]),
                            Convert.ToString(loDR["commententry_username"]),
                            ldCommDate,
                            Convert.ToInt16(loDR["commententry_pageID"]),
                            Convert.ToString(loDR["profile_avatar"])));
                    }
                }
                finally
                {
                    loDB.closeConnection();
                }
            }

            return loCommentEntryList;
        }

       
        /// <summary>
        /// Inserts a new comment entry or updates an existing comment entry.
        /// When updates psCommentEntryUsername parameter is not taken into account.
        /// </summary>
        /// <param name="piCommentEntryID">The comment entry ID.</param>
        /// <param name="psCommentEntryPostingPage">The comment entry posting page.</param>
        /// <param name="psCommentEntryComment">The comment entry comment.</param>
        /// <param name="psCommentEntryUsername">The comment entry username.</param>
        /// <param name="pbIsUpdating">if set to <c>true</c> updates existing comment.</param>
        /// <returns><c>true</c> if operation completed successfully.</returns>
        public static bool SaveCommentEntry(int piCommentEntryID, string psCommentEntryPostingPage,
            string psCommentEntryComment, string psCommentEntryUsername, DateTime pdCommentEntryDateTime, 
            bool pbIsUpdating, int piPageID)
        {
            coPortalSettings = PortalSettingsManagement.GetPortalSettings();
            bool lbResult = false;

            if (coPortalSettings.DBRelational == true)
            {
                IDataRelational loDB = DataFactory.GetDataObject(coPortalSettings);
                string lsSQL = String.Empty;
                int liCount = 0;
                DbParameter[] loParameters;

                if (pbIsUpdating)
                {
                    loParameters = new DbParameter[2];
                    loParameters[0] = new SqlParameter("@commententry_id", SqlDbType.Int);
                    loParameters[0].Value = piCommentEntryID;
                    loParameters[1] = new SqlParameter("@commententry_comment", SqlDbType.Text);
                    loParameters[1].Value = psCommentEntryComment;
                    lsSQL = "spUpdateCommentEntry";
                }
                else
                {
                    loParameters = new DbParameter[5];
                    loParameters[0] = new SqlParameter("@commententry_posting_page", SqlDbType.VarChar);
                    loParameters[0].Value = psCommentEntryPostingPage;
                    loParameters[1] = new SqlParameter("@commententry_comment", SqlDbType.Text);
                    loParameters[1].Value = psCommentEntryComment;
                    loParameters[2] = new SqlParameter("@commententry_username", SqlDbType.VarChar);
                    loParameters[2].Value = psCommentEntryUsername;
                    loParameters[3] = new SqlParameter("@commententry_dateTime", SqlDbType.DateTime);
                    loParameters[3].Value = pdCommentEntryDateTime;
                    loParameters[4] = new SqlParameter("@commententry_pageID", SqlDbType.Int);
                    loParameters[4].Value = piPageID;
                    lsSQL = "spInsertCommentEntry";
                }

                try
                {
                    loDB.openConnection();
                    loDB.ExecDBProc(lsSQL, out liCount, loParameters);
                    lbResult = true;
                }
                finally
                {
                    loDB.closeConnection();
                }
            }

            return lbResult;
        }

        public static bool DeleteCommentEntry(int piCommentEntryID)
        {
            coPortalSettings = PortalSettingsManagement.GetPortalSettings();
            string lsSql = "spDeleteCommentEntry";
            int liCount = 0;
            bool lbResult = false;
            DbParameter[] loParameters = new DbParameter[1];

            if (coPortalSettings.DBRelational == true)
            {
                IDataRelational loDB = DataFactory.GetDataObject(coPortalSettings);
                loParameters[0] = new SqlParameter("@id", SqlDbType.Int);
                loParameters[0].Value = piCommentEntryID;

                try
                {
                    loDB.openConnection();
                    loDB.ExecDBProc(lsSql, out liCount, loParameters);
                    lbResult = true;
                }
                finally
                {
                    loDB.closeConnection();
                }
            }

            return lbResult;
        }

        public static CommentEntryList GetRecentCommentEntryList()
        {
            //selects the five most recent comments
            CommentEntryList loCommentEntryList = new CommentEntryList();
            coPortalSettings = PortalSettingsManagement.GetPortalSettings();
            string lsSQL = "";

            if (coPortalSettings.DBRelational == true)
            {
                IDataRelational loDB = DataFactory.GetDataObject(coPortalSettings);
                CommentEntry loCommentEntry;
                DataTable loComments;

                lsSQL = "select tblCommentEntries.commententry_comment, ";
                lsSQL += "tblCommentEntries.commententry_dateTime, ";
                lsSQL += "tblCommentEntries.commententry_id, ";
                lsSQL += "tblCommentEntries.commententry_posting_page, ";
                lsSQL += "tblCommentEntries.commententry_username, ";
                lsSQL += "tblCommentEntries.commententry_pageID, ";
                lsSQL += "tblUserProfile.profile_avatar ";
                lsSQL += "FROM ";
                lsSQL += "tblCommentEntries  ";
                lsSQL += "INNER JOIN ";
                lsSQL += "tblUserProfile ON  ";
                lsSQL += "tblCommentEntries.commententry_username = tblUserProfile.profile_username ";
                lsSQL += "where tblCommentEntries.commententry_id in ";
                lsSQL += "(select top 5 tblCommentEntries.commententry_id ";
                lsSQL += "from tblCommentEntries ";
                lsSQL += "order by tblCommentEntries.commententry_id DESC) ";
                lsSQL += "order by tblCommentEntries.commententry_dateTime DESC ";

                loDB.openConnection();
                loComments = loDB.QueryDBDataset(lsSQL);
                loDB.closeConnection();

                foreach (DataRow loDR in loComments.Rows)
                {
                    loCommentEntry = new CommentEntry();
                    loCommentEntry.ID = (int)loDR["commententry_id"];
                    loCommentEntry.UserName = (string)loDR["commententry_username"];
                    loCommentEntry.Avatar = (string)loDR["profile_avatar"];
                    loCommentEntry.Comment = (string)loDR["commententry_comment"];
                    loCommentEntry.PostingPage = (string)loDR["commententry_posting_page"];
                    loCommentEntry.PageID = (int)loDR["commententry_pageID"];

                    //Old comments will be null - put in dummy date
                    if (loDR["commententry_dateTime"] == DBNull.Value)
                    {
                        loCommentEntry.ComDateTime = Convert.ToDateTime("01/01/1900");
                    }
                    else
                    {
                        loCommentEntry.ComDateTime = (DateTime)loDR["commententry_dateTime"];
                    }

                    loCommentEntryList.Add(loCommentEntry);
                }
            }

            return loCommentEntryList;
        }
        public static List<CommentEntry> GetCommentsByPageID(int piCommentEntryPageID)
            //selects comment entries for a specific page.
        {
            coPortalSettings = PortalSettingsManagement.GetPortalSettings();
            List<CommentEntry> loCommentEntryList = new List<CommentEntry>();
            DateTime ldCommDate;
            string lsSQL;

            if (coPortalSettings.DBRelational == true)
            {
                IDataRelational loDB = DataFactory.GetDataObject(coPortalSettings);
                DataTable loCommentEntries;

                    lsSQL = "EXEC spGetCommentsByPageID @commententry_pageID=\""
                        + piCommentEntryPageID + "\";";

                try
                {
                    loDB.openConnection();
                    loCommentEntries = loDB.QueryDBDataset(lsSQL);

                    foreach (DataRow loDR in loCommentEntries.Rows)
                    {
                        //If comment date/time is null, set it to a dummy date.
                        if (loDR["commententry_dateTime"].Equals(System.DBNull.Value))
                        {
                            ldCommDate = Convert.ToDateTime("01/01/1900");
                        }
                        else
                        {
                            ldCommDate = Convert.ToDateTime(loDR["commententry_dateTime"]);
                        }

                        loCommentEntryList.Add(new CommentEntry(Convert.ToInt32(loDR["commententry_id"]),
                            Convert.ToString(loDR["commententry_comment"]),
                            Convert.ToString(loDR["commententry_username"]),
                            ldCommDate,
                            Convert.ToInt16(loDR["commententry_pageID"]),
                            Convert.ToString(loDR["profile_avatar"])));
                    }
                }
                finally
                {
                    loDB.closeConnection();
                }
            }

            return loCommentEntryList;
        }
        public static CommentEntry GetCommentEntryByID(string lsCommentID)
        {
            //selects a single comment entry based on comment ID.
            CommentEntry loCommentEntry = new CommentEntry();
            coPortalSettings = PortalSettingsManagement.GetPortalSettings();
            string lsSQL = "";

            if (coPortalSettings.DBRelational == true)
            {
                IDataRelational loDB = DataFactory.GetDataObject(coPortalSettings);
                DataTable loComments;

                lsSQL = "select tblCommentEntries.commententry_comment, ";
                lsSQL += "tblCommentEntries.commententry_dateTime, ";
                lsSQL += "tblCommentEntries.commententry_id, ";
                lsSQL += "tblCommentEntries.commententry_posting_page, ";
                lsSQL += "tblCommentEntries.commententry_username, ";
                lsSQL += "tblCommentEntries.commententry_pageID ";
                lsSQL += "FROM ";
                lsSQL += "tblCommentEntries  ";
                lsSQL += "where tblCommentEntries.commententry_id = '";
                lsSQL += lsCommentID;
                lsSQL += "' ";

                loDB.openConnection();
                loComments = loDB.QueryDBDataset(lsSQL);
                loDB.closeConnection();

                foreach (DataRow loDR in loComments.Rows)
                {
                    loCommentEntry = new CommentEntry();
                    loCommentEntry.ID = (int)loDR["commententry_id"];
                    loCommentEntry.UserName = (string)loDR["commententry_username"];
                    loCommentEntry.Comment = (string)loDR["commententry_comment"];
                    loCommentEntry.PostingPage = (string)loDR["commententry_posting_page"];
                    loCommentEntry.PageID = (int)loDR["commententry_pageID"];

                    //Old comments will be null - put in dummy date
                    if (loDR["commententry_dateTime"] == DBNull.Value)
                    {
                        loCommentEntry.ComDateTime = Convert.ToDateTime("01/01/1900");
                    }
                    else
                    {
                        loCommentEntry.ComDateTime = (DateTime)loDR["commententry_dateTime"];
                    }

                }
            }

            return loCommentEntry;
        }
    }

}