using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

namespace digioz.PollSystem
{
    /// <summary>
    /// Summary description for PollSubjectInfoManagement
    /// </summary>
    public class PollSubjectInfoManagement
    {

        private static PortalSettings coPortalSettings;

        /// <summary>
        /// Return all PollSubject records
        /// </summary>
        /// <param name="sError"></param>
        /// <returns></returns>
        public static PollSubjectInfoList GetPollSubjectList(out string sError)
        {
            PollSubjectInfoList loPollSubjects = new PollSubjectInfoList();
            PollSubjectInfo loSubject;
            sError = "";

            coPortalSettings = PortalSettingsManagement.GetPortalSettings();

            if (coPortalSettings.DBRelational == true)
            {
                IDataRelational loDB = DataFactory.GetDataObject(coPortalSettings);
                String sql = "select * from tblPollSubject order by subject_id ";

                try
                {
                    loDB.openConnection();
                    DataTable ta = loDB.QueryDBDataset(sql);
                    foreach (DataRow dr in ta.Rows)
                    {
                        loSubject = new PollSubjectInfo();
                        loSubject.ID = (int)dr["subject_id"];
                        loSubject.SubjectName = Convert.ToString(dr["subject_name"]);
                        loSubject.SubjectDeadline = (DateTime)dr["subject_deadline"];
                        loSubject.SubjectDescription = Convert.ToString(dr["subject_description"]);
                        loSubject.Closed = (bool)dr["closed"];
                        loSubject.SecretBallot = (bool)dr["secret_ballot"];
                        loPollSubjects.Add(loSubject);
                    }
                }
                finally
                {
                    loDB.closeConnection();
                }
            }

            return loPollSubjects;
        }

        /// <summary>
        /// Get the latest/newest Poll Subject
        /// </summary>
        /// <returns></returns>
        public static int GetLatestOpenPollSubjectID()
        {
            int subjectID = -1;
            coPortalSettings = PortalSettingsManagement.GetPortalSettings();

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

                //***************************************************************
                // Select only those poll subjects that have options for users 
                // to vote for.
                // If polls are defined in the admin area but no options are 
                // associated with them, the user area will not show any poll.
                //***************************************************************
                string sql = "exec spGetLatestOpenPollSubjectID";
                System.Diagnostics.Trace.WriteLine(sql);
                try
                {
                    loDB.openConnection();
                    Object returnValue;
                    returnValue = loDB.ExecDBScalar(sql);
                    if (returnValue != null)
                    {
                        subjectID = int.Parse(returnValue.ToString());
                    }
                }
                finally
                {
                    loDB.closeConnection();
                }
            }
            return subjectID;
        }

        /// <summary>
        /// get the PollSubjectInfo instance from a subject id
        /// </summary>
        /// <param name="SubjectID"></param>
        /// <returns></returns>
        public static PollSubjectInfo GetPollSubjectInfo(int SubjectID)
        {
            PollSubjectInfo loPollSubject = null;
            coPortalSettings = PortalSettingsManagement.GetPortalSettings();

            if (coPortalSettings.DBRelational == true)
            {
                IDataRelational loDB = DataFactory.GetDataObject(coPortalSettings);
                String sql = "SELECT subject_id, subject_name, secret_ballot, closed, subject_deadline, subject_description " +
                             "FROM tblPollSubject " +
                             "WHERE subject_id = " + SubjectID;

                try
                {
                    loDB.openConnection();
                    DataTable loTable = loDB.QueryDBDataset(sql);

                    if (loTable.Rows.Count > 0)
                    {
                        loPollSubject = new PollSubjectInfo();
                        loPollSubject.ID = (int)loTable.Rows[0]["subject_id"];
                        loPollSubject.SubjectName = (string)loTable.Rows[0]["subject_name"];
                        loPollSubject.SecretBallot = (bool)loTable.Rows[0]["secret_ballot"];
                        loPollSubject.Closed = (bool)loTable.Rows[0]["closed"];
                        loPollSubject.SubjectDeadline = (DateTime)loTable.Rows[0]["subject_deadline"];
                        loPollSubject.SubjectDescription = (string)loTable.Rows[0]["subject_description"];
                    }
                }
                finally
                {
                    loDB.closeConnection();
                }
            }
            return loPollSubject;
        }

        /// <summary>
        /// get paging based poll subject results
        /// </summary>
        /// <param name="PageSize"></param>
        /// <param name="CurrentPage"></param>
        /// <returns></returns>
        public static PollSubjectInfoList GetPollSubjectsPaging(int PageSize,
                                                                int CurrentPage)
        {
            PollSubjectInfoList loPollSubjects = new PollSubjectInfoList();
            PollSubjectInfo loSubject;

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

                /*String sql = "select top " + PageSize + " * " +
                                "from tblPollSubject " +
                                "where subject_id not in " +
                                "(select top " + (CurrentPage - 1) * PageSize + " subject_id from tblPollSubject order by subject_id) " +
                                "order by subject_id ";
                 */
                String sql = String.Format("Exec spGetPollSubjectsPaging @PageSize = {0},  @CurrentPage = {1}",
                                            PageSize.ToString(),
                                            CurrentPage.ToString());

                try
                {
                    loDB.openConnection();
                    DataTable ta = loDB.QueryDBDataset(sql);

                    foreach (DataRow dr in ta.Rows)
                    {
                        loSubject = new PollSubjectInfo();
                        loSubject.ID = (int)dr["subject_id"];
                        loSubject.SubjectName = (string)dr["subject_name"];
                        loSubject.SubjectDeadline = (DateTime)dr["subject_deadline"];
                        loSubject.SubjectDescription = Convert.ToString(dr["subject_description"]);
                        loSubject.Closed = (bool)dr["closed"];
                        loSubject.SecretBallot = (bool)dr["secret_ballot"];
                        loPollSubjects.Add(loSubject);
                    }
                }
                finally
                {
                    loDB.closeConnection();
                }
            }

            return loPollSubjects;
        }

        ///<summary>
        ///Insert a new PollSubject into datebase 
        ///</summary>
        ///
        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Insert, true)]
        // true -> default insertion method
        public static bool InsertPollSubject(PollSubjectInfo subject,
                                             out int newPollSubjectID,
                                             out string sError)
        {
            bool lbRetValue=false;
            sError = "";
            newPollSubjectID = -1;
            
            coPortalSettings = PortalSettingsManagement.GetPortalSettings();
            if (coPortalSettings.DBRelational == true)
            {
                IDataRelational loDB = DataFactory.GetDataObject(coPortalSettings);

                string sql = "Exec spSubmitPollSubject N'" +
                              DoubleUpSingleQuotes(subject.SubjectName) + "', " +
                              Convert.ToInt32(subject.SecretBallot).ToString() +
                              ", N'" + subject.SubjectDeadline.ToShortDateString() +
                              "', N'" + DoubleUpSingleQuotes(subject.SubjectDescription) + "'";

                try
                {
                    loDB.openConnection();
                    object loRetValue = loDB.ExecDBScalar(sql);
                    if (loRetValue != null)
                    {
                        newPollSubjectID = int.Parse(loRetValue.ToString());
                        lbRetValue = true;
                    }
                    sError = loDB.sErr;
                }
                finally
                {                    
                    loDB.closeConnection();
                }
            }

            return lbRetValue;
        }


        /// <summary>
        /// Update a PollSubject record in the tblPollSubject table
        /// </summary>
        /// <param name="subject"></param>
        public static bool UpdatePollSubject(PollSubjectInfo subject,
                                             out string sError)
        {
            bool lbRetValue=false;
            sError = "";
      
            coPortalSettings = PortalSettingsManagement.GetPortalSettings();
            if (coPortalSettings.DBRelational == true)
            {
                IDataRelational loDB = DataFactory.GetDataObject(coPortalSettings);
                string sql = "exec spUpdatePollSubject " +
                             subject.ID.ToString() +
                             ", N'" + DoubleUpSingleQuotes(subject.SubjectName) + "', " +
                             Convert.ToInt32(subject.SecretBallot).ToString() + ", " +
                             Convert.ToInt32(subject.Closed).ToString() +
                             ", N'" + subject.SubjectDeadline.ToShortDateString() +
                             "', N'" + DoubleUpSingleQuotes(subject.SubjectDescription) + "'";

                try
                {
                    loDB.openConnection();
                    loDB.ExecDB(sql);
                    lbRetValue = (String.IsNullOrEmpty(loDB.sErr));
                    sError = loDB.sErr;
                }
                finally
                {
                    loDB.closeConnection();
                }
            }
            return lbRetValue;
        }

        /// <summary>
        /// Delete a PollSubject record from the tblPollSubject table
        /// </summary>
        /// <param name="subject"></param>
        public static bool DeletePollSubject(int subjectID,
                                             out string sError)
        {
            bool lbRetValue=false;
            sError = "";
            
            coPortalSettings = PortalSettingsManagement.GetPortalSettings();
            if (coPortalSettings.DBRelational == true)
            {
                IDataRelational loDB = DataFactory.GetDataObject(coPortalSettings);
                string sql = "exec spDeletePollSubject " +
                             subjectID.ToString();
                System.Diagnostics.Trace.WriteLine(sql);
                try
                {
                    loDB.openConnection();
                    loDB.ExecDB(sql);
                    lbRetValue = (String.IsNullOrEmpty(loDB.sErr));
                    sError = loDB.sErr;
                }
                finally
                {
                    loDB.closeConnection();
                }
            }
            return lbRetValue;
        }

        /// <summary>
        /// validate whether the user have submitted
        /// </summary>
        /// <param name="userID"></param>
        /// <param name="subjectID"></param>
        /// <returns></returns>
        public static bool HaveSubmit(string userID, int subjectID)
        {
            int count = 0;
            coPortalSettings = PortalSettingsManagement.GetPortalSettings();
            if (coPortalSettings.DBRelational == true)
            {
                IDataRelational loDB = DataFactory.GetDataObject(coPortalSettings);
                String sql = "SELECT COUNT(record_id) FROM tblPollRecord, tblPollOption, tblPollSubject " +
                         "WHERE user_id = '" + userID + "' AND " +
                         "tblPollSubject.subject_id = " + subjectID + " AND " +
                         "tblPollOption.option_id = tblPollRecord.option_id AND " +
                         "tblPollSubject.subject_id = tblPollOption.subject_id";
                try
                {
                    loDB.openConnection();
                    Object returnValue;
                    if ((returnValue = loDB.ExecDBScalar(sql)) != null)
                    {
                        count = int.Parse((string)returnValue);
                    }
                }
                finally
                {
                    loDB.closeConnection();
                }
            }
            
            if (count >= 1)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// decide whether a poll subject is out of date
        /// </summary>
        /// <param name="subject"></param>
        /// <returns></returns>
        public static bool IsOutOfDate(PollSubjectInfo subject)
        {
            if (subject.SubjectDeadline.CompareTo(DateTime.Now) <= 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// Replace the single quotes in a string with double quotes
        /// </summary>
        /// <param name="strInput"></param>
        /// <returns></returns>
        static string DoubleUpSingleQuotes(string strInput)
        {
            if (!String.IsNullOrEmpty(strInput))
            {
                return strInput.Replace("'", "''");
            }
            else
            {
                return strInput;
            }
        }

    }


}
