using System;
using System.Data;
using System.Data.SqlClient;
using System.ComponentModel;
using System.Configuration;
using System.Web.Mail;
using System.Text.RegularExpressions;
using System.Web.UI.HtmlControls;
using System.Web.UI;
using System.Collections;
using System.Resources;
using System.Globalization;
using vbenc;

namespace infoExpediters
{
    public class User
    {
        //check login credentials
        public Hashtable isValidAdminLogin(string strUsername, string strPassword)
        {
            Hashtable ht = new Hashtable();
            String strCon = ConfigurationSettings.AppSettings["DB_CONNECTION"];
            using (SqlConnection conn = new SqlConnection(strCon))
            {
                SqlCommand mycommand = new SqlCommand("dbo.sp_adminlogin", conn);
                mycommand.CommandType = CommandType.StoredProcedure;
                mycommand.Parameters.Add(new SqlParameter("@Username", SqlDbType.VarChar));
                mycommand.Parameters.Add(new SqlParameter("@Password", SqlDbType.VarChar));

                mycommand.Parameters["@Username"].Value = strUsername;
                mycommand.Parameters["@Password"].Value = strPassword;

                mycommand.Connection.Open();
                SqlDataReader dr = mycommand.ExecuteReader();

                if (dr.Read())
                {
                    ht["UserName"] = dr["username"];
                    ht["PassWord"] = dr["password"];
                    ht["FirstName"] = dr["firstname"];
                    ht["LastName"] = dr["lastname"];
                    ht["Status"] = 1;
                }
                else
                {
                    ht["Status"] = 0;
                }
                dr.Close();
                mycommand.Dispose();
                conn.Close();
                conn.Dispose();
            }
            return ht;
        }
        // gets the content to display in Home page/blog page/ about us page
        public string GetContent(string content)
        {
            string BBCodes = "";
            String strCon = ConfigurationSettings.AppSettings["DB_CONNECTION"];
            using (SqlConnection conn = new SqlConnection(strCon))
            {
                conn.Open();  
                SqlCommand cmd=new SqlCommand("select BBCodeDescription from tblContentPages where ContentPageName='"+content+"'",conn);
                SqlDataReader dr = cmd.ExecuteReader(); 
                while (dr.Read())
                {
                    BBCodes = dr["BBCodeDescription"].ToString(); 
                }
                dr.Close();
                conn.Close();  
            }
            return BBCodes; 
        }

        public int adminchangepassword(Hashtable ht)
        {
            int status;
            String strCon = ConfigurationSettings.AppSettings["DB_CONNECTION"];
            using (SqlConnection con = new SqlConnection(strCon))
            {
                SqlCommand cmd = new SqlCommand("adminchangepassord", con);
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add("@Username", SqlDbType.VarChar);
                cmd.Parameters.Add("@OldPassword", SqlDbType.VarChar);
                cmd.Parameters.Add("@NewPassword", SqlDbType.VarChar);
                cmd.Parameters.Add("@Status", SqlDbType.VarChar);

                cmd.Parameters["@Username"].Value = ht["Username"].ToString();
                cmd.Parameters["@OldPassword"].Value = ht["oldpassword"].ToString();
                cmd.Parameters["@NewPassword"].Value = ht["newpassword"].ToString();
                cmd.Parameters["@Status"].Value = "0";

                cmd.Parameters["@Status"].Direction = ParameterDirection.Output;
                con.Open();

                cmd.ExecuteNonQuery();
                con.Close();
                status = int.Parse(cmd.Parameters["@Status"].Value.ToString());
            }
            return status;
        }

        //This function is used to check whether it is a valid login or not.
        public Hashtable isValidLogin(string strUsername, string strPassword)
        {
            Hashtable ht = new Hashtable();
            String strCon = ConfigurationSettings.AppSettings["DB_CONNECTION"];
            using (SqlConnection conn = new SqlConnection(strCon))
            {
                SqlCommand mycommand = new SqlCommand("dbo.sp_login", conn);
                mycommand.CommandType = CommandType.StoredProcedure;
                mycommand.Parameters.Add(new SqlParameter("@Username", SqlDbType.VarChar));
                mycommand.Parameters.Add(new SqlParameter("@Password", SqlDbType.VarChar));

                mycommand.Parameters["@Username"].Value = strUsername;
                mycommand.Parameters["@Password"].Value = strPassword;

                mycommand.Connection.Open();
                SqlDataReader dr = mycommand.ExecuteReader();

                if (dr.Read())
                {
                    ht["Userid"] = dr["UserId"];
                    ht["UserName"] = dr["Username"];
                    ht["PassWord"] = dr["Password"];
                    ht["FirstName"] = dr["FirstName"];
                    ht["LastName"] = dr["LastName"].ToString();
                    ht["Email"] = dr["Email"].ToString();
                    ht["Status"] = 1;
                }
                else
                {
                    ht["Status"] = 0;
                }
                dr.Close();
                mycommand.Dispose();
                conn.Close();
                conn.Dispose();
            }
            return ht;
        }

        //Function for changing password 
        public int ChangePassword(Hashtable ht)
        {
            int status;
            String strCon = ConfigurationSettings.AppSettings["DB_CONNECTION"];
            using (SqlConnection con = new SqlConnection(strCon))
            {
                SqlCommand cmd = new SqlCommand("ChangePassword", con);
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add("@UserId", SqlDbType.VarChar);
                cmd.Parameters.Add("@OldPassword", SqlDbType.VarChar);
                cmd.Parameters.Add("@NewPassword", SqlDbType.VarChar);
                cmd.Parameters.Add("@Status", SqlDbType.VarChar);

                cmd.Parameters["@UserId"].Value = ht["userid"].ToString();
                cmd.Parameters["@OldPassword"].Value = ht["oldpassword"].ToString();
                cmd.Parameters["@NewPassword"].Value = ht["newpassword"].ToString();
                cmd.Parameters["@Status"].Value = "0";

                cmd.Parameters["@Status"].Direction = ParameterDirection.Output;
                con.Open();

                cmd.ExecuteNonQuery();
                con.Close();
                status = int.Parse(cmd.Parameters["@Status"].Value.ToString());
            }
            return status;
        }

        //This function is used to check the existence of the new email in the system.
        public bool isExistsEmail(string strNewEmail)
        {
            string strSQL = "Select UserId FROM tblUsers WHERE Email='" + strNewEmail + "'";
            bool status = false;
            String strCon = ConfigurationSettings.AppSettings["DB_CONNECTION"];
            using (SqlConnection conn = new SqlConnection(strCon))
            {
                SqlCommand mycommand = new SqlCommand(strSQL, conn);
                mycommand.Connection.Open();

                SqlDataReader dr = mycommand.ExecuteReader();
                if (dr.Read())
                {
                    status = true;
                }
                else
                {
                    status = false;
                }
                dr.Close();
                mycommand.Connection.Close();
                conn.Close();
                conn.Dispose();
            }
            return status;
        }

        //This function is used to get password of the user.
        public Hashtable retrievePassword(string strEmail)
        {
            Hashtable ht = new Hashtable();

            string strSql = "SELECT userid,password FROM tblUsers WHERE Email='" + strEmail + "'";
            String strCon = ConfigurationSettings.AppSettings["DB_CONNECTION"];
            using (SqlConnection conn = new SqlConnection(strCon))
            {
                SqlCommand mycommand = new SqlCommand(strSql, conn);
                mycommand.Connection.Open();
                SqlDataReader dr = mycommand.ExecuteReader();

                if (dr.Read())
                {
                    ht["userid"] = dr["UserId"].ToString();
                    ht["password"] = dr["Password"].ToString();
                }

                dr.Close();
                mycommand.Dispose();
                conn.Close();
                conn.Dispose();
            }
            return ht;
        }
        // Gets the content of the given page (blog page/home page/about us page)
        public string ContentPage(string CntPage)
        {
            string description = "";

            string strSql = "SELECT BBCodeDescription,Description from tblContentPages WHERE ContentPageName='" + CntPage + "'";
            String strCon = ConfigurationSettings.AppSettings["DB_CONNECTION"];
            using (SqlConnection conn = new SqlConnection(strCon))
            {
                SqlCommand mycommand = new SqlCommand(strSql, conn);
                mycommand.Connection.Open();
                SqlDataReader dr = mycommand.ExecuteReader();
                if (dr.Read())
                {
                    description = dr["Description"].ToString();
                }
                dr.Close();
                mycommand.Dispose();
                conn.Close();
                conn.Dispose();
            }
            return description;
        }

        public string EncodeBBcode(String CntPage )
        {
            string Enbbcode = "";
            String strCon = ConfigurationSettings.AppSettings["DB_CONNECTION"];
            using (SqlConnection conn = new SqlConnection(strCon))
            {
                SqlCommand cmd = new SqlCommand("SELECT BBCodeDescription from tblContentPages WHERE ContentPageName='" + CntPage + "'", conn);
                conn.Open();
                SqlDataReader da = cmd.ExecuteReader();
                if (da.Read()) 
                {
                    Enbbcode = da["BBCodeDescription"].ToString();
                }
                da.Close();
                conn.Close();
            }
            return Enbbcode;
        }
    }   
}