/// <summary> /// Toggles the user's active status /// </summary> /// <typeparam name="T">Child of UserInformation </typeparam> /// <param name="id">UserId</param> /// <param name="active">Set to true or false explicitly define what the active state should be </param> /// <param name="error">Error Code Output /// /////Error Codes////// /// 0 - Unknown Error /// 1 - Success /// 2 - User not found /// 3 - Database Error /// </param> /// <returns>T of UserInformation</returns> public static T ToggleActiveUserStatus <T>(int id, bool?active, out int error) where T : UserInformation, new() { error = 0; var ui = GetUser <T>(id); if (ui == null) { error = 2; // user not found return(null); } SQLDatabaseReporting sql = new SQLDatabaseReporting(); int affected = sql.NonQuery("UPDATE [tblCOM_Users] SET [Active] = @IsActive, [TSModified] = GETDATE() WHERE UserID = 2", new SqlParameter("@IsActive", (active ?? !ui.IsActive))); if (affected != 1) { error = 3; //Database Error } error = 1; // Success! return(GetUser <T>(id)); }
/// <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! }