public void UpdateRecentContact(string householdId, string investorid) { var executeResult = _dbWrapper.ExecuteScalar(cmd => { cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "PKG_CRM.UPDATERECENTCONTACTS"; DatabaseWrapperHelper.AddInIntParameter(cmd, "A_NBROKER_ID", _context.Identity.BrokerId); DatabaseWrapperHelper.AddInIntParameter(cmd, "A_NHOUSEHOLD_ID", householdId); DatabaseWrapperHelper.AddInIntParameter(cmd, "A_NINVESTOR_ID", investorid); DatabaseWrapperHelper.AddOutIntParameter(cmd, "A_UTSTATUS"); DatabaseWrapperHelper.AddOutStringParameter(cmd, "A_UTSTATUSMSG", 2000); }, _context.Identity.InstitutionId); }
public List <ProposalsModel> GetRecentProposals() { List <ProfileModelInfo> profileModelInfo; List <string> planIds = new List <string>(); string planTypes = string.Join(",", Enum.GetValues(typeof(planTypeList)).Cast <int>()); List <ProposalsModel> proposalList = new List <ProposalsModel>(); IEnumerable <IDataRecord> records = _dbWrapper.QueryDataRecord(cmd => { cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "PKG_PG.GETRECENTPROPOSALS"; DatabaseWrapperHelper.AddInIntParameter(cmd, "A_NBROKER_ID", _context.Identity.BrokerId); DatabaseWrapperHelper.AddInStringParameter(cmd, "A_VPLANTYPELIST", planTypes); DatabaseWrapperHelper.AddOutCursorParameter(cmd, "A_RCPROPOSALLIST"); DatabaseWrapperHelper.AddOutIntParameter(cmd, "A_UTSTATUS"); DatabaseWrapperHelper.AddOutStringParameter(cmd, "A_UTSTATUSMSG", 2000); }, _context.Identity.InstitutionId); foreach (var row in records) { ProposalsModel proposal = new ProposalsModel(); proposal.ProposalId = row["PLAN_ID"].ToString(); proposal.ProposalName = (!string.IsNullOrEmpty(row["PLAN_NAME"].ToString())) ? row["PLAN_NAME"].ToString() : string.Empty; proposal.PartyID = row["PARTY_ID"].ToString(); proposal.PartyName = row["PARTY_NAME"].ToString(); proposal.PartyType = row["PARTY_TYPE_NAME"].ToString(); proposal.LastModifiedDate = row["ACCESS_DATE"].ToString(); proposal.IsEntitle = row["FULL_ENT"].ToString(); proposal.RowNum = Int32.Parse(row["RN"].ToString()); proposal.PlanTypeId = row["PLAN_TYPE_ID"].ToString(); proposal.ProgramName = row["PROGRAM_NAME"].ToString(); proposal.IsNoPartialEntitlement = row["NO_ENT"].ToString(); proposal.ModelMinorVersion = row["MODEL_MINOR_VERSION"].ToString(); proposalList.Add(proposal); } foreach (ProposalsModel proposals in proposalList) { planIds.Add(proposals.ProposalId); } profileModelInfo = GetProfileModelData(planIds); foreach (ProfileModelInfo infoList in profileModelInfo) { int index = proposalList.FindIndex(0, proposalList.Count, proposal => proposal.ProposalId == infoList.PlanId); if (index != -1) { proposalList[index].RiskFactor = string.IsNullOrEmpty(infoList.ProfileName) ? "-" : infoList.ProfileName; proposalList[index].ModelIncluded = string.IsNullOrEmpty(infoList.ModelName) ? "No Model" : infoList.ModelName; } } return(proposalList); }
public List <Household> SearchAllContacts(SearchParameters searchParameters) { List <Household> resultList = new List <Household>(); IEnumerable <IDataRecord> records = _dbWrapper.QueryDataRecord(cmd => { cmd.CommandType = CommandType.StoredProcedure; if (searchParameters.Searchby == SearchBy.ACCOUNTNUMBER) { cmd.CommandText = SqlConstants.GET_ALL_CONTACTSBY_ACCOUNTNO; DatabaseWrapperHelper.AddInStringParameter(cmd, "A_VACCTNO", HandleSpecialCharacters(searchParameters.AccountNumber)); } else if (searchParameters.Searchby == SearchBy.REPCODE) { cmd.CommandText = SqlConstants.GET_ALL_CONTACTSBY_REPCODE; DatabaseWrapperHelper.AddInStringParameter(cmd, "A_VREPCODE", HandleSpecialCharacters(searchParameters.RepCode)); } else //default SearchBy DEMOGRAPHICS { cmd.CommandText = SqlConstants.GET_ALL_CONTACTSBY_DEMOGRAPHICS; SetDemographicParameters(cmd, searchParameters); } //CommonParameters SetCommonParameters(cmd, searchParameters); //Output Parameters DatabaseWrapperHelper.AddOutCursorParameter(cmd, "A_RCCONTACTLIST"); DatabaseWrapperHelper.AddOutIntParameter(cmd, "A_UTSTATUS"); DatabaseWrapperHelper.AddOutStringParameter(cmd, "A_UTSTATUSMSG", 2000); }, _context.Identity.InstitutionId); foreach (var row in records) { Household result = new Household(); result.HouseholdID = row["HOUSEHOLD_ID"].ToString(); if (!String.IsNullOrEmpty(result.HouseholdID) && Convert.ToInt32(result.HouseholdID) > 0 && !resultList.Any(p => p.HouseholdID == result.HouseholdID)) { result.Type = row["TYPE"].ToString(); //Get Contact Details result.Persons = GetContactsForHousehold(result.HouseholdID); resultList.Add(result); } } return(resultList); }
public List <ProposalsModel> SearchProposals(ProposalSearchParameters searchParameters) { List <ProfileModelInfo> profileModelInfo; List <string> planIdList = new List <string>(); List <ProposalsModel> proposalList = new List <ProposalsModel>(); IEnumerable <IDataRecord> records = _dbWrapper.QueryDataRecord(cmd => { cmd.CommandType = CommandType.StoredProcedure; if (searchParameters.Searchby == SearchBy.ACCOUNTNUMBER) { cmd.CommandText = "PKG_PG.GETINVPROPOSAL_BY_ACCTNO";//: "PKG_PG.GETPROSPPROPOSAL_BY_ACCTNO"; DatabaseWrapperHelper.AddInLongParameter(cmd, "A_NBROKER_ID", _context.Identity.BrokerId); DatabaseWrapperHelper.AddInIntParameter(cmd, "A_NSTARTROW", "1"); DatabaseWrapperHelper.AddInIntParameter(cmd, "A_NCOUNT", searchParameters.Count.ToString()); DatabaseWrapperHelper.AddInStringParameter(cmd, "A_NSORTBYCOL", ""); DatabaseWrapperHelper.AddInStringParameter(cmd, "A_VASC_YN", "Y"); DatabaseWrapperHelper.AddInStringParameter(cmd, "A_VTOTCNTONLY_YN", "N"); DatabaseWrapperHelper.AddInStringParameter(cmd, "A_VPLANTYPELIST", "14,15,17"); DatabaseWrapperHelper.AddInStringParameter(cmd, "A_VACCTNO", HandleSpecialCharacters(searchParameters.AccountNumber)); DatabaseWrapperHelper.AddOutCursorParameter(cmd, "A_RCPROPOSALLIST"); DatabaseWrapperHelper.AddOutIntParameter(cmd, "A_UTSTATUS"); DatabaseWrapperHelper.AddOutStringParameter(cmd, "A_UTSTATUSMSG", 2000); } else { cmd.CommandText = "PKG_PG.GETINVPROPOSAL"; //"PKG_PG.GETPROSPPROPOSAL" DatabaseWrapperHelper.AddInLongParameter(cmd, "A_NBROKER_ID", _context.Identity.BrokerId); DatabaseWrapperHelper.AddInIntParameter(cmd, "A_NSTARTROW", "1"); DatabaseWrapperHelper.AddInIntParameter(cmd, "A_NCOUNT", searchParameters.Count.ToString()); DatabaseWrapperHelper.AddInStringParameter(cmd, "A_NSORTBYCOL", ""); DatabaseWrapperHelper.AddInStringParameter(cmd, "A_VASC_YN", "Y"); DatabaseWrapperHelper.AddInStringParameter(cmd, "A_VTOTCNTONLY_YN", "N"); DatabaseWrapperHelper.AddInStringParameter(cmd, "A_VPLANTYPELIST", "14,15,17"); DatabaseWrapperHelper.AddInStringParameter(cmd, "A_VLNAME", ConvertSpecialCharacters(searchParameters.LastName)); DatabaseWrapperHelper.AddInStringParameter(cmd, "A_VFNAME", ConvertSpecialCharacters(searchParameters.FirstName)); DatabaseWrapperHelper.AddInStringParameter(cmd, "A_VSSN_TIN", string.Empty); DatabaseWrapperHelper.AddInStringParameter(cmd, "A_VCITY", ""); DatabaseWrapperHelper.AddInStringParameter(cmd, "A_VSTATE", ""); DatabaseWrapperHelper.AddInStringParameter(cmd, "A_VZIP", ""); DatabaseWrapperHelper.AddOutCursorParameter(cmd, "A_RCPROPOSALLIST"); DatabaseWrapperHelper.AddOutIntParameter(cmd, "A_UTSTATUS"); DatabaseWrapperHelper.AddOutStringParameter(cmd, "A_UTSTATUSMSG", 2000); } }, _context.Identity.InstitutionId); foreach (var row in records) { ProposalsModel proposal = new ProposalsModel(); proposal.ProposalId = row["PLAN_ID"].ToString(); proposal.ProposalName = (!string.IsNullOrEmpty(row["PLAN_NAME"].ToString())) ? row["PLAN_NAME"].ToString() : string.Empty; proposal.PartyID = row["PARTY_ID"].ToString(); proposal.PartyName = row["PARTY_NAME"].ToString(); proposal.PartyType = row["PARTY_TYPE_NAME"].ToString(); proposal.LastModifiedDate = row["LAST_MODIFIED_DATE"].ToString(); proposal.IsEntitle = row["FULL_ENT"].ToString(); proposal.RowNum = Int32.Parse(row["RN"].ToString()); proposal.PlanTypeId = row["PLAN_TYPE_ID"].ToString(); proposal.ProgramName = row["PROGRAM_NAME"].ToString(); proposal.IsNoPartialEntitlement = row["NO_ENT"].ToString(); proposal.ModelMinorVersion = row["MODEL_MINOR_VERSION"].ToString(); proposalList.Add(proposal); } foreach (ProposalsModel proposals in proposalList) { planIdList.Add(proposals.ProposalId); } profileModelInfo = GetProfileModelData(planIdList); foreach (ProfileModelInfo infoList in profileModelInfo) { int index = proposalList.FindIndex(0, proposalList.Count, proposal => proposal.ProposalId == infoList.PlanId); if (index != -1) { proposalList[index].RiskFactor = string.IsNullOrEmpty(infoList.ProfileName) ? "-" : infoList.ProfileName; proposalList[index].ModelIncluded = string.IsNullOrEmpty(infoList.ModelName) ? "No Model" : infoList.ModelName; } } return(proposalList); }