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.Collections.Generic;
using System.Collections.Specialized;
using System.Text;
using System.Data.SqlClient;
using digioz;

/// <summary>
/// Summary description for PrivateMessageManagement
/// </summary>
public class PrivateMessageManagement
{
    private static PortalSettings coPortalSettings;

    public static PrivateMessageList GetPrivateMessageList(int piUserID, string toFrom, bool pbDeleteExt)
    {
        //selects all private messages from an inbox or outbox
        PrivateMessageList loPrivateMessageList = new PrivateMessageList();
        coPortalSettings = PortalSettingsManagement.GetPortalSettings();
        string lsSQL = "";

        if (coPortalSettings.DBRelational == true)
        {
            IDataRelational loDB = DataFactory.GetDataObject(coPortalSettings);
            PrivateMessage loPrivateMessage;
            DataTable loMessages;

            lsSQL = "SELECT PM.primsg_id, PM.primsg_title, PM.primsg_message, U.username as 'from_user', U1.username as 'to_user',";
            lsSQL += " PM.create_time, PM.sent_time, PM.view_time, PM.action_flag, UP.profile_avatar ";
            lsSQL += "FROM tblPriMsgs PM INNER JOIN tblUsers U ON U.ID = PM.from_userid ";
            lsSQL += "INNER JOIN tblUsers U1 ON U1.ID = PM.to_userid ";

            if (toFrom == "I")
            {
                lsSQL += " INNER JOIN tblUserProfile UP on U.ID = UP.profile_id ";
                lsSQL += " where to_userid = " + piUserID.ToString() + ";";
            }
            else
            {
                lsSQL += " INNER JOIN tblUserProfile UP on U1.ID = UP.profile_id ";
                lsSQL += " where from_userid = " + piUserID.ToString() + ";";
            }

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

            foreach (DataRow loDR in loMessages.Rows)
            {
                loPrivateMessage = new PrivateMessage();
                loPrivateMessage.PriMsg_ID = (int)loDR["primsg_id"];
                loPrivateMessage.PriMsg_title = (string)loDR["primsg_title"];
                loPrivateMessage.PriMsg_message = (string)loDR["primsg_message"];
                loPrivateMessage.From_username = (string)loDR["from_user"];
                loPrivateMessage.To_username = (string)loDR["to_user"];
                loPrivateMessage.Create_time = (DateTime)loDR["create_time"];
                loPrivateMessage.Sent_time = (DateTime)loDR["sent_time"];
                loPrivateMessage.Profile_avatar = (string)loDR["profile_avatar"];
                //view_time will be null until message has been viewed
                if (loDR["view_time"] == DBNull.Value)
                {
                    loPrivateMessage.View_time = DateTime.MinValue;
                }
                else
                {
                    loPrivateMessage.View_time = (DateTime)loDR["view_time"];
                }
                //default action flag to "S" for "Saved message"
                if (loDR["action_flag"] == DBNull.Value)
                {
                    loPrivateMessage.Action_flag = "S";
                }
                else
                {
                    loPrivateMessage.Action_flag = (string)loDR["action_flag"];
                }

                loPrivateMessageList.Add(loPrivateMessage);
            }
            if (pbDeleteExt)
            {
                deletePrivateMessageList(piUserID, toFrom);
            }
        }

        return loPrivateMessageList;
    }

    public static PrivateMessageList GetPrivateMessageList(int piUserID, string toFrom, string fromDate, string toDate, bool pbDeleteExt)
    {
        //selects all private messages in an inbox or outbox between a specified date range
        PrivateMessageList loPrivateMessageList = new PrivateMessageList();
        coPortalSettings = PortalSettingsManagement.GetPortalSettings();

        if (coPortalSettings.DBRelational == true)
        {
            IDataRelational loDB = DataFactory.GetDataObject(coPortalSettings);
            PrivateMessage loPrivateMessage;
            DataTable loMessages;

            string lsSQL = "SELECT PM.primsg_id, PM.primsg_title, PM.primsg_message, U.username as 'from_user', U1.username as 'to_user',";
            lsSQL += " PM.create_time, PM.sent_time, PM.view_time, PM.action_flag, UP.profile_avatar ";
            lsSQL += "FROM tblPriMsgs PM INNER JOIN tblUsers U ON U.ID = PM.from_userid ";
            lsSQL += "INNER JOIN tblUsers U1 ON U1.ID = PM.to_userid ";

            if (toFrom == "I")
            {
                lsSQL += " INNER JOIN tblUserProfile UP on U.ID = UP.profile_id ";
                lsSQL += " where to_userid = " + piUserID.ToString();
            }
            else
            {
                lsSQL += " INNER JOIN tblUserProfile UP on U1.ID = UP.profile_id ";
                lsSQL += " where from_userid = " + piUserID.ToString();
            }

            lsSQL += " AND PM.sent_time >= '" + fromDate + "' AND PM.sent_time <= '" + toDate + "';";

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

            foreach (DataRow loDR in loMessages.Rows)
            {
                loPrivateMessage = new PrivateMessage();
                loPrivateMessage.PriMsg_ID = (int)loDR["primsg_id"];
                loPrivateMessage.PriMsg_title = (string)loDR["primsg_title"];
                loPrivateMessage.PriMsg_message = (string)loDR["primsg_message"];
                loPrivateMessage.From_username = (string)loDR["from_user"];
                loPrivateMessage.To_username = (string)loDR["to_user"];
                loPrivateMessage.Create_time = (DateTime)loDR["create_time"];
                loPrivateMessage.Sent_time = (DateTime)loDR["sent_time"];
                loPrivateMessage.Profile_avatar = (string)loDR["profile_avatar"];
                //view_time will be null until message has been viewed
                if (loDR["view_time"] == DBNull.Value)
                {
                    loPrivateMessage.View_time = DateTime.MinValue;
                }
                else
                {
                    loPrivateMessage.View_time = (DateTime)loDR["view_time"];
                }
                //default action flag to "S" for "Saved message"
                if (loDR["action_flag"] == DBNull.Value)
                {
                    loPrivateMessage.Action_flag = "S";
                }
                else
                {
                    loPrivateMessage.Action_flag = (string)loDR["action_flag"];
                }

                loPrivateMessageList.Add(loPrivateMessage);
            }
            if (pbDeleteExt)
            {
                deletePrivateMessageList(piUserID, toFrom, fromDate, toDate);
            }
        }
        return loPrivateMessageList;
    }

    public static Boolean deletePrivateMessage(StringCollection scMsgIDs)
    {
        coPortalSettings = PortalSettingsManagement.GetPortalSettings();

        if (coPortalSettings.DBRelational == true)
        {
            IDataRelational loDB = DataFactory.GetDataObject(coPortalSettings);
            StringBuilder sbSQL = new StringBuilder(string.Empty);

            foreach (string lsItem in scMsgIDs)
            {
                const string lsStatement = "DELETE FROM tblPriMsgs WHERE primsg_id ";
                sbSQL.AppendFormat("{0}='{1}'; ", lsStatement, lsItem);
            }
            try
            {
                string lsSQL = sbSQL.ToString();
                loDB.openConnection();
                loDB.ExecDB(lsSQL);
                loDB.closeConnection();
                return true;
            }
            catch
            {
                loDB.closeConnection();
                return false;
            }
        }
        else
        {
            return false;
        }
    }

    public static Boolean deletePrivateMessageList(int piUserID, string toFrom)
    {
        //deletes all private messages in an inbox or outbox
        coPortalSettings = PortalSettingsManagement.GetPortalSettings();

        if (coPortalSettings.DBRelational == true)
        {
            IDataRelational loDB = DataFactory.GetDataObject(coPortalSettings);
            //StringBuilder sbSQL = new StringBuilder(string.Empty);

            string lsSQL = "DELETE FROM tblPriMsgs ";

            if (toFrom == "I")
            {
                lsSQL += "where to_userid = " + piUserID.ToString();
            }
            else
            {
                lsSQL += "where from_userid = " + piUserID.ToString();
            }

            try
            {
                loDB.openConnection();
                loDB.ExecDB(lsSQL);
                loDB.closeConnection();
                return true;
            }
            catch
            {
                loDB.closeConnection();
                return false;
            }
        }
        else
        {
            return false;
        }
    }

    public static Boolean deletePrivateMessageList(int piUserID, string toFrom, string fromDate, string toDate)
    {
        //deletes all private messages in an inbox or outbox between a specified date range
        coPortalSettings = PortalSettingsManagement.GetPortalSettings();

        if (coPortalSettings.DBRelational == true)
        {
            IDataRelational loDB = DataFactory.GetDataObject(coPortalSettings);
            StringBuilder sbSQL = new StringBuilder(string.Empty);

            string lsSQL = "DELETE FROM tblPriMsgs ";

            if (toFrom == "I")
            {
                lsSQL += " where to_userid = " + piUserID.ToString();
            }
            else
            {
                lsSQL += " where from_userid = " + piUserID.ToString();
            }

            lsSQL += " AND sent_time >= '" + fromDate + "' AND sent_time <= '" + toDate + "';";

            try
            {
                loDB.openConnection();
                loDB.ExecDB(lsSQL);
                loDB.closeConnection();
                return true;
            }
            catch
            {
                loDB.closeConnection();
                return false;
            }
        }
        else
        {
            return false;
        }
    }

    public static PrivateMessageList GetPrivateMessage(int piMsgID, string toFrom)
    {
        coPortalSettings = PortalSettingsManagement.GetPortalSettings();

        PrivateMessageList loPrivateMessageList = new PrivateMessageList();

        if (coPortalSettings.DBRelational == true)
        {
            IDataRelational loDB = DataFactory.GetDataObject(coPortalSettings);
            PrivateMessage loPrivateMessage = new PrivateMessage();
            DataTable loMessage;

            string lsSQL = "SELECT PM.primsg_id, PM.primsg_title, PM.primsg_message, U.username as 'from_user', U1.username as 'to_user',";
            lsSQL += " PM.create_time, PM.sent_time, PM.view_time, PM.action_flag, UP.profile_avatar, ";
            lsSQL += "PM.from_userID, PM.to_userID ";
            lsSQL += "FROM tblPriMsgs PM INNER JOIN tblUsers U ON U.ID = PM.from_userid ";
            lsSQL += "INNER JOIN tblUsers U1 ON U1.ID = PM.to_userid ";
            lsSQL += " INNER JOIN tblUserProfile UP on U.ID = UP.profile_id ";
            lsSQL += " where PM.primsg_id = " + piMsgID + ";";


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

            foreach (DataRow loDR in loMessage.Rows)
            {
                loPrivateMessage = new PrivateMessage();
                loPrivateMessage.PriMsg_ID = (int)loDR["primsg_id"];
                loPrivateMessage.PriMsg_title = ReformatMessage((string)loDR["primsg_title"]);
                loPrivateMessage.PriMsg_message = ReformatMessage((string)loDR["primsg_message"]);
                loPrivateMessage.From_username = (string)loDR["from_user"];
                loPrivateMessage.To_username = (string)loDR["to_user"];
                loPrivateMessage.Create_time = (DateTime)loDR["create_time"];
                loPrivateMessage.Sent_time = (DateTime)loDR["sent_time"];
                loPrivateMessage.Profile_avatar = (string)loDR["profile_avatar"];
                loPrivateMessage.From_userID = (int)loDR["from_userID"];
                loPrivateMessage.To_userID = (int)loDR["to_userID"];
                //view_time will be null until message has been viewed
                if (loDR["view_time"] == DBNull.Value)
                {
                    loPrivateMessage.View_time = DateTime.MinValue;
                }
                else
                {
                    loPrivateMessage.View_time = (DateTime)loDR["view_time"];
                }
                //default action flag to "S" for "Saved message"
                if (loDR["action_flag"] == DBNull.Value)
                {
                    loPrivateMessage.Action_flag = "S";
                }
                else
                {
                    loPrivateMessage.Action_flag = (string)loDR["action_flag"];
                }

                loPrivateMessageList.Add(loPrivateMessage);
            }
        }
        return loPrivateMessageList;
    }   //end GetPrivateMessages

    public static bool InsertPrivateMessage(PrivateMessage poPrivateMessage, string psMode)
    {
        try
        {
            coPortalSettings = PortalSettingsManagement.GetPortalSettings();

            if (coPortalSettings.DBRelational == true)
            {
                IDataRelational loDB = DataFactory.GetDataObject(coPortalSettings);
                string lsSQL = "EXEC spInsertPrivateMessage "
                                    + poPrivateMessage.From_userID + ","
                                    + poPrivateMessage.To_userID + ","
                                    + "'" + FormatMessage(poPrivateMessage.PriMsg_title) + "',"
                                    + "'" + FormatMessage(poPrivateMessage.PriMsg_message) + "',"
                                    + "'" + poPrivateMessage.Create_time + "',"
                                    + "'" + poPrivateMessage.Sent_time + "';";

                loDB.openConnection();
                loDB.ExecDB(lsSQL);
                loDB.closeConnection();
            }
            return true;
        }
        catch
        {
            return false;
        }
    }   //end insert
    public static string ReformatMessage(string psMsgText)
    {
        string lsOutput = psMsgText;
        lsOutput = System.Text.RegularExpressions.Regex.Replace(psMsgText, "<[^>]*>", string.Empty);
        lsOutput = lsOutput.Replace("\n", "<br />");
        return lsOutput;
    }   //end ReformatMessage

    public static string FormatMessage(string psMsgText)
    {
        string lsOutput = psMsgText;
        lsOutput = System.Text.RegularExpressions.Regex.Replace(psMsgText, "<[^>]*>", string.Empty);
        lsOutput = lsOutput.Replace("'", "''");
        return lsOutput;
    }   //end FormatMessage


    /// <summary>
    /// Update a PrivateMessage record
    /// </summary>
    /// <param name="poPrivateMessage"></param>
    /// <returns></returns>
    public static bool UpdatePrivateMessage(PrivateMessage poPrivateMessage)
    {  
        coPortalSettings = PortalSettingsManagement.GetPortalSettings();
        bool bRetValue = false;

        if (coPortalSettings.DBRelational == true)
        {
            IDataRelational loDB = DataFactory.GetDataObject(coPortalSettings);
            string lsSQL = string.Format(
                    "EXEC spUpdatePrivateMessage " +
                    "@PriMsgId = {0}, @view_time = '{1}', @primsg_read = '{2}'",
                    poPrivateMessage.PriMsg_ID,
                    poPrivateMessage.View_time,
                    poPrivateMessage.ReadFlag);
            try
            {
                loDB.openConnection();
                loDB.ExecDB(lsSQL);
                bRetValue = string.IsNullOrEmpty(loDB.sErr);
            }
            finally
            {
                loDB.closeConnection();
            }
        }
        return bRetValue;
    }   

    /// <summary>
    /// Get the number of unread messages
    /// </summary>
    /// <returns></returns>
    public static int GetUnReadMessagesCount(int piUserID)
    {
        coPortalSettings = PortalSettingsManagement.GetPortalSettings();
        int liMessagesCount = 0;

        if (coPortalSettings.DBRelational == true)
        {
            IDataRelational loDB = DataFactory.GetDataObject(coPortalSettings);
            string lsSQL = string.Format("EXEC spGetUnReadMessagesCount @UserID = {0}",
                                         piUserID);
            /*SqlParameter[] loParameters = new SqlParameter[1];
            loParameters[0] = new SqlParameter("@UserID", SqlDbType.Int);
            loParameters[0].Value = piUserID;*/

            try
            {
                loDB.openConnection();
                object loCount = loDB.ExecDBScalar(lsSQL);
                if (loCount != null)
                {
                    liMessagesCount = Convert.ToInt32(loCount);
                }
            }
            finally
            {
                loDB.closeConnection();
            }
            
        }
        return liMessagesCount;
    }
}
