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);
}
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 SortedList <int, string> GetDefaultProfileAndModelId()
{
SortedList <int, string> defaultProfileAndModelId = new SortedList <int, string>();
List <string> defaultOptonIds = new List <string>();
IEnumerable <IDataRecord> records = _dbWrapper.QueryDataRecord(cmd =>
{
cmd.CommandText = SqlConstants.GET_DEFAULT_OPTIONS_IDS;
DatabaseWrapperHelper.AddInIntParameter(cmd, ":a_nGroupId", _context.Identity.GroupId);
}, _context.Identity.InstitutionId);
foreach (var row in records)
{
defaultOptonIds.Add(row["INV_PROFILE_FACTOR_OPTION_ID"].ToString());
}
var sql = SqlConstants.GET_DEFAULT_PROFILE_MODEL_ID;
sql = _dbWrapper.BuildSqlInClauseQuery(defaultOptonIds, ":OPTION_IDS", sql);
records = _dbWrapper.QueryDataRecord(cmd =>
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
_dbWrapper.BuildParamInClauseQuery(defaultOptonIds, "OPTION_IDS", cmd);
DatabaseWrapperHelper.AddInIntParameter(cmd, ":GROUP_ID", _context.Identity.GroupId);
DatabaseWrapperHelper.AddInIntParameter(cmd, ":OPTION_COUNT", defaultOptonIds.Count.ToString());
}, _context.Identity.InstitutionId);
foreach (var row in records)
{
int _Id = Int32.Parse(row["PROFILE_DTL_ID"].ToString());
string _modelId = row["MODEL_ID"].ToString();
defaultProfileAndModelId[_Id] = _modelId;
}
return(defaultProfileAndModelId);
}
private void GetACViewSetIds(ref int origAcViewId, ref int mappedAcViewId)
{
IEnumerable <IDataRecord> records = _dbWrapper.QueryDataRecord(cmd =>
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = SqlConstants.GET_AC_VIEW_ID;
DatabaseWrapperHelper.AddInIntParameter(cmd, "group_id", _context.Identity.GroupId);
}, _context.Identity.InstitutionId);
foreach (var row in records)
{
if (row["most_gran_yn"].ToString().ToUpper() == "Y")
{
origAcViewId = int.Parse(row["ac_view_id"].ToString());
}
else
{
mappedAcViewId = int.Parse(row["ac_view_id"].ToString());
}
}
if (mappedAcViewId == 0)
{
mappedAcViewId = origAcViewId;
}
}
private void DeleteAllATTR2FromHH(string householdId)
{
var executeResult = _dbWrapper.Execute(cmd =>
{
cmd.CommandText = SqlConstants.DELETE_WEB_HOUSEHOLD_QRY_FILTER;
DatabaseWrapperHelper.AddInIntParameter(cmd, "householdId", householdId.ToString());
}, _context.Identity.InstitutionId);
}
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");
}
public bool IsBackOfficeInstitution()
{
int configId = Convert.ToInt32(_dbWrapper.ExecuteScalar(cmd =>
{
cmd.CommandText = SqlConstants.GET_INSTITUTION_CONFIG;
DatabaseWrapperHelper.AddInIntParameter(cmd, "InstitutionId", _context.Identity.InstitutionId);
}, _context.Identity.InstitutionId));
return(configId == Constants.IDS_BACKOFFICE_CONFIG_ID);
}
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 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 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 bool Delete_Web_Investment_Plan(int Planid)
{
var executeResult = _dbWrapper.Execute(cmd =>
{
cmd.CommandText = SqlConstants.DELETE_WEB_INVESTMENT_PLAN;
DatabaseWrapperHelper.AddInIntParameter(cmd, ":PLAN_ID", Planid.ToString());
}, _context.Identity.InstitutionId);
if (executeResult > 0)
{
return(true);
}
return(false);
}
public List <Team> GetHouseHoldTeams(string householdId)
{
List <Team> teamList = new List <Team>();
//get household teams only for backoffice institution
if (IsBackOfficeInstitution())
{
IEnumerable <IDataRecord> records = _dbWrapper.QueryDataRecord(cmd =>
{
cmd.CommandText = SqlConstants.GET_HOUSEHOLD_TEAMS;
DatabaseWrapperHelper.AddInLongParameter(cmd, "hierarchyType", "3");
DatabaseWrapperHelper.AddInLongParameter(cmd, "household_id", householdId);
}, _context.Identity.InstitutionId);
foreach (var row in records)
{
Team team = new Team();
team.TeamId = row["TEAM_ID"].ToString();
team.RepCode = row["REP_CODE"].ToString();
team.Name = row["NAME"].ToString();
team.Role = (Team.TeamRole)(Convert.ToInt64(row["TEAM_ROLE_ID"]));
team.AssignType = (Team.TeamAssignmentType)(Convert.ToInt64(row["TEAM_ASSIGN_TYPE_ID"]));
team.HierarchyType = (Team.TeamHierarchyType)(Convert.ToInt64(row["TEAM_HIERARCHY_TYPE_ID"]));
teamList.Add(team);
}
}
if (teamList.Count == 0)
{
IEnumerable <IDataRecord> records = _dbWrapper.QueryDataRecord(cmd =>
{
cmd.CommandText = SqlConstants.GET_DEFAULT_TEAMS;
DatabaseWrapperHelper.AddInLongParameter(cmd, "brokerId", _context.Identity.BrokerId);
}, _context.Identity.InstitutionId);
foreach (var row in records)
{
Team team = new Team();
team.Broker_ID = row["BROKER_ID"].ToString();
team.TeamId = row["TEAM_ID"].ToString();
team.RepCode = row["REP_CODE"].ToString();
team.Name = row["NAME"].ToString();
//team.City = row["CITY"].ToString();
//team.State = row["STATE"].ToString();
team.Role = (Team.TeamRole)(Convert.ToInt64(row["TEAM_ROLE_ID"]));
team.AssignType = Team.TeamAssignmentType.UserCreated;
team.HierarchyType = (Team.TeamHierarchyType)(Convert.ToInt64(row["TEAM_HIERARCHY_TYPE_ID"]));
teamList.Add(team);
}
}
return(teamList);
}
//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 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 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 List <Contact> GetContactsForHousehold(string householdId)
{
List <Contact> contactList = new List <Contact>();
bool isValidHH = long.TryParse(householdId, out long hhId);
if (!isValidHH)
{
return(contactList);
}
IEnumerable <IDataRecord> records = _dbWrapper.QueryDataRecord(cmd =>
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = SqlConstants.GET_PERSONS_FOR_HH;
DatabaseWrapperHelper.AddInIntParameter(cmd, "household_id", householdId);
}, _context.Identity.InstitutionId);
foreach (var row in records)
{
Contact contact = new Contact();
contact.HouseholdId = householdId;
contact.InvestorId = row["PERSON_ID"].ToString();
contact.RelationShipType = (RelationShipType)row["RELATIONSHIP_TYPE"];
contact.FirstName = row["first_name"].ToString();
contact.LastName = row["last_name"].ToString();
contact.MiddleName = row["mid_initial"].ToString();
contact.Suffix = row["suffix"].ToString();
contact.PersonalEmail = row["HOME_EMAIL"].ToString();
contact.Phone = row["MOBILE_PHONE"].ToString();
contact.HomePhone = row["HOME_PHONE"].ToString();
contact.AddressLine1 = row["mail_addr1"].ToString();
contact.AddressLine2 = row["mail_addr2"].ToString();
contact.City = row["mail_city"].ToString();
contact.State = row["mail_state"].ToString();
contact.Country = row["mail_country"].ToString();
contact.Zip = row["mail_postal_code"].ToString();
contactList.Add(contact);
}
return(contactList);
}
public Broker GetBrokerInfo()
{
Broker broker = new Broker();
try {
IEnumerable <IDataRecord> records = _dbWrapper.QueryDataRecord(cmd =>
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = SqlConstants.READBROKERINFO_SQL;
DatabaseWrapperHelper.AddInIntParameter(cmd, "broker_id", _context.Identity.BrokerId);
}, _context.Identity.InstitutionId);
foreach (var row in records)
{
broker.BrokerId = _context.Identity.BrokerId;
broker.Suffix = row["DESIGNATION_SUFFIX"].ToString();
broker.Clearing_firm_name = row["CLRG_COUNTRY_NAME"].ToString();
broker.Firm_country_name = row["FIRM_COUNTRY_NAME"].ToString();
broker.Sub_Firm_No = row["SUB_NO"].ToString();
broker.Primary_state = row["PRIMARY_STATE"].ToString();
broker.Affiliation = row["AFFILIATION"].ToString();
broker.Clearing_firm_name = row["CLRG_FIRM_NAME"].ToString();
broker.Firm_name = row["FIRM_NAME"].ToString();
broker.User_name = row["USERNAME"].ToString();
broker.Title = row["BROKER_TITLE"].ToString();
// broker.Sub_Firm_Num = row["SUB_FIRM_NUM"].ToString();
broker.InstitutionId = row["INSTITUTION_ID"].ToString();
//broker.SUB_ID = row["SUB_ID"].ToString();
broker.Name = row["BROKER_NAME"].ToString();
broker.USERNAME = row["USERNAME"].ToString();
broker.BrokerId = row["BROKER_ID"].ToString();
broker.Email = row["FIRM_EMAIL"].ToString();
broker.FirmPhone = row["FIRM_PHONE"].ToString();
broker.contactInfoList = new System.Collections.ArrayList();
ContactDetails firmContact = new ContactDetails();
firmContact.Contact_type = "firm";
firmContact.Phone = row["FIRM_PHONE"].ToString();
firmContact.Fax = row["FIRM_FAX"].ToString();
firmContact.Email = row["FIRM_EMAIL"].ToString();
broker.contactInfoList.Add(firmContact);
ContactDetails clrgFirmContact = new ContactDetails();
clrgFirmContact.Contact_type = "clearing_firm";
clrgFirmContact.Phone = row["CLRG_FIRM_PHONE"].ToString();
clrgFirmContact.Fax = row["CLRG_FIRM_FAX"].ToString();
clrgFirmContact.Email = row["CLRG_FIRM_EMAIL"].ToString();
broker.contactInfoList.Add(clrgFirmContact);
broker.addressInfoList = new System.Collections.ArrayList();
Address firmAddress = new Address();
firmAddress.Address_Type = AddressType.firm;
firmAddress.AddressLine1 = row["FIRM_ADDR1"].ToString();
firmAddress.AddressLine2 = row["FIRM_ADDR2"].ToString();
firmAddress.City = row["FIRM_CITY"].ToString();
firmAddress.State = row["FIRM_STATE"].ToString();
firmAddress.Country = row["FIRM_COUNTRY"].ToString();
firmAddress.ZipCode = row["FIRM_POSTAL_CODE"].ToString();
broker.addressInfoList.Add(firmAddress);
Address clrgFirmAddress = new Address();
clrgFirmAddress.Address_Type = AddressType.clearing_firm;
clrgFirmAddress.AddressLine1 = row["CLRG_FIRM_ADDR1"].ToString();
clrgFirmAddress.AddressLine2 = row["CLRG_FIRM_ADDR2"].ToString();
clrgFirmAddress.City = row["CLRG_FIRM_CITY"].ToString();
clrgFirmAddress.State = row["CLRG_FIRM_STATE"].ToString();
clrgFirmAddress.Country = row["FIRM_COUNTRY"].ToString();
clrgFirmAddress.ZipCode = row["CLRG_FIRM_COUNTRY"].ToString();
broker.addressInfoList.Add(clrgFirmAddress);
}
}
catch (Exception ex)
{
throw ex;
}
return(broker);
}
private List <Team> CalculateListOfTeams(string householdId, List <Team> listOfTeams)
{
List <Team> lstExistingTeams = new List <Team>(), lstFinalTeams = new List <Team>();
IEnumerable <IDataRecord> records = _dbWrapper.QueryDataRecord(cmd =>
{
cmd.CommandText = SqlConstants.GET_HOUSEHOLD_TeamEntitlement;
DatabaseWrapperHelper.AddInLongParameter(cmd, "householdId", householdId);
}, _context.Identity.InstitutionId);
foreach (var row in records)
{
Team team = new Team();
team.TeamId = row["TEAM_ID"].ToString();
team.Role = (Team.TeamRole)(Convert.ToInt64(row["TEAM_ROLE_ID"]));
team.AssignType = (Team.TeamAssignmentType)(Convert.ToInt64(row["TEAM_ASSIGN_TYPE_ID"]));
lstExistingTeams.Add(team);
}
List <Team> tc = GetParentTeams(listOfTeams);
if (tc != null)
{
foreach (Team t in tc)
{
listOfTeams.Add(t);
}
}
if (lstExistingTeams == null)
{
return(listOfTeams);
}
if (listOfTeams != null)
{
//need to take care of user created team removed from UI case.
foreach (Team oldTeam in lstExistingTeams)
{
bool isExist = false;
foreach (Team newTeam in listOfTeams)
{
if (newTeam.TeamId == oldTeam.TeamId)
{
isExist = true;
lstFinalTeams.Add(newTeam);
break;
}
}
}
foreach (Team newTm in listOfTeams)
{
bool isExist = false;
foreach (Team oldTm in lstExistingTeams)
{
if (oldTm.TeamId == newTm.TeamId)
{
isExist = true;
break;
}
}
if (!isExist)
{
lstFinalTeams.Add(newTm);
}
}
}
return(lstFinalTeams);
}
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);
}
public List <AssetClass> GetAssetClassification(bool broadAssetClass)
{
string query = @"SELECT x.asset_class_id,a.name,a.descr,a.red,a.green,
a.blue,a.abbreviation,NVL(a.perf_descr, a.descr) AS perf_descr,
parent_ac AS broad_asset_class_id,a.idxid,WIXM.Name as Index_Name,
wcxac.asset_class_universe_id, NULL AS asset_class_type, nvl(wcxac.seq_num, 0) as seq_num ,acs.sec_id
FROM
(SELECT ac.asset_class_id,
NVL(
(SELECT asset_class_id_2
FROM web_asset_class_relationship
WHERE asset_class_id_2 IN
(SELECT asset_class_id FROM web_ac_view_asset_class WHERE ac_view_id = :a_mappedACViewId
) START
WITH asset_class_id_1= ac.asset_class_id CONNECT BY asset_class_id_1= prior asset_class_id_2
),
(SELECT asset_class_id
FROM web_ac_view_asset_class
WHERE ac_view_id = :a_mappedACViewId
AND asset_class_id = ac.asset_class_id
)) parent_ac
FROM web_ac_view_asset_class ac
WHERE ac_view_id = :a_origACViewId
) x ,
web_asset_class a,
web_asset_class b,
web_ix_master WIXM,
web_classification_x_ac wcxac,
ac_security acs
WHERE x.asset_class_id=a.asset_class_id
AND x.parent_ac = b.asset_class_id (+)
and wcxac.asset_class_id = x.asset_class_id
and acs.asset_class_id =x.asset_class_id
and WIXM.IDXID(+)=a.IDXID order by a.name asc";
string broadACquery = @"select wac.asset_class_id, wac.NAME, wac.descr, wac.red, wac.green, wac.blue,
wac.abbreviation, NVL(wac.perf_descr, wac.descr) AS perf_descr, wac.broad_asset_class_id,
wac.idxid, WIXM.Name as Index_Name, acs.sec_id,
wcxac.asset_class_universe_id, wcxac.asset_class_type, nvl(wcxac.seq_num, 0) as seq_num
from web_asset_class wac,web_ac_view_asset_class acx,web_ix_master wixm,web_classification_x_ac wcxac ,
ac_security acs
where wac.asset_class_id = acx.asset_class_id
and WIXM.IDXID(+)=WAC.IDXID
and acx.ac_view_id=:a_mappedACViewId
and wcxac.asset_class_id (+)= wac.asset_class_id
and acs.asset_class_id =x.asset_class_id
order by wac.asset_class_id";
//if (_context.Identity.InstitutionId < 0)
//institutionId = 6018;
List <AssetClass> acClassList = new List <AssetClass>();
int origACViewId = 0;
int mappedACViewID = 0;
GetACViewSetIds(ref origACViewId, ref mappedACViewID);
IEnumerable <IDataRecord> records = _dbWrapper.QueryDataRecord(cmd =>
{
if (broadAssetClass == true)
{
cmd.CommandText = broadACquery;
DatabaseWrapperHelper.AddInIntParameter(cmd, "a_mappedACViewId", mappedACViewID.ToString());
}
else
{
cmd.CommandText = query;
DatabaseWrapperHelper.AddInIntParameter(cmd, "a_origACViewId", origACViewId.ToString());
DatabaseWrapperHelper.AddInIntParameter(cmd, "a_mappedACViewId", mappedACViewID.ToString());
}
}, _context.Identity.InstitutionId);
foreach (var row in records)
{
AssetClass assetClass = new AssetClass();
assetClass.Acid = Convert.ToInt64(row["asset_class_id"]);
assetClass.Red = Convert.ToInt64(row["red"]);
assetClass.Green = Convert.ToInt64(row["green"]);
assetClass.Blue = Convert.ToInt64(row["blue"]);
assetClass.Idxid = Convert.ToInt64(row["idxid"]);
assetClass.BroadACId = Convert.ToInt64(row["broad_asset_class_id"]);
assetClass.SortOrder = Convert.ToInt64(row["seq_num"]);
assetClass.Name = row["NAME"].ToString();
assetClass.Descr = row["descr"].ToString();
assetClass.Abbreviation = row["abbreviation"].ToString();
assetClass.PerfDescr = row["perf_descr"].ToString();
assetClass.IndexName = row["Index_Name"].ToString();
assetClass.AssetType = row["asset_class_type"].ToString();
assetClass.SecId = row["sec_id"].ToString();
acClassList.Add(assetClass);
}
return(acClassList);
}
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 <ProposalByHH> GetProposalsByHH(string householdID)
{
List <ProfileModelInfo> profileModelInfo;
List <string> planIdList = new List <string>();
List <string> planTypeList = Constants.PlanTypeList;
List <string> reportTypeList = Constants.ReportTypeList;
List <ProposalByHH> proposalList = new List <ProposalByHH>();
string sql = SqlConstants.GET_PROPOSALS_BY_HH;
sql = _dbWrapper.BuildSqlInClauseQuery(planTypeList, ":PLAN_TYPE_IDS", sql);
IEnumerable <IDataRecord> records = _dbWrapper.QueryDataRecord(cmd =>
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
_dbWrapper.BuildParamInClauseQuery(planTypeList, "PLAN_TYPE_IDS", cmd);
DatabaseWrapperHelper.AddInIntParameter(cmd, ":HOUSEHOLD_ID", householdID);
DatabaseWrapperHelper.AddInIntParameter(cmd, ":PARTY_TYPE_ID", "1");
}, _context.Identity.InstitutionId);
foreach (var row in records)
{
ProposalByHH proposal = new ProposalByHH();
proposal.ProposalId = row["PLAN_ID"].ToString();
proposal.ProposalName = (!string.IsNullOrEmpty(row["NAME"].ToString())) ? row["NAME"].ToString() : string.Empty;
proposal.PlanTypeId = row["PLAN_TYPE_ID"].ToString();
proposal.ProgramName = (!string.IsNullOrEmpty(row["PROGRAM_NAME"].ToString())) ? row["PROGRAM_NAME"].ToString() : string.Empty;
proposal.ModelMinorVersion = row["MODEL_MINOR_VERSION"].ToString();
proposal.LastModifiedDate = row["LAST_MODIFIED_DATE"].ToString();
proposalList.Add(proposal);
}
foreach (var plan in proposalList)
{
List <string> planIds = new List <string>()
{
plan.ProposalId
};
var sqlReports = SqlConstants.GET_INV_REPORTS_BY_HH;
sqlReports = _dbWrapper.BuildSqlInClauseQuery(planIds, ":PLAN_IDS", sqlReports);
sqlReports = _dbWrapper.BuildSqlInClauseQuery(reportTypeList, ":REPORT_TYPE_IDS", sqlReports);
sqlReports = _dbWrapper.BuildSqlInClauseQuery(planTypeList, ":PLAN_TYPE_IDS", sqlReports);
IEnumerable <IDataRecord> records1 = _dbWrapper.QueryDataRecord(cmd =>
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = sqlReports;
DatabaseWrapperHelper.AddInIntParameter(cmd, ":BROKER_ID", _context.Identity.BrokerId);
_dbWrapper.BuildParamInClauseQuery(planIds, "PLAN_IDS", cmd);
_dbWrapper.BuildParamInClauseQuery(reportTypeList, "REPORT_TYPE_IDS", cmd);
_dbWrapper.BuildParamInClauseQuery(planTypeList, "PLAN_TYPE_IDS", cmd);
}, _context.Identity.InstitutionId);
foreach (var reportRow in records1)
{
ArchivedReportInfo reportInfo = new ArchivedReportInfo();
reportInfo.Name = reportRow["NAME"].ToString();
reportInfo.ReportId = reportRow["ARCHIVED_REPORT_ID"].ToString();
reportInfo.PlanId = reportRow["PLAN_ID"].ToString();
reportInfo.FileName = reportRow["FILENAME"].ToString();
reportInfo.Path = reportRow["LOCATION"].ToString();
reportInfo.EntitledYN = reportRow["ENTITLED_YN"].ToString();
reportInfo.IS_STREETSCAPE = reportRow["STREETSCAPEYN"].ToString();
plan.ReportInfoList.Add(reportInfo);
}
}
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 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);
}
