/// <summary>
/// Creates an account request for a new user. New request will be in pending status until approved or denied.
/// </summary>
/// <param name="accountRequest">AccountRequest</param>
/// <returns>The RowNo of the new account request.</returns>
public int CreateAccountRequest(AccountRequest accountRequest)
{
var salt = Guid.NewGuid().ToString(); // note: create a new salt every time they change their password
var passwordHash = CreatePasswordHash(accountRequest.Password, salt);
var proc = "[Security].[CreateAccountRequest]";
var paramz = new List <SqlParameter>();
paramz.Add(new SqlParameter("firstName", accountRequest.FirstName));
paramz.Add(new SqlParameter("lastName", accountRequest.LastName));
paramz.Add(new SqlParameter("email", accountRequest.Email));
paramz.Add(new SqlParameter("salt", salt));
paramz.Add(new SqlParameter("passwordHash", passwordHash));
//paramz.Add(new SqlParameter("pastorName", accountRequest.PastorName));
paramz.Add(new SqlParameter("churchId", accountRequest.ChurchId));
paramz.Add(new SqlParameter("line1", accountRequest.Line1));
paramz.Add(new SqlParameter("city", accountRequest.City));
paramz.Add(new SqlParameter("state", accountRequest.State));
paramz.Add(new SqlParameter("zip", accountRequest.Zip));
paramz.Add(new SqlParameter("comments", accountRequest.Comments));
Func <SqlDataReader, int> readFx = (reader) =>
{
return((int)reader["AccountRequestID"]);
};
var executor = new SqlCmdExecutor(ConnectionString);
var list = executor.ExecuteSql <int>(proc, CommandType.StoredProcedure, paramz, readFx);
return(list.First());
}
public int SaveAccessDbFilePath(int churchid, string filePath)
{
var paramz = new List <SqlParameter>();
paramz.Add(new SqlParameter("churchId", churchid));
paramz.Add(new SqlParameter("accessDbFilePath", filePath));
Func <SqlDataReader, int> readFx = (reader) =>
{
return((int)reader["Id"]);
};
var list = _executor.ExecuteSql <int>("AddImportFilePath", CommandType.StoredProcedure, paramz, readFx);
return(list.First());
}
public UserProfile SaveUserProfile(UserProfile userProfile)
{
var proc = "[Security].[SaveUserProfile]";
var paramz = new List <SqlParameter>();
paramz.Add(new SqlParameter("userId", userProfile.UserId));
paramz.Add(new SqlParameter("roleId", userProfile.RoleId));
var table = new DataTable();
table.Columns.Add("Id", typeof(int));
userProfile.ChurchIds.ToList().ForEach(s => table.Rows.Add(s));
paramz.Add(new SqlParameter("@churchIds", table));
Func <SqlDataReader, string> readFx = (reader) =>
{
return(reader["status"].ToString());
};
var executor = new SqlCmdExecutor(ConnectionString);
var list = executor.ExecuteSql <string>(proc, CommandType.StoredProcedure, paramz, readFx);
return(userProfile);
}
/// <summary>
/// Change a user's password
/// </summary>
/// <param name="accountRequest">AccountPasswordChange</param>
/// <returns>true if the password was changed, falst if it was not</returns>
public bool ChangePassword(AccountPasswordChange accountRequest)
{
// perform login to validate the old credentials
var spice = GetUserLoginSpice(accountRequest.Email);
if (spice == null)
{
return(false);
}
var oldPasswordHash = CreatePasswordHash(accountRequest.OldPassword, spice.Salt);
// save new password.
var salt = Guid.NewGuid().ToString(); // note: create a new salt every time they change their password
var passwordHash = CreatePasswordHash(accountRequest.NewPassword, salt);
var proc = "[Security].[ChangePassword]";
var paramz = new List <SqlParameter>();
paramz.Add(new SqlParameter("email", accountRequest.Email));
paramz.Add(new SqlParameter("oldPasswordHash", passwordHash));
paramz.Add(new SqlParameter("newPasswordHash", passwordHash));
paramz.Add(new SqlParameter("newSalt", salt));
Func <SqlDataReader, bool> readFx = (reader) =>
{
return((bool)reader["Success"]);
};
var executor = new SqlCmdExecutor(ConnectionString);
var list = executor.ExecuteSql <bool>(proc, CommandType.StoredProcedure, paramz, readFx);
return(list.FirstOrDefault());
}
/// <summary>
/// Get all account requests for review and approval or denial
/// </summary>
public List <AccountRequest> GetAccountRequests()
{
var proc = "[Security].[GetAccountRequests]";
Func <SqlDataReader, AccountRequest> readFx = (reader) =>
{
var acctReq = new AccountRequest {
RequestId = (int)reader["AccountRequestID"],
FirstName = reader["FirstName"] + "",
LastName = reader["LastName"] + "",
Line1 = reader["Line1"] + "",
City = reader["City"] + "",
State = reader["State"] + "",
Zip = reader["Zip"] + "",
Email = reader["Email"] + "",
Comments = reader["Comments"] + "",
ChurchId = (int)reader["ChurchId"],
DateSubmitted = (DateTime)reader["DateSubmitted"],
RoleId = (int)Roles.User // default to user
};
return(acctReq);
};
var executor = new SqlCmdExecutor(ConnectionString);
var list = executor.ExecuteSql <AccountRequest>(proc, CommandType.StoredProcedure, null, readFx);
return(list);
}
public RepositoryActionResult <Email> MergeEmail(Email email)
{
var ciParamz = CreateAddressInfoParams(email);
ciParamz.Add(new SqlParameter("@email", email.EmailAddress.ToSqlString()));
var list = _executor.ExecuteSql <int>("SaveEmail", CommandType.StoredProcedure, ciParamz, ContactInfoReadFx);
var contactInfoId = list.First();
if (email.ContactInfoId == contactInfoId)
{
return(new RepositoryActionResult <Email>(email, RepositoryActionStatus.Ok));
}
else
{
email.ContactInfoId = contactInfoId;
return(new RepositoryActionResult <Email>(email, RepositoryActionStatus.Created));
}
}
public RepositoryActionResult <ITeam> SaveTeam(ITeam team)
{
var proc = "SaveTeam";
var paramz = new List <SqlParameter>();
paramz.Add(new SqlParameter("id", team.Id));
paramz.Add(new SqlParameter("name", team.Name));
paramz.Add(new SqlParameter("desc", team.Desc));
paramz.Add(new SqlParameter("churchId", team.ChurchId));
paramz.Add(new SqlParameter("teamTypeEnumId", team.TeamTypeEnumId));
paramz.Add(new SqlParameter("comment", team.Comment));
Func <SqlDataReader, Tuple <int, int> > readFx = (reader) =>
{
var id = (int)reader["Id"];
var teamPositionEnumTypeId = (int)reader["TeamPositionEnumTypeId"];
return(new Tuple <int, int>(id, teamPositionEnumTypeId));
};
var list = _executor.ExecuteSql <Tuple <int, int> >(proc, CommandType.StoredProcedure, paramz, readFx);
var newTeam = list.FirstOrDefault();
if (newTeam.Item1 > 0)
{
// update id and position type
team.Id = newTeam.Item1;
team.TeamPositionEnumTypeId = newTeam.Item2;
return(new RepositoryActionResult <ITeam>(team, RepositoryActionStatus.Created));
}
else
{
return(new RepositoryActionResult <ITeam>(null, RepositoryActionStatus.Error));
}
}
public RepositoryActionResult <Church> Add(Church church)
{
var proc = "CreateChurch";
var paramz = new List <SqlParameter>();
paramz.Add(new SqlParameter("createdByUserId", church.CreatedByUserId));
paramz.Add(new SqlParameter("name", church.Name.ToSqlString()));
paramz.Add(new SqlParameter("pastorId", church.PastorId));
paramz.Add(new SqlParameter("line1", church.Line1.ToSqlString()));
paramz.Add(new SqlParameter("city", church.City.ToSqlString()));
paramz.Add(new SqlParameter("state", church.State.ToSqlString()));
paramz.Add(new SqlParameter("zip", church.Zip.ToSqlString()));
paramz.Add(new SqlParameter("phone", church.Phone.ToSqlString()));
paramz.Add(new SqlParameter("email", church.Email.ToSqlString()));
paramz.Add(new SqlParameter("timeZoneOffset", church.TimeZoneOffset.ToSqlString()));
Func <SqlDataReader, int> readFx = (reader) =>
{
return((int)reader["ChurchId"]);
};
var list = _executor.ExecuteSql <int>(proc, CommandType.StoredProcedure, paramz, readFx);
var churchId = list.FirstOrDefault();
if (churchId != 0)
{
church.id = churchId;
return(new RepositoryActionResult <Church>(church, RepositoryActionStatus.Created));
}
else
{
return(new RepositoryActionResult <Church>(church, RepositoryActionStatus.NotFound));
}
}
public List <ActiveGuestListReportData> GetActiveGuestList(List <KeyValuePair <string, string> > paramsCollection)
{
var churchId = paramsCollection.FirstOrDefault(p => p.Key.Equals("churchId")).Value;
//var period = paramsCollection.FirstOrDefault(p => p.Key.Equals("period")).Value;
//var date = paramsCollection.FirstOrDefault(p => p.Key.Equals("date")).Value;
var statusIds = paramsCollection.FirstOrDefault(p => p.Key.Equals("statusIds")).Value;
var teamId = paramsCollection.FirstOrDefault(p => p.Key.Equals("teamId")).Value;
var sponsorId = paramsCollection.FirstOrDefault(p => p.Key.Equals("sponsorId")).Value;
var idlist = statusIds.Split(new[] { '-' }, StringSplitOptions.RemoveEmptyEntries);
var arylist = Array.ConvertAll <string, int>(idlist, int.Parse);
var statusTable = new DataTable();
statusTable.Columns.Add("Id", typeof(int));
arylist.ToList().ForEach(s => statusTable.Rows.Add(s));
var paramz = new List <SqlParameter>();
paramz.Add(new SqlParameter("churchId", churchId));
paramz.Add(new SqlParameter("teamId", teamId));
paramz.Add(new SqlParameter("sponsorId", sponsorId));
paramz.Add(new SqlParameter("statusEnumIDs", statusTable));
Func <SqlDataReader, ActiveGuestListReportData> readFx = (reader) =>
{
var activityDateIdx = reader.GetOrdinal("ActivityDate");
var data = new ActiveGuestListReportData();
data.LastActivityDate = reader.IsDBNull(activityDateIdx) ? (DateTimeOffset?)null : reader.ValueOrDefault <DateTimeOffset>(activityDateIdx);
data.MemberId = reader.ValueOrDefault <int>("ID");
data.MemberName = reader.ValueOrDefault <string>("Name");
data.MemberAddress = reader.ValueOrDefault <string>("Address");
data.MemberEmail = reader.ValueOrDefault <string>("Email");
data.MemberPhone = reader.ValueOrDefault <string>("Number");
data.SponsorId = reader.ValueOrDefault <int>("SponsorId");
data.SponsorName = reader.ValueOrDefault <string>("Sponsor");
data.TeamId = reader.ValueOrDefault <int>("TeamId");
data.TeamName = reader.ValueOrDefault <string>("TeamName");
data.Comments = reader.ValueOrDefault <string>("Comment", "").Trim();
data.StatusId = reader.ValueOrDefault <int>("StatusId");
data.Status = reader.ValueOrDefault <string>("Status");
data.StatusChangeTypeId = reader.ValueOrDefault <int>("StatusChangeTypeId");
data.StatusChangeType = reader.ValueOrDefault <string>("StatusChangeType");
return(data);
};
var list = _executor.ExecuteSql <ActiveGuestListReportData>("GetActiveGuestListReport", CommandType.StoredProcedure, paramz, readFx);
return(list);
}
/// <summary>
/// Creates an account request for a new user. New request will be in pending status until approved or denied.
/// </summary>
/// <param name="accountRequest">AccountRequest</param>
/// <returns>The RowNo of the new account request.</returns>
public string GetAccountRequestStatus(int accountRequestId)
{
var proc = "[Security].[GetAccountRequestStatus]";
var paramz = new List <SqlParameter>();
paramz.Add(new SqlParameter("accountRequestId", accountRequestId));
Func <SqlDataReader, string> readFx = (reader) =>
{
return(reader["Status"].ToString());
};
var executor = new SqlCmdExecutor(ConnectionString);
var list = executor.ExecuteSql <string>(proc, CommandType.StoredProcedure, paramz, readFx);
return(list.First());
}
public List <Role> GetRoles()
{
var proc = "[Security].[GetRoles]";
Func <SqlDataReader, Role> readFx = (reader) =>
{
return(new Role
{
RoleId = (int)reader["RoleID"],
RoleDesc = reader["RoleDesc"].ToString(),
});
};
var executor = new SqlCmdExecutor(ConnectionString);
var list = executor.ExecuteSql <Role>(proc, CommandType.StoredProcedure, null, readFx);
return(list);
}
/// <summary>
/// Gets a user's PersonIdentityID & Salt
/// </summary>
/// <param name="email">Email is the Username</param>
/// <returns>LoginSpice</returns>
private LoginSpice GetUserLoginSpice(string email)
{
var proc = "[Security].[Login_GetUserSpice]";
var paramz = new List <SqlParameter>();
paramz.Add(new SqlParameter("userName", email));
Func <SqlDataReader, LoginSpice> readFx = (reader) =>
{
var spice = new LoginSpice();
spice.PersonIdentityID = (int)reader["PersonIdentityID"];
spice.Salt = reader["Salt"].ToString();
return(spice);
};
var executor = new SqlCmdExecutor(ConnectionString);
var list = executor.ExecuteSql <LoginSpice>(proc, CommandType.StoredProcedure, paramz, readFx);
return(list.FirstOrDefault());
}
/// <summary>
/// returs the new IdentityID of the user
/// </summary>
/// <param name="accountRequest"></param>
/// <returns></returns>
public int ProcessAccountRequest(AccountRequest accountRequest)
{
var proc = "[Security].[ProcessAccountRequest]";
var paramz = new List <SqlParameter>();
paramz.Add(new SqlParameter("accountRequestId", accountRequest.RequestId));
paramz.Add(new SqlParameter("approved", accountRequest.IsApproved));
paramz.Add(new SqlParameter("denied", !accountRequest.IsApproved));
paramz.Add(new SqlParameter("processedByUserID", accountRequest.ReviewerUserId));
paramz.Add(new SqlParameter("defaultUserRoleId", Roles.User));
paramz.Add(new SqlParameter("memberTypeEnumId", MemberType.Member));
paramz.Add(new SqlParameter("roleId", accountRequest.RoleId));
paramz.Add(new SqlParameter("firstName", accountRequest.FirstName));
paramz.Add(new SqlParameter("lastName", accountRequest.LastName));
paramz.Add(new SqlParameter("line1", accountRequest.Line1));
paramz.Add(new SqlParameter("city", accountRequest.City));
paramz.Add(new SqlParameter("state", accountRequest.State));
paramz.Add(new SqlParameter("zip", accountRequest.Zip));
paramz.Add(new SqlParameter("email", accountRequest.Email));
paramz.Add(new SqlParameter("churchId", accountRequest.ChurchId));
paramz.Add(new SqlParameter("comments", accountRequest.Comments));
// pass all info as parameters
Func <SqlDataReader, int> readFx = (reader) =>
{
return(reader.ValueOrDefault("IdentityID", 0));
};
var executor = new SqlCmdExecutor(ConnectionString);
var list = executor.ExecuteSql <int>(proc, CommandType.StoredProcedure, paramz, readFx);
return(list.First());
}
/// <summary>
/// Adds a new member
/// </summary>
/// <param name="member"></param>
/// <param name="createdByUserId">The ID of the user that is creating this member</param>
/// <param name="churchId">The churchId of the church that this new member belongs</param>
/// <returns>New ID of the member, or -1 if no id was returned</returns>
public RepositoryActionResult <NewMember> Add(NewMember member)
{
var proc = "CreateMember";
var paramz = new List <SqlParameter>();
paramz.Add(new SqlParameter("ChurchId", member.ChurchId));
paramz.Add(new SqlParameter("createdByUserId", member.CreatedByUserId));
paramz.Add(new SqlParameter("firstName", member.FirstName.ToSqlString()));
paramz.Add(new SqlParameter("middleName", member.MiddleName.ToSqlString()));
paramz.Add(new SqlParameter("lastName", member.LastName.ToSqlString()));
paramz.Add(new SqlParameter("dateCame", member.DateCame));
paramz.Add(new SqlParameter("isGroup", member.IsGroup));
paramz.Add(new SqlParameter("prayed", member.Prayed));
paramz.Add(new SqlParameter("line1", member.Line1.ToSqlString()));
paramz.Add(new SqlParameter("city", member.City.ToSqlString()));
paramz.Add(new SqlParameter("state", member.State.ToSqlString()));
paramz.Add(new SqlParameter("zip", member.Zip.ToSqlString()));
paramz.Add(new SqlParameter("phone", member.Phone.ToSqlString()));
paramz.Add(new SqlParameter("phone2", member.Phone2.ToSqlString()));
paramz.Add(new SqlParameter("email", member.Email.ToSqlString()));
var table = new DataTable();
table.Columns.Add("Id", typeof(int));
member.SponsorList.ToList().ForEach(s => table.Rows.Add(s.SponsorId));
paramz.Add(new SqlParameter("sponsorIds", table));
Func <SqlDataReader, int> readFx = (reader) =>
{
return((int)reader["MemberId"]);
};
var list = _executor.ExecuteSql <int>(proc, CommandType.StoredProcedure, paramz, readFx);
var memberId = list.FirstOrDefault();
if (memberId != 0)
{
member.id = memberId;
return(new RepositoryActionResult <NewMember>(member, RepositoryActionStatus.Created));
}
else
{
return(new RepositoryActionResult <NewMember>(member, RepositoryActionStatus.NotFound));
}
}