//filename: DH_Users.cs

namespace DH_Users {

    using System;
    using System.Web;
        using System.Web.Services;
    //using System.Web.Util;
    using System.Web.Mail;
    using System.Web.UI;
    using System.Data;
    using System.Data.SqlClient;
    using System.Collections;
    using DH_Constants;
    using RATINGS;

    /// <summary>
    /// A class containing datebase interactions for users on the Devhood System.
    /// DH_Users holds all the methods that interact with the Users table in the database.
    /// </summary>
    ///
  [WebService (Namespace="DH_UserWS", Description="The Devhood Users Web Service")]
    public class DH_UserDB : Page {


public static bool IsSC(string strUserID) {
DataView dv = DH_Constants.Select("select first_names+' '+last_name as Name, email, student_consultant_p as SC, school_name as School, users.school_id as SchoolID from users, schools where users.school_id=schools.school_id and user_id='"+strUserID+"'");
return Convert.ToBoolean(dv[0]["SC"]);
}

        /// <summary>
        /// Used to check if the email entered is in the database to avoid duplicate email addresses.
        /// Used by the custom validator on the register.aspx page.
        /// </summary>
        /// <returns>Returns false if the email address is already in the database and true if not</returns>
        /// <param name="strEmail">Email address </param>
        public static bool ValidateEmail(string strEmail){
            //LOWER procedure invoked to avoid case sensitivity issuse.

            SqlConnection myConnection = new SqlConnection(DH_Constants.strConnection);
            SqlCommand myCommand  = new SqlCommand("DH_Users_ValidateEmail", myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;

            SqlParameter Email = new SqlParameter("@strEmail", SqlDbType.VarChar, 100);
            Email.Value           = strEmail.ToLower();
            myCommand.Parameters.Add(Email);

            bool bResult = false;

            try {
                myConnection.Open();  
                SqlDataReader myReader;  
                myReader = myCommand.ExecuteReader();
                myReader.Read();
                bResult = (0 == myReader.GetInt32(0));
            } catch (Exception) {
                bResult = false;
            }
            return bResult;
        }

        /// <summary>
        /// Attemps to authenticate a user by checking for the user in the database.
        /// </summary>
        /// <param name="strEmail">The user's email.</param>
        /// <param name="strPassword">The user's password.</param>
        /// <return>
        /// The DefaultValue is returned if nothing happens or an exception is thrown
        /// If the user has been blacklisted, -2 is returned.
        /// If the user has not confirmed his registration, -3 is returned.
        /// Otherwise the user's ID is returned.
        /// </return>
        /// <value>
        /// An <c>int</c> representing the user's ID or DefaultValue if the process fails
        /// </value>
        public static int AuthenticateUser(string strEmail, string strPassword) {

            SqlConnection myConnection = new SqlConnection(DH_Constants.strConnection);
            SqlCommand myCommand  = new SqlCommand("DH_Users_AuthenticateUser", myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;

            SqlParameter Email = new SqlParameter("@strEmail", SqlDbType.VarChar, 100);
            Email.Value           = strEmail.ToLower();
            myCommand.Parameters.Add(Email);

            SqlParameter Password = new SqlParameter("@strPassword", SqlDbType.VarChar, 12);
            Password.Value          = strPassword;
            myCommand.Parameters.Add(Password);

            int iResult = DH_Constants.DefaultValue;
            try {
                myConnection.Open();  
                SqlDataReader myReader;  
                myReader = myCommand.ExecuteReader();
                myReader.Read();
                if (myReader.IsDBNull(1)) {
                    iResult = DH_Constants.InvalidOpException;
                } 
                //else if (myReader.IsDBNull(2)) {
                //    iResult= -3;
                //} 
                else {
                    iResult = myReader.GetInt32(0);
                }
                    
                myReader.Close(); 
            } catch {            
                iResult = DH_Constants.DefaultValue;
            } finally {
                if (myConnection.State == ConnectionState.Open)
                    myCommand.Connection.Close();;
            }
            return iResult;
        }

        public static int GetSchoolIDFromUserID(string strUserID) {

                SqlConnection myConnection = new SqlConnection(DH_Constants.strConnection);
            SqlCommand myCommand  = new SqlCommand("DH_Users_GetSchoolIDFromUserID", myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;

            SqlParameter UserID = new SqlParameter("@strUserID", SqlDbType.VarChar, 100);
            UserID.Value           = strUserID;
            myCommand.Parameters.Add(UserID);

            int iResult = -1;
            try {
                myConnection.Open();  
                SqlDataReader myReader;  
                myReader = myCommand.ExecuteReader();  
                myReader.Read();
                iResult = (myReader.IsDBNull(0)) ? -1 : myReader.GetInt32(0);
                myReader.Close(); 
            } catch {            
                                iResult = -1;
            }
            return iResult;
        }            
              
                

        /// <summary>
        /// Returns the user's ID given his email
        /// </summary>
        /// <param name="strEmail">The user's email.</param>
        /// <value>
        /// An <c>int</c> representing the user's ID or DefaultValue if the process fails
        /// </value>
        public static string GetUserIDFromEmail(string strEmail) {
            
            SqlConnection myConnection = new SqlConnection(DH_Constants.strConnection);
            SqlCommand myCommand  = new SqlCommand("DH_Users_GetUserIDFromEmail", myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;

            SqlParameter Email = new SqlParameter("@strEmail", SqlDbType.VarChar, 100);
            Email.Value           = strEmail;
            myCommand.Parameters.Add(Email);

            string strResult = "";
            try {
                myConnection.Open();  
                SqlDataReader myReader;  
                myReader = myCommand.ExecuteReader();  
                myReader.Read();
                strResult = myReader.GetString(0);
                myReader.Close(); 
            } catch {            
              strResult = "";
            } finally {
                if (myConnection.State == ConnectionState.Open)  
                    myCommand.Connection.Close();
            }
            return strResult;
        }

        /// <summary>
        /// Retrieves all user information FROM database for use with the public page.
        /// </summary>
        /// <param name="strUserID">The user's id</param>
        public static DH_User GetAllUserInfoFromID(string strUserID) {

            SqlConnection myConnection = new SqlConnection(DH_Constants.strConnection);
            SqlCommand myCommand  = new SqlCommand("DH_Users_GetAllUserInfoFromID", myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;

            SqlParameter UserID = new SqlParameter("@strUserID", SqlDbType.VarChar, 50);
            UserID.Value        = strUserID;
            myCommand.Parameters.Add(UserID);

            DH_User uResult = null;
            try {
                myConnection.Open();
                SqlDataReader myReader;
                myReader = myCommand.ExecuteReader();
                
                while (myReader.Read()) {

                    uResult = new DH_User(myReader.GetString(0), myReader.GetString(1), myReader.GetString(2), 
                        myReader.GetString(3), myReader.GetString(4), myReader.GetDateTime(5), myReader.GetString(6), 
                        (myReader.IsDBNull(7)) ? "" : myReader.GetString(7), 
                        (myReader.IsDBNull(8)) ? "" : myReader.GetString(8), 
                        (myReader.IsDBNull(9)) ? "" : myReader.GetString(9));
                                        if (!myReader.IsDBNull(10)) {
                                          uResult.Affiliation = (string)myReader["school_affiliation"];
                                        }
                }
                myReader.Close();
            } catch (Exception e) {
                uResult = new DH_User("-1", e.ToString());
            } finally {
                if (myConnection.State == ConnectionState.Open) {
                    myCommand.Connection.Close();;
                }
            }
            return uResult;
        }

                public static SqlDataReader GetTopUsers(int n) {
                  SqlConnection myConnection = new SqlConnection(DH_Constants.strConnection);
                  string cmd = "select top "+n.ToString()+" * from users, avg_user_rating where users.user_id = avg_user_rating.user_id order by total desc";
            SqlCommand myCommand  = new SqlCommand(cmd, myConnection);

                        SqlDataReader myReader;  
                        myConnection.Open();  
                        myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);  
            return myReader;
        }

      public static SqlDataReader GetFastUsers(int n) 
      {
          SqlConnection myConnection = new SqlConnection(DH_Constants.strConnection);
          string cmd = "select top "+n.ToString()+" f.user_id as user_id, points, first_names, last_name from users as u, fast_movers as f where u.user_id=f.user_id order by points desc";
          SqlCommand myCommand  = new SqlCommand(cmd, myConnection);

          SqlDataReader myReader;  
          myConnection.Open();  
          myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);  
          return myReader;
      }

        public static ArrayList GetLatestUsers() {
            SqlConnection myConnection = new SqlConnection(DH_Constants.strConnection);  
            SqlCommand myCommand  = new SqlCommand("DH_Users_GetLatestMembers", myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;
 
            ArrayList alResult = null;
            try {  
                myConnection.Open();  
                SqlDataReader myReader;  
                myReader = myCommand.ExecuteReader();  
                alResult = new ArrayList();
                DH_User usr;
                while (myReader.Read()) {
                    usr = new DH_User((string)myReader["user_id"], (string)myReader["email"], (string)myReader["first_names"], (string)myReader["last_name"], (DateTime)myReader["registration_date"]);

                    //check for school name
                    if (myReader["school_id"] != DBNull.Value) {
                        usr.SchoolName = DH_Schools.DH_SchoolsDB.GetSchoolAbbrFromID(Convert.ToInt32(myReader["school_id"]));
                    }
                    else {
                        usr.SchoolName = "";
                    }
                    alResult.Add(usr);
                    }
                    
                                    
                myReader.Close();  
            } catch (Exception e) {HttpContext.Current.Response.Write(e.ToString());
                alResult = null;
            } finally {  
                if (myConnection.State == ConnectionState.Open)  
                    myCommand.Connection.Close();
            }  
            return alResult;  
        }

        public static string GetUserPasswordFromID(string strUserID) {
            
            SqlConnection myConnection = new SqlConnection(DH_Constants.strConnection);  
            SqlCommand myCommand  = new SqlCommand("DH_Users_GetUserPasswordFromID", myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;

            SqlParameter UserID = new SqlParameter("@strUserID", SqlDbType.VarChar, 50);
            UserID.Value        = strUserID;
            myCommand.Parameters.Add(UserID);

            string strResult = null;
            try {  
                myConnection.Open();  
                SqlDataReader myReader;  
                myReader = myCommand.ExecuteReader();  
                myReader.Read();
                strResult = myReader.GetString(0);                
                myReader.Close();  
            } catch (Exception) {
                strResult = "-1";
            } finally {  
                if (myConnection.State == ConnectionState.Open)  
                    myCommand.Connection.Close();;  
            }  
            return strResult;  
        }  

        /// <summary>
        /// Retrieves the user FROM the database using their email email
        /// </summary>
        /// <param name="strEmail"> The user's email</param>
        public static DH_User GetUserFromEmail(string strEmail) {  

            SqlConnection myConnection = new SqlConnection(DH_Constants.strConnection);  
            SqlCommand myCommand  = new SqlCommand("DH_Users_GetUserFromEmail", myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;

            SqlParameter Email = new SqlParameter("@strEmail", SqlDbType.VarChar, 100);
            Email.Value           = strEmail;
            myCommand.Parameters.Add(Email);

            DH_User uResult = null; 
            try {  
                myConnection.Open();  
                SqlDataReader myReader;
                myReader = myCommand.ExecuteReader();  
                while (myReader.Read())  
                    uResult = new DH_User(myReader.GetString(0), myReader.GetString(1), myReader.GetString(2), myReader.GetString(3), (myReader.IsDBNull(4)) ? null : myReader.GetString(4), myReader.GetString(5), (myReader.IsDBNull(6)) ? null : myReader.GetString(6));
                myReader.Close();
            } catch (Exception e) {
                uResult = new DH_User("-1", e.ToString());
            } finally {  
                if (myConnection.State == ConnectionState.Open)  
                    myCommand.Connection.Close();;  
            }  
            return uResult;  
        }

        public static int CountUserID(string strUserID) {

            SqlConnection myConnection = new SqlConnection(DH_Constants.strConnection);
            SqlCommand myCommand  = new SqlCommand("DH_Users_CountUserID", myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;

            SqlParameter UserID = new SqlParameter("@strUserID", SqlDbType.VarChar, 50);
            UserID.Value           = strUserID;
            myCommand.Parameters.Add(UserID);

            int iCount = DH_Constants.DefaultValue;

            try 
            {  
                myConnection.Open();  
                SqlDataReader myReader;  
                myReader = myCommand.ExecuteReader();  
                myReader.Read();
                iCount = myReader.GetInt32(0);
                myReader.Close();
            } 
            catch (Exception) 
            {
                iCount = DH_Constants.GeneralException;
            } 
            finally 
            {  
                if (myConnection.State == ConnectionState.Open)  
                    myCommand.Connection.Close();;  
            }  
            return iCount;  
        }

        /// <summary>
        /// Retrieves the user with the matching email and code.
        /// </summary>
        /// <param name="strEmail"> </param>
        /// <param name="iCode"> </param>
        /// 
        /*   Used for email confirmation... if we ever want to use it

        public static DH_User GetUserFromEmailAndCode(string strEmail, int iCode) {  
            string strCmd = "SELECT user_id, email, first_names, last_name, difficulty_level, newsletter_frequency, biography FROM users WHERE email = @email and confirmation_code =" + iCode;  
            SqlConnection myConnection = new SqlConnection(DH_Constants.strConnection);  
            SqlCommand myCommand = new SqlCommand(strCmd, myConnection);  
            myCommand.Parameters.Add(new SqlParameter("@email", SqlDbType.VarChar, 100));
            myCommand.Parameters["@email"].Value = strEmail.ToLower();
            DH_User uResult = null; 
            try {  
                myConnection.Open();  
                SqlDataReader myReader;  
                myReader = myCommand.ExecuteReader();  
                while (myReader.Read())  
                    uResult = new DH_User(myReader.GetString(0), myReader.GetString(1), myReader.GetString(2), myReader.GetString(3), (myReader.IsDBNull(4)) ? null : myReader.GetString(4), myReader.GetString(5), (myReader.IsDBNull(6)) ? null : myReader.GetString(6));
                myReader.Close();  
            } catch (Exception e) {
                uResult = new DH_User("-1", e.ToString());
            } finally {  
                if (myConnection.State == ConnectionState.Open)  
                    myCommand.Connection.Close();;  
            }  
            return uResult;  
        }
        */


        /// <summary>
        /// Retrieves a user FROM the database using their ID.
        /// </summary>
        /// <param name="strUserID">The user's id</param>
        /// <value>
        /// A <c>DH_User</c> class representing the user's information
        /// </value>
        public static DH_User GetUserFromID(string strUserID) {  

            SqlConnection myConnection = new SqlConnection(DH_Constants.strConnection);  
            SqlCommand myCommand  = new SqlCommand("DH_Users_GetUserFromID", myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;

            SqlParameter UserID = new SqlParameter("@strUserID", SqlDbType.VarChar, 50);
            UserID.Value        = strUserID;
            myCommand.Parameters.Add(UserID);

            DH_User uResult = null; 
            try {  
                myConnection.Open();  
                SqlDataReader myReader;  
                myReader = myCommand.ExecuteReader();  
                while (myReader.Read())  
                    uResult = new DH_User(
                        myReader.GetString(0), 
                        myReader.GetString(1), 
                        myReader.GetString(2), 
                        myReader.GetString(3),
                        (myReader.IsDBNull(4)) ? "" : myReader.GetString(4),
                        myReader.GetString(5),
                        (myReader.IsDBNull(6)) ? "" : myReader.GetString(6),
                        (myReader.IsDBNull(7)) ? "" : myReader.GetString(7));
                myReader.Close();  
            } catch (Exception e) {
                HttpContext.Current.Response.Redirect("/new_user.aspx");
                uResult = new DH_User("-1", e.ToString());
            } finally {  
                if (myConnection.State == ConnectionState.Open)  
                    myCommand.Connection.Close();;  
            }  
            return uResult;  
        }

        /// <summary>
        /// Retrieves a user's name the database using their ID.
        /// </summary>
        /// <param name="strUserID">The user's id</param>
        /// <value>
        /// A <c>string</c> representing the user's name
        /// </value>
        public static string GetUserNameFromID(string strUserID) {  

            SqlConnection myConnection = new SqlConnection(DH_Constants.strConnection);  
            SqlCommand myCommand  = new SqlCommand("DH_Users_GetUserNameFromID", myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;

            SqlParameter UserID = new SqlParameter("@strUserID", SqlDbType.VarChar, 50);
            UserID.Value        = strUserID;
            myCommand.Parameters.Add(UserID);

            string strResult = null;
            try {  
                myConnection.Open();  
                SqlDataReader myReader;  
                myReader = myCommand.ExecuteReader();
                myReader.Read();
                strResult = String.Concat(myReader.GetString(0), " ", myReader.GetString(1));
                myReader.Close();  
            } catch (Exception) {
                strResult = "Error";
            } finally {  
                if (myConnection.State == ConnectionState.Open)  
                    myCommand.Connection.Close();
            }  
            return strResult;  
        }
        
        /// <summary>
        /// Returns the users in the DevHood system
        /// </summary>
        /// <value>
        /// An <c>ArrayList</c> of users WHERE each user is of the class <c>DH_User</c>
        /// </value>
        public static ArrayList GetUsers(int iStart, int iEnd, string strSort, string strOrder) {  
            
            SqlConnection myConnection = new SqlConnection(DH_Constants.strConnection);  
            if ("last_updated" == strSort) {
                strSort = "users.last_updated";
            }
            string strCommand = "SELECT users.user_id, email, first_names, last_name, registration_date, users.last_updated, total, school_name, school_abbr FROM users FULL OUTER JOIN avg_user_rating ON users.user_id=avg_user_rating.user_id FULL OUTER JOIN schools ON users.school_id=schools.school_id where users.user_id is not null ORDER BY " + strSort + " " + strOrder;

            SqlCommand myCommand = new SqlCommand(strCommand, myConnection);

            ArrayList alResult = new ArrayList();  
            try {  
                myConnection.Open();  
                SqlDataReader myReader;  
                myReader = myCommand.ExecuteReader();  
                for (int iIndex = 1; iIndex<=iEnd && myReader.Read(); iIndex++) {
                    if (iIndex >= iStart) {
                                          DH_User usr = new DH_User(
                            myReader.GetString(0), 
                            myReader.GetString(1), 
                            myReader.GetString(2), 
                            myReader.GetString(3),
                            myReader.GetDateTime(4),
                            (myReader.IsDBNull(5)) ? DateTime.MinValue : myReader.GetDateTime(5),
                            (myReader.IsDBNull(6)) ? 0 : myReader.GetFloat(6)
                                                  );
                                                if (myReader["school_abbr"] == DBNull.Value) {
                                                  if (myReader["school_name"] == DBNull.Value) {
                                                    usr.SchoolName = "";
                                                  }
                                                  else {
                                                    usr.SchoolName = (string)myReader["school_name"];
                                                  }
                                                }
                                                else {
                                                  usr.SchoolName = (string)myReader["school_abbr"];
                                                }
                        alResult.Add(usr); 
                    }
                }
        
                myReader.Close();  
            } catch (Exception e) {
                          HttpContext.Current.Response.Write("exceptiion here");
                alResult.Add(new DH_User("-1", e.ToString()));
            } finally {  
                if (myConnection.State == ConnectionState.Open)  
                    myCommand.Connection.Close();;  
            }  
            return alResult;  
        }
        
        public static ArrayList GetUsers(int iSchoolID, int iStart, int iEnd, string strSort, string strOrder) {  
            
            SqlConnection myConnection = new SqlConnection(DH_Constants.strConnection);  
            if ("last_updated" == strSort) {
                strSort = "users.last_updated";
            }
            string strCommand = "SELECT users.user_id, email, first_names, last_name, registration_date, users.last_updated, total, school_name, school_abbr FROM users FULL OUTER JOIN avg_user_rating ON avg_user_rating.user_id=users.user_id FULL OUTER JOIN schools ON users.school_id=schools.school_id WHERE users.user_id is not null and users.school_id='"+iSchoolID.ToString()+"' ORDER BY " + strSort + " " + strOrder;

            SqlCommand myCommand = new SqlCommand(strCommand, myConnection);

            ArrayList alResult = new ArrayList();  
            try {  
                myConnection.Open();  
                SqlDataReader myReader;  
                myReader = myCommand.ExecuteReader();  
                for (int iIndex = 1; iIndex<=iEnd && myReader.Read(); iIndex++) {
                    if (iIndex >= iStart) {
                        DH_User usr = new DH_User(
                        myReader.GetString(0), 
                            myReader.GetString(1), 
                            myReader.GetString(2), 
                            myReader.GetString(3),
                            myReader.GetDateTime(4),
                            (myReader.IsDBNull(5)) ? DateTime.MinValue : myReader.GetDateTime(5),
                            (myReader.IsDBNull(6)) ? 0 : myReader.GetFloat(6)
                                                  );
                                                if (myReader["school_abbr"] == DBNull.Value) {
                                                  if (myReader["school_name"] == DBNull.Value) {
                                                    usr.SchoolName = "";
                                                  }
                                                  else {
                                                    usr.SchoolName = (string)myReader["school_name"];
                                                  }
                                                }
                                                else {
                                                  usr.SchoolName = (string)myReader["school_abbr"];
                                                }
                                                alResult.Add(usr); 
                    }
                }
        
                myReader.Close();  
            } catch (Exception e) {
                alResult.Add(new DH_User("-1", e.ToString()));
            } finally {  
                if (myConnection.State == ConnectionState.Open)  
                    myCommand.Connection.Close();;  
            }  
            return alResult;  
        }
        
        public static int GetUserCount(string strLetter) {

            SqlConnection myConnection = new SqlConnection(DH_Constants.strConnection);
            
            string strCommand = ("all" == strLetter || null == strLetter) ? "SELECT count(1) FROM users" : "SELECT count(1) FROM users WHERE Lower(first_names) LIKE Lower(@strLetter)+'%'";

            SqlCommand myCommand = new SqlCommand(strCommand, myConnection);

            SqlParameter Letter = new SqlParameter("@strLetter", SqlDbType.VarChar, 1);
            Letter.Value        = strLetter;
            myCommand.Parameters.Add(Letter);

            int iCount;
            try {
                myConnection.Open();  
                SqlDataReader myReader;  
                myReader = myCommand.ExecuteReader();
                myReader.Read();
                iCount = myReader.GetInt32(0);
                myReader.Close();  
            } catch {  
                iCount = DH_Constants.GeneralException;
            } finally {  
                if (myConnection.State == ConnectionState.Open)  
                    myCommand.Connection.Close();;  
            }  
            return iCount;
        }
        
        public static int GetUserCount(int iSchoolID, string strLetter) {

            SqlConnection myConnection = new SqlConnection(DH_Constants.strConnection);
            
            string strCommand = ("all" == strLetter || null == strLetter) ? "SELECT count(1) FROM users where users.school_id = '"+iSchoolID.ToString()+"'" : "SELECT count(1) FROM users WHERE users.school_id = '"+iSchoolID.ToString()+"' AND Lower(first_names) LIKE Lower(@strLetter)+'%'";

            SqlCommand myCommand = new SqlCommand(strCommand, myConnection);

            SqlParameter Letter = new SqlParameter("@strLetter", SqlDbType.VarChar, 1);
            Letter.Value        = strLetter;
            myCommand.Parameters.Add(Letter);

            int iCount;
            try {
                myConnection.Open();  
                SqlDataReader myReader;  
                myReader = myCommand.ExecuteReader();
                myReader.Read();
                iCount = myReader.GetInt32(0);
                myReader.Close();  
            } catch {  
                iCount = DH_Constants.GeneralException;
            } finally {  
                if (myConnection.State == ConnectionState.Open)  
                    myCommand.Connection.Close();;  
            }  
            return iCount;
        }

                [WebMethod (Description = "Gets all users from a particular school. Results returned included experience points")]
                  public DataSet GetUsersService(int iSchoolID, string strSort, string strOrder) {
                  if (strSort == null || strSort == "") strSort = "first_names";
                  if (strOrder == null || strOrder == "") strOrder = "desc";
                  SqlConnection mySqlSrvConn = new SqlConnection(DH_Constants.strConnection);
                  string mySelectQuery = "SELECT users.user_id, email, first_names, last_name, registration_date, users.last_updated, total FROM users FULL OUTER JOIN avg_user_rating ON users.user_id=avg_user_rating.user_id WHERE users.school_id = "+iSchoolID.ToString()+" ORDER BY " + strSort + " " + strOrder;
                  SqlDataAdapter adapter = new SqlDataAdapter();
                  adapter.SelectCommand = new SqlCommand(mySelectQuery, mySqlSrvConn);
                  DataSet ds = new DataSet();
                  adapter.Fill(ds, "Table");
                  return ds;
                }
                  /// <summary>
        /// Returns the users in the DevHood system
        /// </summary>
        /// <value>
        /// An <c>ArrayList</c> of users WHERE each user is of the class <c>DH_User</c>
        /// </value>
        public static ArrayList GetUsers(int iStart, int iEnd, string strLetter, string strSort, string strOrder) {  
            SqlConnection myConnection = new SqlConnection(DH_Constants.strConnection); 
            if ("last_updated" == strSort) {
                strSort = "users.last_updated";
            }
            string strCommand = "SELECT users.user_id, email, first_names, last_name, registration_date, users.last_updated, total, school_name, school_abbr FROM users FULL OUTER JOIN avg_user_rating ON users.user_id=avg_user_rating.user_id FULL OUTER JOIN schools ON users.school_id=schools.school_id WHERE Lower(first_names) LIKE Lower(@strLetter)+'%' and users.user_id is not null ORDER BY " + strSort + " " + strOrder;

            SqlCommand myCommand = new SqlCommand(strCommand, myConnection);

            SqlParameter Letter = new SqlParameter("@strLetter", SqlDbType.VarChar, 1);
            Letter.Value        = strLetter;
            myCommand.Parameters.Add(Letter);

            ArrayList alResult = new ArrayList();  
            try {  
                myConnection.Open();  
                SqlDataReader myReader;  
                myReader = myCommand.ExecuteReader();  
                
                for (int iIndex = 1; iIndex<=iEnd && myReader.Read(); iIndex++) {
                    if (iIndex >= iStart) {
                        DH_User usr = new DH_User(
                            myReader.GetString(0), 
                            myReader.GetString(1), 
                            myReader.GetString(2), 
                            myReader.GetString(3),
                            myReader.GetDateTime(4),
                            (myReader.IsDBNull(5)) ? DateTime.MinValue : myReader.GetDateTime(5),
                            (myReader.IsDBNull(6)) ? 0 : myReader.GetFloat(6)
                                                  );
                                                 if (myReader["school_abbr"] == DBNull.Value) {
                                                  if (myReader["school_name"] == DBNull.Value) {
                                                    usr.SchoolName = "";
                                                  }
                                                  else {
                                                    usr.SchoolName = (string)myReader["school_name"];
                                                  }
                                                }
                                                else {
                                                  usr.SchoolName = (string)myReader["school_abbr"];
                                                }
                        alResult.Add(usr); 
                    }
                }
                
                myReader.Close();  
            } catch (Exception e) {
                alResult.Add(new DH_User("-1", e.ToString()));
            } finally {  
                if (myConnection.State == ConnectionState.Open)  
                    myCommand.Connection.Close();;  
            }  
            return alResult;  
        }
        
        public static ArrayList GetUsers(int iSchoolID, int iStart, int iEnd, string strLetter, string strSort, string strOrder) {  
            SqlConnection myConnection = new SqlConnection(DH_Constants.strConnection); 
            if ("last_updated" == strSort) {
                strSort = "users.last_updated";
            }
            string strCommand = "SELECT users.user_id, email, first_names, last_name, registration_date, users.last_updated, total, school_name, school_abbr FROM users FULL OUTER JOIN avg_user_rating ON users.user_id=avg_user_rating.user_id FULL OUTER JOIN schools ON users.school_id=schools.school_id WHERE users.school_id = "+iSchoolID.ToString()+" AND Lower(first_names) LIKE Lower(@strLetter)+'%' and users.user_id is not null ORDER BY " + strSort + " " + strOrder;

            SqlCommand myCommand = new SqlCommand(strCommand, myConnection);

            SqlParameter Letter = new SqlParameter("@strLetter", SqlDbType.VarChar, 1);
            Letter.Value        = strLetter;
            myCommand.Parameters.Add(Letter);

            ArrayList alResult = new ArrayList();  
            try {  
                myConnection.Open();  
                SqlDataReader myReader;  
                myReader = myCommand.ExecuteReader();  
                
                for (int iIndex = 1; iIndex<=iEnd && myReader.Read(); iIndex++) {
                    if (iIndex >= iStart) {
                        DH_User usr = new DH_User(
                            myReader.GetString(0), 
                            myReader.GetString(1), 
                            myReader.GetString(2), 
                            myReader.GetString(3),
                            myReader.GetDateTime(4),
                            (myReader.IsDBNull(5)) ? DateTime.MinValue : myReader.GetDateTime(5),
                            (myReader.IsDBNull(6)) ? 0 : myReader.GetFloat(6)
                                                  );
                                                if (myReader["school_abbr"] == DBNull.Value) {
                                                  if (myReader["school_name"] == DBNull.Value) {
                                                    usr.SchoolName = "";
                                                  }
                                                  else {
                                                    usr.SchoolName = (string)myReader["school_name"];
                                                  }
                                                }
                                                else {
                                                  usr.SchoolName = (string)myReader["school_abbr"];
                                                }
                        alResult.Add(usr); 
                    }
                }
                
                myReader.Close();  
            } catch (Exception e) {
                alResult.Add(new DH_User("-1", e.ToString()));
            } finally {  
                if (myConnection.State == ConnectionState.Open)  
                    myCommand.Connection.Close();;  
            }  
            return alResult;  
        }

        /// <summary>
        /// Returns the users in the DevHood system
        /// </summary>
        /// <value>
        /// An <c>ArrayList</c> of users WHERE each user is of the class <c>DH_User</c>
        /// </value>
        public static ArrayList GetUsers(char cLetter) {  

            SqlConnection myConnection = new SqlConnection(DH_Constants.strConnection);  
            SqlCommand myCommand  = new SqlCommand("DH_Users_GetUsersByLetter", myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;

            SqlParameter Letter = new SqlParameter("@cLetter", SqlDbType.Char, 1);
            Letter.Value        = cLetter;
            myCommand.Parameters.Add(Letter);

            ArrayList alResult = new ArrayList();  
            try {  
                myConnection.Open();  
                SqlDataReader myReader;  
                myReader = myCommand.ExecuteReader();  
                while (myReader.Read())  
                    alResult.Add(new DH_User(
                        myReader.GetString(0), 
                        myReader.GetString(1), 
                        myReader.GetString(2), 
                        myReader.GetString(3),
                        myReader.GetDateTime(4),
                        (myReader.IsDBNull(5)) ? DateTime.MinValue : myReader.GetDateTime(5),
                        DH_Update_Ratings.GetExperiencePoints(myReader.GetString(0)))
                        );  
                myReader.Close();  
            } catch (Exception e) {
                alResult.Add(new DH_User("-1", e.ToString()));
            } finally {  
                if (myConnection.State == ConnectionState.Open)  
                    myCommand.Connection.Close();;  
            }  
            return alResult;  
        }

        /// <summary>
        /// Returns an ArrayList of dh_user objects that satisfy the constraints of the input.
        /// </summary>
        /// <param name="strField"> The field for which a match is required. </param>
        /// <param name="strCriteria"> The criteria of the match: "is", "contains", "begins with", or "ends with".</param>
        /// <param name="strMatch"> The required match string. </param>
        public static ArrayList GetUsersFromQuery(string strField, string strCriteria, string strMatch) {

            string strCommand = "";

            switch (strCriteria) {

                case "contains":
                    strCommand = "SELECT user_id, email, first_names, last_name FROM users WHERE " + strField + " like '%" + strMatch + "%' and registration_approved is not null";
                    break;
                    
                case "begins with":
                    strCommand = "SELECT user_id, email, first_names, last_name FROM users WHERE " + strField + " like '" + strMatch + "%' and registration_approved is not null";
                    break;

                case "ends with":
                    strCommand = "SELECT user_id, email, first_names, last_name FROM users WHERE " + strField + " like '%" + strMatch + "' and registration_approved is not null";
                    break;

                case "is":
                default:
                    strCommand = "SELECT user_id, email, first_names, last_name FROM users WHERE " + strField + "='" + strMatch + "' and registration_approved is not null";
                    break;
            }
            
            SqlConnection myConnection = new SqlConnection(DH_Constants.strConnection);
            SqlCommand myCommand = new SqlCommand(strCommand, myConnection);

            ArrayList alResults = new ArrayList();

            try {
                myConnection.Open();
                SqlDataReader myReader;
                myReader = myCommand.ExecuteReader();
                while (myReader.Read()) {
                    alResults.Add(new DH_User( myReader.GetString(0),
                                               myReader.GetString(1),
                                               myReader.GetString(2),
                                               myReader.GetString(3)));
                }
                myReader.Close();
            }
            catch (Exception e) {
                alResults.Add(new DH_User("-1", e.ToString()));
            }
            finally {
                if (myConnection.State == ConnectionState.Open) {
                    myCommand.Connection.Close();;
                }
            }
            return alResults;
        }

        public static int CountUserFavorites(string strUserA, char chType) {

            SqlConnection myConnection = new SqlConnection(DH_Constants.strConnection);
            SqlCommand myCommand  = new SqlCommand("DH_Users_CountUserFavorites", myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;

            SqlParameter UserA = new SqlParameter("@strUserA", SqlDbType.VarChar, 4);
            UserA.Value           = strUserA;
            myCommand.Parameters.Add(UserA);

            SqlParameter Type = new SqlParameter("@chType", SqlDbType.Char, 1);
            Type.Value          = chType;
            myCommand.Parameters.Add(Type);

            SqlDataReader myReader;
            int iResult;
            try {
                myConnection.Open();  
                myReader = myCommand.ExecuteReader();
                myReader.Read();
                iResult = myReader.GetInt32(0);
                myReader.Close(); 
            } catch (Exception) {            
                iResult = DH_Constants.GeneralException;
            } finally {
                if (ConnectionState.Open == myConnection.State)  
                    myCommand.Connection.Close();;
            }
            return iResult;
        }

        public static ArrayList GetUserFavorites(string strUserA, int iMin, int iMax, char chType) {

            SqlConnection myConnection = new SqlConnection(DH_Constants.strConnection);
            SqlCommand myCommand  = new SqlCommand("DH_Users_GetUserFavorites", myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;

            SqlParameter UserA = new SqlParameter("@strUserA", SqlDbType.VarChar, 50);
            UserA.Value           = strUserA;
            myCommand.Parameters.Add(UserA);

            SqlParameter Type = new SqlParameter("@chType", SqlDbType.Char, 1);
            Type.Value          = chType;
            myCommand.Parameters.Add(Type);

            SqlDataReader myReader;
            ArrayList alFavorites = new ArrayList();
            try {
                myConnection.Open();  
                myReader = myCommand.ExecuteReader();
                for (int iIndex=1; iIndex<=iMax && myReader.Read(); iIndex++)
                    if (iIndex >= iMin)
                        alFavorites.Add(new DH_User(myReader.GetString(0), myReader.GetString(1), myReader.GetString(2), myReader.GetString(3)));
                myReader.Close(); 
            } catch (Exception e) {            
                alFavorites.Add(new DH_User("GeneralException", e.ToString(), String.Empty, String.Empty));
            } finally {
                if (ConnectionState.Open == myConnection.State)  
                    myCommand.Connection.Close();;
            }
            return alFavorites;
        }

        public static int CountUserBFavorites(string strUserB, char chType) {

            SqlConnection myConnection = new SqlConnection(DH_Constants.strConnection);
            SqlCommand myCommand  = new SqlCommand("DH_Users_CountUserBFavorites", myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;

            SqlParameter UserB = new SqlParameter("@strUserB", SqlDbType.VarChar, 50);
            UserB.Value           = strUserB;
            myCommand.Parameters.Add(UserB);

            SqlParameter Type = new SqlParameter("@chType", SqlDbType.Char, 1);
            Type.Value          = chType;
            myCommand.Parameters.Add(Type);
            
            SqlDataReader myReader;
            int iResult;
            try {
                myConnection.Open();  
                myReader = myCommand.ExecuteReader();
                myReader.Read();
                iResult = myReader.GetInt32(0);
                myReader.Close(); 
            } catch (Exception) {            
                iResult = DH_Constants.GeneralException;
            } finally {
                if (ConnectionState.Open == myConnection.State)  
                    myCommand.Connection.Close();;
            }
            return iResult;
        }

        public static ArrayList GetUserBFavorites(string strUserB, char chType) {

            SqlConnection myConnection = new SqlConnection(DH_Constants.strConnection);
            SqlCommand myCommand  = new SqlCommand("DH_Users_GetUserBFavorites", myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;

            SqlParameter UserB = new SqlParameter("@strUserB", SqlDbType.VarChar, 50);
            UserB.Value           = strUserB;
            myCommand.Parameters.Add(UserB);

            SqlParameter Type = new SqlParameter("@chType", SqlDbType.Char, 1);
            Type.Value          = chType;
            myCommand.Parameters.Add(Type);

            SqlDataReader myReader;
            ArrayList alFavorites = new ArrayList();
            try {
                myConnection.Open();  
                myReader = myCommand.ExecuteReader();
                while (myReader.Read()) {
                    alFavorites.Add(new DH_User(myReader.GetString(0), myReader.GetString(1), myReader.GetString(2), myReader.GetString(3)));
                }
                myReader.Close(); 
            } catch (Exception e) {            
                alFavorites.Add(new DH_User("GeneralException", e.ToString(), String.Empty, String.Empty));
            } finally {
                if (ConnectionState.Open == myConnection.State)  
                    myCommand.Connection.Close();;
            }
            return alFavorites;
        }

        public static ArrayList GetUserBFavorites(string strUserB, int iMin, int iMax, char chType) {

            SqlConnection myConnection = new SqlConnection(DH_Constants.strConnection);
            SqlCommand myCommand  = new SqlCommand("DH_Users_GetUserBFavorites", myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;

            SqlParameter UserB = new SqlParameter("@strUserB", SqlDbType.VarChar, 50);
            UserB.Value           = strUserB;
            myCommand.Parameters.Add(UserB);

            SqlParameter Type = new SqlParameter("@chType", SqlDbType.Char, 1);
            Type.Value          = chType;
            myCommand.Parameters.Add(Type);

            SqlDataReader myReader;
            ArrayList alFavorites = new ArrayList();
            try {
                myConnection.Open();  
                myReader = myCommand.ExecuteReader();
                for (int iIndex=1; iIndex<=iMax && myReader.Read(); iIndex++)
                    if (iIndex >= iMin)
                        alFavorites.Add(new DH_User(myReader.GetString(0), myReader.GetString(1), myReader.GetString(2), myReader.GetString(3)));
                myReader.Close(); 
            } catch (Exception e) {            
                alFavorites.Add(new DH_User("GeneralException", e.ToString(), String.Empty, String.Empty));
            } finally {
                if (ConnectionState.Open == myConnection.State)  
                    myCommand.Connection.Close();;
            }
            return alFavorites;
        }

        public static int DeleteUserFavorite(string strUserA, string strUserB) {

            SqlConnection myConnection = new SqlConnection(DH_Constants.strConnection);
            SqlCommand myCommand  = new SqlCommand("DH_Users_DeleteUserFavorite", myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;

            SqlParameter UserA = new SqlParameter("@strUserA", SqlDbType.VarChar, 50);
            UserA.Value           = strUserA;
            myCommand.Parameters.Add(UserA);

            SqlParameter UserB = new SqlParameter("@strUserB", SqlDbType.VarChar, 50);
            UserB.Value           = strUserB;
            myCommand.Parameters.Add(UserB);

            int iSuccess = DH_Constants.DefaultValue;
            try {
                myCommand.Connection.Open();
                myCommand.ExecuteNonQuery();
                iSuccess = DH_Constants.Success;
                    
            } catch (Exception) {
                iSuccess = DH_Constants.GeneralException;
            }
            return iSuccess;
        }

        public static int AddUserFavorite(string strUserA, string strUserB, char chType) {
            
            SqlConnection myConnection = new SqlConnection(DH_Constants.strConnection);
            SqlCommand myCommand  = new SqlCommand("DH_Users_AddUserFavorite", myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;

            SqlParameter UserA = new SqlParameter("@strUserA", SqlDbType.VarChar, 50);
            UserA.Value           = strUserA;
            myCommand.Parameters.Add(UserA);

            SqlParameter UserB = new SqlParameter("@strUserB", SqlDbType.VarChar, 50);
            UserB.Value           = strUserB;
            myCommand.Parameters.Add(UserB);

            SqlParameter Type = new SqlParameter("@chType", SqlDbType.Char, 1);
            Type.Value          = chType;
            myCommand.Parameters.Add(Type);

            int iSuccess = DH_Constants.DefaultValue;
            try 
            {
                myCommand.Connection.Open();
                myCommand.ExecuteNonQuery();
                iSuccess = DH_Constants.Success;
                    
            } 
            catch (SqlException e) 
            {
                if (2627 == e.Number) 
                {
                    iSuccess = DH_Constants.SqlUniqueKeyViolation;
                } 
                else 
                {
                    //HttpContext.Current.Response.Write(e.ToString());
                    iSuccess = DH_Constants.GeneralException;
                }
            } catch (Exception) {
            //} catch (Exception e) {
                //HttpContext.Current.Response.Write(e.ToString());
                iSuccess = DH_Constants.GeneralException;
            }
            return iSuccess;
        }

        public static int CountUserBookmarks(string strUserID) {

            SqlConnection myConnection = new SqlConnection(DH_Constants.strConnection);
            SqlCommand myCommand  = new SqlCommand("DH_Users_CountUserBookmarks", myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;

            SqlParameter UserID = new SqlParameter("@strUserID", SqlDbType.VarChar, 50);
            UserID.Value        = strUserID;
            myCommand.Parameters.Add(UserID);

            SqlDataReader myReader;
            int iResult;
            try {
                myConnection.Open();  
                myReader = myCommand.ExecuteReader();
                myReader.Read();
                iResult = myReader.GetInt32(0);
                myReader.Close(); 
            } catch (Exception) {            
                iResult = DH_Constants.GeneralException;
            } finally {
                if (ConnectionState.Open == myConnection.State)  
                    myCommand.Connection.Close();;
            }
            return iResult;
        }

        public static ArrayList GetUserBookmarks(string strUserID, int iMin, int iMax) {

            SqlConnection myConnection = new SqlConnection(DH_Constants.strConnection);
            SqlCommand myCommand  = new SqlCommand("DH_Users_GetUserBookmarks", myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;

            SqlParameter UserID = new SqlParameter("@strUserID", SqlDbType.VarChar, 50);
            UserID.Value        = strUserID;
            myCommand.Parameters.Add(UserID);

            SqlDataReader myReader;
            ArrayList alBookmarks = new ArrayList();
            try {
                myConnection.Open();  
                myReader = myCommand.ExecuteReader();
                for (int iIndex=1; iIndex<=iMax && myReader.Read(); iIndex++)
                    if (iIndex >= iMin)
                        alBookmarks.Add(new DH_Bookmark(myReader.GetInt32(0), myReader.GetString(1), myReader.GetString(2)));
                myReader.Close(); 
            } catch (Exception e) {            
                alBookmarks.Add(new DH_Bookmark(-1, e.ToString(), String.Empty));
            } finally {
                if (ConnectionState.Open == myConnection.State)  
                    myCommand.Connection.Close();;
            }
            return alBookmarks;
        }

        public static DH_UserCustomization GetUserCustomization(string strUserID) {

            SqlConnection myConnection = new SqlConnection(DH_Constants.strConnection);
            SqlCommand myCommand  = new SqlCommand("DH_Users_GetUserCustomization", myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;

            SqlParameter UserID = new SqlParameter("@strUserID", SqlDbType.VarChar, 50);
            UserID.Value        = strUserID;
            myCommand.Parameters.Add(UserID);

            SqlDataReader myReader;
            DH_UserCustomization ucResult = null;
            try {
                myConnection.Open();  
                myReader = myCommand.ExecuteReader();
                myReader.Read();
                ucResult = new DH_UserCustomization(("t"==myReader.GetString(0)), ("t"==myReader.GetString(1)), ("t"==myReader.GetString(2)),
                    ("t"==myReader.GetString(3)), ("t"==myReader.GetString(4)), ("t"==myReader.GetString(5)), ("t"==myReader.GetString(6)),
                    ("t"==myReader.GetString(7)), ("t"==myReader.GetString(8)), ("t"==myReader.GetString(9)), ("t"==myReader.GetString(10)),
                    ("t"==myReader.GetString(11)), ("t"==myReader.GetString(12)), ("t"==myReader.GetString(13)), ("t"==myReader.GetString(14)));
                myReader.Close(); 
            } catch (Exception e) {
                HttpContext.Current.Response.Write(e.ToString());
            } finally {
                if (ConnectionState.Open == myConnection.State)  
                    myCommand.Connection.Close();;
            }
            return ucResult;
        }

        public static int SetUserCustomization(string strUserID, bool bRating, bool bFavorites, bool bIgnored, bool bFavoriteFor, bool bMsgThread,
                bool bMsgCategory, bool bRecentNews, bool bRecentTutorials, bool bRecentPosts, bool bApprovedTutorials,
                bool bPendingTutorials, bool bBookmarks, bool bJobs, bool bSavedTutorials, bool bTools) {

            SqlConnection myConnection = new SqlConnection(DH_Constants.strConnection);
            SqlCommand myCommand  = new SqlCommand("DH_Users_SetUserCustomization", myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;

            SqlParameter UserID = new SqlParameter("@strUserID", SqlDbType.VarChar, 50);
            UserID.Value        = strUserID;
            myCommand.Parameters.Add(UserID);

            SqlParameter Rating = new SqlParameter("@bRating", SqlDbType.VarChar, 1);
            Rating.Value        = (bRating) ? 't' : 'f';
            myCommand.Parameters.Add(Rating);

            SqlParameter Favorites = new SqlParameter("@bFavorites", SqlDbType.VarChar, 1);
            Favorites.Value           = (bFavorites) ? 't' : 'f';
            myCommand.Parameters.Add(Favorites);

            SqlParameter Ignored = new SqlParameter("@bIgnored", SqlDbType.VarChar, 1);
            Ignored.Value         = (bIgnored) ? 't' : 'f';
            myCommand.Parameters.Add(Ignored);

            SqlParameter FavoriteFor = new SqlParameter("@bFavoriteFor", SqlDbType.VarChar, 1);
            FavoriteFor.Value         = (bFavoriteFor) ? 't' : 'f';
            myCommand.Parameters.Add(FavoriteFor);

            SqlParameter MsgThread = new SqlParameter("@bMsgThread", SqlDbType.VarChar, 1);
            MsgThread.Value           = (bMsgThread) ? 't' : 'f';
            myCommand.Parameters.Add(MsgThread);

            SqlParameter MsgCategory = new SqlParameter("@bMsgCategory", SqlDbType.VarChar, 1);
            MsgCategory.Value         = (bMsgCategory) ? 't' : 'f';
            myCommand.Parameters.Add(MsgCategory);
        
            SqlParameter RecentNews = new SqlParameter("@bRecentNews", SqlDbType.VarChar, 1);
            RecentNews.Value        = (bRecentNews) ? 't' : 'f';
            myCommand.Parameters.Add(RecentNews);

            SqlParameter RecentTutorials = new SqlParameter("@bRecentTutorials", SqlDbType.VarChar, 1);
            RecentTutorials.Value         = (bRecentTutorials) ? 't' : 'f';
            myCommand.Parameters.Add(RecentTutorials);

            SqlParameter RecentPosts = new SqlParameter("@bRecentPosts", SqlDbType.VarChar, 1);
            RecentPosts.Value         = (bRecentPosts) ? 't' : 'f';
            myCommand.Parameters.Add(RecentPosts);

            SqlParameter ApprovedTutorials = new SqlParameter("@bApprovedTutorials", SqlDbType.VarChar, 1);
            ApprovedTutorials.Value           = (bApprovedTutorials) ? 't' : 'f';
            myCommand.Parameters.Add(ApprovedTutorials);

            SqlParameter PendingTutorials = new SqlParameter("@bPendingTutorials", SqlDbType.VarChar, 1);
            PendingTutorials.Value          = (bPendingTutorials) ? 't' : 'f';
            myCommand.Parameters.Add(PendingTutorials);

            SqlParameter Bookmarks = new SqlParameter("@bBookmarks", SqlDbType.VarChar, 1);
            Bookmarks.Value           = (bBookmarks) ? 't' : 'f';
            myCommand.Parameters.Add(Bookmarks);

            SqlParameter Jobs = new SqlParameter("@bJobs", SqlDbType.VarChar, 1);
            Jobs.Value          = (bJobs) ? 't' : 'f';
            myCommand.Parameters.Add(Jobs);

            SqlParameter SavedTutorials = new SqlParameter("@bSavedTutorials", SqlDbType.VarChar, 1);
            SavedTutorials.Value        = (bSavedTutorials) ? 't' : 'f';
            myCommand.Parameters.Add(SavedTutorials);

            SqlParameter Tools = new SqlParameter("@bTools", SqlDbType.VarChar, 1);
            Tools.Value           = (bTools) ? 't' : 'f';
            myCommand.Parameters.Add(Tools);

            int iSuccess = DH_Constants.DefaultValue;
            try {
                myCommand.Connection.Open();
                myCommand.ExecuteNonQuery();
                iSuccess = DH_Constants.Success;
            } catch (Exception e) {
                HttpContext.Current.Response.Write(e.ToString());
                iSuccess = DH_Constants.GeneralException;
            }
            return iSuccess;
        }    

        public static int AddUserBookmark(string strUserID, string strDescription, string strUrl) {

            SqlConnection myConnection = new SqlConnection(DH_Constants.strConnection);
            SqlCommand myCommand  = new SqlCommand("DH_Users_AddUserBookmark", myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;

            SqlParameter UserID = new SqlParameter("@strUserID", SqlDbType.VarChar, 50);
            UserID.Value        = strUserID;
            myCommand.Parameters.Add(UserID);

            SqlParameter Description = new SqlParameter("@strDescription", SqlDbType.VarChar, 30);
            Description.Value         = strDescription;
            myCommand.Parameters.Add(Description);

            SqlParameter Url = new SqlParameter("@strUrl", SqlDbType.VarChar, 200);
            Url.Value         = strUrl;
            myCommand.Parameters.Add(Url);
            
            int iSuccess = DH_Constants.DefaultValue;
            try {
                myCommand.Connection.Open();
                myCommand.ExecuteNonQuery();
                iSuccess = DH_Constants.Success;
                    
            } catch (SqlException e) {
                if (2627 == e.Number) {
                    iSuccess = DH_Constants.SqlUniqueKeyViolation;
                } else {
                    iSuccess = DH_Constants.GeneralException;
                }
            } catch (Exception) {
                iSuccess = DH_Constants.GeneralException;
            }
            return iSuccess;
        }

        public static int DeleteUserBookmark(int iBookmarkID) {

            SqlConnection myConnection = new SqlConnection(DH_Constants.strConnection);
            SqlCommand myCommand  = new SqlCommand("DH_Users_DeleteUserBookmark", myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;

            SqlParameter BookmarkID = new SqlParameter("@iBookmarkID", SqlDbType.Int, 4);
            BookmarkID.Value        = iBookmarkID;
            myCommand.Parameters.Add(BookmarkID);

            int iSuccess = DH_Constants.DefaultValue;
            try {
                myCommand.Connection.Open();
                myCommand.ExecuteNonQuery();
                iSuccess = DH_Constants.Success;
            } catch (Exception) {
                iSuccess = DH_Constants.GeneralException;
            }
            return iSuccess;
        }

        /// <summary>
        /// Called during registration to INSERT a new user into the database.
        /// <return>
        /// If the email already exists in the database it will return SqlException.
        /// If an exception occurs it will return GeneralException.
        /// Otherwise it will return the randomly generated confirmation code.
        /// </return>
        /// </summary>
        /// <param name="strEmail">The user's email </param>
        /// <param name="strUserPassword"> The user's password</param>
        /// <param name="strFirstNames"> The user's firstname</param>
        /// <param name="strLastName"> The user's lastname</param>
        /// <param name="strDifficultyLevel"> The user's difficulty level (can be null)</param>
        /// <param name="strNewsletterFrequency">The user's newsletterfrequency </param>
        /// <param name="strBiography"> Optional user's biography</param>
        public static int InsertUser(string strEmail, string strUserPassword, string strFirstNames, string strLastName, string strNewsletterFrequency, string strIpAddress) {
        
            SqlConnection myConnection = new SqlConnection(DH_Constants.strConnection);
            SqlCommand myCommand  = new SqlCommand("DH_Users_InsertUser", myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;

            int iCode = (new Random()).Next(Int32.MaxValue);
            SqlParameter Code = new SqlParameter("@iCode", SqlDbType.Int, 4);
            Code.Value          = iCode;
            myCommand.Parameters.Add(Code);

            SqlParameter FirstNames = new SqlParameter("@strFirstNames", SqlDbType.VarChar, 100);
            FirstNames.Value        = strFirstNames;
            myCommand.Parameters.Add(FirstNames);

            SqlParameter LastName = new SqlParameter("@strLastName", SqlDbType.VarChar, 50);
            LastName.Value          = strLastName;
            myCommand.Parameters.Add(LastName);

            SqlParameter Email = new SqlParameter("@strEmail", SqlDbType.VarChar, 100);
            Email.Value           = strEmail;
            myCommand.Parameters.Add(Email);

            SqlParameter UserPassword = new SqlParameter("@strUserPassword", SqlDbType.VarChar, 12);
            UserPassword.Value          = strUserPassword;
            myCommand.Parameters.Add(UserPassword);

            SqlParameter NewsletterFrequency = new SqlParameter("@strNewsletterFrequency", SqlDbType.VarChar, 20);
            NewsletterFrequency.Value         = strNewsletterFrequency;
            myCommand.Parameters.Add(NewsletterFrequency);

            SqlParameter IpAddress = new SqlParameter("@strIpAddress", SqlDbType.Char, 15);
            IpAddress.Value = strIpAddress;
            myCommand.Parameters.Add(IpAddress);
            
            int iSuccess = DH_Constants.DefaultValue;

            try {
                myCommand.Connection.Open();
                myCommand.ExecuteNonQuery();
                iSuccess = iCode;
            } catch (SqlException) {
                iSuccess = DH_Constants.DatabaseException;
            } catch (Exception) {
                iSuccess = DH_Constants.GeneralException;
            } finally {
                if (myConnection.State == ConnectionState.Open)
                    myCommand.Connection.Close();;
            }
            return iSuccess;
        }

        public static int UploadPhoto(string strUserID, string strPhotoFileName) {
            
            SqlConnection myConnection = new SqlConnection(DH_Constants.strConnection);
            SqlCommand myCommand  = new SqlCommand("DH_Users_UploadPhoto", myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;

            SqlParameter UserID = new SqlParameter("@strUserID", SqlDbType.VarChar, 50);
            UserID.Value        = strUserID;
            myCommand.Parameters.Add(UserID);

            SqlParameter PhotoFileName = new SqlParameter("@strPhotoFileName", SqlDbType.VarChar, 50);
            PhotoFileName.Value           = strPhotoFileName;
            myCommand.Parameters.Add(PhotoFileName);

            int iSuccess = DH_Constants.DefaultValue;
            try {
                myCommand.Connection.Open();
                myCommand.ExecuteNonQuery();
                iSuccess = DH_Constants.Success;
            } catch (Exception e) {
                HttpContext.Current.Response.Write(e.ToString());
                iSuccess = DH_Constants.GeneralException;
            }
            return iSuccess;
        }    
        
        public static int UpdateSchoolInfo(string strUserID, int iSchoolID, string strAffiliation, int iMajorID, string strOtherMajor) {
            SqlConnection myConnection = new SqlConnection(DH_Constants.strConnection);
            SqlCommand myCommand  = new SqlCommand("DH_Users_UpdateSchoolInfo", myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;
    
            SqlParameter UserID = new SqlParameter("@strUserID", SqlDbType.VarChar, 50);
            UserID.Value          = strUserID;
            myCommand.Parameters.Add(UserID);
            
            SqlParameter SchoolID = new SqlParameter("@iSchoolID", SqlDbType.Int, 4);
            SchoolID.Value          = iSchoolID;
            myCommand.Parameters.Add(SchoolID);

            SqlParameter Affiliation = new SqlParameter("@strAffiliation", SqlDbType.VarChar, 50);
            Affiliation.Value         = strAffiliation;
            myCommand.Parameters.Add(Affiliation);

            SqlParameter MajorID = new SqlParameter("@iMajorID", SqlDbType.Int, 4);
            if (iMajorID == -1) {
                MajorID.Value    = DBNull.Value;
            }
            else {
                    MajorID.Value   = iMajorID;
            }
            myCommand.Parameters.Add(MajorID);

            SqlParameter OtherMajor = new SqlParameter("@strOtherMajor", SqlDbType.VarChar, 50);
            if (strOtherMajor == String.Empty) {
                OtherMajor.Value = DBNull.Value;
            }
            else {
                OtherMajor.Value = strOtherMajor;
            }
            myCommand.Parameters.Add(OtherMajor);

            
            int iSuccess = DH_Constants.DefaultValue;
            try {
                myConnection.Open(); 
                myCommand.ExecuteNonQuery();
                iSuccess = DH_Constants.Success;
            } 
            catch (SqlException) {
                iSuccess = DH_Constants.DatabaseException;
            }
            catch (Exception) {
                iSuccess = DH_Constants.GeneralException;
            }
            finally {
                if (myConnection.State == ConnectionState.Open)
                    myConnection.Close();
            }
            return iSuccess;
        }

        public static int CreateUser(string strUserID, string strEmail, string strFirstNames, string strLastName, string strDifficultyLevel, string strNewsletterFrequency, string strBiography, string strUrl, string strIpAddress) 
        {

            SqlConnection myConnection = new SqlConnection(DH_Constants.strConnection);
            SqlCommand myCommand  = new SqlCommand("DH_Users_CreateUser", myConnection);  
            myCommand.CommandType = CommandType.StoredProcedure;

            SqlParameter UserID = new SqlParameter("@strUserID", SqlDbType.VarChar, 50);
            UserID.Value        = strUserID;
            myCommand.Parameters.Add(UserID);

            SqlParameter FirstNames = new SqlParameter("@strFirstNames", SqlDbType.VarChar, 100);
            FirstNames.Value        = strFirstNames;
            myCommand.Parameters.Add(FirstNames);

            SqlParameter LastName = new SqlParameter("@strLastName", SqlDbType.VarChar, 50);
            LastName.Value          = strLastName;
            myCommand.Parameters.Add(LastName);

            SqlParameter Email = new SqlParameter("@strEmail", SqlDbType.VarChar, 100);
            Email.Value           = strEmail;
            myCommand.Parameters.Add(Email);

            SqlParameter NewsletterFrequency = new SqlParameter("@strNewsletterFrequency", SqlDbType.VarChar, 20);
            NewsletterFrequency.Value         = strNewsletterFrequency;
            myCommand.Parameters.Add(NewsletterFrequency);

            SqlParameter DifficultyLevel = new SqlParameter("@strDifficulty", SqlDbType.VarChar, 20);
            DifficultyLevel.Value         = strDifficultyLevel;
            myCommand.Parameters.Add(DifficultyLevel);
            
            SqlParameter Biography = new SqlParameter("@strBiography", SqlDbType.VarChar, 8000);
            Biography.Value           = strBiography;
            myCommand.Parameters.Add(Biography);

            SqlParameter Url = new SqlParameter("@strUrl", SqlDbType.VarChar, 100);
            Url.Value         = strUrl;
            myCommand.Parameters.Add(Url);

            SqlParameter IpAddress = new SqlParameter("@strIpAddress", SqlDbType.Char, 15);
            IpAddress.Value         = strIpAddress;
            myCommand.Parameters.Add(IpAddress);

            SqlCommand myCommand1  = new SqlCommand("DH_Users_UpdateLastUpdated", myConnection);
            myCommand1.CommandType = CommandType.StoredProcedure;

            SqlParameter UserID1 = new SqlParameter("@strUserID", SqlDbType.VarChar, 50);
            UserID1.Value        = strUserID;
            myCommand1.Parameters.Add(UserID1);

            //SqlTransaction myTrans = null;

            int iSuccess = DH_Constants.DefaultValue;
            try 
            {
                myConnection.Open(); 
                //myTrans = myConnection.BeginTransaction();
                //myCommand.Transaction = myTrans;
                //myCommand1.Transaction = myTrans;

                //myCommand.Connection.Open();
                myCommand.ExecuteNonQuery();
                myCommand1.ExecuteNonQuery();
                //myTrans.Commit();
                iSuccess = DH_Constants.Success;
            } 
            catch (SqlException) 
            {
                iSuccess = DH_Constants.DatabaseException;
                //myTrans.Rollback();
            }
            catch (Exception) 
            {
                //myTrans.Rollback();
                iSuccess = DH_Constants.GeneralException;
            }
            finally 
            {
                if (myConnection.State == ConnectionState.Open)
                    myConnection.Close();
            }
            return iSuccess;
        }
        
        /// <summary>
        /// Called when the User wants to change their details
        /// Returns the string true if done.
        /// </summary>
        /// <param name="strUserID"> </param>
        /// <param name="strEmail"> </param>
        /// <param name="strUserPassword"> </param>
        /// <param name="strFirstNames"> </param>
        /// <param name="strLastName"> </param>
        /// <param name="strDifficultyLevel"> </param>
        /// <param name="strNewsletterFrequency"> </param>
        /// <param name="strBiography"> </param>
        /// <param name="strUrl"> </param>
        public static int UpdateUser(string strUserID, string strEmail, string strUserPassword, string strFirstNames, string strLastName, string strDifficultyLevel, string strNewsletterFrequency, string strBiography, string strUrl) {

            SqlConnection myConnection = new SqlConnection(DH_Constants.strConnection);
            SqlCommand myCommand  = new SqlCommand("DH_Users_UpdateUser", myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;

            SqlParameter UserID = new SqlParameter("@strUserID", SqlDbType.VarChar, 50);
            UserID.Value        = strUserID;
            myCommand.Parameters.Add(UserID);

            SqlParameter FirstNames = new SqlParameter("@strFirstNames", SqlDbType.VarChar, 100);
            FirstNames.Value        = strFirstNames;
            myCommand.Parameters.Add(FirstNames);

            SqlParameter LastName = new SqlParameter("@strLastName", SqlDbType.VarChar, 50);
            LastName.Value          = strLastName;
            myCommand.Parameters.Add(LastName);

            SqlParameter Email = new SqlParameter("@strEmail", SqlDbType.VarChar, 100);
            Email.Value           = strEmail;
            myCommand.Parameters.Add(Email);

            SqlParameter UserPassword = new SqlParameter("@strUserPassword", SqlDbType.VarChar, 12);
            UserPassword.Value          = strUserPassword;
            myCommand.Parameters.Add(UserPassword);

            SqlParameter NewsletterFrequency = new SqlParameter("@strNewsletterFrequency", SqlDbType.VarChar, 20);
            NewsletterFrequency.Value         = strNewsletterFrequency;
            myCommand.Parameters.Add(NewsletterFrequency);

            SqlParameter DifficultyLevel = new SqlParameter("@strDifficulty", SqlDbType.VarChar, 20);
            DifficultyLevel.Value         = strDifficultyLevel;
            myCommand.Parameters.Add(DifficultyLevel);
            
            SqlParameter Biography = new SqlParameter("@strBiography", SqlDbType.VarChar, 8000);
            Biography.Value           = strBiography;
            myCommand.Parameters.Add(Biography);

            SqlParameter Url = new SqlParameter("@strUrl", SqlDbType.VarChar, 100);
            Url.Value         = strUrl;
            myCommand.Parameters.Add(Url);

            SqlCommand myCommand1  = new SqlCommand("DH_Users_UpdateLastUpdated", myConnection);
            myCommand1.CommandType = CommandType.StoredProcedure;

            SqlParameter UserID1 = new SqlParameter("@strUserID", SqlDbType.VarChar, 50);
            UserID1.Value        = strUserID;
            myCommand1.Parameters.Add(UserID1);

            SqlTransaction myTrans = null;
            int iSuccess = DH_Constants.DefaultValue;
            try {
                myConnection.Open(); 
                myTrans = myConnection.BeginTransaction();
                myCommand.Transaction = myTrans;
                myCommand1.Transaction = myTrans;

                //myCommand.Connection.Open();
                myCommand.ExecuteNonQuery();
                myCommand1.ExecuteNonQuery();
                myTrans.Commit();
                iSuccess = DH_Constants.Success;
            } 
            catch (SqlException) {
                iSuccess = DH_Constants.DatabaseException;
                myTrans.Rollback();
            }
            catch (Exception) {
                myTrans.Rollback();
                iSuccess = DH_Constants.GeneralException;
            }
            finally {
                if (myConnection.State == ConnectionState.Open)
                    myConnection.Close();
            }
            return iSuccess;
        }

        public static int UpdateUser(string strUserID, string strEmail, string strFirstNames, string strLastName, string strDifficultyLevel, string strNewsletterFrequency, string strBiography, string strUrl) {

            SqlConnection myConnection = new SqlConnection(DH_Constants.strConnection);
            SqlCommand myCommand  = new SqlCommand("DH_Users_UpdateUser2", myConnection);  
            myCommand.CommandType = CommandType.StoredProcedure;

            SqlParameter UserID = new SqlParameter("@strUserID", SqlDbType.VarChar, 50);
            UserID.Value        = strUserID;
            myCommand.Parameters.Add(UserID);

            SqlParameter FirstNames = new SqlParameter("@strFirstNames", SqlDbType.VarChar, 100);
            FirstNames.Value        = strFirstNames;
            myCommand.Parameters.Add(FirstNames);

            SqlParameter LastName = new SqlParameter("@strLastName", SqlDbType.VarChar, 50);
            LastName.Value          = strLastName;
            myCommand.Parameters.Add(LastName);

            SqlParameter Email = new SqlParameter("@strEmail", SqlDbType.VarChar, 100);
            Email.Value           = strEmail;
            myCommand.Parameters.Add(Email);

            SqlParameter NewsletterFrequency = new SqlParameter("@strNewsletterFrequency", SqlDbType.VarChar, 20);
            NewsletterFrequency.Value         = strNewsletterFrequency;
            myCommand.Parameters.Add(NewsletterFrequency);

            SqlParameter DifficultyLevel = new SqlParameter("@strDifficulty", SqlDbType.VarChar, 20);
            DifficultyLevel.Value         = strDifficultyLevel;
            myCommand.Parameters.Add(DifficultyLevel);
            
            SqlParameter Biography = new SqlParameter("@strBiography", SqlDbType.VarChar, 8000);
            Biography.Value           = strBiography;
            myCommand.Parameters.Add(Biography);

            SqlParameter Url = new SqlParameter("@strUrl", SqlDbType.VarChar, 100);
            Url.Value         = strUrl;
            myCommand.Parameters.Add(Url);

            SqlCommand myCommand1  = new SqlCommand("DH_Users_UpdateLastUpdated", myConnection);
            myCommand1.CommandType = CommandType.StoredProcedure;

            SqlParameter UserID1 = new SqlParameter("@strUserID", SqlDbType.VarChar, 50);
            UserID1.Value        = strUserID;
            myCommand1.Parameters.Add(UserID1);

            SqlTransaction myTrans = null;

            int iSuccess = DH_Constants.DefaultValue;
            try {
                myConnection.Open(); 
                myTrans = myConnection.BeginTransaction();
                myCommand.Transaction = myTrans;
                myCommand1.Transaction = myTrans;

                //myCommand.Connection.Open();
                myCommand.ExecuteNonQuery();
                myCommand1.ExecuteNonQuery();
                myTrans.Commit();
                iSuccess = DH_Constants.Success;
            } 
            catch (SqlException) {
                iSuccess = DH_Constants.DatabaseException;
                myTrans.Rollback();
            }
            catch (Exception) {
                //HttpContext.Current.Response.Write(e.ToString());
                myTrans.Rollback();
                iSuccess = DH_Constants.GeneralException;
            }
            finally {
                if (myConnection.State == ConnectionState.Open)
                    myConnection.Close();
            }
            return iSuccess;
        }

    /// <summary>
    /// 
    /// </summary>
    /// <param name="strEmail"> </param>
    /// <param name="iConfirmationCode"> </param>
    /// 
    /* we're not using this right now

        public static int ConfirmUser(string strEmail, int iConfirmationCode)
        {
            
            string strCmd = "UPDATE users SET registration_confirmed = getdate(), confirmation_code = null WHERE registration_confirmed is null and email=@email and confirmation_code = " + iConfirmationCode;  
            SqlConnection myConnection = new SqlConnection(DH_Constants.strConnection);  
            SqlCommand myCommand = new SqlCommand(strCmd, myConnection);
            myCommand.Parameters.Add(new SqlParameter("@email", SqlDbType.VarChar, 100));
            myCommand.Parameters["@email"].Value = strEmail;

            int iRetVal = DH_Constants.DefaultValue;
            try {  
                myCommand.Connection.Open();
                int iAffectedRows = myCommand.ExecuteNonQuery();
                if (iAffectedRows == 1) {
                    iRetVal = DH_Constants.Success;
                }
            } catch (Exception e) {
                iRetVal = DH_Constants.GeneralException;
            } finally {  
                if (myConnection.State == ConnectionState.Open)  
                    myCommand.Connection.Close();;  
            }  
            return iRetVal; 
        }
        */

        /// <summary>
        /// Update's a user's password
        /// </summary>
        /// <param name="strUserID">The User_ID associated with the user.</param>
        /// <param name="strNewPassword">The user's new password</param>
        /// <value>
        /// A <c>string</c> representing success or an exception if the method fails
        /// </value>
        public static string UpdateUserPassword(string strUserID, string strNewPassword) {
            return null;
        }

        /// <summary>
        /// Delete's a user FROM DevHood
        /// </summary>
        /// <param name="strUserID">The User_ID associated with the user.</param>
        /// <value>
        /// A <c>string</c> representing success or an exception if the method fails
        /// </value>
        public static string DeleteUser(string strUserID) {
            return null;
        }

        /// <summary>
        /// Black list's a user FROM DevHood
        /// </summary>
        /// <param name="strUserID">The User_ID associated with the user.</param>
        /// <value>
        /// An <c>int</c> representing success or an exception if the method fails
        /// </value>
        public static int BlackListUser(string strUserID) {
            
            SqlConnection myConnection = new SqlConnection(DH_Constants.strConnection);
            SqlCommand myCommand  = new SqlCommand("DH_Users_BlackListUser", myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;

            SqlParameter UserID = new SqlParameter("@strUserID", SqlDbType.VarChar, 50);
            UserID.Value        = strUserID;
            myCommand.Parameters.Add(UserID);

            int iSuccess = DH_Constants.Success;

            try {
                myCommand.Connection.Open();
                int iAffectedRows = myCommand.ExecuteNonQuery();    
                if (0 == iAffectedRows) {
                    iSuccess = DH_Constants.InvalidOpException;
                }
            } catch {
                iSuccess = DH_Constants.GeneralException;
            } finally {
                if (myConnection.State == ConnectionState.Open)
                    myCommand.Connection.Close();;
            }
            return iSuccess;
        }

        /// <summary>
        /// Called to resend a confirmation email to a user specified by strEmail.  
        /// If the user does not exist, returns "false".
        /// If the user is already confirmed, returns "exists".
        /// If an exception is thrown, returns the exception string.
        /// Otherwise an email is sent to the user and returns true.
        /// </summary>
        /// <param name="strEmail"> </param>
        /// 
        /*  not using this rigth now

        public static string ResendConfirmationEmail(string strEmail) {
            string strRetVal = "false";
            string strCmd = "SELECT user_id, email, user_password, first_names, last_name, registration_confirmed FROM users WHERE email = @email";  
            SqlConnection myConnection = new SqlConnection(DH_Constants.strConnection);  
            SqlCommand myCommand = new SqlCommand(strCmd, myConnection);  
            myCommand.Parameters.Add(new SqlParameter("@email", SqlDbType.VarChar, 100));
            myCommand.Parameters["@email"].Value = strEmail;
            try {
                myConnection.Open();  
                SqlDataReader myReader;  
                myReader = myCommand.ExecuteReader();
                if (myReader.HasMoreResults)
                {
                    myReader.Read();
                    if (myReader.IsDBNull(5))
                    {
                        SmtpMail.Send("DevHood <emitix@mit.edu>", strEmail, "Your Citizenship Confirmation",
                        "Dear " +  myReader.GetString(3) + " " + myReader.GetString(4) + "\n \n This is an aututomatic reply to your password request.  \n \n Your password: " + 
                        myReader.GetString(2) + " \n \n Thank you for using DevHood.  \n \n DevHood High Council ");
                        strRetVal = "true";
                    }
                    else 
                    {
                        strRetVal = "confirmed";
                    }
                }

            }
            catch (Exception e){
                strRetVal = e.ToString();
            } 
            finally {  
                if (myConnection.State == ConnectionState.Open)  
                    myCommand.Connection.Close();;  
            }  
            return strRetVal;
        }
        */


        /// <summary>
        /// Sends the user their password if they forget it via email
        /// Used by forget_password.aspx
        /// </summary>
        /// <param name="strEmail"> The users's email address</param>
        public static int ForgotPassword(string strEmail) {
            
            SqlConnection myConnection = new SqlConnection(DH_Constants.strConnection);  
            SqlCommand myCommand  = new SqlCommand("DH_Users_ForgotPassword", myConnection);  
            myCommand.CommandType = CommandType.StoredProcedure;

            SqlParameter Email = new SqlParameter("@strEmail", SqlDbType.VarChar, 100);
            Email.Value           = strEmail;
            myCommand.Parameters.Add(Email);

            int iSuccess = DH_Constants.DefaultValue;
            try {
                myConnection.Open();  
                SqlDataReader myReader;  
                myReader = myCommand.ExecuteReader();
                while (myReader.Read()) {
                    SmtpMail.Send("DevHood <emitix@mit.edu>", strEmail, "Your Password Request",
                        "Dear " +  myReader.GetString(3) + " " + myReader.GetString(4) + "\n \n This is an aututomatic reply to your password request.  \n \n Your password: " + 
                        myReader.GetString(2) + " \n \n Thank you for using DevHood.  \n \n DevHood");
                    iSuccess = DH_Constants.Success;
                }
            } catch (Exception){
                iSuccess = DH_Constants.GeneralException;
            } finally {  
                if (myConnection.State == ConnectionState.Open)  
                    myCommand.Connection.Close();;  
            }  
            return iSuccess;
        }

                [WebMethod (Description = "Emails all users registered under a specific school")]
                  public void EmailSchoolUsers(int iSchoolID, string strFrom, string strSubject, string strBody) {
                }
                  
        [WebMethod (Description = "Retrieves our user database.") ]
          public DataSet GetUsers(int iSchoolID, string strSort, string strOrder) {  

                  if (strSort == null || strSort == String.Empty) {
                    strSort = "school_name";
                  }
                  if (strOrder == null || strOrder == String.Empty) {
                    strOrder = "asc";
                  }
          SqlConnection myConnection = new SqlConnection(DH_Constants.strConnection);  
          if ("last_updated" == strSort) {
            strSort = "users.last_updated";
          }
          string strCommand;
          if (iSchoolID == -1) {
            strCommand = "SELECT users.user_id, email, first_names, last_name, school_name, registration_date, users.last_updated FROM users, schools where users.school_id = schools.school_id ORDER BY " + strSort + " " + strOrder;
            }
            else {
              strCommand = "SELECT users.user_id, email, first_names, last_name, school_name, registration_date, users.last_updated FROM users, schools where users.school_id = schools.school_id and users.school_id='"+iSchoolID.ToString()+"' ORDER BY " + strSort + " " + strOrder;
            }
              
            SqlDataAdapter myAdapter = new SqlDataAdapter();
            myAdapter.SelectCommand = new SqlCommand(strCommand, myConnection);
            DataSet ds = new DataSet();

            try {  
                myConnection.Open();  
                myAdapter.Fill(ds);
            } catch (Exception) {
                    return null;
            } finally {  
                if (myConnection.State == ConnectionState.Open)  
                    myConnection.Close();
            }  
            return ds;
        }
    }
    
    /// <summary>
    /// A class representing a User on the Devhood System.
    /// </summary>
    public class DH_User {
        
        private string strUserID;
        private float fExperiencePoints;
        private string strEmail;
        private string strFirstNames;
        private string strLastName;
        private string strUrl;
        private string strDifficultyLevel, strNewsletterFrequency, strBiography;
                private string strAffiliation;
        private double dUserRating;
        private DateTime dtRegistrationDate, dtRegistrationConfirmed, dtRegistrationApproved, dtLastUpdated;
        
        private string strSchoolName;
        

        public DH_User(string strUserID, string strEmail, string strFirstNames, string strLastName, DateTime dtRegistrationDate) { 
            this.strUserID       = strUserID;
            this.strEmail       = strEmail;
            this.strFirstNames = strFirstNames;
            this.strLastName   = strLastName;
            this.dtRegistrationDate = dtRegistrationDate;
        }
        /// <summary>
        /// Initializes a new instance of the <c>DH_User</c> class. 
        /// </summary>
        /// <param name="strUserID"> </param>
        /// <param name="strEmail"> </param>
        /// <param name="strFirstNames"> </param>
        /// <param name="strLastName"> </param>
        /// <param name="strDifficultyLevel"> </param>
        /// <param name="strNewsletterFrequency"> </param>
        /// <param name="strBiography"></param>

        public DH_User(string strUserID, string strEmail, string strFirstNames, string strLastName, string strDifficultyLevel, string strNewsletterFrequency, string strBiography) { 
            this.strUserID       = strUserID;
            this.strEmail       = strEmail;
            this.strFirstNames = strFirstNames;
            this.strLastName   = strLastName;
            this.strDifficultyLevel = strDifficultyLevel;
            this.strNewsletterFrequency = strNewsletterFrequency;
            this.strBiography = strBiography;
        }

        public DH_User(string strUserID, string strEmail, string strFirstNames, string strLastName, DateTime dtRegistrationDate, DateTime dtLastUpdated, float fExperiencePoints) {
            this.strUserID       = strUserID;
            this.strEmail       = strEmail;
            this.strFirstNames = strFirstNames;
            this.strLastName   = strLastName;
            this.dtRegistrationDate = dtRegistrationDate;
            this.dtLastUpdated = dtLastUpdated;
            this.fExperiencePoints = fExperiencePoints;
        }
        
        public DH_User(string strUserID, string strEmail, string strFirstNames, string strLastName, string strDifficultyLevel, string strNewsletterFrequency, string strBiography, string strUrl) { 
            this.strUserID       = strUserID;
            this.strEmail       = strEmail;
            this.strFirstNames = strFirstNames;
            this.strLastName   = strLastName;
            this.strDifficultyLevel = strDifficultyLevel;
            this.strNewsletterFrequency = strNewsletterFrequency;
            this.strBiography = strBiography;
            this.strUrl          = strUrl;
        }

        public DH_User(string strUserID, string strEmail, string strFirstNames, string strLastName) { 
            this.strUserID       = strUserID;
            this.strEmail       = strEmail;
            this.strFirstNames = strFirstNames;
            this.strLastName   = strLastName;
            this.strDifficultyLevel = null;
            this.strNewsletterFrequency = "none";
            this.strBiography = null;
        }

        public DH_User(string strUserID, string strEmail, string strFirstNames, string strLastName, DateTime dtRegistrationDate, string strDifficultyLevel) { 
            this.strUserID       = strUserID;
            this.strEmail       = strEmail;
            this.strFirstNames = strFirstNames;
            this.strLastName   = strLastName;
            this.dtRegistrationDate = dtRegistrationDate;
            this.strDifficultyLevel = strDifficultyLevel;
        }

        public DH_User(string strUserID, string strEmail, string strPassword, string strFirstNames, string strLastName, DateTime dtRegistrationDate, string strNewsletter, string strDifficultyLevel, string strBio, string strUrl) {
            this.strUserID       = strUserID;
            this.strEmail       = strEmail;
            this.strFirstNames = strFirstNames;
            this.strLastName   = strLastName;
            this.dtRegistrationDate = dtRegistrationDate;
            this.strDifficultyLevel = strDifficultyLevel;
            this.strBiography  = strBio;
            this.strUrl = strUrl;

        }

        /// <summary>
        /// Initializes a new instance of the <c>DH_User</c> class.
        /// </summary>
        /// <param name="strUserID">The User_ID associated with the user.</param>
        /// <param name="strEmail">The Email Address of the user.</param>
        public DH_User(string strUserID, string strEmail) { 
            this.strUserID  = strUserID;
            this.strEmail = strEmail;
        }
        
        /// <summary>
        /// Gets the User ID of the <c>DH_User</c> object.
        /// </summary>
        /// <value>
        /// A <c>int</c> representing a User ID.
        /// </value>
        public string ID {
            get {
                return strUserID;
            }
        }

        /// <summary>
        /// Gets the Email Address of the <c>DH_User</c> object.
        /// </summary>
        /// <value>
        /// A <c>string</c> representing an Email Address.
        /// </value>
        public string Email {
            get {
              return strEmail;
            }
        }

        /// <summary>
        /// Gets the First Names of the <c>DH_User</c> object.
        /// </summary>
        /// <value>
        /// A <c>string</c> representing First Names.
        /// </value>
        public string FirstNames {
            get {
                return strFirstNames;
            }
        }

        /// <summary>
        /// Gets the Last Name of the <c>DH_User</c> object.
        /// </summary>
        /// <value>
        /// A <c>string</c> representing a Last Name.
        /// </value>
        public string LastName {
            get {
                return strLastName;
            }
        }

                public string FullName {
                  get { return FirstNames + " " + LastName; }
                }
                
        public double UserRating {
            get {
                return dUserRating;
            }
        }

        public string DifficultyLevel {
            get {
                return strDifficultyLevel;
            }
        }

        public string NewsLetterFrequency {
            get {
                return strNewsletterFrequency;
            }
        }

        public string Url {
            get {
                return strUrl;
            }
        }

        public string Biography 
        {
            get 
            {
            return strBiography;
            }
        }

        public DateTime RegistrationDate {
            get {
                return dtRegistrationDate;
            }
        }

        public DateTime RegistrationApproved {
            get {
                return dtRegistrationApproved;
            }
        }
        
        public DateTime RegistrationConfirmed {
            get {
                return dtRegistrationConfirmed;
            }
        }

        public DateTime LastUpdated {
            get {
                return dtLastUpdated;
            }
        }

        public float ExperiencePoints {
            get {
                return fExperiencePoints;
            }
        }
    
        public string SchoolName {
            get { return strSchoolName; }
            set { strSchoolName = value; }
        }

                public string Affiliation {
                  get {
                    if (strAffiliation == null) {
                      return String.Empty;
                    }
                    return strAffiliation;
                  }
                  set {strAffiliation = value;}
                }
    }

    public class DH_Bookmark {
        private int iBookmarkID;
        private string strDescription, strUrl;

        public DH_Bookmark(int iBookmarkID, string strDescription, string strUrl) {
            this.iBookmarkID    = iBookmarkID;
            this.strDescription = strDescription;
            this.strUrl            = strUrl;
        }

        public int BookmarkID {
            get {
                return iBookmarkID;
            }
        }

        public string Description {
            get {
                return strDescription;
            }
        }

        public string Url {
            get {
                return strUrl;
            }
        }
    }

    public class DH_UserCustomization {
        private bool bRating = false, bFavorites, bIgnored, bFavoriteFor, bMsgThread, bMsgCategory, bRecentNews, bRecentTutorials, 
            bRecentPosts, bApprovedTutorials, bPendingTutorials, bBookmarks, bJobs, bSavedTutorials, bTools;

        public DH_UserCustomization(bool bRating, bool bFavorites, bool bIgnored, bool bFavoriteFor, bool bMsgThread, 
            bool bMsgCategory, bool bRecentNews, bool bRecentTutorials, bool bRecentPosts, bool bApprovedTutorials, 
            bool bPendingTutorials, bool bBookmarks, bool bJobs, bool bSavedTutorials, bool bTools) {
            
            this.bRating            = bRating;
            this.bFavorites            = bFavorites;
            this.bIgnored            = bIgnored;
            this.bFavoriteFor        = bFavoriteFor;
            this.bMsgThread            = bMsgThread;
            this.bMsgCategory        = bMsgCategory;
            this.bRecentNews        = bRecentNews;
            this.bRecentTutorials    = bRecentTutorials;
            this.bRecentPosts        = bRecentPosts;
            this.bApprovedTutorials = bApprovedTutorials;
            this.bPendingTutorials  = bPendingTutorials;
            this.bBookmarks            = bBookmarks;
            this.bJobs                = bJobs;
            this.bSavedTutorials    = bSavedTutorials;
            this.bTools                = bTools;
        }
    
        public bool Rating {
            get {
                return bRating;
            }
        }

        public bool Favorites {
            get {
                return bFavorites;
            }
        }

        public bool Ignored {
            get {
                return bIgnored;
            }
        }

        public bool FavoriteFor {
            get {
                return bFavoriteFor;
            }
        }

        public bool MsgThread {
            get {
                return bMsgThread;
            }
        }

        public bool MsgCategory {
            get {
                return bMsgCategory;
            }
        }

        public bool RecentNews {
            get {
                return bRecentNews;
            }
        }

        public bool RecentTutorials {
            get {
                return bRecentTutorials;
            }
        }

        public bool RecentPosts {
            get {
                return bRecentPosts;
            }
        }

        public bool ApprovedTutorials {
            get {
                return bApprovedTutorials;
            }
        }

        public bool PendingTutorials {
            get {
                return bPendingTutorials;
            }
        }

        public bool SavedTutorials {
            get {
                return bSavedTutorials;
            }
        }

        public bool Jobs {
            get {
                return bJobs;
            }
        }

        public bool Bookmarks {
            get {
                return bBookmarks;
            }
        }

        public bool Tools {
            get {
                return bTools;
            }
        }
    }
}