/// <summary>
/// Get the Asset and Liability details for a customer.
/// </summary>
/// <param name="report"></param>
/// <remarks>Porfolio is per Portfolio but Liability is per Customer.</remarks>
/// <returns></returns>
public DataSet GetPortfolioSummary(PortfolioReportVo report, int adviserId)
{
Database db;
DbCommand cmd;
DataSet ds = null;
try
{
db = DatabaseFactory.CreateDatabase("wealtherp");
cmd = db.GetStoredProcCommand("SP_RPT_GetPortfolioSummary");
db.AddInParameter(cmd, "@PortfolioIds", DbType.String, report.PortfolioIds);
db.AddInParameter(cmd, "@StartDate", DbType.DateTime, DateBo.GetPreviousMonthLastDate(report.ToDate));
db.AddInParameter(cmd, "@EndDate", DbType.DateTime, report.ToDate);
db.AddInParameter(cmd, "@AdviserId", DbType.Int32, adviserId);
cmd.CommandTimeout = 60 * 60;
ds = db.ExecuteDataSet(cmd);
DataSet dsLiablities = GetLiabilities(report.PortfolioIds); //Get liability details (Customerwise)
if (dsLiablities != null && dsLiablities.Tables[0].Rows.Count > 0) //Add liabilities data table to Portfolio dataset
{
dsLiablities.Tables[0].TableName = "Liabilities";
ds.Tables.Add(dsLiablities.Tables[0].Copy());
}
else //If no liabilities present add a dummy liability row to avoid errors in Crystal Report.
{
DataTable dtLiabilities = new DataTable();
dtLiabilities.Columns.Add("CustomerId", Type.GetType("System.Int64"));
dtLiabilities.Columns.Add("LoanType");
dtLiabilities.Columns.Add("LoanAmount", Type.GetType("System.Int64"));
dtLiabilities.Columns.Add("CustomerName");
DataRow drLiabilities = dtLiabilities.NewRow();
drLiabilities["CustomerId"] = "-1"; //customer Id is -1 so that it will not be displayed in report.
drLiabilities["CustomerName"] = "CustomerName";
drLiabilities["LoanType"] = "LoanType";
drLiabilities["LoanAmount"] = 0;
dtLiabilities.Rows.Add(drLiabilities);
ds.Tables.Add(dtLiabilities);
}
}
catch (BaseApplicationException Ex)
{
throw Ex;
}
catch (Exception Ex)
{
BaseApplicationException exBase = new BaseApplicationException(Ex.Message, Ex);
NameValueCollection FunctionInfo = new NameValueCollection();
FunctionInfo.Add("Method", "Reports.cs:GetPortfolioSummary()");
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 GetComprehensiveNetworthSummary(PortfolioReportVo report, int adviserId)
{
PortfolioReportsDao portfolioReport = new PortfolioReportsDao();
DataSet dsComprehensiveNetworthSummary = null;
try {
dsComprehensiveNetworthSummary = portfolioReport.GetComprehensiveNetworthSummary(report, adviserId);
}
catch (Exception ex)
{
}
return(dsComprehensiveNetworthSummary);
}
public DataSet GetCustomerAssetAllocationDetails(PortfolioReportVo report, int adviserId, string reportType)
{
PortfolioReportsDao portfolioReport = new PortfolioReportsDao();
DataSet dsAssetAllocation = null;
try
{
dsAssetAllocation = portfolioReport.GetCustomerAssetAllocationDetails(report, adviserId, reportType);
}
catch (Exception ex)
{
}
return(dsAssetAllocation);
}
/// <summary>
/// Get the Asset and Liability details for a customer.
/// </summary>
/// <param name="report"></param>
/// <remarks>Porfolio is per Portfolio but Liability is per Customer.</remarks>
/// <returns></returns>
public DataSet GetCustomerAssetAllocationDetails(PortfolioReportVo report, int adviserId, string reportType)
{
Database db;
DbCommand cmd;
DataSet dsAssetAllocation = null;
try
{
db = DatabaseFactory.CreateDatabase("wealtherp");
cmd = db.GetStoredProcCommand("SP_RPT_GetCustomerAssetAllocation");
db.AddInParameter(cmd, "@PortfolioIds", DbType.String, report.PortfolioIds);
db.AddInParameter(cmd, "@AdviserId", DbType.Int32, adviserId);
db.AddInParameter(cmd, "@Valuation", DbType.DateTime, report.ToDate);
if (!string.IsNullOrEmpty(reportType))
{
db.AddInParameter(cmd, "@ReportType", DbType.String, reportType);
}
cmd.CommandTimeout = 60 * 60;
dsAssetAllocation = db.ExecuteDataSet(cmd);
}
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[1];
objects[0] = report;
FunctionInfo = exBase.AddObject(FunctionInfo, objects);
exBase.AdditionalInformation = FunctionInfo;
ExceptionManager.Publish(exBase);
throw exBase;
}
return(dsAssetAllocation);
}
/// <summary>
/// Get the Asset and Liability details for a customer.
/// </summary>
/// <param name="report"></param>
/// <remarks>Porfolio is per Portfolio but Liability is per Customer.</remarks>
/// <returns></returns>
public DataSet GetComprehensiveNetworthSummary(PortfolioReportVo report, int adviserId)
{
Database db;
DbCommand cmd;
DataSet ds = null;
try
{
db = DatabaseFactory.CreateDatabase("wealtherp");
cmd = db.GetStoredProcCommand("SP_RPT_GetComprehensiveNetworthSummary");
db.AddInParameter(cmd, "@PortfolioIds", DbType.String, report.PortfolioIds);
db.AddInParameter(cmd, "@AsonDate", DbType.DateTime, report.ToDate);
db.AddInParameter(cmd, "@AdviserId", DbType.Int32, adviserId);
cmd.CommandTimeout = 60 * 60;
ds = db.ExecuteDataSet(cmd);
}
catch (BaseApplicationException Ex)
{
throw Ex;
}
catch (Exception Ex)
{
BaseApplicationException exBase = new BaseApplicationException(Ex.Message, Ex);
NameValueCollection FunctionInfo = new NameValueCollection();
FunctionInfo.Add("Method", "Reports.cs:GetPortfolioSummary()");
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 GetPortfolioSummary(PortfolioReportVo report, int adviserId)
{
PortfolioReportsDao portfolioReports = new PortfolioReportsDao();
DataSet dsPortfolio = new DataSet();
DataTable dtAssetPrevious = new DataTable();
DataTable dtAssetCurrent = new DataTable();
DataTable dtAssets = new DataTable();
//DataTable dtLiabilities = new DataTable();
dtAssets.Columns.Add("PortfolioId");
dtAssets.Columns.Add("AssetType");
dtAssets.Columns.Add("PreviousValue", Type.GetType("System.Double"));
dtAssets.Columns.Add("CurrentValue", Type.GetType("System.Double"));
dtAssets.Columns.Add("CustomerId", Type.GetType("System.Int64"));
dtAssets.Columns.Add("CustomerName");
dtAssets.Columns.Add("PortfolioName");
DataSet dsPortfolioSummary = portfolioReports.GetPortfolioSummary(report, adviserId);
dtAssetCurrent = dsPortfolioSummary.Tables[0];
dtAssetPrevious = dsPortfolioSummary.Tables[1];
//dtAssetCurrent.Merge(dtAssetPrevious);
try
{
foreach (DataRow drCurrent in dtAssetCurrent.Rows)
{
foreach (DataRow drPrevious in dtAssetPrevious.Rows)
{
if (drCurrent["PortfolioId"].ToString() == drPrevious["PortfolioId"].ToString() && drCurrent["AssetType"].ToString() == drPrevious["AssetType"].ToString())
{
drCurrent["PreviousValue"] = Convert.ToInt64(drPrevious["PreviousValue"]);
break;
}
}
dtAssets.ImportRow(drCurrent);
}
foreach (DataRow drPrevious in dtAssetPrevious.Rows)
{
string expression = string.Empty;
expression = "PortfolioId = " + drPrevious["PortfolioId"] + " and AssetType = '" + drPrevious["AssetType"] + "'";
if (dtAssets.Select(expression) == null)
{
dtAssets.ImportRow(drPrevious);
break;
}
}
}
catch (Exception ex)
{
throw ex;
}
dsPortfolio.Tables.Add(dtAssets);
dsPortfolioSummary.Tables[2].TableName = "dtLiabilities";
dsPortfolio.Tables.Add(dsPortfolioSummary.Tables[2].Copy());
DataTable dtNetWorth = CalculateCustomersNetWorth(dsPortfolio);
//DataTable dtNetWorth = CalculateNetWorth(dsPortfolio);
dsPortfolio.Tables.Add(dtNetWorth);
return(dsPortfolio);
}
