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