/// <summary>
        ///     Load method for user information class. If UserID = -1, the user failed to be loaded. Override this method to handle user logins.
        /// </summary>
        /// <param name="usernameOrEmail">The username or email to check. Which one to pass depends on the useEmailForLogin parameter.</param>
        /// <param name="password">The password to try.</param>
        /// <param name="useEmailForLogin">If true, the usernameOrEmail value will be assumed to be an email.</param>
        /// <param name="clientID">The client ID of the login attempt.</param>
        /// <param name="outputValue">
        ///     <para>The value output from the query. Use this to generate response messages. Values are:</para>
        ///     <para>-1 - Unknown error. Something went wrong with the query.</para>
        ///     <para>0 - Success</para>
        ///     <para>1 - User is locked out.</para>
        ///     <para>2 - Login failed.</para>
        ///     <para>3 - No user or email was specified.</para>
        ///     <para>4 - User doesn't exist.</para>
        /// </param>
        protected virtual void Load(string usernameOrEmail, string password, bool useEmailForLogin, int clientID,
                                    out int outputValue)
        {
            outputValue = -1;
            //Set up the sql request
            SQLDatabaseReporting sql       = new SQLDatabaseReporting();
            SQLParamList         sqlParams = new SQLParamList();

            sqlParams.Add(useEmailForLogin ? "@Email" : "@Username", usernameOrEmail);

            SqlParameter outParam;

            sqlParams.Add("@Password", password)
            .Add("@ClientID", clientID)
            .Add("@IP", RequestVars.GetRequestIPv4Address())
            .AddOutputParam("@OutputValue", 4, out outParam);

            //Try and get the user's info
            DataTable dt = sql.ExecStoredProcedureDataTable("spCOM_WebReportingLogon", sqlParams);

            if (!sql.HasError)
            {
                outputValue = Conversion.StringToInt(outParam.Value.ToString(), -1);
                if (outputValue == 0 && dt.Rows.Count > 0)
                {
                    //Success!
                    SetUserDataFromDataRow(dt.Rows[0]);
                    return;
                }
            }
            UserID = -1;
        }
        /// <summary>
        /// Perform Password Update
        /// </summary>
        /// <param name="ui">Retrieved User</param>
        /// <param name="oldPass">User's current password</param>
        /// <param name="newPass">User's new password </param>
        /// <returns>
        /// /////Error Codes//////
        /// 1 - Success
        /// 2 - User not found
        /// 3 - Reset code expired
        /// 4 - Invalid password
        /// 5 - Database error
        /// 6 - No rows updated
        /// 7 - Provided Password is incorrect
        /// </returns>
        public static int UpdatePassword <T>(T ui, string oldPass, string newPass)
            where T : UserInformation, new()
        {
            //oldPass == null only when it's a GUID reset, so check if the reset date is within the limits
            if (oldPass == null && DateTime.Now > ui.PasswordResetDate.AddHours(12))
            {
                return(3); //reset code expired
            }
            if (newPass.Length > 20 || newPass.Length < 6)
            {
                return(4); //invalid password length
            }
            SQLDatabaseReporting sql = new SQLDatabaseReporting();

            SqlParameter outParam;

            SQLParamList sqlParams = new SQLParamList();

            sqlParams.Add("@UserId", ui.UserID)
            .Add("@NewPassword", newPass)
            .AddOutputParam("@AffectedRows", 4, out outParam);

            if (oldPass != null)
            {
                sqlParams.Add("@OldPassword", oldPass);
            }

            sql.ExecStoredProcedureDataTable("spCOM_UpdatePassword", sqlParams);

            if (sql.HasError)
            {
                return(5);
            }

            int affectedRows = Conversion.StringToInt(outParam.Value.ToString(), -1);

            if (affectedRows > 1)
            {
                return(5); // Database Error
            }
            // Database Error

            if (oldPass != null && affectedRows == 0)
            {
                return(7); // assume if the old password was provided and no rows are returned that the password is incorrect
            }
            if (affectedRows == 0)
            {
                return(6);                     // information is valid but no rows updated
            }
            return(affectedRows == 1 ? 1 : 0); // if not successful by this point return unknown error (1 = success, 0, unknown)
        }
        /// <summary>
        /// Get User by username or email address
        /// </summary>
        /// <param name="userNameOrEmail">Username or Email for user</param>
        /// <param name="clientId">Client Id user is associated to</param>
        /// <param name="isEmailSearch">(Optional: defaults to false if not specified) Search by email instead of username</param>
        /// <typeparam name="T">Child of UserInformation</typeparam>
        /// <returns>T</returns>
        public static T GetUser <T>(string userNameOrEmail, int clientId, bool isEmailSearch) where T : UserInformation, new()
        {
            string query = string.Format("SELECT [UserID],[Username],[ClientID],[Email] FROM [tblCOM_Users] WHERE {0} = @UserNameOrEmail AND ClientID = @ClientID",

                                         isEmailSearch ? "[Email]" : "[Username]");

            SQLParamList paramList = new SQLParamList();

            paramList
            .Add("@ClientID", clientId)
            .Add("@UserNameOrEmail", userNameOrEmail);
            SQLDatabaseReporting sql = new SQLDatabaseReporting();

            var dt = sql.QueryDataTable(query, paramList);


            if (sql.HasError)
            {
                foreach (var ex in sql.ExceptionList)
                {
                    ErrorHandler.WriteLog(
                        typeof(UserInformation).FullName,
                        "Database Error Encountered",
                        ErrorHandler.ErrorEventID.SQLError, ex);
                }

                return(null);
            }

            return(dt.Rows.Count < 1 ?
                   null :
                   GetUser <T>(Conversion.StringToInt(dt.Rows[0]["UserID"].ToString(), -1)));
        }
        /// <summary>
        /// Add a user to a security group for a given client
        /// </summary>
        /// <param name="userId">Id of User to add to group</param>
        /// <param name="groupId">Id of Group to add user to</param>
        /// <param name="clientId">Client Id Group belongs to </param>
        /// <param name="customFlag1">Custom Int Flag (Application specific)</param>
        /// <param name="customFlag2">Custom Int Flag (Application specific)</param>
        /// <param name="customChar1">Custom String Flag (Application specific)</param>
        /// <param name="customChar2">Custom String Flag (Application specific)</param>
        /// <returns>
        /// /////Error Codes/////
        /// 0 - Unknown Error
        /// 1 - Success
        /// 2 - Group Not Found
        /// 3 - User already in group
        /// 4 - Database Error
        /// </returns>
        public static int AddUserToGroup(int userId, int groupId, int clientId, int?customFlag1, int?customFlag2, string customChar1, string customChar2)
        {
            //sanitize empty string as null
            customChar1 = customChar1 == "" ? null : customChar1;
            customChar2 = customChar2 == "" ? null : customChar2;

            SQLDatabaseReporting sql = new SQLDatabaseReporting();

            var dt = sql.QueryDataTable(
                @"SELECT Count([GroupID]) as GroupCount
                              FROM [dbo].[tblCOM_Groups]
                              Where GroupID = @GroupId AND ClientID = @ClientId",
                new SqlParameter("@GroupId", groupId),
                new SqlParameter("@ClientId", clientId));
            int count = Conversion.StringToInt(dt.Rows[0]["GroupCount"].ToString(), -1);

            if (count < 0)
            {
                return(4); // database error
            }
            bool groupExists = count > 0;

            if (!groupExists)
            {
                return(2);// group doesn't exist
            }
            dt = sql.QueryDataTable(
                @"SELECT Count([UserID]) as GroupCount
                          FROM [dbo].[tblCOM_UserToGroups]
                          Where UserID = 2 AND GroupID = 1",
                new SqlParameter("@UserId", userId),
                new SqlParameter("@GroupId", groupId));
            count = Conversion.StringToInt(dt.Rows[0]["GroupId"].ToString(), -1);

            if (count < 0)
            {
                return(4); // database error
            }
            bool userInGroup = count > 0;

            if (userInGroup)
            {
                return(3);//user already in group;
            }
            SQLParamList paramList = new SQLParamList();

            paramList
            .Add("@UserID", userId)
            .Add("@GroupID", groupId)
            .Add("@IntFlag1", customFlag1)
            .Add("@IntFlag2", customFlag2)
            .Add("@CharFlag1", customChar1)
            .Add("@CharFlag2", customChar2);

            int rows = sql.NonQuery(
                @"INSERT INTO [dbo].[tblCOM_UserToGroups]
                           ([UserID],[GroupID]
                           ,[CustomFlag1],[CustomFlag2]
                           ,[CustomChar1],[CustomChar2])
                     VALUES
                           (@UserId,@GroupId
                           ,@IntFlag1,@IntFlag2
                           ,@CharFlag1,@CharFlag2)",
                paramList);


            if (rows < 1 || sql.HasError)
            {
                return(4); //database error
            }
            return(1);     // Success!
        }