/// <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>
/// Take data from msg for log
/// </summary>
/// <param name="msg"></param>
/// <returns></returns>
public static bool LogEmail(MailMessage msg)
{
string emailTo = msg.To.ToString();
string emailFrom = msg.From.ToString();
string subject = msg.Subject.ToString();
string project = msg.Body.ToString();
SQLParamList sqlParams = new SQLParamList()
.Add("@To", emailTo)
.Add("@From", emailFrom)
.Add("@Subject", subject)
.Add("@Project", project)
.Add("@Timestamp", System.DateTime.Now.ToString());
SQLDatabaseWeb sql = new SQLDatabaseWeb();
int logEmail = sql.NonQuery(
"INSERT INTO [dbo].[EmailLog] (toEmail, fromEmail, emailSubject, project, timestamp) VALUES (@To, @From, @Subject, @Project, @Timestamp)",
sqlParams);
if (!sql.HasError && logEmail > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <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>
/// Runs the query string passed as a parameter. Returns true if records were returned for the query and false if none were returned.
/// </summary>
/// <param name="query">The select statement.</param>
/// <param name="sqlDB">The database object to run the query on.</param>
/// <param name="sqlParams">Optional. SQLParamList for the query.</param>
/// <returns>True if results exist, false otherwise</returns>
public static bool IsInDBTable(this string query, SQLDatabase sqlDB, SQLParamList sqlParams)
{
if (query.Length > 0)
{
DataTable dt = sqlDB.QueryDataTable(query, sqlParams);
if (dt.Rows.Count >= 1)
{
return(true);
}
}
return(false);
}
/// <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>
/// Create New User Login in database
/// </summary>
/// <param name="username">requested username, must be unique to client id</param>
/// <param name="password">Users's password - must be between 6-20 characters</param>
/// <param name="clientId">Client Id client id for application</param>
/// <param name="firstName">User's First Name</param>
/// <param name="lastName">User's Last Name</param>
/// <param name="email">User's Email - Must be globalally unique</param>
/// <param name="error">
/// Error output -
/// //////Codes//////
/// 0- Unknown Error
/// 1- Success
/// 2- Username or Email Exists
/// 3- Invalid Email address
/// 4- Invalid Characters in first or last name
/// 5- Password invalid
/// 6- Database Error
///
/// </param>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public static T CreateNewUser <T>(string username, string password, int clientId, string firstName, string lastName, string email, out int error)
where T : UserInformation, new()
{
SQLDatabaseReporting sql = new SQLDatabaseReporting();
error = 1;
if (!Validation.RegExCheck(email, ValidationType.Email))
{
error = 3; // Invalid email address
}
if (!Validation.RegExCheck(firstName, ValidationType.Name) || !Validation.RegExCheck(lastName, ValidationType.Name))
{
error = 4; // Invalid characters in name
}
if (password.Length < 6 || password.Length > 20)
{
error = 5;// invalid password
}
if (error != 1) // if not successful up to this point return null to prevent unnecessary queries
{
return(null);
}
bool usernameExists = sql.NonQuery("SELECT * AS [UserCount] FROM [tblCOM_Users] WHERE [Username] = @Username AND [ClientId] = @ClientId",
new SqlParameter("@Username", username), new SqlParameter("@ClientId", clientId)) > 0;
bool emailExists = sql.NonQuery("SELECT * AS [UserCount] FROM [tblCOM_Users] WHERE [Email] = @Email AND [ClientId] = @ClientId",
new SqlParameter("@Email", email)) > 0;
if (usernameExists || emailExists)
{
error = 2; // user exists with same email or (username and clientId)
}
if (error != 1)
{
return(null);
}
//if you made it this far you should be good to create the user
SqlParameter userId;
var sqlParams = new SQLParamList()
.Add("@Username", username)
.Add("@Password", password)
.Add("@Password", clientId)
.Add("@Email", email)
.Add("@FirstName", firstName)
.Add("@LastName", lastName)
.Add("@ClientId", clientId)
.AddOutputParam("@UserID", 4, out userId);
sql.ExecStoredProcedureDataTable("spCOM_CreateNewUser", sqlParams);
if (sql.HasError)
{
error = 6;
} //Database Error
if (error == 1)
{
var cuser = GetUser <T>(Conversion.StringToInt(userId.Value.ToString(), -1));
if (cuser == null)
{
error = 6;
return(null);
}
return(cuser); // return created user here
}
error = 0;
return(null);
}
/// <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!
}
/// <summary>
/// Executes a scalar query with parameters and returns the value as type T or the defaultreturn value if there is an error in conversion.
/// </summary>
/// <typeparam name="T">The type of the value being returned.</typeparam>
/// <param name="querytext">The query to run.</param>
/// <param name="defaultreturn">The default value to return if there is an error converting the value.</param>
/// <param name="paramlist">An SQLParamList object containing SqlParameters to pass into the SqlCommand object.</param>
/// <returns>The scalar value of type T returned by the query.</returns>
public T QueryScalarValue <T>(string querytext, T defaultreturn, SQLParamList paramlist)
{
return(QueryScalarValue <T>(querytext, defaultreturn, paramlist.ToArray()));
}
/// <summary>
/// Will execute an insert query and return the identity value for the inserted row. Used to insert a row and retrieve the auto-generated identity value at the same time. On error, it returns the default value of T.
/// </summary>
/// <typeparam name="T">The type of the identity.</typeparam>
/// <param name="querytext">An insert query.</param>
/// <param name="paramlist">A SQLParamList object containing SqlParameters to pass into the SqlCommand object.</param>
/// <returns>The identity value for the inserted row.</returns>
public int QueryAndReturnIdentity(string querytext, SQLParamList paramlist)
{
return(QueryAndReturnIdentity(querytext, paramlist.ToArray()));
}
/// <summary>
/// Executes a query on the database with parameters.
/// </summary>
/// <param name="querytext">The text of the query.</param>
/// <param name="paramlist">A SQLParamList object containing SqlParameters to pass into the SqlCommand object.</param>
/// <returns>An object of type System.Data.DataTable containing the results of the queried data.</returns>
public DataTable QueryDataTable(string querytext, SQLParamList paramlist)
{
return(QueryDataTable(querytext, paramlist.ToArray()));
}
/// <summary>
/// Executes a stored procedure.
/// </summary>
/// <param name="procedureName">The name of the stored procedure. Do not prepend "EXEC".</param>
/// <param name="paramlist">An SQLParamList object containing SqlParameters to pass into the SqlCommand object.</param>
/// <returns>A DataSet representing the returned dataset(s).</returns>
public DataSet ExecStoredProcedureDataSet(string procedureName, SQLParamList paramlist)
{
return(ExecStoredProcedureDataSet(procedureName, paramlist.ToArray()));
}
/// <summary>
/// Executes a non-query with parameters on the database connection specified in the constructor.
/// </summary>
/// <param name="querytext">The text of the query.</param>
/// <param name="paramlist">An SQLParamList object containing SqlParameters to pass into the SqlCommand object.</param>
/// <returns>The number of rows affected or -1 on error.</returns>
public int NonQuery(string querytext, SQLParamList paramlist)
{
return(NonQuery(querytext, paramlist.ToArray()));
}
