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