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