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