/mojoPortal.Data.MySql/dbUserPage.cs

/// Author:					Joe Audette

/// Created:				2007-11-03

/// Last Modified:			2012-07-20

/// 

/// The use and distribution terms for this software are covered by the 

/// Common Public License 1.0 (http://opensource.org/licenses/cpl.php)  

/// which can be found in the file CPL.TXT at the root of this distribution.

/// By using this software in any fashion, you are agreeing to be bound by 

/// the terms of this license.

///

/// You must not remove this notice, or any other, from this software.

/// 

/// Note moved into separate class file from dbPortal 2007-11-03



using System;

using System.Text;

using System.Data;

using System.Data.Common;

using System.Configuration;

using System.Globalization;

using System.IO;

using MySql.Data.MySqlClient;



namespace mojoPortal.Data

{

    public static class DBUserPage

    {



        public static int AddUserPage(

            Guid userPageId,

            Guid siteGuid,

            int siteId,

            Guid userGuid,

            string pageName,

            string pagePath,

            int pageOrder)

        {

            StringBuilder sqlCommand = new StringBuilder();

            sqlCommand.Append("INSERT INTO mp_UserPages ");

            sqlCommand.Append("( ");

            sqlCommand.Append("UserPageID, ");

            sqlCommand.Append("SiteGuid, ");

            sqlCommand.Append("SiteID, ");

            sqlCommand.Append("UserGuid, ");

            sqlCommand.Append("PageName, ");

            sqlCommand.Append("PagePath, ");

            sqlCommand.Append("PageOrder ");

            sqlCommand.Append(")");



            sqlCommand.Append(" VALUES (");

            sqlCommand.Append("?UserPageID, ");

            sqlCommand.Append("?SiteGuid, ");

            sqlCommand.Append("?SiteID, ");

            sqlCommand.Append("?UserGuid, ");

            sqlCommand.Append("?PageName, ");

            sqlCommand.Append("?PagePath, ");

            sqlCommand.Append("?PageOrder ");



            sqlCommand.Append(");");





            MySqlParameter[] arParams = new MySqlParameter[7];



            arParams[0] = new MySqlParameter("?UserPageID", MySqlDbType.VarChar, 36);

            arParams[0].Direction = ParameterDirection.Input;

            arParams[0].Value = userPageId.ToString();



            arParams[1] = new MySqlParameter("?SiteID", MySqlDbType.Int32);

            arParams[1].Direction = ParameterDirection.Input;

            arParams[1].Value = siteId;



            arParams[2] = new MySqlParameter("?UserGuid", MySqlDbType.VarChar, 36);

            arParams[2].Direction = ParameterDirection.Input;

            arParams[2].Value = userGuid.ToString();



            arParams[3] = new MySqlParameter("?PageName", MySqlDbType.VarChar, 255);

            arParams[3].Direction = ParameterDirection.Input;

            arParams[3].Value = pageName;



            arParams[4] = new MySqlParameter("?PagePath", MySqlDbType.VarChar, 255);

            arParams[4].Direction = ParameterDirection.Input;

            arParams[4].Value = pagePath;



            arParams[5] = new MySqlParameter("?PageOrder", MySqlDbType.Int32);

            arParams[5].Direction = ParameterDirection.Input;

            arParams[5].Value = pageOrder;



            arParams[6] = new MySqlParameter("?SiteGuid", MySqlDbType.VarChar, 36);

            arParams[6].Direction = ParameterDirection.Input;

            arParams[6].Value = siteGuid.ToString();



            int rowsAffected = MySqlHelper.ExecuteNonQuery(

                ConnectionString.GetWriteConnectionString(),

                sqlCommand.ToString(),

                arParams);



            return rowsAffected;



        }



        public static bool UpdateUserPage(

        Guid userPageId,

        string pageName,

        int pageOrder)

        {

            StringBuilder sqlCommand = new StringBuilder();

            sqlCommand.Append("UPDATE mp_UserPages ");

            sqlCommand.Append("SET ");

            sqlCommand.Append("PageName = ?PageName, ");

            sqlCommand.Append("PageOrder = ?PageOrder ");

            sqlCommand.Append("WHERE UserPageID = ?UserPageID; ");



            MySqlParameter[] arParams = new MySqlParameter[3];



            arParams[0] = new MySqlParameter("?UserPageID", MySqlDbType.VarChar, 36);

            arParams[0].Direction = ParameterDirection.Input;

            arParams[0].Value = userPageId.ToString();



            arParams[1] = new MySqlParameter("?PageName", MySqlDbType.VarChar, 255);

            arParams[1].Direction = ParameterDirection.Input;

            arParams[1].Value = pageName;



            arParams[2] = new MySqlParameter("?PageOrder", MySqlDbType.Int32);

            arParams[2].Direction = ParameterDirection.Input;

            arParams[2].Value = pageOrder;



            int rowsAffected = MySqlHelper.ExecuteNonQuery(

                ConnectionString.GetWriteConnectionString(),

                sqlCommand.ToString(),

                arParams);



            return (rowsAffected > 0);



        }



        public static bool DeleteUserPage(Guid userPageId)

        {

            StringBuilder sqlCommand = new StringBuilder();

            sqlCommand.Append("DELETE FROM mp_UserPages ");

            sqlCommand.Append("WHERE UserPageID = ?UserPageID; ");



            MySqlParameter[] arParams = new MySqlParameter[1];



            arParams[0] = new MySqlParameter("?UserPageID", MySqlDbType.VarChar, 36);

            arParams[0].Direction = ParameterDirection.Input;

            arParams[0].Value = userPageId.ToString();



            int rowsAffected = MySqlHelper.ExecuteNonQuery(

                ConnectionString.GetWriteConnectionString(),

                sqlCommand.ToString(),

                arParams);



            return (rowsAffected > 0);



        }



        public static bool DeleteByUser(Guid userGuid)

        {

            StringBuilder sqlCommand = new StringBuilder();

            sqlCommand.Append("DELETE FROM mp_UserPages ");

            sqlCommand.Append("WHERE UserGuid = ?UserGuid; ");



            MySqlParameter[] arParams = new MySqlParameter[1];



            arParams[0] = new MySqlParameter("?UserGuid", MySqlDbType.VarChar, 36);

            arParams[0].Direction = ParameterDirection.Input;

            arParams[0].Value = userGuid.ToString();



            int rowsAffected = MySqlHelper.ExecuteNonQuery(

                ConnectionString.GetWriteConnectionString(),

                sqlCommand.ToString(),

                arParams);



            return (rowsAffected > 0);

        }



        public static IDataReader GetUserPage(Guid userPageId)

        {

            StringBuilder sqlCommand = new StringBuilder();

            sqlCommand.Append("SELECT  * ");

            sqlCommand.Append("FROM	mp_UserPages ");

            sqlCommand.Append("WHERE ");

            sqlCommand.Append("UserPageID = ?UserPageID ;");



            MySqlParameter[] arParams = new MySqlParameter[1];



            arParams[0] = new MySqlParameter("?UserPageID", MySqlDbType.VarChar, 36);

            arParams[0].Direction = ParameterDirection.Input;

            arParams[0].Value = userPageId.ToString();



            return MySqlHelper.ExecuteReader(

                ConnectionString.GetReadConnectionString(),

                sqlCommand.ToString(),

                arParams);



        }



        public static IDataReader SelectByUser(Guid userGuid)

        {

            StringBuilder sqlCommand = new StringBuilder();

            sqlCommand.Append("SELECT  * ");

            sqlCommand.Append("FROM	mp_UserPages ");

            sqlCommand.Append("WHERE ");

            sqlCommand.Append("UserGuid = ?UserGuid ");

            sqlCommand.Append("ORDER BY PageOrder ;");



            MySqlParameter[] arParams = new MySqlParameter[1];



            arParams[0] = new MySqlParameter("?UserGuid", MySqlDbType.VarChar, 36);

            arParams[0].Direction = ParameterDirection.Input;

            arParams[0].Value = userGuid.ToString();



            return MySqlHelper.ExecuteReader(

                ConnectionString.GetReadConnectionString(),

                sqlCommand.ToString(),

                arParams);



        }



        public static int GetNextPageOrder(Guid userGuid)

        {

            StringBuilder sqlCommand = new StringBuilder();

            sqlCommand.Append("SELECT COALESCE(MAX(PageOrder),-1)  ");

            sqlCommand.Append("FROM	mp_UserPages ");



            sqlCommand.Append("WHERE UserGuid = ?UserGuid ; ");



            MySqlParameter[] arParams = new MySqlParameter[1];



            arParams[0] = new MySqlParameter("?UserGuid", MySqlDbType.VarChar, 36);

            arParams[0].Direction = ParameterDirection.Input;

            arParams[0].Value = userGuid.ToString();



            int nextPageOrder = Convert.ToInt32(MySqlHelper.ExecuteScalar(

                ConnectionString.GetReadConnectionString(),

                sqlCommand.ToString(),

                arParams)) + 2;



            if (nextPageOrder == 1)

            {

                nextPageOrder = 3;

            }



            return nextPageOrder;



        }



        public static bool UpdatePageOrder(Guid userPageId, int pageOrder)

        {

            StringBuilder sqlCommand = new StringBuilder();

            sqlCommand.Append("UPDATE mp_UserPages ");

            sqlCommand.Append("SET ");

            sqlCommand.Append("PageOrder = ?PageOrder ");

            sqlCommand.Append("WHERE UserPageID = ?UserPageID; ");



            MySqlParameter[] arParams = new MySqlParameter[2];



            arParams[0] = new MySqlParameter("?UserPageID", MySqlDbType.VarChar, 36);

            arParams[0].Direction = ParameterDirection.Input;

            arParams[0].Value = userPageId.ToString();



            arParams[1] = new MySqlParameter("?PageOrder", MySqlDbType.Int32);

            arParams[1].Direction = ParameterDirection.Input;

            arParams[1].Value = pageOrder;



            int rowsAffected = MySqlHelper.ExecuteNonQuery(

                ConnectionString.GetWriteConnectionString(),

                sqlCommand.ToString(),

                arParams);



            return (rowsAffected > 0);



        }



        





    }

}