private void ImportSoulwinners(OleDbConnection cn)
{
var sql = @"SELECT [Soul Winners].SWID, [Soul Winners].SWLSNM, [Soul Winners].SWFSNM, [Soul Winners].SWMDNM, [Soul Winners].SWGNNM, [Soul Winners].SWNMCB, [Soul Winners].SWHERE, [Soul Winners].SWGNDR, [Soul Winners].SWMNSTR, [Soul Winners].SWLPST, [Soul Winners].SWACTV, [Soul Winners].SWMRRD, [Soul Winners].SWSPSE, [Soul Winners].SWSPTNR, [Soul Winners].SWLPTR, [Soul Winners].SWDOOR, [Soul Winners].SWCASUAL, [Soul Winners].SWPHN, [Soul Winners].EMAIL
FROM [Soul Winners];";
using (var cmd = new OleDbCommand(sql, cn))
{
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var member = new Soulwinner();
member.SoulwinnerId = reader.ValueOrDefault <int>("SWID", 0);
member.FirstName = reader.ValueOrDefault <string>("SWFSNM", "");
member.MiddleName = reader.ValueOrDefault <string>("SWMDNM", "");
member.LastName = reader.ValueOrDefault <string>("SWLSNM", "");
member.Generation = reader.ValueOrDefault <string>("SWGNNM", "");
member.IsLayPastor = reader.ValueOrDefault <bool>("SWLPST", false);
member.IsMinister = reader.ValueOrDefault <bool>("SWMNSTR", false);
member.Gender = reader.ValueOrDefault <string>("SWGNDR", "");
member.IsHere = reader.ValueOrDefault <bool>("SWHERE", false);
member.IsActive = reader.ValueOrDefault <bool>("SWACTV", false);
member.MarriedStatus = reader.ValueOrDefault <string>("SWMRRD", ""); // (M)arried, (O)ther or (S)ingle
member.SpouseName = reader.ValueOrDefault <string>("SWSPSE", "");
member.IsSpousePartner = reader.ValueOrDefault <bool>("SWSPTNR", false);
member.LayPastorId = reader.ValueOrDefault <int>("SWLPTR", 0);
member.IsDoorToDoor = reader.ValueOrDefault <bool>("SWDOOR", false);
member.IsCasualStatus = reader.ValueOrDefault <bool>("SWCASUAL", false);
member.Email = reader.ValueOrDefault <string>("EMAIL", "");
member.PhoneNumber = reader.ValueOrDefault <string>("SWPHN", "");
this.soulwinners.Add(member);
}
}
}
foreach (var soulwinner in this.soulwinners)
{
var comments = string.Empty;
// create member profile
var memberProfile = new MemberProfile();
memberProfile.FirstName = soulwinner.FirstName;
memberProfile.MiddleName = soulwinner.MiddleName;
memberProfile.LastName = soulwinner.LastName;
memberProfile.Gender = soulwinner.Gender;
memberProfile.StatusId = soulwinner.IsActive ? 49 : 51; // 49 = Active, 51 = Inactive
//memberProfile.MemberStatusEnumType = soulwinner.IsActive ? 49 : 51; // 49 = Active, 51 = Inactive
memberProfile.MemberTypeEnumId = soulwinner.IsMinister ? 64 : 62; // 64 = minister, 62 = member
memberProfile.Married = soulwinner.MarriedStatus == "M"; // (M)arried, (O)ther or (S)ingle They are either married or they are not
memberProfile.IsHere = soulwinner.IsHere;
memberProfile.ChurchId = this.church.id;
memberProfile.LanguageTypeEnumId = 107; // default to English
if (!string.IsNullOrWhiteSpace(soulwinner.SpouseName))
{
comments = $"Spouse: {soulwinner.SpouseName}, " + (soulwinner.IsSpousePartner ? "Is soulwinning partner" : "Is not soulwinning partner");
}
memberProfile.Comments = comments; // last before saving
if (string.IsNullOrWhiteSpace(memberProfile.FirstName) &&
string.IsNullOrWhiteSpace(memberProfile.MiddleName) &&
string.IsNullOrWhiteSpace(memberProfile.LastName))
{
var msg = $"First, Middle, and Last Name are all null from [Soul Winners] table. " +
$"ChurchId: { this.church.id }. " +
$"SWID: { soulwinner.SoulwinnerId }. " +
$"Here are the comments: { memberProfile.Comments }";
var x = new Exception(msg);
LogError(x, Core.Framework.LogLevel.Warning);
continue;
}
var result = this.memberRepo.SaveProfile(memberProfile);
if (result.Status == RepositoryActionStatus.Error)
{
LogError(result.Exception, Core.Framework.LogLevel.Error);
continue;
}
soulwinner.IdentityId = result.Entity.MemberId;
this.importRepo.SaveImportMember(this.accessDbFilePathId, soulwinner.IdentityId, soulwinner.SoulwinnerId, (int)AccessDbTableType.Soulwinners);
// Save the addresses after the profile is saved, because they don't get saved with the profile
this.SaveEmail(soulwinner.IdentityId, soulwinner.Email);
this.SavePhone(soulwinner.IdentityId, soulwinner.PhoneNumber);
// Save Teammate
var layPastor = this.layPastors.FirstOrDefault(lp => lp.LayPastorID == soulwinner.LayPastorId);
if (layPastor != null)
{
var team = new Core.Models.Members.Team {
TeamId = layPastor.IdentityId, MemberId = soulwinner.IdentityId
};
memberProfile.TeamList.Add(team);
var positionEnumId = soulwinner.IsLayPastor ? 74 : 75; // 75 = soulwinner, 74 = TeamLeader
var teamStatusTypeEnumId = soulwinner.IsDoorToDoor ? 103 : soulwinner.IsCasualStatus ? 104 : 105; // 103 Door to Door, 104 Casual, 105 Inactive
this.SaveTeammate(soulwinner.IdentityId, layPastor.IdentityId, positionEnumId, teamStatusTypeEnumId);
}
}
}
public MemberProfile GetProfile(int id, int churchId)
{
MemberProfile member = null;
var proc = "GetMemberProfile";
using (var cn = new SqlConnection(_executor.ConnectionString))
using (var cmd = new SqlCommand(proc, cn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("memberId", id);
cmd.Parameters.AddWithValue("churchId", churchId);
cn.Open();
using (var reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
reader.Read();
// member info
member = new MemberProfile();
member.MemberId = reader.ValueOrDefault <int>("IdentityID");
member.FirstName = reader.ValueOrDefault("FirstName", string.Empty);
member.MiddleName = reader.ValueOrDefault("MiddleName", string.Empty);
member.LastName = reader.ValueOrDefault("LastName", string.Empty);
member.PreferredName = reader.ValueOrDefault("PreferredName", string.Empty);
member.Gender = reader.ValueOrDefault("Gender", string.Empty);
member.Married = reader.ValueOrDefault <bool>("Married", false);
member.Veteran = reader.ValueOrDefault <bool>("Veteran", false);
member.IsHere = reader.ValueOrDefault <bool>("IsHere", true);
member.BirthDate = reader.ValueOrDefault <DateTime?>("DateOfBirth", null);
member.DateSaved = reader.ValueOrDefault <DateTime?>("DateSaved", null);
member.DateBaptizedHolyGhost = reader.ValueOrDefault <DateTime?>("DateBaptizedHolyGhost", null);
member.DateBaptizedWater = reader.ValueOrDefault <DateTime?>("DateBaptizedWater", null);
member.ChurchId = reader.ValueOrDefault <int>("ChurchId", 0);
member.ChurchName = reader["ChurchName"].ToString();
member.StatusId = reader.ValueOrDefault <int>("StatusId", 0);
member.StatusDesc = reader.ValueOrDefault("StatusDesc", string.Empty);
member.StatusChangeTypeId = reader.ValueOrDefault <int>("StatusChangeTypeId", 0);
member.StatusChangeTypeDesc = reader.ValueOrDefault("StatusChangeTypeDesc", string.Empty);
member.SponsorId = reader.ValueOrDefault <int>("SponsorId", 0);
member.Sponsor = reader.ValueOrDefault("Sponsor", string.Empty);
member.Comments = reader.ValueOrDefault("Comment", string.Empty);
member.MemberTypeEnumId = reader.ValueOrDefault <int>("MemberTypeEnumID", 0);
member.Suffix = reader.ValueOrDefault("Suffix", string.Empty);
member.HasBeenBaptized = reader.ValueOrDefault <bool>("HasBeenBaptized", false);
member.LanguageTypeEnumId = reader.ValueOrDefault <int>("LanguageTypeEnumId", 0);
member.NeedsPastoralVisit = reader.ValueOrDefault <bool>("NeedsPastoralVisit", false);
member.AssociatePastorId = reader.ValueOrDefault <int>("AssociatePastorId", 0);
// address info
reader.NextResult();
while (reader.Read())
{
var addy = new Address();
addy.ContactInfoId = reader.ValueOrDefault <int>("ContactInfoID");
addy.ContactInfoType = reader.ValueOrDefault <int>("ContactInfoTypeEnumID");
addy.ContactInfoLocationType = reader.ValueOrDefault <int>("ContactInfoLocationTypeEnumID");
addy.Preferred = reader.ValueOrDefault <bool>("Preferred");
addy.Verified = reader.ValueOrDefault <bool>("Verified");
addy.Line1 = reader.ValueOrDefault <string>("Line1");
addy.Line2 = reader.ValueOrDefault <string>("Line2");
addy.Line3 = reader.ValueOrDefault <string>("Line3");
addy.City = reader.ValueOrDefault <string>("City");
addy.State = reader.ValueOrDefault <string>("State");
addy.Zip = reader.ValueOrDefault <string>("Zip");
member.AddressList.Add(addy);
}
// phone info
reader.NextResult();
while (reader.Read())
{
var addy = new Phone();
addy.ContactInfoId = reader.ValueOrDefault <int>("ContactInfoID");
addy.ContactInfoType = reader.ValueOrDefault <int>("ContactInfoTypeEnumID");
addy.ContactInfoLocationType = reader.ValueOrDefault <int>("ContactInfoLocationTypeEnumID");
addy.Preferred = reader.ValueOrDefault <bool>("Preferred");
addy.Verified = reader.ValueOrDefault <bool>("Verified");
addy.PhoneNumber = reader.ValueOrDefault <string>("Number");
addy.PhoneType = reader.ValueOrDefault <int>("PhoneTypeEnumID", 0); // cell phone, home phone, etc.
member.PhoneList.Add(addy);
}
// email info
reader.NextResult();
while (reader.Read())
{
var addy = new Email();
addy.ContactInfoId = reader.ValueOrDefault <int>("ContactInfoID");
addy.ContactInfoType = reader.ValueOrDefault <int>("ContactInfoTypeEnumID");
addy.ContactInfoLocationType = reader.ValueOrDefault <int>("ContactInfoLocationTypeEnumID");
addy.Preferred = reader.ValueOrDefault <bool>("Preferred");
addy.Verified = reader.ValueOrDefault <bool>("Verified");
addy.EmailAddress = reader.ValueOrDefault <string>("Email");
member.EmailList.Add(addy);
}
// Teams
reader.NextResult();
while (reader.Read())
{
var team = new Core.Models.Members.Team();
team.MemberId = reader.ValueOrDefault <int>("MemberId");
team.TeamId = reader.ValueOrDefault <int>("TeamId");
team.TeamName = reader.ValueOrDefault <string>("TeamName");
member.TeamList.Add(team);
}
// Sponsors
reader.NextResult();
while (reader.Read())
{
var sponsor = new Sponsor();
sponsor.MemberId = reader.ValueOrDefault <int>("MemberId");
sponsor.SponsorId = reader.ValueOrDefault <int>("SponsorId");
sponsor.FirstName = reader.ValueOrDefault <string>("FirstName");
sponsor.LastName = reader.ValueOrDefault <string>("LastName");
member.SponsorList.Add(sponsor);
}
// attributes
//reader.NextResult();
//while (reader.Read())
//{
// var attr = new CustomAttribute();
// attr.IdentityID = reader.ValueOrDefault<int>("IdentityID");
// attr.CustomAttrDefID = reader.ValueOrDefault<int>("CustomAttrDefID");
// attr.Value = reader.ValueOrDefault<string>("Value", string.Empty);
// attr.Name = reader.ValueOrDefault<string>("CustomAttrName", string.Empty);
// attr.DataType = reader.ValueOrDefault<string>("DataType", string.Empty);
// attr.AttrTypeEnumID = reader.ValueOrDefault<int>("AttrTypeEnumID");
// attr.AttrTypeEnumDesc = reader.ValueOrDefault<string>("AttrTypeEnumDesc", string.Empty);
// attr.IsEditable = reader.ValueOrDefault<bool>("IsEditable");
// member.CustomAttributeList.Add(attr);
//}
}
}
}
return(member);
}