/// <summary> /// Get the Asset and Liability details for a customer. /// </summary> /// <param name="report"></param> /// <remarks>Get All the details of Financial Planning of customers</remarks> /// <returns></returns> public DataSet GetCustomerFPDetails(FinancialPlanningVo report, out double assetTotal, out double liabilitiesTotal, out double netWorthTotal, out string riskClass, out double sumAssuredLI, out int dynamicAdvisorRiskClass) { Database db; DbCommand cmdCustomerFPReportDetails; DataSet dsCustomerFPReportDetails = null; DataTable dtAsset = new DataTable(); DataTable dtLiabilities = new DataTable(); try { db = DatabaseFactory.CreateDatabase("wealtherp"); cmdCustomerFPReportDetails = db.GetStoredProcCommand("SP_RPT_GetFPReportDetails"); db.AddInParameter(cmdCustomerFPReportDetails, "@AdvisorId", DbType.Int32, report.advisorId); db.AddInParameter(cmdCustomerFPReportDetails, "@CustomerId", DbType.Int32, report.CustomerId); db.AddOutParameter(cmdCustomerFPReportDetails, "@RiskClass", DbType.String, 50); db.AddOutParameter(cmdCustomerFPReportDetails, "@InsuranceSUMAssured", DbType.Decimal, 20); db.AddOutParameter(cmdCustomerFPReportDetails, "@AssetTotal", DbType.Decimal, 20); db.AddOutParameter(cmdCustomerFPReportDetails, "@DynamicRiskClsaaAdvisor", DbType.Int16, 2); dsCustomerFPReportDetails = db.ExecuteDataSet(cmdCustomerFPReportDetails); Object riskClassObj = db.GetParameterValue(cmdCustomerFPReportDetails, "@RiskClass"); if (riskClassObj != DBNull.Value) { riskClass = db.GetParameterValue(cmdCustomerFPReportDetails, "@RiskClass").ToString(); } else { riskClass = string.Empty; } Object objSumAssuredLI = db.GetParameterValue(cmdCustomerFPReportDetails, "@InsuranceSUMAssured"); if (objSumAssuredLI != DBNull.Value) { sumAssuredLI = double.Parse(db.GetParameterValue(cmdCustomerFPReportDetails, "@InsuranceSUMAssured").ToString()); } else { sumAssuredLI = 0; } Object objAssetTotal = db.GetParameterValue(cmdCustomerFPReportDetails, "@AssetTotal"); if (objAssetTotal != DBNull.Value) { assetTotal = double.Parse(db.GetParameterValue(cmdCustomerFPReportDetails, "@AssetTotal").ToString()); } else { assetTotal = 0; } Object objDynamicRiskClass = db.GetParameterValue(cmdCustomerFPReportDetails, "@DynamicRiskClsaaAdvisor"); if (objAssetTotal != DBNull.Value) { dynamicAdvisorRiskClass = int.Parse(db.GetParameterValue(cmdCustomerFPReportDetails, "@DynamicRiskClsaaAdvisor").ToString()); } else { dynamicAdvisorRiskClass = 0; } dtAsset = dsCustomerFPReportDetails.Tables[2]; dtLiabilities = dsCustomerFPReportDetails.Tables[3]; if (dtLiabilities.Rows.Count > 0) { liabilitiesTotal = double.Parse(dtLiabilities.Rows[0][0].ToString()); } else { liabilitiesTotal = 0; } netWorthTotal = assetTotal - liabilitiesTotal; dsCustomerFPReportDetails.Tables[0].TableName = "ReportSection"; dsCustomerFPReportDetails.Tables[1].TableName = "CustomerFamilyDetails"; dsCustomerFPReportDetails.Tables[2].TableName = "AssetToal"; dsCustomerFPReportDetails.Tables[3].TableName = "LiabilitiesTotal"; dsCustomerFPReportDetails.Tables[4].TableName = "KeyAssumption"; dsCustomerFPReportDetails.Tables[5].TableName = "OtherGoal"; dsCustomerFPReportDetails.Tables[6].TableName = "MonthlyGoalTotal"; dsCustomerFPReportDetails.Tables[7].TableName = "RTGoal"; dsCustomerFPReportDetails.Tables[8].TableName = "Income"; dsCustomerFPReportDetails.Tables[9].TableName = "Expense"; dsCustomerFPReportDetails.Tables[10].TableName = "CashFlow"; dsCustomerFPReportDetails.Tables[11].TableName = "AssetDetails"; dsCustomerFPReportDetails.Tables[12].TableName = "LiabilitiesDetail"; dsCustomerFPReportDetails.Tables[13].TableName = "RiskProfile"; dsCustomerFPReportDetails.Tables[14].TableName = "LifeInsurance"; dsCustomerFPReportDetails.Tables[15].TableName = "GeneralInsurance"; dsCustomerFPReportDetails.Tables[16].TableName = "HLV"; dsCustomerFPReportDetails.Tables[17].TableName = "AdvisorRiskClass"; dsCustomerFPReportDetails.Tables[18].TableName = "AdvisorPortfolioAllocation"; dsCustomerFPReportDetails.Tables[19].TableName = "FPRatio"; dsCustomerFPReportDetails.Tables[20].TableName = "FPRatioDetails"; dsCustomerFPReportDetails.Tables[21].TableName = "LoanEMI"; dsCustomerFPReportDetails.Tables[22].TableName = "HLVAssumption"; dsCustomerFPReportDetails.Tables[23].TableName = "RMRecommendation"; } catch (BaseApplicationException Ex) { throw Ex; } catch (Exception Ex) { BaseApplicationException exBase = new BaseApplicationException(Ex.Message, Ex); NameValueCollection FunctionInfo = new NameValueCollection(); FunctionInfo.Add("Method", "Reports.cs:GetCustomerAssetAllocationDetails()"); object[] objects = new object[2]; objects[0] = report.CustomerId; objects[0] = report.CustomerId; FunctionInfo = exBase.AddObject(FunctionInfo, objects); exBase.AdditionalInformation = FunctionInfo; ExceptionManager.Publish(exBase); throw exBase; } return(dsCustomerFPReportDetails); }
/// <summary> /// /// </summary> /// <param name="report"></param> /// <returns></returns> public DataSet GetFinancialPlanningReport(FinancialPlanningVo report) { FinancialPlanningReportsDao financialPlanningReports = new FinancialPlanningReportsDao(); return(financialPlanningReports.GetFinancialPlanningReport(report)); }
/// <summary> /// /// </summary> /// <param name="report"></param> /// <returns></returns> public DataSet GetFinancialPlanningReport(FinancialPlanningVo report) { Database db; DbCommand cmd; DataSet ds = null; DataTable dtCustomerDetails = new DataTable(); DataTable dtSpouseDetails = new DataTable(); DataTable dtChildrenDetails = new DataTable(); DataTable dtTempChildrenDetails = new DataTable(); try { db = DatabaseFactory.CreateDatabase("wealtherp"); cmd = db.GetStoredProcCommand("SP_RPT_GetFinancialPlanningReport"); db.AddInParameter(cmd, "@CustomerId", DbType.String, report.CustomerId); cmd.CommandTimeout = 60 * 60; ds = db.ExecuteDataSet(cmd); dtCustomerDetails.Columns.Add("Name", typeof(string)); dtCustomerDetails.Columns.Add("Dob", typeof(string)); dtSpouseDetails.Columns.Add("Name", typeof(string)); dtSpouseDetails.Columns.Add("Dob", typeof(string)); dtTempChildrenDetails.Columns.Add("Name", typeof(string)); dtTempChildrenDetails.Columns.Add("Dob", typeof(string)); dtTempChildrenDetails.Columns.Add("YearEducation", typeof(string)); dtTempChildrenDetails.Columns.Add("YearMarriage", typeof(string)); dtChildrenDetails.Columns.Add("Name", typeof(string)); dtChildrenDetails.Columns.Add("Dob", typeof(string)); dtChildrenDetails.Columns.Add("YearEducation", typeof(string)); dtChildrenDetails.Columns.Add("YearMarriage", typeof(string)); if (ds.Tables[2].Rows.Count > 0) { foreach (DataRow dr in ds.Tables[2].Rows) { if (dr["RelationShipCode"].ToString() == "SELF") { DataRow drCustomer = dtCustomerDetails.NewRow(); drCustomer["Name"] = dr["Name"].ToString(); if (!string.IsNullOrEmpty(dr["DOB"].ToString().Trim())) { drCustomer["Dob"] = String.Format("{0:dd MMM yyyy}", DateTime.Parse(dr["DOB"].ToString().Trim())); } else { drCustomer["Dob"] = "-"; } dtCustomerDetails.Rows.Add(drCustomer); } else if (dr["RelationShipCode"].ToString() == "SP") { DataRow drSpouse = dtSpouseDetails.NewRow(); if (!string.IsNullOrEmpty(dr["Name"].ToString())) { drSpouse["Name"] = dr["Name"].ToString(); } else { drSpouse["Name"] = "-"; } if (!string.IsNullOrEmpty(dr["DOB"].ToString().Trim())) { drSpouse["Dob"] = String.Format("{0:dd MMM yyyy}", DateTime.Parse(dr["DOB"].ToString().Trim())); } else { drSpouse["Dob"] = "-"; } dtSpouseDetails.Rows.Add(drSpouse); } else if (dr["RelationShipCode"].ToString() == "CH" && (dr["IsGoalActive"].ToString() == "1" || string.IsNullOrEmpty(dr["IsGoalActive"].ToString()))) { DataRow drChild = dtTempChildrenDetails.NewRow(); if (!string.IsNullOrEmpty(dr["Name"].ToString())) { drChild["Name"] = dr["Name"].ToString(); } else { drChild["Name"] = "-"; } if (!string.IsNullOrEmpty(dr["DOB"].ToString().Trim())) { drChild["Dob"] = String.Format("{0:dd MMM yyyy}", DateTime.Parse(dr["DOB"].ToString().Trim())); } else { drChild["Dob"] = "-"; } if (!string.IsNullOrEmpty(dr["YearOfEducation"].ToString())) { drChild["YearEducation"] = dr["YearOfEducation"].ToString(); } else { drChild["YearEducation"] = "-"; } if (!string.IsNullOrEmpty(dr["YearOfMarriage"].ToString())) { drChild["YearMarriage"] = dr["YearOfMarriage"].ToString(); } else { drChild["YearMarriage"] = "-"; } dtTempChildrenDetails.Rows.Add(drChild); } } } if (dtTempChildrenDetails.Rows.Count > 0) { for (int rowNo = 0; rowNo < dtTempChildrenDetails.Rows.Count; rowNo++) { DataRow drChild = dtChildrenDetails.NewRow(); if (rowNo != dtTempChildrenDetails.Rows.Count) { if (rowNo == dtTempChildrenDetails.Rows.Count - 1) { drChild["Name"] = dtTempChildrenDetails.Rows[rowNo]["Name"].ToString(); drChild["Dob"] = dtTempChildrenDetails.Rows[rowNo]["Dob"].ToString(); drChild["YearEducation"] = dtTempChildrenDetails.Rows[rowNo]["YearEducation"].ToString(); drChild["YearMarriage"] = dtTempChildrenDetails.Rows[rowNo]["YearMarriage"].ToString(); dtChildrenDetails.Rows.Add(drChild); } else { if (dtTempChildrenDetails.Rows[rowNo]["Name"].ToString().Trim() == dtTempChildrenDetails.Rows[rowNo + 1]["Name"].ToString().Trim()) { drChild["Name"] = dtTempChildrenDetails.Rows[rowNo]["Name"].ToString(); drChild["Dob"] = dtTempChildrenDetails.Rows[rowNo]["Dob"].ToString(); if (dtTempChildrenDetails.Rows[rowNo]["YearEducation"].ToString() != "-") { drChild["YearEducation"] = dtTempChildrenDetails.Rows[rowNo]["YearEducation"].ToString(); } else { drChild["YearEducation"] = dtTempChildrenDetails.Rows[rowNo + 1]["YearEducation"].ToString(); } if (dtTempChildrenDetails.Rows[rowNo]["YearMarriage"].ToString() != "-") { drChild["YearMarriage"] = dtTempChildrenDetails.Rows[rowNo]["YearMarriage"].ToString(); } else { drChild["YearMarriage"] = dtTempChildrenDetails.Rows[rowNo + 1]["YearMarriage"].ToString(); } dtChildrenDetails.Rows.Add(drChild); rowNo++; } else { drChild["Name"] = dtTempChildrenDetails.Rows[rowNo]["Name"].ToString(); drChild["Dob"] = dtTempChildrenDetails.Rows[rowNo]["Dob"].ToString(); drChild["YearEducation"] = dtTempChildrenDetails.Rows[rowNo]["YearEducation"].ToString(); drChild["YearMarriage"] = dtTempChildrenDetails.Rows[rowNo]["YearMarriage"].ToString(); dtChildrenDetails.Rows.Add(drChild); } } } } //int rowCount = 0; //foreach (DataRow dr in dtTempChildrenDetails.Rows) //{ // if (rowCount == 0) // { // } // DataRow drChild = dtChildrenDetails.NewRow(); //} } ds.Tables.Add(dtCustomerDetails); ds.Tables.Add(dtSpouseDetails); ds.Tables.Add(dtChildrenDetails); //if (ds.Tables[0].Rows.Count < 1) //{ // DataTable dtGoal = new DataTable(); // dtGoal.Columns.Add("GoalId"); // dtGoal.Columns.Add("GoalName"); // dtGoal.Columns.Add("ChildName"); // dtGoal.Columns.Add("CostToday", System.Type.GetType("System.Double")); // dtGoal.Columns.Add("MonthlySavingsRequired", System.Type.GetType("System.Double")); // dtGoal.Columns.Add("CalculatedOn", System.Type.GetType("System.DateTime")); // dtGoal.Columns.Add("Year"); // DataRow drGoal = ds.Tables[0].NewRow(); // drGoal["GoalId"] = -1; // drGoal["GoalName"] = "Test"; // drGoal["ChildName"] = "Test"; // drGoal["CostToday"] = 0.00; // drGoal["MonthlySavingsRequired"] = 0; // drGoal["CalculatedOn"] = DateTime.MinValue; // //drGoal["Year"] =0; // //dtGoal.Rows.Add(drGoal); // ds.Tables[0].Rows.Add(drGoal); //} } catch (BaseApplicationException Ex) { throw Ex; } catch (Exception Ex) { BaseApplicationException exBase = new BaseApplicationException(Ex.Message, Ex); NameValueCollection FunctionInfo = new NameValueCollection(); FunctionInfo.Add("Method", "Reports.cs:GetFinancialPlanningReport()"); object[] objects = new object[1]; objects[0] = report; FunctionInfo = exBase.AddObject(FunctionInfo, objects); exBase.AdditionalInformation = FunctionInfo; ExceptionManager.Publish(exBase); throw exBase; } return(ds); }
public DataSet GetCustomerFPDetails(FinancialPlanningVo report, out double asset, out double liabilities, out double netWorth, out string riskClass, out int dynamicRiskClass, out double totalAnnualIncome, out int financialAssetTotal) { DataSet dsCustomerFPReportDetails; DataTable dtHLVAnalysis; DataTable dtCurrentObservation; DataTable dtHealthAnalysis; DataTable dtAssetClass; DataTable dtPortfolioAllocation; DataTable dtExpense; DataTable dtIncome; DataTable dtLoanEMI; DataTable dtHLVAssumption; double HLVbasedIncome = 0; double inflationPer = 0; double discountRate = 0; double yearsLeftRT = 0; double totalIncomeMonthly = 0; double totalExpenseMonthly = 0; double sumAssuredLI; DataTable dtHLVBasedIncome = new DataTable("HLVBasedIncome"); double surplus = 0; double lifeProtectionTotal = 0; double totalEquity = 0; double totalMF = 0; double totalFixedIncome = 0; double totalOther = 0; double homeLoan = 0; double autoLoan = 0; double currEquity = 0; double toatlGoalAmount = 0; DataTable dtCustomerFPRatio = new DataTable("CustomerFPRatio"); double loan_To_Assets_Ratio = 0; double savings_To_Income_Ratio = 0; double debt_Ratio = 0; double debt_To_Income_Ratio = 0; double solvency_Ratio = 0; double life_Insurance_Cover_Ratio = 0; double annualSalary = 0; double cashAndSaving = 0; double totalMonthlyEMI = 0; double totalIncomeAnnual = 0; Dictionary <string, decimal> dicCustomerFPRatio = new Dictionary <string, decimal>(); string strInvestment = string.Empty; string strHomeLoan = string.Empty; string strAutoLoan = string.Empty; FinancialPlanningReportsDao financialPlanningReports = new FinancialPlanningReportsDao(); dsCustomerFPReportDetails = financialPlanningReports.GetCustomerFPDetails(report, out asset, out liabilities, out netWorth, out riskClass, out sumAssuredLI, out dynamicRiskClass, out financialAssetTotal); dtHLVAnalysis = dsCustomerFPReportDetails.Tables["HLV"]; dtAssetClass = dsCustomerFPReportDetails.Tables["AdvisorRiskClass"]; dtPortfolioAllocation = dsCustomerFPReportDetails.Tables["AdvisorPortfolioAllocation"]; dsCustomerFPReportDetails.Tables.Remove("HLV"); dsCustomerFPReportDetails.AcceptChanges(); dtExpense = dsCustomerFPReportDetails.Tables["Expense"]; dtIncome = dsCustomerFPReportDetails.Tables["Income"]; dtLoanEMI = dsCustomerFPReportDetails.Tables["LoanEMI"]; dtHLVAssumption = dsCustomerFPReportDetails.Tables["HLVAssumption"]; //**************GEARING RATIO***************** if (asset != 0) { loan_To_Assets_Ratio = (liabilities / asset) * 100; } double.TryParse(dtExpense.Compute("Sum(ExpenseAmount)", "").ToString(), out totalExpenseMonthly); foreach (DataRow dr in dtIncome.Rows) { //if (dr["IncomeCategory"].ToString().Trim() == "Salary") if (dr["IncomeCategory"].ToString().Trim() == "Gross Salary") { annualSalary = Convert.ToDouble(Math.Round(double.Parse(dr["IncomeAmount"].ToString())).ToString()); } totalIncomeAnnual += Convert.ToDouble(Math.Round(double.Parse(dr["IncomeAmount"].ToString())).ToString()) * 12; } if (Convert.ToString(dtLoanEMI.Rows[0][0]) != string.Empty) { totalMonthlyEMI = double.Parse(dtLoanEMI.Rows[0][0].ToString()) / 12; } //**************LIFE INSURANCE COVER***************** if (annualSalary != 0) { life_Insurance_Cover_Ratio = sumAssuredLI / (annualSalary * 12); } //Objetc sumObject = _itemCheckData.Table.Compute("Sum(reco_price)", ""); // dtExpense.Sum("ExpenseAmount"); //dsCustomerFPReportDetails.Tables.RemoveAt(16); //dsCustomerFPReportDetails.AcceptChanges(); //dsCustomerFPReportDetails.Tables.RemoveAt(16); //dsCustomerFPReportDetails.AcceptChanges(); foreach (DataRow dr in dtHLVAssumption.Rows) { if (dr["Assumption_Type"].ToString().Trim() == "Discount Rate") { if (!string.IsNullOrEmpty(dr["Assumption_Values"].ToString())) { discountRate = double.Parse(dr["Assumption_Values"].ToString()); } } } foreach (DataRow dr in dtHLVAnalysis.Rows) { if (dr["HLV_Type"].ToString().Trim() == "Inflation Rate") { if (!string.IsNullOrEmpty(dr["HLV_Values"].ToString())) { inflationPer = double.Parse(dr["HLV_Values"].ToString()); } } //if (dr["HLV_Type"].ToString().Trim() == "Discount Rate") //{ // if (!string.IsNullOrEmpty(dr["HLV_Values"].ToString())) // discountRate = double.Parse(dr["HLV_Values"].ToString()); //} if (dr["HLV_Type"].ToString().Trim() == "Years left till retirement") { if (!string.IsNullOrEmpty(dr["HLV_Values"].ToString())) { yearsLeftRT = double.Parse(dr["HLV_Values"].ToString()); } } if (dr["HLV_Type"].ToString().Trim() == "Income") { if (!string.IsNullOrEmpty(dr["HLV_Values"].ToString())) { totalIncomeMonthly = double.Parse(dr["HLV_Values"].ToString()); } } } //**************SAVING RATIO***************** if (totalIncomeMonthly != 0) { savings_To_Income_Ratio = ((totalIncomeMonthly - totalExpenseMonthly) / totalIncomeMonthly) * 100; } //**************SAVING RATIO***************** //**************Debt Service Ratio ***************** if (totalIncomeMonthly != 0) { debt_To_Income_Ratio = (totalMonthlyEMI / totalIncomeMonthly) * 100; } //**************Debt Service Ratio ***************** //**************Debt Ratio ***************** if (netWorth != 0) { debt_Ratio = liabilities / netWorth; } //**************Debt Ratio ***************** HLVbasedIncome = PV(discountRate / 100, yearsLeftRT, -(totalIncomeAnnual), 0, 1); totalAnnualIncome = totalIncomeMonthly * 12; DataRow drHLVbasedAnalysis; drHLVbasedAnalysis = dtHLVAnalysis.NewRow(); drHLVbasedAnalysis["HLV_Type"] = "HLV based on income"; drHLVbasedAnalysis["HLV_Values"] = convertUSCurrencyFormat(Math.Round(double.Parse(HLVbasedIncome.ToString()), 0)); dtHLVAnalysis.Rows.Add(drHLVbasedAnalysis); drHLVbasedAnalysis = dtHLVAnalysis.NewRow(); drHLVbasedAnalysis["HLV_Type"] = "Income(annual)"; drHLVbasedAnalysis["HLV_Values"] = convertUSCurrencyFormat(Math.Round(double.Parse((totalIncomeAnnual).ToString()), 0)); dtHLVAnalysis.Rows.InsertAt(drHLVbasedAnalysis, 0); dtHLVAnalysis.Rows.RemoveAt(1); dsCustomerFPReportDetails.Tables.Add(dtHLVAnalysis); dtHLVBasedIncome.Columns.Add("HLVIncomeType"); dtHLVBasedIncome.Columns.Add("HLVIncomeValue"); DataRow drHLVBasedIncome; drHLVBasedIncome = dtHLVBasedIncome.NewRow(); drHLVBasedIncome["HLVIncomeType"] = "Financial Net Worth"; drHLVBasedIncome["HLVIncomeValue"] = convertUSCurrencyFormat(Math.Round(netWorth, 0)); dtHLVBasedIncome.Rows.Add(drHLVBasedIncome); drHLVBasedIncome = dtHLVBasedIncome.NewRow(); drHLVBasedIncome["HLVIncomeType"] = "Insurance Cover Recommended(HLV – Financial Net Worth)"; drHLVBasedIncome["HLVIncomeValue"] = convertUSCurrencyFormat(Math.Round(double.Parse((HLVbasedIncome - netWorth).ToString()), 0)); dtHLVBasedIncome.Rows.Add(drHLVBasedIncome); drHLVBasedIncome = dtHLVBasedIncome.NewRow(); drHLVBasedIncome["HLVIncomeType"] = "Current Insurance Cover"; drHLVBasedIncome["HLVIncomeValue"] = convertUSCurrencyFormat(Math.Round(sumAssuredLI)); dtHLVBasedIncome.Rows.Add(drHLVBasedIncome); drHLVBasedIncome = dtHLVBasedIncome.NewRow(); drHLVBasedIncome["HLVIncomeType"] = "Insurance Cover Required"; drHLVBasedIncome["HLVIncomeValue"] = convertUSCurrencyFormat(Math.Round(double.Parse(((HLVbasedIncome - netWorth) - sumAssuredLI).ToString()), 0)); dtHLVBasedIncome.Rows.Add(drHLVBasedIncome); dsCustomerFPReportDetails.Tables.Add(dtHLVBasedIncome); foreach (DataRow dr in dsCustomerFPReportDetails.Tables["CashFlow"].Rows) { if (dr["CashCategory"].ToString() == "Annual Surplus") { if (!string.IsNullOrEmpty(dr["Amount"].ToString())) { surplus = double.Parse(dr["Amount"].ToString()); } } } foreach (DataRow dr in dsCustomerFPReportDetails.Tables["LifeInsurance"].Rows) { if (!string.IsNullOrEmpty(dr["InsuranceValues"].ToString())) { lifeProtectionTotal += double.Parse(dr["InsuranceValues"].ToString()); } } foreach (DataRow dr in dsCustomerFPReportDetails.Tables["AssetDetails"].Rows) { if (dr["AssetGroupCode"].ToString() == "MF") { if (!string.IsNullOrEmpty(dr["AssetValues"].ToString())) { totalMF = double.Parse(dr["AssetValues"].ToString()); } } else if (dr["AssetGroupCode"].ToString() == "DE") { if (!string.IsNullOrEmpty(dr["AssetValues"].ToString())) { totalEquity = double.Parse(dr["AssetValues"].ToString()); } } else if (dr["AssetGroupCode"].ToString() == "FI") { if (!string.IsNullOrEmpty(dr["AssetValues"].ToString())) { totalFixedIncome = double.Parse(dr["AssetValues"].ToString()); } } else if (dr["AssetGroupCode"].ToString() == "OT" || dr["AssetGroupCode"].ToString() == "PG" || dr["AssetGroupCode"].ToString() == "GS" || dr["AssetGroupCode"].ToString() == "CS" || dr["AssetGroupCode"].ToString() == "SP" || dr["AssetGroupCode"].ToString() == "PM" || dr["AssetGroupCode"].ToString() == "GD" || dr["AssetGroupCode"].ToString() == "CM" || dr["AssetGroupCode"].ToString() == "CL") { if (!string.IsNullOrEmpty(dr["AssetValues"].ToString())) { totalOther += double.Parse(dr["AssetValues"].ToString()); } if (dr["AssetGroupCode"].ToString() == "CS") { if (!string.IsNullOrEmpty(dr["AssetValues"].ToString())) { cashAndSaving = double.Parse(dr["AssetValues"].ToString()); } } } } //**************Emergency Fund Ratio:***************** if (totalExpenseMonthly != 0) { solvency_Ratio = cashAndSaving / totalExpenseMonthly; } //**************Emergency Fund Ratio:***************** if (totalEquity > 0) { strInvestment = "Your current investments are ` " + convertUSCurrencyFormat(totalEquity) + " in Equity"; } else { strInvestment = string.Empty; } if (totalMF > 0) { if (!string.IsNullOrEmpty(strInvestment)) { if (totalFixedIncome > 0 || totalOther > 0) { strInvestment += ", " + "` " + convertUSCurrencyFormat(totalMF) + " in Mutual Fund"; } else if (totalFixedIncome == 0 && totalOther == 0) { strInvestment += " and " + "` " + convertUSCurrencyFormat(totalMF) + " in Mutual Fund"; } } else { strInvestment = "Your current investments are ` " + convertUSCurrencyFormat(totalMF) + " in Mutual Fund"; } } if (totalFixedIncome > 0) { if (!string.IsNullOrEmpty(strInvestment)) { if (totalOther > 0) { strInvestment += ", " + "` " + convertUSCurrencyFormat(totalFixedIncome) + " in Fixed Income "; } else { strInvestment += " and " + "` " + convertUSCurrencyFormat(totalFixedIncome) + " in Fixed Income "; } } else { strInvestment = "Your current investments are ` " + convertUSCurrencyFormat(totalFixedIncome) + " in Fixed Income "; } } if (totalOther > 0) { if (!string.IsNullOrEmpty(strInvestment)) { strInvestment += " and " + "` " + convertUSCurrencyFormat(totalOther) + " in Others"; } else { strInvestment = "Your current investments is ` " + convertUSCurrencyFormat(totalOther) + " in Others"; } } foreach (DataRow dr in dsCustomerFPReportDetails.Tables["LiabilitiesDetail"].Rows) { if (dr["LoanType"].ToString() == "Home Loan") { if (dr["LoanValues"] != null) { strHomeLoan = "Principal Outstanding is ` " + convertUSCurrencyFormat(double.Parse(dr["LoanValues"].ToString())); } } else if (dr["LoanType"].ToString() == "Home Loan") { if (dr["LoanValues"] != null) { strAutoLoan = "Principal Outstanding is ` " + convertUSCurrencyFormat(double.Parse(dr["LoanValues"].ToString())); } } } dtCurrentObservation = new DataTable("CurrentObservation"); dtCurrentObservation.Columns.Add("ObjType"); dtCurrentObservation.Columns.Add("ObjSummary"); DataRow drCurrentObservation; if (Math.Abs(netWorth) > 0) { drCurrentObservation = dtCurrentObservation.NewRow(); drCurrentObservation["ObjType"] = "Net Worth"; drCurrentObservation["ObjSummary"] = "Your current Net Worth is ` " + convertUSCurrencyFormat(netWorth).ToString(); dtCurrentObservation.Rows.Add(drCurrentObservation); } if (Math.Abs(surplus) > 0) { drCurrentObservation = dtCurrentObservation.NewRow(); drCurrentObservation["ObjType"] = "Cash flow"; drCurrentObservation["ObjSummary"] = "Your current surplus is ` " + convertUSCurrencyFormat(surplus).ToString() + " per year"; dtCurrentObservation.Rows.Add(drCurrentObservation); } if (Math.Abs(lifeProtectionTotal) > 0) { drCurrentObservation = dtCurrentObservation.NewRow(); drCurrentObservation["ObjType"] = "Protection"; drCurrentObservation["ObjSummary"] = "You have Life insurance protection of ` " + convertUSCurrencyFormat(lifeProtectionTotal).ToString(); dtCurrentObservation.Rows.Add(drCurrentObservation); } if (Math.Abs(totalMF) > 0 || Math.Abs(totalEquity) > 0 || Math.Abs(totalFixedIncome) > 0 || Math.Abs(totalOther) > 0) { drCurrentObservation = dtCurrentObservation.NewRow(); drCurrentObservation["ObjType"] = "Investments"; drCurrentObservation["ObjSummary"] = strInvestment; dtCurrentObservation.Rows.Add(drCurrentObservation); } if (Math.Abs(homeLoan) > 0) { drCurrentObservation = dtCurrentObservation.NewRow(); drCurrentObservation["ObjType"] = "Home Loan"; drCurrentObservation["ObjSummary"] = strHomeLoan; dtCurrentObservation.Rows.Add(drCurrentObservation); } if (Math.Abs(autoLoan) > 0) { drCurrentObservation = dtCurrentObservation.NewRow(); drCurrentObservation["ObjType"] = "Car Loan"; drCurrentObservation["ObjSummary"] = strAutoLoan; dtCurrentObservation.Rows.Add(drCurrentObservation); } dsCustomerFPReportDetails.Tables.Add(dtCurrentObservation); dtHealthAnalysis = new DataTable("HealthAnalysis"); dtHealthAnalysis.Columns.Add("Ratio"); dtHealthAnalysis.Columns.Add("value"); DataRow drHealthAnalysis; foreach (DataRow dr in dsCustomerFPReportDetails.Tables["RiskProfile"].Rows) { if (dr["Class"].ToString() == "Equity") { currEquity = double.Parse(dr["CurrentPercentage"].ToString()); } } drHealthAnalysis = dtHealthAnalysis.NewRow(); //drHealthAnalysis["Ratio"] = "Financial Asset allocation -equity(%)"; //drHealthAnalysis["value"] = convertUSCurrencyFormat(Math.Round(currEquity, 3)).ToString(); drHealthAnalysis["Ratio"] = "Gearing Ratio (%)"; drHealthAnalysis["value"] = 39; dtHealthAnalysis.Rows.Add(drHealthAnalysis); foreach (DataRow dr in dsCustomerFPReportDetails.Tables["MonthlyGoalTotal"].Rows) { if (!string.IsNullOrEmpty(dr["MonthyTotal"].ToString())) { toatlGoalAmount = double.Parse(dr["MonthyTotal"].ToString()); } } drHealthAnalysis = dtHealthAnalysis.NewRow(); //drHealthAnalysis["Ratio"] = "Savings/Income Ratio"; drHealthAnalysis["Ratio"] = "Savings Ratio (%)"; if (totalIncomeMonthly != 0) { //drHealthAnalysis["value"] =Math.Round((toatlGoalAmount / totalIncome), 3).ToString(); drHealthAnalysis["value"] = 56; } else { drHealthAnalysis["value"] = 56; } dtHealthAnalysis.Rows.Add(drHealthAnalysis); drHealthAnalysis = dtHealthAnalysis.NewRow(); //drHealthAnalysis["Ratio"] = "Loan/Financial Assets Ratio"; drHealthAnalysis["Ratio"] = "Emergency Fund Ratio (Months)"; if (asset != 0) { //drHealthAnalysis["value"] = Math.Round((liabilities / asset), 3).ToString(); drHealthAnalysis["value"] = 50; } else { drHealthAnalysis["value"] = 50; } dtHealthAnalysis.Rows.Add(drHealthAnalysis); dsCustomerFPReportDetails.Tables.Add(dtHealthAnalysis); //*****************Test Scenario-1***************** //debt_Ratio = -2; //debt_To_Income_Ratio = 44; //life_Insurance_Cover_Ratio = 1; //loan_To_Assets_Ratio = 43; //savings_To_Income_Ratio = 14; //solvency_Ratio = 2; ////*****************Test Scenario-2***************** //debt_Ratio = -1; //debt_To_Income_Ratio = 45; //life_Insurance_Cover_Ratio = 2; //loan_To_Assets_Ratio = 44; //savings_To_Income_Ratio = 15; //solvency_Ratio = 3; ////*****************Test Scenario-3***************** //debt_Ratio = 0; //debt_To_Income_Ratio = 46; //life_Insurance_Cover_Ratio = 3; //loan_To_Assets_Ratio = 45; //savings_To_Income_Ratio = 16; //solvency_Ratio = 4; ////*****************Test Scenario-4***************** //debt_Ratio = 0.5; //debt_To_Income_Ratio = 50; //life_Insurance_Cover_Ratio = 3.5; //loan_To_Assets_Ratio = 50; //savings_To_Income_Ratio = 20; //solvency_Ratio = 10; ////*****************Test Scenario-5***************** //debt_Ratio = 1; //debt_To_Income_Ratio = 65; //life_Insurance_Cover_Ratio = 4; //loan_To_Assets_Ratio = 59; //savings_To_Income_Ratio = 25; //solvency_Ratio = 12; ////*****************Test Scenario-6***************** //debt_Ratio = 2; //debt_To_Income_Ratio = 66; //life_Insurance_Cover_Ratio = 5; //loan_To_Assets_Ratio = 60; //savings_To_Income_Ratio = 26; //solvency_Ratio = 13; ////*****************Test Scenario-7***************** //debt_Ratio = 3; //debt_To_Income_Ratio = 67; //life_Insurance_Cover_Ratio = 6; //loan_To_Assets_Ratio = 61; //savings_To_Income_Ratio = 27; //solvency_Ratio = 14; //adding customer all ratio to dictionary. foreach (DataRow dr in dsCustomerFPReportDetails.Tables["FPRatio"].Rows) { switch (dr[0].ToString()) { case "1": { dicCustomerFPRatio.Add(dr[0].ToString(), decimal.Parse(Math.Round(savings_To_Income_Ratio, 2).ToString())); break; } case "2": { dicCustomerFPRatio.Add(dr[0].ToString(), decimal.Parse(Math.Round(debt_To_Income_Ratio, 2).ToString())); break; } case "4": { dicCustomerFPRatio.Add(dr[0].ToString(), decimal.Parse(Math.Round(solvency_Ratio, 2).ToString())); break; } case "5": { dicCustomerFPRatio.Add(dr[0].ToString(), decimal.Parse(Math.Round(debt_Ratio, 2).ToString())); break; } case "8": { dicCustomerFPRatio.Add(dr[0].ToString(), decimal.Parse(Math.Round(loan_To_Assets_Ratio, 2).ToString())); break; } case "10": { dicCustomerFPRatio.Add(dr[0].ToString(), decimal.Parse(Math.Round(life_Insurance_Cover_Ratio, 2).ToString())); break; } default: break; } } //dicCustomerFPRatio.Add("Savings Ratio", int.Parse(Math.Round(savingRatio).ToString())); //dicCustomerFPRatio.Add("Debt service ratio", int.Parse(Math.Round(debtServiceRatio).ToString())); //dicCustomerFPRatio.Add("Emergency fund ratio", int.Parse(Math.Round(emergencyFundRatio).ToString())); //dicCustomerFPRatio.Add("Gearing Ratio", int.Parse(Math.Round(gearingRatio).ToString())); //dicCustomerFPRatio.Add("Life Insurance Cover", int.Parse(Math.Round(lifeInsuranceCoverRatio).ToString())); //dsCustomerFPReportDetails.Tables.Add("AssetClass",dtAssetClass); //dsCustomerFPReportDetails.Tables.Add(dtPortfolioAllocation); //dtAllGoalChart = new DataTable(); //dtAllGoalChart.Columns.Add("GoalYear"); //dtAllGoalChart.Columns.Add("GoalAmount"); //DataRow drAllGoalChart; //if (dsCustomerFPReportDetails.Tables[5].Rows.Count > 0) //{ // foreach (DataRow drOtherGoal in dsCustomerFPReportDetails.Tables[5].Rows) // { // drAllGoalChart = dtAllGoalChart.NewRow(); // drAllGoalChart["GoalYear"] = drOtherGoal["GoalYear"]; // drAllGoalChart["GoalAmount"] = drOtherGoal["GoalAmount"]; // dtAllGoalChart.Rows.Add(drAllGoalChart); // } //} //if (dsCustomerFPReportDetails.Tables[7].Rows.Count > 0) //{ // foreach (DataRow drOtherGoal in dsCustomerFPReportDetails.Tables[7].Rows) // { // drAllGoalChart = dtAllGoalChart.NewRow(); // drAllGoalChart["GoalYear"] = drOtherGoal["GoalYear"]; // drAllGoalChart["GoalAmount"] = drOtherGoal["FVofCostToday"]; // dtAllGoalChart.Rows.Add(drAllGoalChart); // } //} //dsCustomerFPReportDetails.Tables.Add(dtAllGoalChart); dtCustomerFPRatio = createCustomerRatioTable(dsCustomerFPReportDetails.Tables["FPRatio"], dsCustomerFPReportDetails.Tables["FPRatioDetails"], dicCustomerFPRatio); dsCustomerFPReportDetails.Tables.Add(dtCustomerFPRatio); return(dsCustomerFPReportDetails); }