/// <summary>
/// Create new user in S4_USER, USER_CNTY, USER_ROLE
/// </summary>
/// <param name="id"></param>
/// <param name="newStatus"></param>
/// <returns></returns>
public async Task <NewUserRequest> ApproveNewUser(int id, RequestApproval approval)
{
var newStatus = approval.NewStatus;
var request = approval.Request;
var preferredUserName = (request.RequestorFirstNm[0] + request.RequestorLastNm).ToLower();
var userName = await GenerateUserName(preferredUserName);
var user = new S4IdentityUser <S4UserProfile>(userName);
request.HandledBy = approval.AdminUserName;
user.Profile = CreateS4UserProfile(request);
var passwordText = GenerateRandomPassword(8, 0);
await _userManager.CreateAsync(user, passwordText);
await _userManager.SetEmailAsync(user, user.Profile.EmailAddress);
// TODO: need to be more generic here -hard coded for testing
var roles = request.UserManagerCd
? new List <string> {
"User", "User Manager"
}
: new List <string> {
"User"
};
await _userManager.AddToRolesAsync(user, roles);
// Send password cred to new user.
var subject = "Signal Four Analytics user account created";
var body = $@"<div>Dear {request.RequestorFirstNm}, <br><br>
Your Signal Four Analytics individual account has been created.
You can access the system at <a href=""http://s4.geoplan.ufl.edu/"">http://s4.geoplan.ufl.edu.</a>
<br><br>To login, click on the Login link at the upper right corner of the screen
and enter the information below: <br><br>
username = {userName} <br>
password = {passwordText} <br><br>
Upon login you will be prompted to change your password. You will also be
prompted to read and accept the Signal Four Analytics user agreement before
using the system.
<br><br>Below are some links to some resources that should help familiarize you
with the system. <br><br>
<a href =""{_newUserDocumentsUrl}/S4_Analytics_FAQ.PDF"">S4 Analytics FAQ</a><br>
<a href=""{_newUserDocumentsUrl}/S4_Analytics_Slides_&_Recordings_10-6-2015.pdf"">S4 Analytics Training Slides</a><br>
<a href=""{_webinarUrl}"">S4 Analytics Training Webinar Recording</a>
<br><br>Please let us know if you need further assistance.<br><br></div>";
var closing = GetEmailNotificationClosing();
SendEmail(user.Profile.EmailAddress, null, _supportEmail, subject, body, closing);
request.RequestStatus = newStatus;
request.UserId = userName;
request.UserCreatedDt = DateTime.Now;
UpdateApprovedNewUserRequest(request);
return(request);
}
public async Task <IdentityResult> CreateProfileAsync(S4IdentityUser <S4UserProfile> user, CancellationToken cancellationToken)
{
cancellationToken.ThrowIfCancellationRequested();
if (user.Profile == null)
{
// return IdentityResult.Failed(new IdentityError() { Description = "User profile cannot be null." });
throw new Exception("User profile cannot be null."); // temporary
}
// INSERT INTO S4_USER
var insertTxt = @"INSERT INTO S4_USER
(APPLICATION_NM, USER_NM, FIRST_NM, LAST_NM, NAME_SUFFIX,
CREATED_BY, FORCE_PASSWORD_CHANGE,
TIME_LIMITED_ACCOUNT_CD, ACCOUNT_START_DT, ACCOUNT_EXPIRATION_DT,
AGNCY_ID, EMAIL, CREATED_DT, CAN_VIEW, CONTRACTOR_ID)
VALUES (:appNm, :userName, :firstName, :lastName, :suffixName,
:createdBy, :forcePasswordChange, :timeLimitedAccount,
:accountStartDate, :accountExpirationDate, :agencyId, :emailAddress,
:createdDate, :crashReportAccess, :vendorId)";
await _connection.ExecuteAsync(insertTxt,
new
{
user.UserName,
user.Profile.FirstName,
user.Profile.LastName,
user.Profile.SuffixName,
createdBy = user.Profile.CreatedBy,
createdDate = DateTime.Now,
forcePasswordChange = user.Profile.ForcePasswordChange ? "Y" : "N",
timeLimitedAccount = user.Profile.TimeLimitedAccount ? "Y" : "N",
user.Profile.AccountStartDate,
user.Profile.AccountExpirationDate,
agencyId = user.Profile.Agency == null ? null : (int?)user.Profile.Agency.AgencyId,
vendorId = user.Profile.VendorCompany == null ? null : (int?)user.Profile.VendorCompany.VendorId,
emailAddress = user.Profile.EmailAddress,
user.Profile.CrashReportAccess,
appNm = _applicationName
});
// MERGE INTO USER_CNTY
foreach (var county in user.Profile.ViewableCounties)
{
MergeUserCounty(user.UserName, county, user.Profile.CreatedBy);
}
// MERGE INTO USER_AGREEMENT
foreach (var agreement in user.Profile.Agreements)
{
MergeUserAgreement(user.UserName, agreement);
}
return(IdentityResult.Success);
}
private async Task <S4IdentityUser <S4UserProfile> > CreateBasicUser(
string userName,
string password,
S4UserProfile profile)
{
var user = new S4IdentityUser <S4UserProfile>(userName);
user.Profile = profile;
await _userManager.CreateAsync(user, password);
await _userManager.SetEmailAsync(user, profile.EmailAddress);
return(user);
}
public async Task <IdentityResult> DeleteProfileAsync(S4IdentityUser <S4UserProfile> user, CancellationToken cancellationToken)
{
cancellationToken.ThrowIfCancellationRequested();
var @params = new { appName = _applicationName, user.UserName };
// DELETE FROM USER_CNTY
var deleteTxt = @"DELETE FROM user_cnty WHERE application_nm = :appName AND user_nm = :userName";
await _connection.ExecuteAsync(deleteTxt, @params);
// DELETE FROM USER_AGREEMENT
deleteTxt = @"DELETE FROM user_agreement WHERE application_nm = :appName AND user_nm = :userName";
await _connection.ExecuteAsync(deleteTxt, @params);
// DELETE FROM S4_USER
deleteTxt = @"DELETE FROM s4_user WHERE application_nm = :appName AND user_nm = :userName";
await _connection.ExecuteAsync(deleteTxt, @params);
return(IdentityResult.Success);
}
public static bool IsFDOTAdmin(this S4IdentityUser <S4UserProfile> user)
{
return(user.Roles.Any(role => role.RoleName == RoleNames.FDOTAdmin.ToLowerInvariant()));
}
public static bool IsPbcatEditor(this S4IdentityUser <S4UserProfile> user)
{
return(user.Roles.Any(role => role.RoleName == RoleNames.PbcatEditor.ToLowerInvariant()));
}
public async Task <S4UserProfile> FindProfileForUserAsync(S4IdentityUser <S4UserProfile> user, CancellationToken cancellationToken)
{
cancellationToken.ThrowIfCancellationRequested();
var selectText = @"BEGIN
OPEN :q1 FOR
SELECT
FIRST_NM AS FirstName,
LAST_NM AS LastName,
NAME_SUFFIX AS SuffixName,
CREATED_BY AS CreatedBy,
MODIFIED_BY AS ModifiedBy,
MODIFIED_DT AS ModifiedDate,
CASE FORCE_PASSWORD_CHANGE WHEN 'Y' THEN 1 ELSE 0 END AS ForcePasswordChange,
CASE TIME_LIMITED_ACCOUNT_CD WHEN 'Y' THEN 1 ELSE 0 END AS TimeLimitedAccount,
ACCOUNT_START_DT AS AccountStartDate,
ACCOUNT_EXPIRATION_DT AS AccountExpirationDate,
EMAIL AS EmailAddress,
CREATED_DT AS CreatedDate,
CAN_VIEW AS CrashReportAccess
FROM S4_USER
WHERE APPLICATION_NM = :appName
AND USER_NM = :userName;
OPEN :q2 FOR
SELECT
uc.CNTY_CD AS CountyCode,
s4_cnty.cnty_nm AS CountyName,
CASE uc.CAN_EDIT WHEN 'Y' THEN 1 ELSE 0 END AS CanEdit,
uc.CREATED_BY AS CreatedBy,
uc.CREATED_DT AS CreatedDate,
uc.MODIFIED_BY AS ModifiedBy,
uc.MODIFIED_DT AS ModifiedDate
FROM
USER_CNTY uc
INNER JOIN S4_CNTY
ON S4_CNTY.CNTY_CD = uc.CNTY_CD
WHERE APPLICATION_NM = :appName
AND USER_NM = :userName;
OPEN :q3 FOR
SELECT
ag.agncy_id AS AGENCYID,
ag.agncy_nm AS AGENCYNAME,
ag.agncy_type_cd AS AGENCYTYPECD,
ag.created_by AS CREATEDBY,
ag.created_dt AS CREATEDDT,
CASE WHEN ag.is_active = 'Y' THEN 1 ELSE 0 END AS ISACTIVE,
ag.parent_agncy_id AS PARENTAGENCYID,
ag.can_view AS CRASHREPORTACCESS,
ag.email_domain AS EMAILDOMAIN
FROM S4_AGNCY ag
INNER JOIN s4_user u
ON u.agncy_id = ag.agncy_id
WHERE APPLICATION_NM = :appName
AND u.user_nm = :userName;
OPEN :q4 FOR
SELECT
co.CONTRACTOR_ID as vendorId,
co.CONTRACTOR_NM as vendorName,
co.CREATED_BY as createdBy,
co.CREATED_DT as createdDate,
co.EMAIL_DOMAIN as emailDomain,
CASE WHEN co.IS_ACTIVE = 'Y' THEN 1 ELSE 0 END AS isActive
FROM CONTRACTOR co
INNER JOIN s4_user u
ON u.contractor_id = co.contractor_id
WHERE APPLICATION_NM = :appName
AND u.user_nm = :userName;
OPEN :q5 FOR
SELECT
AGREEMENT_NM AS AgreementName,
AGREEMENT_SIGNED_DT AS SignedDate,
AGREEMENT_EXPIRATION_DT AS ExpirationDate
FROM
USER_AGREEMENT
WHERE APPLICATION_NM = :appName
AND USER_NM = :userName;
END;";
string[] refCursorNames = { "q1", "q2", "q3", "q4", "q5" };
var queryParams = new OracleDynamicParameters(new { appName = _applicationName, user.UserName }, refCursorNames);
S4UserProfile profile;
using (var multi = await _connection.QueryMultipleAsync(selectText, queryParams))
{
profile = multi.ReadFirstOrDefault <S4UserProfile>();
if (profile != null)
{
profile.ViewableCounties = multi.Read <UserCounty>().ToList();
profile.Agency = multi.ReadFirstOrDefault <Agency>();
profile.VendorCompany = multi.ReadFirstOrDefault <Vendor>();
profile.Agreements = multi.Read <UserAgreement>().ToList();
}
}
return(profile);
}
public async Task <IdentityResult> UpdateProfileAsync(S4IdentityUser <S4UserProfile> user, CancellationToken cancellationToken)
{
cancellationToken.ThrowIfCancellationRequested();
if (user.Profile == null)
{
// return IdentityResult.Failed(new IdentityError() { Description = "User profile cannot be null." });
throw new Exception("User profile cannot be null."); // temporary
}
// UPDATE S4_USER
var updateTxt = @"UPDATE S4_USER
SET FIRST_NM = :firstName,
LAST_NM = :lastName,
NAME_SUFFIX = :suffixName,
FORCE_PASSWORD_CHANGE = :forcePasswordChange,
TIME_LIMITED_ACCOUNT_CD = :timeLimitedAccount,
ACCOUNT_START_DT = :accountStartDate,
ACCOUNT_EXPIRATION_DT = :accountExpirationDate,
AGNCY_ID = :agencyId,
CONTRACTOR_ID = :vendorId,
EMAIL = :emailAddress,
CAN_VIEW = :crashReportAccess,
MODIFIED_BY = :modifiedBy,
MODIFIED_DT = :modifiedDate
WHERE APPLICATION_NM = :appName
AND USER_NM = :userName";
await _connection.ExecuteAsync(updateTxt, new {
user.Profile.FirstName,
user.Profile.LastName,
user.Profile.SuffixName,
forcePasswordChange = user.Profile.ForcePasswordChange ? "Y" : "N",
timeLimitedAccount = user.Profile.TimeLimitedAccount ? "Y" : "N",
user.Profile.AccountStartDate,
user.Profile.AccountExpirationDate,
agencyId = user.Profile.Agency == null ? null : (int?)user.Profile.Agency.AgencyId,
vendorId = user.Profile.VendorCompany == null ? null : (int?)user.Profile.VendorCompany.VendorId,
user.Profile.EmailAddress,
user.Profile.CrashReportAccess,
modifiedBy = user.Profile.ModifiedBy,
modifiedDate = DateTime.Now,
appName = _applicationName,
user.UserName
});
// MERGE INTO USER_CNTY
foreach (var county in user.Profile.ViewableCounties)
{
MergeUserCounty(user.UserName, county, user.Profile.ModifiedBy);
}
// DELETE USER_CNTY
var deleteText = @"DELETE FROM user_cnty
WHERE APPLICATION_NM = :appName
AND USER_NM = :userName";
if (user.Profile.ViewableCounties.Count > 0)
{
deleteText += " AND cnty_cd NOT IN :countyCodes";
}
_connection.Execute(deleteText, new {
appName = _applicationName,
user.UserName,
countyCodes = user.Profile.ViewableCounties.Select(c => c.CountyCode)
});
// MERGE INTO USER_AGREEMENT
foreach (var agreement in user.Profile.Agreements)
{
MergeUserAgreement(user.UserName, agreement);
}
return(IdentityResult.Success);
}
