public List <TemplateReport> GetTemplateReportList(int ProductModuleID)
{
List <TemplateReport> TemplateReports = new List <TemplateReport>();
IEnumerable <IDataRecord> records = _dbWrapper.QueryDataRecord(cmd =>
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = SqlConstants.READ_REPORT_TEMPLATE_LIST;
DatabaseWrapperHelper.AddInStringParameter(cmd, "INSTITUTION_ID", _context.Identity.InstitutionId);
DatabaseWrapperHelper.AddInIntParameter(cmd, "PRODUCTMODULE_ID", ProductModuleID.ToString());
}, _context.Identity.InstitutionId);
foreach (var row in records)
{
TemplateReport TemplateReport = new TemplateReport();
//SELECT WRT.REPORT_TEMPLATE_ID,WRTR.REPORT_ID ,WRTR.REPORT_TYPE_ID, WRTR.INCLUDE_YN,WRTR.DISPLAY_OPTION
TemplateReport.ReportId = row["REPORT_ID"] is DBNull ? 0 : Convert.ToInt32(row["REPORT_ID"]);
TemplateReport.TemplateId = row["REPORT_TEMPLATE_ID"] is DBNull ? 0 : Convert.ToInt32(row["REPORT_TEMPLATE_ID"]);
TemplateReport.TypeId = row["REPORT_TYPE_ID"] is DBNull ? 0 : Convert.ToInt32(row["REPORT_TYPE_ID"]);
TemplateReport.IncludedYN = row["INCLUDE_YN"].ToString();
TemplateReport.DisplayOption = row["DISPLAY_OPTION"].ToString();
TemplateReports.Add(TemplateReport);
}
return(TemplateReports);
}
public bool UpdateContact(Contact contact)
{
int executeResult = _dbWrapper.Execute(cmd =>
{
cmd.CommandText = SqlConstants.UPDATE_CONTACT;
DatabaseWrapperHelper.AddInLongParameter(cmd, "INVESTOR_ID", contact.InvestorId);
DatabaseWrapperHelper.AddInStringParameter(cmd, "FIRST_NAME", contact.FirstName);
DatabaseWrapperHelper.AddInStringParameter(cmd, "MID_INITIAL", contact.MiddleName);
DatabaseWrapperHelper.AddInStringParameter(cmd, "LAST_NAME", contact.LastName);
//Home and Mail correspondence
DatabaseWrapperHelper.AddInStringParameter(cmd, ":EMAIL_ADDR", contact.PersonalEmail);
//Home and Mail Address
DatabaseWrapperHelper.AddInStringParameter(cmd, "HOME_ADDR1", contact.AddressLine1);
DatabaseWrapperHelper.AddInStringParameter(cmd, "HOME_ADDR2", contact.AddressLine2);
DatabaseWrapperHelper.AddInStringParameter(cmd, "HOME_CITY", contact.City);
DatabaseWrapperHelper.AddInStringParameter(cmd, "HOME_STATE", contact.State);
DatabaseWrapperHelper.AddInStringParameter(cmd, "HOME_POSTAL_CODE", contact.Zip);
}, _context.Identity.InstitutionId);
if (executeResult > 0)
{
return(true);
}
return(false);
}
private List <ReportInfo> GetInstiutionReportList(int ProductId)
{
List <ReportInfo> reportInfos = new List <ReportInfo>();
IEnumerable <IDataRecord> records = _dbWrapper.QueryDataRecord(cmd =>
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = SqlConstants.READ_REPORT_LIST;
DatabaseWrapperHelper.AddInStringParameter(cmd, "INSTITUTION_ID", _context.Identity.InstitutionId);
DatabaseWrapperHelper.AddInIntParameter(cmd, "PRODUCT_ID", ProductId.ToString());
}, _context.Identity.InstitutionId);
foreach (var row in records)
{
ReportInfo reportInfo = new ReportInfo();
reportInfo.UiSeq = row["INST_REPORT_SEQ"] is DBNull ? 0 : Convert.ToInt32(row["INST_REPORT_SEQ"]);
reportInfo.PrintSeq = row["INST_PRINT_SEQ"] is DBNull ? 0 : Convert.ToInt32(row["INST_PRINT_SEQ"]);
reportInfo.UsageCode = row["INST_USAGE_CODE"].ToString();
reportInfo.TypeId = row["REPORT_TYPE_ID"] is DBNull ? 0 : Convert.ToInt32(row["REPORT_TYPE_ID"]);
reportInfo.TypeName = row["REPORT_TYPE_NAME"].ToString();
reportInfo.ReportId = row["REPORT_ID"] is DBNull ? 0 : Convert.ToInt32(row["REPORT_ID"]);
reportInfo.ReportName = row["REPORT_NAME"].ToString();
reportInfo.WorkflowTypeId = row["WORKFLOW_TYPE_ID"] is DBNull ? 0 : Convert.ToInt32(row["WORKFLOW_TYPE_ID"]);
reportInfos.Add(reportInfo);
}
return(reportInfos);
}
private void SetDemographicParameters(IDbCommand cmd, SearchParameters searchParameters)
{
DatabaseWrapperHelper.AddInStringParameter(cmd, "A_VLNAME", HandleSpecialCharacters(searchParameters.LastName));
DatabaseWrapperHelper.AddInStringParameter(cmd, "A_VFNAME", HandleSpecialCharacters(searchParameters.FirstName));
DatabaseWrapperHelper.AddInStringParameter(cmd, "A_VSSN_TIN", string.Empty);
DatabaseWrapperHelper.AddInStringParameter(cmd, "A_VCITY", HandleSpecialCharacters(searchParameters.City));
DatabaseWrapperHelper.AddInStringParameter(cmd, "A_VSTATE", HandleSpecialCharacters(searchParameters.State));
DatabaseWrapperHelper.AddInStringParameter(cmd, "A_VZIP", HandleSpecialCharacters(searchParameters.Zip));
DatabaseWrapperHelper.AddInStringParameter(cmd, "a_HouseholdLevel", "Y");
}
private void SetCommonParameters(IDbCommand cmd, SearchParameters searchParameters)
{
DatabaseWrapperHelper.AddInLongParameter(cmd, "A_NBROKER_ID", _context.Identity.BrokerId);
DatabaseWrapperHelper.AddInIntParameter(cmd, "A_NSTARTROW", searchParameters.StartRow);
DatabaseWrapperHelper.AddInIntParameter(cmd, "A_NCOUNT", searchParameters.Count.ToString());
DatabaseWrapperHelper.AddInStringParameter(cmd, "A_NSORTBYCOL", "");
DatabaseWrapperHelper.AddInStringParameter(cmd, "A_VASC_YN", (searchParameters.IsSortAsc) ? "Y" : "N");
DatabaseWrapperHelper.AddInStringParameter(cmd, "A_VTOTCNTONLY_YN", "N");
DatabaseWrapperHelper.AddInStringParameter(cmd, "A_SUPPACCT", "Y");
}
private string GetSequencePattern(string tableName, string recordType)
{
string seq = _dbWrapper.ExecuteScalar(cmd =>
{
cmd.CommandText = SqlConstants.GET_SEQUENCE_PORTFOLIO;
DatabaseWrapperHelper.AddInStringParameter(cmd, ":Pi_Inst_ID", _context.Identity.InstitutionId.ToString());
DatabaseWrapperHelper.AddInStringParameter(cmd, ":Pi_Table_Name", tableName);
DatabaseWrapperHelper.AddInStringParameter(cmd, ":Pi_Record_Type", recordType);
}, _context.Identity.InstitutionId).ToString();
return(seq);
}
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);
}
//CreateHouseholdForTeams
private Household CreateHousehold(List <Contact> contacts)
{
Household result = new Household();
Dictionary <long, long> contactDic = new Dictionary <long, long>();
string hhName = "Client Household";
//Get next householdid
long householdId = Convert.ToInt64(_dbWrapper.ExecuteScalar(cmd =>
{
cmd.CommandText = SqlConstants.GET_NEXT_SEQUENCE_HOUSEHOLDID;
}, _context.Identity.InstitutionId));
//calcualte the household name from the primary contact details
foreach (Contact cp in contacts)
{
if (cp.RelationShipType == RelationShipType.HEAD)
{
hhName = cp.FirstName + " " + cp.LastName;
//name = hhName.Trim();
break;
}
}
var executeResult = _dbWrapper.Execute(cmd =>
{
cmd.CommandText = SqlConstants.CREATE_HOUSEHOLD;
DatabaseWrapperHelper.AddInIntParameter(cmd, "householdId", householdId.ToString());
DatabaseWrapperHelper.AddInStringParameter(cmd, "householdName", hhName);
DatabaseWrapperHelper.AddInIntParameter(cmd, "householdTypeId", ((int)HouseholdType.PROSPECT).ToString());
}, _context.Identity.InstitutionId);
// insert into web_investor
foreach (Contact contact in contacts)
{
CreateContact(contact, householdId);
}
result.HouseholdID = householdId.ToString();
result.Name = hhName;
result.Type = "PROSPECT";
result.Persons = contacts;
return(result);
}
public void UpdateTeamsForHousehold(string householdId, List <Team> listOfTeam)
{
if (listOfTeam != null && listOfTeam.Count > 0)
{
List <Team> finalHHTeams = CalculateListOfTeams(householdId, listOfTeam);
List <Action <IDbCommand> > configureCommandList = new List <Action <IDbCommand> >();
configureCommandList.Add(new Action <IDbCommand>(cmd =>
{
cmd.CommandText = SqlConstants.DELETE_TeamEntitlement;
DatabaseWrapperHelper.AddInStringParameter(cmd, "householdId", householdId);
}));
bool containsPrimary = false;
foreach (Team team in listOfTeam)
{
if (team.Role == Team.TeamRole.Primary && (int)team.AssignType != 0)
{
if (!containsPrimary)
{
containsPrimary = true;
}
else
{
team.Role = Team.TeamRole.Secondary;
}
}
configureCommandList.Add(new Action <IDbCommand>(cmd =>
{
cmd.CommandText = team.AssignType != Team.TeamAssignmentType.UnKnown ? SqlConstants.CREATE_TEAM_ENTITLEMENT : SqlConstants.CREATE_TEAM_ENTITLEMENT_ASSIGNTYPE_0;
DatabaseWrapperHelper.AddInStringParameter(cmd, "teamId", team.TeamId);
DatabaseWrapperHelper.AddInStringParameter(cmd, "householdId", householdId);
if (team.AssignType != Team.TeamAssignmentType.UnKnown)
{
DatabaseWrapperHelper.AddInStringParameter(cmd, "teamRole", ((int)team.Role).ToString());
DatabaseWrapperHelper.AddInStringParameter(cmd, "teamAssignmentType", ((int)team.AssignType).ToString());
}
}));
}
bool result = _dbWrapper.ExecuteBatch(configureCommandList.ToArray(), _context.Identity.InstitutionId);
}
}
public bool CreateContact(Contact newCt, long householdId)
{
int investorId = Convert.ToInt32(_dbWrapper.ExecuteScalar(cmd =>
{
cmd.CommandText = SqlConstants.GET_NEXT_SEQUENCE_INVESTORID;
}, _context.Identity.InstitutionId));
var executeResult = _dbWrapper.Execute(cmd =>
{
cmd.CommandText = SqlConstants.CREATE_WEB_USER;
DatabaseWrapperHelper.AddInLongParameter(cmd, "INVESTOR_ID", investorId.ToString());
DatabaseWrapperHelper.AddInLongParameter(cmd, "INSTITUTION_ID", _context.Identity.InstitutionId);
DatabaseWrapperHelper.AddInStringParameter(cmd, "USERNAME", string.Empty);
DatabaseWrapperHelper.AddInStringParameter(cmd, "PASSWORD", string.Empty);
DatabaseWrapperHelper.AddInStringParameter(cmd, "PASSWORD_HINT", string.Empty);
DatabaseWrapperHelper.AddInDateTimeParameter(cmd, "CREATE_DATE", DateTime.Now.Date);
DatabaseWrapperHelper.AddInStringParameter(cmd, "STATUS", string.Empty);
DatabaseWrapperHelper.AddInStringParameter(cmd, "USER_TYPE_ID", "2");
DatabaseWrapperHelper.AddInStringParameter(cmd, "INSTITUTION_ID_LOGIN", string.Empty);
}, _context.Identity.InstitutionId);
executeResult = _dbWrapper.Execute(cmd =>
{
cmd.CommandText = SqlConstants.CREATE_WEB_INVESTOR;
DatabaseWrapperHelper.AddInLongParameter(cmd, "INVESTOR_ID", investorId.ToString());
DatabaseWrapperHelper.AddInStringParameter(cmd, "PREFIX", String.Empty);
DatabaseWrapperHelper.AddInStringParameter(cmd, "SUFFIX", String.Empty);
DatabaseWrapperHelper.AddInStringParameter(cmd, "FIRST_NAME", newCt.FirstName);
DatabaseWrapperHelper.AddInStringParameter(cmd, "MID_INITIAL", newCt.MiddleName);
DatabaseWrapperHelper.AddInStringParameter(cmd, "LAST_NAME", newCt.LastName);
DatabaseWrapperHelper.AddInStringParameter(cmd, "BIRTHDATE", string.Empty);
DatabaseWrapperHelper.AddInStringParameter(cmd, "SSN_TIN", string.Empty);
DatabaseWrapperHelper.AddInStringParameter(cmd, "MARK_DELETED_YN", "N");
DatabaseWrapperHelper.AddInStringParameter(cmd, "GENDER", string.Empty);
DatabaseWrapperHelper.AddInStringParameter(cmd, "INVESTOR_NUM", investorId.ToString());
DatabaseWrapperHelper.AddInDateTimeParameter(cmd, "CREATE_DATE", DateTime.Now.Date);
DatabaseWrapperHelper.AddInDateTimeParameter(cmd, "LAST_ACCESS_DATE", DateTime.Now.Date);
DatabaseWrapperHelper.AddInStringParameter(cmd, "MARITAL_STATUS", string.Empty);
DatabaseWrapperHelper.AddInStringParameter(cmd, "SAME_AS_MAIL_YN", string.Empty);
//Home and Mail correspondence
DatabaseWrapperHelper.AddInStringParameter(cmd, "HOME_PHONE", newCt.HomePhone);
DatabaseWrapperHelper.AddInStringParameter(cmd, "FAX", string.Empty);
DatabaseWrapperHelper.AddInStringParameter(cmd, ":EMAIL_ADDR", newCt.PersonalEmail);
DatabaseWrapperHelper.AddInStringParameter(cmd, ":PHONE_MOBILE", string.Empty);
DatabaseWrapperHelper.AddInStringParameter(cmd, ":RELATIONSHIP", string.Empty);
//Home and Mail Address
DatabaseWrapperHelper.AddInStringParameter(cmd, "HOME_ADDR1", string.Empty);
DatabaseWrapperHelper.AddInStringParameter(cmd, "HOME_ADDR2", string.Empty);
DatabaseWrapperHelper.AddInStringParameter(cmd, "HOME_CITY", string.Empty);
DatabaseWrapperHelper.AddInStringParameter(cmd, "HOME_STATE", string.Empty);
DatabaseWrapperHelper.AddInStringParameter(cmd, "HOME_POSTAL_CODE", string.Empty);
DatabaseWrapperHelper.AddInStringParameter(cmd, "HOME_COUNTRY", string.Empty);
DatabaseWrapperHelper.AddInStringParameter(cmd, "MAIL_ADDR1", newCt.AddressLine1);
DatabaseWrapperHelper.AddInStringParameter(cmd, "MAIL_ADDR2", newCt.AddressLine2);
DatabaseWrapperHelper.AddInStringParameter(cmd, "MAIL_CITY", newCt.City);
DatabaseWrapperHelper.AddInStringParameter(cmd, "MAIL_STATE", newCt.State);
DatabaseWrapperHelper.AddInStringParameter(cmd, "MAIL_POSTAL_CODE", newCt.Zip);
DatabaseWrapperHelper.AddInStringParameter(cmd, "MAIL_COUNTRY", newCt.Country);
}, _context.Identity.InstitutionId);
executeResult = _dbWrapper.Execute(cmd =>
{
cmd.CommandText = SqlConstants.CREATE_HOUSEHOLD_MEMBER;
DatabaseWrapperHelper.AddInLongParameter(cmd, "HOUSEHOLD_ID", householdId.ToString());
DatabaseWrapperHelper.AddInLongParameter(cmd, "INVESTOR_ID", investorId.ToString());
DatabaseWrapperHelper.AddInIntParameter(cmd, "RELATIONSHIP_TYPE_ID", ((int)newCt.RelationShipType).ToString());
DatabaseWrapperHelper.AddInStringParameter(cmd, "DEFAULT_ACCOUNT_ID", string.Empty);
}, _context.Identity.InstitutionId);
newCt.InvestorId = investorId.ToString();
if (executeResult > 0)
{
return(true);
}
return(false);
}
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);
}
public long CreateNewProposal(string houseHoldID, string proposalName)
{
long planId = -1;
long solutionID = -1;
string portfolioID = "-1";
long defaultPropAClassLevelId = -1;
long processID = -1;
long processLogID = -1;
long investorID = -1;
defaultPropAClassLevelId = Convert.ToInt64(_dbWrapper.ExecuteScalar(cmd =>
{
cmd.CommandText = SqlConstants.GET_DEFAULT_ASSETCLASS_VIEW_ID;;
DatabaseWrapperHelper.AddInLongParameter(cmd, ":GROUP_ID", _context.Identity.GroupId);
}, _context.Identity.InstitutionId));
solutionID = Convert.ToInt64(_dbWrapper.ExecuteScalar(cmd =>
{
cmd.CommandText = SqlConstants.GET_SEQUENCE_SOLUTION;
}, _context.Identity.InstitutionId));
//Create PlanID
planId = Convert.ToInt64(_dbWrapper.ExecuteScalar(cmd =>
{
cmd.CommandText = SqlConstants.GET_SEQUENCE_INVESTMENT_PLAN;
}, _context.Identity.InstitutionId));
//Create PlanID
processID = Convert.ToInt64(_dbWrapper.ExecuteScalar(cmd =>
{
cmd.CommandText = SqlConstants.GET_SEQUENCE_PROCESS;
}, _context.Identity.InstitutionId));
processLogID = Convert.ToInt64(_dbWrapper.ExecuteScalar(cmd =>
{
cmd.CommandText = SqlConstants.GET_SEQUENCE_PROCESS_LOG;
}, _context.Identity.InstitutionId));
portfolioID = GetSequencePattern("WEB_PORTFOLIO_PRODUCT", "");
//Get the default profile and Model Ids
SortedList <int, string> _list = GetDefaultProfileAndModelId();
IEnumerator <KeyValuePair <int, string> > itVal = _list.GetEnumerator();
int profileid = -1;
string modelId = string.Empty;
string defaultProgramId = string.Empty;;
string defaultProposalPath = string.Empty;
while (itVal.MoveNext())
{
profileid = Int32.Parse(itVal.Current.Key.ToString());
modelId = itVal.Current.Value.ToString();
}
//Need to revisit
//ProposalPathCollection proposalPaths = new ProposalPathCollection();
//proposalPaths = GetProposalList(context);
//string defaultProposalPath = (from proposalPath in proposalPaths.List
// where proposalPath.IsDefault == true
// select proposalPath.ProposalPathId).FirstOrDefault();
//while (itVal.MoveNext())
//{
// profileid = Int32.Parse(itVal.Key.ToString());
// modelId = itVal.Value.ToString();
// if (!string.IsNullOrEmpty(modelId) && defaultProposalPath.ToUpper() != ProposalPathType.NO.ToString() && defaultProposalPath.ToUpper() != ProposalPathType.YES.ToString())
// defaultProgramId = GetPortfolioProgramId(modelId, context);
//}
List <Action <IDbCommand> > configureCommandList = new List <Action <IDbCommand> >();
if (solutionID != -1)
{
configureCommandList.Add(new Action <IDbCommand>(cmd =>
{
cmd.CommandText = SqlConstants.CREATE_SOLUTION;
DatabaseWrapperHelper.AddInStringParameter(cmd, "SOLUTION_ID", solutionID.ToString());
DatabaseWrapperHelper.AddInStringParameter(cmd, "NAME", "");
DatabaseWrapperHelper.AddInStringParameter(cmd, "DESCR", "");
DatabaseWrapperHelper.AddInStringParameter(cmd, "CREATED_BY", _context.Identity.BrokerId);
}));
//Create WEB_SOLUTION_X_PARTY
int.TryParse(houseHoldID, out int householdID);
if (householdID > 0)
{
//Get InvestorID
investorID = Convert.ToInt64(_dbWrapper.ExecuteScalar(cmd =>
{
cmd.CommandText = SqlConstants.GET_INVESTOR_ID;
DatabaseWrapperHelper.AddInIntParameter(cmd, ":HOUSEHOLD_ID", householdID.ToString());
}, _context.Identity.InstitutionId));
configureCommandList.Add(new Action <IDbCommand>(cmd =>
{
cmd.CommandText = SqlConstants.CREATE_SOLUTION_PARTY;
DatabaseWrapperHelper.AddInStringParameter(cmd, "SOLUTION_ID", solutionID.ToString());
DatabaseWrapperHelper.AddInStringParameter(cmd, "PARTY_ID", householdID.ToString());
DatabaseWrapperHelper.AddInStringParameter(cmd, "PARTY_TYPE_ID", "1");
}));
configureCommandList.Add(new Action <IDbCommand>(cmd =>
{
cmd.CommandText = SqlConstants.CREATE_SOLUTION_PARTY;
DatabaseWrapperHelper.AddInStringParameter(cmd, "SOLUTION_ID", solutionID.ToString());
DatabaseWrapperHelper.AddInStringParameter(cmd, "PARTY_ID", investorID.ToString());
DatabaseWrapperHelper.AddInStringParameter(cmd, "PARTY_TYPE_ID", "2");
}));
}
}
if (planId != -1)
{
configureCommandList.Add(new Action <IDbCommand>(cmd =>
{
cmd.CommandText = SqlConstants.CREATE_INVESTMENT_PORTFOLIO;
DatabaseWrapperHelper.AddInStringParameter(cmd, "PORTFOLIO_ID", portfolioID);
DatabaseWrapperHelper.AddInStringParameter(cmd, "PORTFOLIO_TYPE_ID", "0");
DatabaseWrapperHelper.AddInStringParameter(cmd, "CREATE_BY_USER_ID", _context.Identity.BrokerId);
}));
configureCommandList.Add(new Action <IDbCommand>(cmd =>
{
cmd.CommandText = SqlConstants.CREATE_HOLDING_PORTFOLIO;
DatabaseWrapperHelper.AddInStringParameter(cmd, "PORTFOLIO_ID", portfolioID);
DatabaseWrapperHelper.AddInStringParameter(cmd, "PORTFOLIO_TYPE_ID", "0");
DatabaseWrapperHelper.AddInStringParameter(cmd, "EXTERNAL_ID", "");
DatabaseWrapperHelper.AddInStringParameter(cmd, "DATASOURCE_ID", "1");
DatabaseWrapperHelper.AddInStringParameter(cmd, "CREATE_BY_USER_ID", _context.Identity.BrokerId);
}));
configureCommandList.Add(new Action <IDbCommand>(cmd =>
{
cmd.CommandText = SqlConstants.CREATE_WEB_PROCESS;
DatabaseWrapperHelper.AddInStringParameter(cmd, "PROCESS_ID", processID.ToString());
DatabaseWrapperHelper.AddInStringParameter(cmd, "PROCESS_DEFINITION_ID", "1");
DatabaseWrapperHelper.AddInStringParameter(cmd, "BROKER_ID", _context.Identity.BrokerId);
}));
configureCommandList.Add(new Action <IDbCommand>(cmd =>
{
cmd.CommandText = SqlConstants.CREATE_WEB_PROCESS_PARAM;
DatabaseWrapperHelper.AddInStringParameter(cmd, "PROCESS_ID", processID.ToString());
DatabaseWrapperHelper.AddInStringParameter(cmd, "SEQ_NUM", "1");
DatabaseWrapperHelper.AddInStringParameter(cmd, "ENTITY_TYPE_ID", Constants.PROPOPOSAL_PATH_ENTITY_TYPE_ID);
DatabaseWrapperHelper.AddInStringParameter(cmd, "ENTITY_ID", defaultProposalPath);
}));
//create web_process_activity
configureCommandList.Add(new Action <IDbCommand>(cmd =>
{
cmd.CommandText = SqlConstants.CREATE_WEB_PROCESS_ACTIVITY;
DatabaseWrapperHelper.AddInStringParameter(cmd, "PROCESS_ID", processID.ToString());
DatabaseWrapperHelper.AddInStringParameter(cmd, "BROKER_ID", _context.Identity.BrokerId);
DatabaseWrapperHelper.AddInStringParameter(cmd, "ACTIVITY_ID", "1");
}));
configureCommandList.Add(new Action <IDbCommand>(cmd =>
{
cmd.CommandText = SqlConstants.CREATE_WEB_PROCESS_ACTIVITY_LOG;
DatabaseWrapperHelper.AddInStringParameter(cmd, "PROCESS_ID", processID.ToString());
DatabaseWrapperHelper.AddInStringParameter(cmd, "ACTIVITY_ID", "1");
DatabaseWrapperHelper.AddInStringParameter(cmd, "BROKER_ID", _context.Identity.BrokerId);
DatabaseWrapperHelper.AddInStringParameter(cmd, "ENTITY_TYPE_ID", "779");
DatabaseWrapperHelper.AddInStringParameter(cmd, "LOG_ENTRY_ID", processLogID.ToString());
}));
//Create web_investment_plan
configureCommandList.Add(new Action <IDbCommand>(cmd =>
{
cmd.CommandText = SqlConstants.CREATE_NEW_PROPOSAL;
DatabaseWrapperHelper.AddInStringParameter(cmd, "PLAN_ID", planId.ToString());
DatabaseWrapperHelper.AddInStringParameter(cmd, "SOLUTION_ID", solutionID.ToString());
DatabaseWrapperHelper.AddInStringParameter(cmd, "NAME", proposalName);
DatabaseWrapperHelper.AddInStringParameter(cmd, "DESCR", "");
DatabaseWrapperHelper.AddInStringParameter(cmd, "PROFILE_ID", profileid.ToString());
DatabaseWrapperHelper.AddInStringParameter(cmd, "MODEL_ID", modelId);
DatabaseWrapperHelper.AddInStringParameter(cmd, "PROGRAM_ID", defaultProgramId);
DatabaseWrapperHelper.AddInStringParameter(cmd, "HOLDING_PORTFOLIO_ID", portfolioID);
DatabaseWrapperHelper.AddInStringParameter(cmd, "PLAN_PROCESS_ID", processID.ToString());
DatabaseWrapperHelper.AddInStringParameter(cmd, "CASH_AMOUNT", "0");
DatabaseWrapperHelper.AddInStringParameter(cmd, "MARK_DELETED_YN", "N");
DatabaseWrapperHelper.AddInStringParameter(cmd, "CREATED_BY", _context.Identity.BrokerId);
DatabaseWrapperHelper.AddInStringParameter(cmd, "AC_VIEW_ID", defaultPropAClassLevelId.ToString());
DatabaseWrapperHelper.AddInStringParameter(cmd, "MODELBASED_YN", !string.IsNullOrEmpty(modelId) ? "Y" : "N");
DatabaseWrapperHelper.AddInStringParameter(cmd, "HOLDINGLEVEL", "Account");
DatabaseWrapperHelper.AddInStringParameter(cmd, "PLAN_TYPE_ID", "17");
}));
//insert the data in the web_broker_recent_plan and delete the old ones if the count is more than 30
configureCommandList.Add(new Action <IDbCommand>(cmd =>
{
cmd.CommandText = SqlConstants.INSERT_RECENT_PROPOSAL;
DatabaseWrapperHelper.AddInStringParameter(cmd, "BROKER_ID", _context.Identity.BrokerId);
DatabaseWrapperHelper.AddInStringParameter(cmd, "PLAN_ID", planId.ToString());
DatabaseWrapperHelper.AddInStringParameter(cmd, "PLAN_TYPE_ID", Convert.ToInt64(planTypeList.recentProposal).ToString());
}));
}
bool result = _dbWrapper.ExecuteBatch(configureCommandList.ToArray(), _context.Identity.InstitutionId);
return(planId);
}
