/// <summary>
/// Commit and handle error messages
/// </summary>
/// <param name="context"></param>
public static void SaveChanges(GleanerDbContext context)
{
try
{
context.SaveChanges();
}
catch (System.Data.Entity.Validation.DbEntityValidationException ex)
{
throw new Exception(String.Format("Validation error(s): {0}",
String.Join(" ",
ex.EntityValidationErrors.Select(
e => String.Join(" ", e.ValidationErrors.Select(v => v.ErrorMessage))
)
)
));
}
catch (System.Data.Entity.Infrastructure.DbUpdateException ex)
{
var errorMessage = GetInnerExceptionMessage(ex);
if (errorMessage.Contains("CK_AGENT_SYSTEM_USERNAME"))
{
throw new Exception("There is a user with the same name.");
}
throw;
}
}
public AgentService()
{
_db = new GleanerDbContext();
_db.Database.Log = s => Debug.WriteLine(s);
var settingService = GleanerContext.Current.Services.SettingService;
_emailSender = settingService.AllSettings["emailSender"].IfNotNull(x => x.Value);
}
/// <summary>
/// Delete agent
/// </summary>
/// <param name="id"></param>
public void Delete(string id)
{
using (var context = new GleanerDbContext()) {
context.Database.Log = s => Debug.WriteLine(s);
var agentSystem = GetAgentSystemByKey(context, id);
agentSystem.IsDeleted = true;
agentSystem.UpdateDate = DateTime.Now;
context.AgentSystems.AddOrUpdate(agentSystem);
DbHelper.SaveChanges(context);
}
}
/// <summary>
/// Activate agent
/// </summary>
/// <param name="agentNumer"></param>
/// <param name="activationKey"></param>
public void Activate(string agentNumer, string activationKey)
{
using (var context = new GleanerDbContext()) {
context.Database.Log = s => Debug.WriteLine(s);
var agentSystem = GetAgentSystemByKey(context, agentNumer);
if (agentSystem.ConfirmKey != activationKey) {
throw new Exception("Wrong activation key.");
}
if (agentSystem.IsActivated) {
throw new Exception("Agent is already activated.");
}
agentSystem.IsActivated = true;
agentSystem.UpdateDate = DateTime.Now;
context.AgentSystems.AddOrUpdate(agentSystem);
DbHelper.SaveChanges(context);
}
}
public CertificateDetails GetCertificate(string id)
{
var result = new CertificateDetails();
using (var context = new GleanerDbContext())
{
var certificateEntity = context.Inforces.FirstOrDefault(inforce => inforce.InfPolicyNumber == id);
if (certificateEntity == null) { return null; }
result.Number = certificateEntity.InfPolicyNumber;
result.Name = certificateEntity.InfPolicyName;
result.Type = GetCertificateType(certificateEntity.InfPolicyType, certificateEntity.InfPolicyStatus);
result.CurrentAsOfDate = certificateEntity.InfCurrentAsOfDate;
result.SubmitDate = certificateEntity.InfSubmitDate;
result.IssueDate = certificateEntity.InfIssueDate;
result.FaceAmount = certificateEntity.InfFaceAmount;
result.DeathBenefit = certificateEntity.InfDeathBenefit;
result.IsWaiverPremium = certificateEntity.InfWaiverPremiumUniversal == "Y";
result.IsAccidentialDeathBenefit = certificateEntity.InfAccidentalDeathUniversal == "Y";
result.AccumulatedValue = certificateEntity.InfAccumulatedValue;
result.SurrenderValue = certificateEntity.InfSurrenderValue;
result.RateOfInterest = certificateEntity.InfRateOfInterest;
result.DeathBenefitOption = certificateEntity.InfDeathBenefitOption;
result.LoanBalanace = certificateEntity.InfLoanBalanace;
result.NonForfeitureOption = certificateEntity.InfNonForfeitureOption;
result.PaidThroughDate = certificateEntity.InfPaidThroughDate;
result.LastPaymentDate = certificateEntity.InfLastPaymentDate;
result.InterestDue = certificateEntity.InfInterestDue;
result.LoanPayoff = certificateEntity.InfLoanPayoff;
result.LoanAvailable = certificateEntity.InfLoanAvailable;
result.WithdrawalAmount = certificateEntity.InfWithdrawalAmount;
var insuredDetail = context.InsuredDetails.FirstOrDefault(insured => insured.IndId == certificateEntity.IndId);
if (insuredDetail != null)
{
result.InsuredName = insuredDetail.IndInsuredFirstName + " " + insuredDetail.IndInsuredLastName;
result.InsuredBirthDate = insuredDetail.IndDob;
result.InsuredSex = insuredDetail.IndInsuredSex == "M"
? "Male"
: insuredDetail.IndInsuredSex == "F" ? "Female" : "";
result.InsuredAddress = new Address()
{
Street = insuredDetail.IndInsuredStreet,
City = insuredDetail.IndInsuredCity,
State = insuredDetail.IndInsuredState,
ZipCode = insuredDetail.IndInsuredZip
};
}
var ownerDetail = context.OwnerDetails.FirstOrDefault(owner => owner.OwdId == certificateEntity.OwdId);
if (ownerDetail != null)
{
result.OwnerName = ownerDetail.OwdOwnerFirstName + " " + ownerDetail.OwdOwnerLastName;
if (String.IsNullOrEmpty(result.OwnerName.Replace(" ", ""))) result.OwnerName = result.InsuredName;
result.OwnerAddress = new Address()
{
Street = ownerDetail.OwdOwnerStreet,
City = ownerDetail.OwdOwnerCity,
State = ownerDetail.OwdOwnerState,
ZipCode = ownerDetail.OwdOwnerZip
};
if (String.IsNullOrEmpty(result.OwnerAddress.ToString())) result.OwnerAddress = result.InsuredAddress;
}
var ownerHeader = context.OwnerHeaders.FirstOrDefault(owner => owner.OwhId == certificateEntity.OwhId);
if (ownerHeader != null)
{
result.OwnerSsn = ownerHeader.OwhLast4;
}
var agent = context.Agents.FirstOrDefault(a => a.AgentNumber == certificateEntity.InfServicingAgentNumber);
if (agent != null) result.AgentName = agent.AgentFirstName + " " + agent.AgentLastName;
result.ProductDetails = context.PolicyNames.Where(p => p.PnmId == certificateEntity.PnmId).Select(p => p.PnmPolicyName).FirstOrDefault();
result.Payments =
context.InforceDetails.Where(inforceDetail => inforceDetail.InfPolicyNumber == id)
.OrderBy(inforceDetail => inforceDetail.IfdPaymentDate)
.ToList()
.Select(inforceDetail => string.Format("{0} - {1}", inforceDetail.IfdPaymentDate.ToString("MM/dd/yyyy"),
inforceDetail.IfdPaymentAmount.HasValue ? string.Format("{0:C}", inforceDetail.IfdPaymentAmount.Value) : ""))
.ToList();
result.Beneficiaries =
new List<string>
{
certificateEntity.InfBeneficiaryLine1,
certificateEntity.InfBeneficiaryLine2,
certificateEntity.InfBeneficiaryLine3,
certificateEntity.InfBeneficiaryLine4,
certificateEntity.InfBeneficiaryLine5,
certificateEntity.InfBeneficiaryLine6,
certificateEntity.InfBeneficiaryLine7,
certificateEntity.InfBeneficiaryLine8,
certificateEntity.InfBeneficiaryLine9,
certificateEntity.InfBeneficiaryLine10,
certificateEntity.InfBeneficiaryLine11
}.Where(x => !string.IsNullOrEmpty(x.Trim())).ToList();
}
return result;
}
public QueryResult<Certificate> SearchCertificates(string memberName, string certificateNumber, string insuredName, int currentPage, int itemsPerPage, string sortColumn = null, SortDirection sortDirection = SortDirection.Ascending)
{
var result = new QueryResult<Certificate>(currentPage, itemsPerPage);
using (var context = new GleanerDbContext())
{
// member filter
var inforces = from i in context.Inforces
join m in context.AspnetMembersCertificates on i.InfPolicyNumber equals m.InfPolicyNumber
where m.AmuUsername == memberName select i;
// certificate number filter
if (!String.IsNullOrEmpty(certificateNumber))
inforces = inforces.Where(inforce => inforce.InfPolicyNumber.ToLower().Contains(certificateNumber.ToLower()));
// get certificate data
var certificates = new List<Certificate>();
foreach (var inforce in inforces)
{
int arborNumber;
Int32.TryParse(inforce.ArbArborNumber, out arborNumber);
certificates.Add(new Certificate()
{
Number = inforce.InfPolicyNumber,
InsuredPersonName = context.InsuredDetails.Where(insured => insured.IndId == inforce.IndId)
.Select(insured => insured.IndInsuredFirstName + " " + insured.IndInsuredLastName).FirstOrDefault(),
ArborName = context.Arbors.Where(arbor => arbor.ArbArborNumber == arborNumber)
.Select(arbor => arbor.ArbArbor).FirstOrDefault(),
AgentName = String.IsNullOrEmpty(inforce.InfServicingAgentNumber) ? ""
: context.Agents.Where(agent => agent.AgentNumber == inforce.InfServicingAgentNumber)
.Select(agent => agent.AgentFirstName + " " + agent.AgentLastName).FirstOrDefault(),
ProductType = GetCertificateType(inforce.InfPolicyType, inforce.InfPolicyStatus)
});
}
// insured name filter
if (!String.IsNullOrEmpty(insuredName))
certificates = certificates.Where(cert => cert.InsuredPersonName.ToLower().Contains(insuredName.ToLower())).ToList();
result.TotalItems = certificates.Count;
// sort
var prop = typeof (Certificate).GetProperty(sortColumn ?? "Number");
if (prop != null)
{
certificates = (sortDirection == SortDirection.Ascending
? certificates.OrderBy(x => prop.GetValue(x, null))
: certificates.OrderByDescending(x => prop.GetValue(x, null))).ToList();
}
else certificates = certificates.OrderBy(x => x.Number).ToList();
// paging
certificates = certificates.Skip((currentPage - 1)*itemsPerPage).Take(itemsPerPage).ToList();
result.Items = certificates;
}
return result;
}
public bool MemberHasAccess(string memberName, string id)
{
using (var context = new GleanerDbContext())
{
return (from i in context.Inforces
join m in context.AspnetMembersCertificates on i.InfPolicyNumber equals m.InfPolicyNumber
where m.AmuUsername == memberName && i.InfPolicyNumber == id
select i).Any();
}
}
public ActionResult GetAgentsSearchResult(string locationSearchText, string agentSearchText)
{
object result;
try
{
UmbracoHelper umbracoHelper = new UmbracoHelper(UmbracoContext);
int agentsMediaFolderId = 1408;
IEnumerable<Agent> agentEntities;
if (!String.IsNullOrEmpty(locationSearchText))
{
agentEntities = new GleanerDbContext().Agents.Where(x => x.HomeCity.Contains(locationSearchText)
|| x.HomeState.Contains(locationSearchText)
|| x.HomeZip.Contains(locationSearchText));
}
else if (!String.IsNullOrEmpty(agentSearchText))
{
agentEntities = new GleanerDbContext().Agents.Where(x => x.AgentFirstName.Contains(agentSearchText)
|| x.AgentLastName.Contains(agentSearchText));
}
else
{
agentEntities = new GleanerDbContext().Agents;
}
IEnumerable<IPublishedContent> agentMedias = null;
var agents = agentEntities.OrderBy(y => y.AgentFirstName + " " + y.AgentLastName).Select(x =>
{
var address = String.Format("{0}, {1}, {2} {3}", x.HomeCity == null ? null : x.HomeCity.Trim(), x.HomeStreet == null ? null : x.HomeStreet.Trim(), x.HomeState, x.HomeZip);
if (agentMedias == null)
{
agentMedias = umbracoHelper.TypedMedia(agentsMediaFolderId).Children;
}
IPublishedContent media;
char firstLetter = String.IsNullOrEmpty(x.AgentLastName) ? '0' : x.AgentLastName[0];
if (firstLetter <= 60)
{
media = agentMedias.First();
}
else if (firstLetter <= 72)
{
media = agentMedias.ElementAt(1);
}
else
{
media = agentMedias.Last();
}
return new
{
name = x.AgentFirstName + " " + x.AgentLastName,
url = "/agents/agent/?agentId=" + x.AgentNumber,
LatLng = "33.095783,-96.746641".Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries),
direction = "",
AgentPhoto = media.Url,
address,
x.AgcAgencyName,
x.BusinessPhone1,
x.EmailAddress
};
}
);
result = new
{
success = true,
data = HtmlHelperExtension.SerializeToJson(null, agents, false).ToHtmlString()
};
}
catch (Exception ex)
{
result = new
{
error = true,
exception = ex.Message
};
}
return Json(result, JsonRequestBehavior.AllowGet);
}
public bool CertificateExists(string certNumber, string firstName, string lastName, string socSecNum)
{
using (var context = new GleanerDbContext())
{
var res = from x in context.OwnerDetails
join y in context.OwnerHeaders
on x.OwhId equals y.OwhId
where (String.Compare(x.InfPolicyNumber, certNumber) == 0 &&
String.Compare(x.OwdOwnerFirstName.Trim(), firstName.Trim()) == 0 &&
String.Compare(x.OwdOwnerLastName.Trim(), lastName.Trim()) == 0 &&
String.Compare(y.OwhLast4.Trim(), socSecNum.Trim()) == 0)
select new { x.OwhId, x.InfPolicyNumber, y.OwhLast4 };
return res.Any();
}
}
public OwnerService()
{
_db = new GleanerDbContext();
_db.Database.Log = s => Debug.WriteLine(s);
}
/// <summary>
/// Create or update agent
/// </summary>
/// <param name="agentView"></param>
/// <returns></returns>
public AgentView SaveAgent(AgentView agentView)
{
using (var context = new GleanerDbContext()) {
context.Database.Log = s => Debug.WriteLine(s);
var isNew = String.IsNullOrEmpty(agentView.AgentNumber);
if (isNew) {
throw new Exception("You can not create an agent!");
}
// get agent system data
var agentSystem = isNew ? new AgentSystem() : GetAgentSystemByKey(context, agentView.AgentNumber);
/*
// get agency
var agencies = context.Agencies.Where(x => x.AgcAgencyName == agentView.AgencyName);
if (!agencies.Any()) throw new Exception("Could not find an agency with name '" + agentView.AgencyName + "'.");
if (agencies.Count() > 2) throw new Exception("There are several agencies with the same name.");
var agencyId = agencies.First().AgcAgencyNumber;
// create agent record
var agent = agentView.ToAgent();
agent.AgcAgencyNumber = agencyId;
if (isNew)
{
// get new agent number
var isSuccess = 0;
var newAgentNumberResult = context.GetNewAgentNumber(agencyId, out isSuccess);
if (!newAgentNumberResult.Any()) throw new Exception("Could not create new agent number.");
var newAgentNumber = newAgentNumberResult.First().Value;
agent.AgentNumber = newAgentNumber;
agentSystem.AgentNumber = newAgentNumber;
agentSystem.IsActivated = false;
agentSystem.IsLocked = false;
agentSystem.IsDeleted = false;
agentSystem.ConfirmKey = String.Format("{0}{1}", Guid.NewGuid(), Guid.NewGuid()).Replace("-", "");
}
else
*/
{
agentSystem.IsActivated = agentView.IsActivated;
agentSystem.IsLocked = agentView.IsLocked;
agentSystem.UpdateDate = DateTime.Now;
}
agentSystem.UserName = agentView.UserName;
// create/update agent
// context.Agents.AddOrUpdate(agent);
context.AgentSystems.AddOrUpdate(agentSystem);
DbHelper.SaveChanges(context);
/*
if (isNew)
{
// Send the email for activation
umbraco.library.SendMail(_emailSender, agent.EmailAddress,
"Gleaner Agent Activation",
String.Format("<a href='http://gleaner.local/activation?agent={0}&activationKey={1}' target='_blank'>Activation link</a>", agentSystem.AgentNumber, agentSystem.ConfirmKey),
true);
}
*/
return GetAgent(agentView.AgentNumber);
}
}
/// <summary>
/// Reset password and send email to agent with new
/// </summary>
/// <param name="agentNumer"></param>
/// <param name="token"></param>
/// <param name="password"></param>
public void ResetPassword(string agentNumer, string token, string password)
{
if (String.IsNullOrEmpty(agentNumer)) {
throw new Exception("Agent number is not specified.");
}
if (String.IsNullOrEmpty(token)) {
throw new Exception("Security token is not specified.");
}
using (var context = new GleanerDbContext()) {
context.Database.Log = s => Debug.WriteLine(s);
var agentSystem = GetAgentSystemByKey(context, agentNumer);
if (agentSystem.ConfirmKey != token) {
throw new Exception("Wrong security token.");
}
if (agentSystem.UpdateDate < DateTime.Now.AddDays(-1)) {
throw new Exception("Security token is expired.");
}
agentSystem.Password = EncryptPassword(password);
agentSystem.ConfirmKey = "";
agentSystem.UpdateDate = DateTime.Now;
context.AgentSystems.AddOrUpdate(agentSystem);
DbHelper.SaveChanges(context);
/*
// Send the email with new password
umbraco.library.SendMail(_emailSender, agent.EmailAddress,
"Gleaner Agent Reset Password",
String.Format("New password: {0}", newPassword),
true);
*/
}
}
/// <summary>
/// Query for Reset Password
/// </summary>
/// <param name="agentNumer"></param>
/// <param name="emailMessage"></param>
public void QueryForResetPassword(string agentNumer, string emailMessage)
{
using (var context = new GleanerDbContext()) {
context.Database.Log = s => Debug.WriteLine(s);
var agentSystem = GetAgentSystemByKey(context, agentNumer);
var agent = context.Agents.First(x => x.AgentNumber == agentNumer);
if (String.IsNullOrEmpty(agent.EmailAddress)) {
throw new Exception("Agent has no email!");
}
agentSystem.ConfirmKey = String.Format("{0}{1}", Guid.NewGuid(), Guid.NewGuid()).Replace("-", "");
agentSystem.UpdateDate = DateTime.Now;
context.AgentSystems.AddOrUpdate(agentSystem);
DbHelper.SaveChanges(context);
// Send the email with link to password recovery page
umbraco.library.SendMail(_emailSender, agent.EmailAddress,
"Gleaner Agent Reset Password",
/*String.Format("<a href='{0}?agent={1}&token={2}' target='_blank'>Reset Password</a>", resetPageUrl, agentSystem.AgentNumber, agentSystem.ConfirmKey),*/
emailMessage,
true);
}
}
/// <summary>
/// Lock/Unlock agent
/// </summary>
/// <param name="agentNumer"></param>
/// <param name="lockAgent"></param>
public void Lock(string agentNumer, bool lockAgent)
{
using (var context = new GleanerDbContext()) {
context.Database.Log = s => Debug.WriteLine(s);
var agentSystem = GetAgentSystemByKey(context, agentNumer);
if (agentSystem.IsLocked && lockAgent) {
throw new Exception("Agent is already locked.");
}
if (!agentSystem.IsLocked && !lockAgent) {
throw new Exception("Agent is already unlocked.");
}
agentSystem.IsLocked = lockAgent;
agentSystem.UpdateDate = DateTime.Now;
context.AgentSystems.AddOrUpdate(agentSystem);
DbHelper.SaveChanges(context);
}
}
public ActionResult GetAgentsSearchResult(string locationSearchText, string agentSearchText)
{
object result;
try
{
var agentIdsForLocation = new List<int>();
UmbracoHelper umbracoHelper = new UmbracoHelper(UmbracoContext);
int agentsMediaFolderId = 1408;
IEnumerable<Agent> agentEntities;
if (!String.IsNullOrEmpty(locationSearchText))
{
agentEntities = new GleanerDbContext().Agents.Where(x => x.HomeCity.Contains(locationSearchText)
|| x.HomeState.Contains(locationSearchText)
|| x.HomeZip.Contains(locationSearchText));
}
else if (!String.IsNullOrEmpty(agentSearchText))
{
agentEntities = new GleanerDbContext().Agents.Where(x => x.AgentFirstName.Contains(agentSearchText)
|| x.AgentLastName.Contains(agentSearchText));
}
else
{
agentEntities = new GleanerDbContext().Agents;
}
var agentsPage = Umbraco.TypedContentSingleAtXPath("/root/Homepage/Agents");
var noPhotoMedia = agentsPage == null ? null : Umbraco.TypedMedia(agentsPage.GetPropertyValue<int>("noPhoto"));
var noPhotoUrl = noPhotoMedia == null ? null : noPhotoMedia.Url;
var ms = Services.MemberService;
var agentMuUsernames = Roles.GetUsersInRole("agents");
var agentUms = agentMuUsernames.Select(x => ms.GetByUsername(x));
var agentUmsByAgentNumbers = new Dictionary<string, IMember>();
foreach (var agentUm in agentUms)
{
var agentNumber = agentUm.GetValue<string>("agentNumber");
if (String.IsNullOrEmpty(agentNumber)) continue;
agentUmsByAgentNumbers[agentNumber] = agentUm;
}
var agents = agentEntities.OrderBy(y => y.AgentFirstName + " " + y.AgentLastName).Select(x =>
{
string coords;
string photoUrl = noPhotoUrl;
var address = MakeAgentAddress(x.HomeStreet, x.HomeCity, x.HomeState, x.HomeZip);
IMember agentMember;
if (agentUmsByAgentNumbers.TryGetValue(x.AgentNumber, out agentMember))
{
var agentCoords = agentMember.GetValue<string>("gmapsCoords");
var agentMemberAddress = agentMember.GetValue<string>("address");
if (String.IsNullOrEmpty(agentCoords) || agentMemberAddress != address)
{
//agent member doesn't have coords or have his address changed since last coords retrieval
agentIdsForLocation.Add(agentMember.Id);
}
coords = agentMember.GetValue<string>("gmapsCoords");
if (coords == null) coords = String.Empty;
var photoMedia = Umbraco.TypedMedia(agentMember.GetValue<int>("photo"));
if (photoMedia != null) photoUrl = photoMedia.Url;
if (String.IsNullOrEmpty(photoUrl)) photoUrl = noPhotoUrl;
}
else
{
coords = String.Empty;
}
return new
{
id = x.AgentNumber,
name = x.AgentFirstName + " " + x.AgentLastName,
url = "/agents/agent/?id=" + x.AgentNumber,
LatLng = coords.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries),
direction = "",
AgentPhoto = photoUrl,
address,
x.AgcAgencyName,
x.BusinessPhone1,
x.EmailAddress
};
});
result = new
{
success = true,
data = HtmlHelperExtension.SerializeToJson(null, agents, false).ToHtmlString()
};
AgentLocationsController.AppendAgentIdsForLocation(agentIdsForLocation);
}
catch (Exception ex)
{
result = new
{
error = true,
exception = ex.Message
};
}
return Json(result, JsonRequestBehavior.AllowGet);
}
