public Dictionary <string, List <DashboardReportData> > GetNetWealthReportData(int userId)
{
if (db.Database.Connection.State == ConnectionState.Closed)
{
db.Database.Connection.Open();
}
DbCommand cmd = db.Database.Connection.CreateCommand();
cmd.CommandText = "proc_ClientFunding_NetWealthReportData";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("userId", userId));
DataTable dtReportData = new DataTable();
dtReportData.Load(cmd.ExecuteReader());
Dictionary <string, List <DashboardReportData> > oCompleteWealthReportData = new Dictionary <string, List <DashboardReportData> >();
List <DashboardReportData> oReportData = new List <DashboardReportData>();
List <DashboardReportData> oReportDataWealth = new List <DashboardReportData>();
DashboardReportData ActualWealth = new DashboardReportData
{
color = "",
ApplyOrder = "1",
key = "Actual Wealth",
values = new List <List <object> >()
};
DashboardReportData ExpectedWealth = new DashboardReportData
{
color = "",
ApplyOrder = "2",
key = "Expected Wealth",
values = new List <List <object> >()
};
DashboardReportData oData = new DashboardReportData();
foreach (DataRow dr in dtReportData.Rows)
{
oData = oReportData.Find(i => i.key == dr["key"].ToString());
if (oData == null)
{
oData = new DashboardReportData();
oData.key = dr["Key"].ToString();
oData.values = new List <List <object> >();
oReportData.Add(oData);
}
List <object> value = new List <object>();
List <object> actualWealth = new List <object>();
List <object> expectedWealth = new List <object>();
if (oData.key == "Personal Assets")
{
if (dr["OnlyProposed"].ToString() == "0")
{
actualWealth.Add(double.Parse(dr["FundingYear"].ToString()));
actualWealth.Add(double.Parse(dr["ActualAsset"].ToString()));
ActualWealth.values.Add(actualWealth);
}
else
{
actualWealth.Add(double.Parse(dr["FundingYear"].ToString()));
actualWealth.Add(null);
ActualWealth.values.Add(actualWealth);
}
if (dr["ExpectedAsset"].ToString() != "")
{
expectedWealth.Add(double.Parse(dr["FundingYear"].ToString()));
expectedWealth.Add(dr["ExpectedAsset"].ToString() == "" ? null : dr["ExpectedAsset"].ToString());
ExpectedWealth.values.Add(expectedWealth);
}
}
if (dr["OnlyProposed"].ToString() == "0")
{
value.Add(double.Parse(dr["FundingYear"].ToString()));
if (dr["Value"].ToString() == "")
{
value.Add(0);
}
else
{
value.Add(double.Parse(dr["Value"].ToString()));
}
oData.values.Add(value);
}
}
//ActualWealth.color = "#a9bb82";
//ExpectedWealth.color = "#3d7475";
oReportDataWealth.Add(ActualWealth);
oReportDataWealth.Add(ExpectedWealth);
oCompleteWealthReportData.Add("All", oReportData);
oCompleteWealthReportData.Add("Net", oReportDataWealth);
return(oCompleteWealthReportData);
}
public Dictionary <string, List <DashboardReportData> > GetAssetClassesReportData(int userId)
{
if (db.Database.Connection.State == ConnectionState.Closed)
{
db.Database.Connection.Open();
}
DbCommand cmd = db.Database.Connection.CreateCommand();
cmd.CommandText = "proc_ClientFunding_AssetClassesReportData";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("userId", userId));
DataTable dtReportData = new DataTable();
dtReportData.Load(cmd.ExecuteReader());
List <DashboardReportData> oReportData = new List <DashboardReportData>();
List <DashboardReportData> oReportDataGrowth = new List <DashboardReportData>();
DashboardReportData ActualGrowth = new DashboardReportData
{
color = "",
ApplyOrder = "1",
key = "Actual Growth Exposure",
values = new List <List <object> >()
};
DashboardReportData ExpectedGrowth = new DashboardReportData
{
color = "",
ApplyOrder = "2",
key = "Expected Growth Exposure",
values = new List <List <object> >()
};
Dictionary <string, List <DashboardReportData> > oCompleteAssetReportData = new Dictionary <string, List <DashboardReportData> >();
DashboardReportData oData = new DashboardReportData();
foreach (DataRow dr in dtReportData.Rows)
{
oData = oReportData.Find(i => i.key == dr["key"].ToString());
if (oData == null)
{
oData = new DashboardReportData();
oReportDataGrowth = new List <DashboardReportData>();
oData.ApplyOrder = dr["ApplyOrder"].ToString();
oData.key = dr["Key"].ToString();
oData.values = new List <List <object> >();
oReportData.Add(oData);
}
List <object> value = new List <object>();
List <object> actualGrowth = new List <object>();
List <object> expectedGrowth = new List <object>();
if (oData.key == "Property")
{
actualGrowth.Add(double.Parse(dr["FundingYear"].ToString()));
actualGrowth.Add(double.Parse(dr["ActualGrowth"].ToString()));
ActualGrowth.values.Add(actualGrowth);
expectedGrowth.Add(double.Parse(dr["FundingYear"].ToString()));
expectedGrowth.Add(double.Parse(dr["ExpectedGrowth"].ToString()));
ExpectedGrowth.values.Add(expectedGrowth);
}
value.Add(double.Parse(dr["FundingYear"].ToString()));
value.Add(double.Parse(dr["Value"].ToString()));
oData.values.Add(value);
}
//ActualGrowth.color = "#a9bb82";
//ExpectedGrowth.color = "#dde5a8";
oReportDataGrowth.Add(ActualGrowth);
oReportDataGrowth.Add(ExpectedGrowth);
oCompleteAssetReportData.Add("All", oReportData);
oCompleteAssetReportData.Add("Growth", oReportDataGrowth);
return(oCompleteAssetReportData);
}
