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;

using System.ComponentModel;

using System.Data.SqlClient;

using System.Collections.Generic;





namespace PigeonCms

{

    /// <summary>

    /// Data Access Layer for EmailNewsletter obj (in table emailInserite)

    /// </summary>

    [DataObject()]

    public static class EmailNewsletterManager

    {

        [DataObjectMethod(DataObjectMethodType.Select, true)]

        public static List<EmailNewsletter> GetListByFilter(EmailNewsletterFilter filter, string sort)

        {

            SqlConnection myConn = new SqlConnection();

            SqlDataReader myRd = null;

            SqlCommand myCmd = new SqlCommand();

            string sSql;

            List<EmailNewsletter> result = new List<EmailNewsletter>();



            try

            {

                myConn.ConnectionString = Database.ConnString;

                myConn.Open();

                myCmd.Connection = myConn;



                sSql = "SELECT EmailId FROM #__EmailInserite t WHERE t.EmailId > 0 ";

                if (filter.EmailId > 0 || filter.EmailId == -1)

                {

                    sSql += " AND t.EmailId = @EmailId ";

                    myCmd.Parameters.AddWithValue("EmailId", filter.EmailId);

                }

                if (!string.IsNullOrEmpty(filter.Email))

                {

                    sSql += " AND t.Email = @Email ";

                    myCmd.Parameters.AddWithValue("Email", filter.Email);

                }

                if (!string.IsNullOrEmpty(filter.Ip))

                {

                    sSql += " AND t.Ip = @Ip ";

                    myCmd.Parameters.AddWithValue("Ip", filter.Ip);

                }

                if (!string.IsNullOrEmpty(sort))

                {

                    sSql += " ORDER BY " + sort;

                }

                else

                {

                    sSql += " ORDER BY EmailId ";

                }

                myCmd.CommandText = Database.ParseSql(sSql);

                myRd = myCmd.ExecuteReader();

                while (myRd.Read())

                {

                    EmailNewsletter item = GetById((int)myRd["EmailId"]);

                    result.Add(item);

                }

                myRd.Close();

            }

            finally

            {

                myConn.Dispose();

            }

            return result;

        }



        [DataObjectMethod(DataObjectMethodType.Select, false)]

        public static EmailNewsletter GetById(int emailId)

        {

            SqlConnection myConn = new SqlConnection();

            SqlDataReader myRd = null;

            SqlCommand myCmd = new SqlCommand();

            string sSql;

            EmailNewsletter result = new EmailNewsletter();



            try

            {

                myConn.ConnectionString = Database.ConnString;

                myConn.Open();

                myCmd.Connection = myConn;



                sSql = "SELECT EmailId, InsDate, Email, Ip "

                + " FROM #__emailInserite m "

                + " WHERE EmailId = @EmailId ";

                myCmd.CommandText = Database.ParseSql(sSql);

                myCmd.Parameters.AddWithValue("EmailId", emailId);

                myRd = myCmd.ExecuteReader();

                if (myRd.Read())

                {

                    if (!Convert.IsDBNull(myRd["EmailId"]))

                        result.EmailId = (int)myRd["EmailId"];

                    if (!Convert.IsDBNull(myRd["InsDate"]))

                        result.InsDate = Convert.ToDateTime(myRd["InsDate"]);

                    if (!Convert.IsDBNull(myRd["Email"]))

                        result.Email = (string)myRd["Email"];

                    if (!Convert.IsDBNull(myRd["Ip"]))

                        result.Ip = (string)myRd["Ip"];

                }

                myRd.Close();

            }

            finally

            {

                myConn.Dispose();

            }

            return result;

        }



        [DataObjectMethod(DataObjectMethodType.Delete, true)]

        public static int DeleteById(int emailId)

        {

            SqlConnection myConn = new SqlConnection();

            SqlCommand myCmd = new SqlCommand();

            string sSql;

            int res = 0;



            try

            {

                myConn.ConnectionString = Database.ConnString;

                myConn.Open();

                myCmd.Connection = myConn;



                sSql = "DELETE FROM #__emailInserite WHERE emailId = @emailId ";

                myCmd.CommandText = Database.ParseSql(sSql);

                myCmd.Parameters.AddWithValue("emailId", emailId);

                res = myCmd.ExecuteNonQuery();

            }

            finally

            {

                myConn.Dispose();

            }

            return res;

        }





        /// <summary>

        /// Insert a new record in table emailInserite

        /// </summary>

        /// <param name="newObj">The info about new EmailNewsletter, newObj.EmaiId and newObj.InsDate will be calculated in automatic</param>

        /// <returns>The new object</returns>

        [DataObjectMethod(DataObjectMethodType.Insert, false)]

        public static EmailNewsletter Insert(EmailNewsletter newObj)

        {

            SqlConnection myConn = new SqlConnection();

            SqlCommand myCmd = new SqlCommand();

            string sSql;

            EmailNewsletter result = new EmailNewsletter();



            try

            {

                myConn.ConnectionString = Database.ConnString;

                myConn.Open();

                myCmd.Connection = myConn;



                result.EmailId = getNextId();

                result.InsDate = newObj.InsDate;

                result.Email = newObj.Email;

                result.Ip = newObj.Ip;



                sSql = "INSERT INTO #__emailInserite(EmailId, InsDate, Email, Ip) "

                + "VALUES(@EmailId, getdate(), @Email, @Ip) ";

                myCmd.CommandText = Database.ParseSql(sSql);

                myCmd.Parameters.AddWithValue("EmailId", result.EmailId);

                myCmd.Parameters.AddWithValue("Email", result.Email);

                myCmd.Parameters.AddWithValue("Ip", result.Ip);



                myCmd.ExecuteNonQuery();

            }

            finally

            {

                myConn.Dispose();

            }

            return result;

        }





        private static int getNextId()

        {

            SqlConnection myConn = new SqlConnection();

            SqlDataReader myRd = null;

            SqlCommand myCmd = new SqlCommand();

            string sSql;

            int result = 0;



            try

            {

                myConn.ConnectionString = Database.ConnString;

                myConn.Open();

                myCmd.Connection = myConn;



                sSql = "SELECT max(EmailId) FROM #__emailInserite ";

                myCmd.CommandText = Database.ParseSql(sSql);

                myRd = myCmd.ExecuteReader();

                if (myRd.Read())

                {

                    if (myRd[0] != DBNull.Value)

                    {

                        result = (int)myRd[0];

                    }

                }

                myRd.Close();

                result++;

            }

            finally

            {

                myConn.Dispose();

            }

            return result;

        }

    }

}