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);
 }
Exemplo n.º 2
0
        public List <ProposalsModel> GetRecentProposals()
        {
            List <ProfileModelInfo> profileModelInfo;
            List <string>           planIds = new List <string>();
            string planTypes = string.Join(",", Enum.GetValues(typeof(planTypeList)).Cast <int>());
            List <ProposalsModel>     proposalList = new List <ProposalsModel>();
            IEnumerable <IDataRecord> records      = _dbWrapper.QueryDataRecord(cmd => {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "PKG_PG.GETRECENTPROPOSALS";
                DatabaseWrapperHelper.AddInIntParameter(cmd, "A_NBROKER_ID", _context.Identity.BrokerId);
                DatabaseWrapperHelper.AddInStringParameter(cmd, "A_VPLANTYPELIST", planTypes);
                DatabaseWrapperHelper.AddOutCursorParameter(cmd, "A_RCPROPOSALLIST");
                DatabaseWrapperHelper.AddOutIntParameter(cmd, "A_UTSTATUS");
                DatabaseWrapperHelper.AddOutStringParameter(cmd, "A_UTSTATUSMSG", 2000);
            }, _context.Identity.InstitutionId);

            foreach (var row in records)
            {
                ProposalsModel proposal = new ProposalsModel();
                proposal.ProposalId             = row["PLAN_ID"].ToString();
                proposal.ProposalName           = (!string.IsNullOrEmpty(row["PLAN_NAME"].ToString())) ? row["PLAN_NAME"].ToString() : string.Empty;
                proposal.PartyID                = row["PARTY_ID"].ToString();
                proposal.PartyName              = row["PARTY_NAME"].ToString();
                proposal.PartyType              = row["PARTY_TYPE_NAME"].ToString();
                proposal.LastModifiedDate       = row["ACCESS_DATE"].ToString();
                proposal.IsEntitle              = row["FULL_ENT"].ToString();
                proposal.RowNum                 = Int32.Parse(row["RN"].ToString());
                proposal.PlanTypeId             = row["PLAN_TYPE_ID"].ToString();
                proposal.ProgramName            = row["PROGRAM_NAME"].ToString();
                proposal.IsNoPartialEntitlement = row["NO_ENT"].ToString();
                proposal.ModelMinorVersion      = row["MODEL_MINOR_VERSION"].ToString();

                proposalList.Add(proposal);
            }
            foreach (ProposalsModel proposals in proposalList)
            {
                planIds.Add(proposals.ProposalId);
            }

            profileModelInfo = GetProfileModelData(planIds);
            foreach (ProfileModelInfo infoList in profileModelInfo)
            {
                int index = proposalList.FindIndex(0, proposalList.Count, proposal => proposal.ProposalId == infoList.PlanId);
                if (index != -1)
                {
                    proposalList[index].RiskFactor    = string.IsNullOrEmpty(infoList.ProfileName) ? "-" : infoList.ProfileName;
                    proposalList[index].ModelIncluded = string.IsNullOrEmpty(infoList.ModelName) ? "No Model" : infoList.ModelName;
                }
            }
            return(proposalList);
        }
        public List <Household> SearchAllContacts(SearchParameters searchParameters)
        {
            List <Household>          resultList = new List <Household>();
            IEnumerable <IDataRecord> records    = _dbWrapper.QueryDataRecord(cmd =>
            {
                cmd.CommandType = CommandType.StoredProcedure;
                if (searchParameters.Searchby == SearchBy.ACCOUNTNUMBER)
                {
                    cmd.CommandText = SqlConstants.GET_ALL_CONTACTSBY_ACCOUNTNO;
                    DatabaseWrapperHelper.AddInStringParameter(cmd, "A_VACCTNO", HandleSpecialCharacters(searchParameters.AccountNumber));
                }
                else if (searchParameters.Searchby == SearchBy.REPCODE)
                {
                    cmd.CommandText = SqlConstants.GET_ALL_CONTACTSBY_REPCODE;
                    DatabaseWrapperHelper.AddInStringParameter(cmd, "A_VREPCODE", HandleSpecialCharacters(searchParameters.RepCode));
                }
                else //default SearchBy DEMOGRAPHICS
                {
                    cmd.CommandText = SqlConstants.GET_ALL_CONTACTSBY_DEMOGRAPHICS;
                    SetDemographicParameters(cmd, searchParameters);
                }
                //CommonParameters
                SetCommonParameters(cmd, searchParameters);
                //Output Parameters
                DatabaseWrapperHelper.AddOutCursorParameter(cmd, "A_RCCONTACTLIST");
                DatabaseWrapperHelper.AddOutIntParameter(cmd, "A_UTSTATUS");
                DatabaseWrapperHelper.AddOutStringParameter(cmd, "A_UTSTATUSMSG", 2000);
            }, _context.Identity.InstitutionId);

            foreach (var row in records)
            {
                Household result = new Household();
                result.HouseholdID = row["HOUSEHOLD_ID"].ToString();
                if (!String.IsNullOrEmpty(result.HouseholdID) && Convert.ToInt32(result.HouseholdID) > 0 && !resultList.Any(p => p.HouseholdID == result.HouseholdID))
                {
                    result.Type = row["TYPE"].ToString();
                    //Get Contact Details
                    result.Persons = GetContactsForHousehold(result.HouseholdID);
                    resultList.Add(result);
                }
            }
            return(resultList);
        }
Exemplo n.º 4
0
        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);
        }