/// <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); }