/// <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);
}
/// <summary>
/// Get Transaction Report
/// </summary>
/// <param name="reports"></param>
/// <returns></returns>
public DataSet GetEquityScripwiseSummary(EquityReportVo reports, int adviserId)
{
Microsoft.Practices.EnterpriseLibrary.Data.Database db;
DbCommand getCustomerNPListCmd;
DataSet dsEquitySectorwise;
try
{
db = DatabaseFactory.CreateDatabase("wealtherp");
getCustomerNPListCmd = db.GetStoredProcCommand("SP_RPT_GetCustomerSectorwiseEqTransactions");
//reports.PortfolioIds = "13708,14675";
db.AddInParameter(getCustomerNPListCmd, "@PortfolioIds", DbType.String, reports.PortfolioIds); //35437
// reports.FromDate = Convert.ToDateTime("01/01/2008");
db.AddInParameter(getCustomerNPListCmd, "@FromDate", DbType.DateTime, DateBo.GetPreviousMonthLastDate(reports.FromDate));
//reports.ToDate = Convert.ToDateTime("01/01/2012");
db.AddInParameter(getCustomerNPListCmd, "@Todate", DbType.DateTime, reports.ToDate);
db.AddInParameter(getCustomerNPListCmd, "@AdviserId", DbType.Int32, adviserId);
getCustomerNPListCmd.CommandTimeout = 60 * 60;
dsEquitySectorwise = db.ExecuteDataSet(getCustomerNPListCmd);
return(dsEquitySectorwise);
}
catch (Exception ex)
{
throw (ex);
}
// return null;
//DataTable dtMFSummary = new DataTable();
//dtMFSummary.Columns.Add("CustomerName");
//dtMFSummary.Columns.Add("CustomerId");
//dtMFSummary.Columns.Add("PortfolioName");
//dtMFSummary.Columns.Add("PortfolioId");
//dtMFSummary.Columns.Add("Category");
//dtMFSummary.Columns.Add("PreviousValue", System.Type.GetType("System.Int32"));
//dtMFSummary.Columns.Add("CurrentValue", System.Type.GetType("System.Int32"));
//DataRow dtRow = dtMFSummary.NewRow();
//// Mahesh
//dtRow = dtMFSummary.NewRow();
//dtRow["CustomerName"] = "Mahesh";
//dtRow["CustomerId"] = "200";
//dtRow["PortfolioName"] = "FOLIOMH1";
//dtRow["PortfolioId"] = 2;
//dtRow["Category"] = "Equity";
//dtRow["PreviousValue"] = 700.555;
//dtRow["CurrentValue"] = 900;
//dtMFSummary.Rows.Add(dtRow);
//dtRow = dtMFSummary.NewRow();
//dtRow["CustomerName"] = "Mahesh";
//dtRow["CustomerId"] = "200";
//dtRow["PortfolioName"] = "FOLIOMH1";
//dtRow["PortfolioId"] = 2;
//dtRow["Category"] = "Hybrid";
//dtRow["PreviousValue"] = 200;
//dtRow["CurrentValue"] = 200;
//dtMFSummary.Rows.Add(dtRow);
//dtRow = dtMFSummary.NewRow();
//dtRow["CustomerName"] = "Mahesh";
//dtRow["CustomerId"] = "200";
//dtRow["PortfolioName"] = "FOLIOMH1";
//dtRow["PortfolioId"] = 2;
//dtRow["Category"] = "Debt";
//dtRow["PreviousValue"] = 400;
//dtRow["CurrentValue"] = 200;
//dtMFSummary.Rows.Add(dtRow);
//dtRow = dtMFSummary.NewRow();
//dtRow["CustomerName"] = "Mahesh";
//dtRow["CustomerId"] = "200";
//dtRow["PortfolioName"] = "FOLIOMH1";
//dtRow["PortfolioId"] = 2;
//dtRow["Category"] = "Others";
//dtRow["PreviousValue"] = 600;
//dtRow["CurrentValue"] = 700;
//dtMFSummary.Rows.Add(dtRow);
////MAHESH 2
//dtRow = dtMFSummary.NewRow();
//dtRow["CustomerName"] = "Mahesh";
//dtRow["CustomerId"] = "200";
//dtRow["PortfolioName"] = "FOLIOMH2";
//dtRow["PortfolioId"] = 20;
//dtRow["Category"] = "Equity";
//dtRow["PreviousValue"] = 700;
//dtRow["CurrentValue"] = 9;
//dtMFSummary.Rows.Add(dtRow);
//dtRow = dtMFSummary.NewRow();
//dtRow["CustomerName"] = "Mahesh";
//dtRow["CustomerId"] = "200";
//dtRow["PortfolioName"] = "FOLIOMH2";
//dtRow["PortfolioId"] = 20;
//dtRow["Category"] = "Hybrid";
//dtRow["PreviousValue"] = 200;
//dtRow["CurrentValue"] = 200;
//dtMFSummary.Rows.Add(dtRow);
//dtRow = dtMFSummary.NewRow();
//dtRow["CustomerName"] = "Mahesh";
//dtRow["CustomerId"] = "200";
//dtRow["PortfolioName"] = "FOLIOMH2";
//dtRow["PortfolioId"] = 20;
//dtRow["Category"] = "Debt";
//dtRow["PreviousValue"] = 400;
//dtRow["CurrentValue"] = 200;
//dtMFSummary.Rows.Add(dtRow);
//dtRow = dtMFSummary.NewRow();
//dtRow["CustomerName"] = "Mahesh";
//dtRow["CustomerId"] = "200";
//dtRow["PortfolioName"] = "FOLIOMH2";
//dtRow["PortfolioId"] = 20;
//dtRow["Category"] = "Others";
//dtRow["PreviousValue"] = 600;
//dtRow["CurrentValue"] = 700;
//dtMFSummary.Rows.Add(dtRow);
////Robin
//dtRow = dtMFSummary.NewRow();
//dtRow["CustomerName"] = "Robin";
//dtRow["CustomerId"] = "100";
//dtRow["PortfolioName"] = "FOLIORT";
//dtRow["PortfolioId"] = 20;
//dtRow["Category"] = "Equity";
//dtRow["PreviousValue"] = 700;
//dtRow["CurrentValue"] = 900;
//dtMFSummary.Rows.Add(dtRow);
//dtRow = dtMFSummary.NewRow();
//dtRow["CustomerName"] = "Robin";
//dtRow["CustomerId"] = "100";
//dtRow["PortfolioName"] = "FOLIORT";
//dtRow["PortfolioId"] = 20;
//dtRow["Category"] = "Hybrid";
//dtRow["PreviousValue"] = 200;
//dtRow["CurrentValue"] = 200;
//dtMFSummary.Rows.Add(dtRow);
//dtRow = dtMFSummary.NewRow();
//dtRow["CustomerName"] = "Robin";
//dtRow["CustomerId"] = "100";
//dtRow["PortfolioName"] = "FOLIORT";
//dtRow["PortfolioId"] = 20;
//dtRow["Category"] = "Debt";
//dtRow["PreviousValue"] = 400;
//dtRow["CurrentValue"] = 200;
//dtMFSummary.Rows.Add(dtRow);
//dtRow = dtMFSummary.NewRow();
//dtRow["CustomerName"] = "Robin";
//dtRow["CustomerId"] = "100";
//dtRow["PortfolioName"] = "FOLIORT";
//dtRow["PortfolioId"] = 20;
//dtRow["Category"] = "Others";
//dtRow["PreviousValue"] = 600;
//dtRow["CurrentValue"] = 700;
//dtMFSummary.Rows.Add(dtRow);
// return dtMFSummary;
}
public DataSet GetEquityTransaction(EquityReportVo reports, int adviserId)
{
Microsoft.Practices.EnterpriseLibrary.Data.Database db;
DbCommand getCustomerNPListCmd;
DataSet dsEquityTransactionwise;
try
{
db = DatabaseFactory.CreateDatabase("wealtherp");
getCustomerNPListCmd = db.GetStoredProcCommand("SP_RPT_GetCustomerEquityTransactions");
//reports.PortfolioIds = "13708,14675";
db.AddInParameter(getCustomerNPListCmd, "@PortfolioIds", DbType.String, reports.PortfolioIds); //35437
// reports.FromDate = Convert.ToDateTime("01/01/2008");
db.AddInParameter(getCustomerNPListCmd, "@FromDate", DbType.DateTime, DateBo.GetPreviousMonthLastDate(reports.FromDate));
//reports.ToDate = Convert.ToDateTime("01/01/2012");
db.AddInParameter(getCustomerNPListCmd, "@Todate", DbType.DateTime, reports.ToDate);
getCustomerNPListCmd.CommandTimeout = 60 * 60;
dsEquityTransactionwise = db.ExecuteDataSet(getCustomerNPListCmd);
return(dsEquityTransactionwise);
}
catch (Exception ex)
{
throw (ex);
}
}
/// <summary>
/// Get MF All Position data for MF Summary Report(Data for subreports).
/// </summary>
/// <param name="report"></param>
/// <returns></returns>
public DataSet GetMFFundSummaryReportAllPosition(MFReportVo report, int adviserId)
{
Microsoft.Practices.EnterpriseLibrary.Data.Database db;
DbCommand getCustomerNPListCmd;
DataSet dsMFCategorySummary;
try
{
db = DatabaseFactory.CreateDatabase("wealtherp");
getCustomerNPListCmd = db.GetStoredProcCommand("SP_RPT_MF_FundSummaryAllPosition");
db.AddInParameter(getCustomerNPListCmd, "@PortfolioIds", DbType.String, report.PortfolioIds);
db.AddInParameter(getCustomerNPListCmd, "@StartDate", DbType.DateTime, DateBo.GetPreviousMonthLastDate(report.FromDate));
db.AddInParameter(getCustomerNPListCmd, "@EndDate", DbType.DateTime, report.ToDate);
db.AddInParameter(getCustomerNPListCmd, "@AdviserId", DbType.Int32, adviserId);
getCustomerNPListCmd.CommandTimeout = 60 * 60;
dsMFCategorySummary = db.ExecuteDataSet(getCustomerNPListCmd);
}
catch (Exception ex)
{
throw (ex);
}
return(dsMFCategorySummary);
}
private bool ExportToPDF(MFReportVo reportVo)
{
MFReportsBo mfReports = new MFReportsBo();
CustomerBo customerBo = new CustomerBo();
AdvisorBo advisorBo = new AdvisorBo();
////////////////////////////report = (MFReportVo)Session["reportParams"];
//customerVo = (CustomerVo)Session["CusVo"];
//if (Session["CusVo"] != null)
// customerVo = (CustomerVo)Session["CusVo"];
string fileExtension = ".pdf";
string finalReportPath = string.Empty;
string exportReportFullPath = string.Empty;
crmain = new ReportDocument();
try
{
if (string.IsNullOrEmpty(tempAdviserId))
{
tempAdviserId = reportVo.AdviserId.ToString();
advisorVo = advisorBo.GetAdvisor(reportVo.AdviserId);
}
if (string.IsNullOrEmpty(tempCustomerId))
{
tempCustomerId = reportVo.CustomerIds.ToString();
customerVo = customerBo.GetCustomer(Convert.ToInt32(reportVo.CustomerIds));
}
if (tempAdviserId != reportVo.AdviserId.ToString())
{
advisorVo = advisorBo.GetAdvisor(reportVo.AdviserId);
}
if (tempRMId != reportVo.RMId.ToString())
{
customerRMVo = adviserStaffBo.GetAdvisorStaffDetails(reportVo.RMId);
}
if (tempCustomerId != reportVo.CustomerIds.ToString())
{
customerVo = customerBo.GetCustomer(Convert.ToInt32(reportVo.CustomerIds));
}
if (string.IsNullOrEmpty(tempRMId))
{
tempRMId = reportVo.RMId.ToString();
customerRMVo = adviserStaffBo.GetAdvisorStaffDetails(customerVo.RmId);
}
switch (reportVo.SubType)
{
case "CAPITAL_GAIN_SUMMARY":
{
finalReportPath = reportFilePath + @"\CapitalGainSummary.rpt";
crmain.Load(finalReportPath);
DataTable dtCapitalGainSummary = mfReports.GetCapitalGainSummaryReport(reportVo);
if (dtCapitalGainSummary.Rows.Count > 0)
{
crmain.SetDataSource(dtCapitalGainSummary);
setLogo(adviserLogoPath);
crmain.SetParameterValue("DateRange", "Period: " + reportVo.FromDate.ToString("dd/MM/yy") + " to " + reportVo.ToDate.ToString("dd/MM/yy"));
//crmain.SetParameterValue("FromDate", report.FromDate.ToShortDateString());
//crmain.SetParameterValue("ToDate", report.ToDate.ToShortDateString());
AssignReportViewerProperties();
reportFileName = reportVo.SubType + "_" + DateTime.Now.Ticks.ToString() + fileExtension;
exportReportFullPath = savedLocation + @"/" + reportFileName;
crmain.ExportToDisk(ExportFormatType.PortableDocFormat, exportReportFullPath);
reportStatus = "Exported_Successfully";
}
else
{
reportStatus = "No_Record_Found";
}
break;
}
case "CAPITAL_GAIN_DETAILS":
{
finalReportPath = reportFilePath + @"\CapitalGainDetails.rpt";
crmain.Load(finalReportPath);
DataTable dtCapitalGainDetails = mfReports.GetCapitalGainDetailsReport(reportVo);
if (dtCapitalGainDetails.Rows.Count > 0)
{
crmain.SetDataSource(dtCapitalGainDetails);
setLogo(adviserLogoPath);
crmain.SetParameterValue("CustomerName", customerVo.FirstName + " " + customerVo.MiddleName + " " + customerVo.LastName);
crmain.SetParameterValue("DateRange", "Period: " + reportVo.FromDate.ToString("dd/MM/yy") + " to " + reportVo.ToDate.ToString("dd/MM/yy"));
//crmain.SetParameterValue("FromDate", report.FromDate.ToShortDateString());
//crmain.SetParameterValue("ToDate", report.ToDate.ToShortDateString());
AssignReportViewerProperties();
reportFileName = reportVo.SubType + "_" + DateTime.Now.Ticks.ToString() + fileExtension;
exportReportFullPath = savedLocation + @"/" + reportFileName;
crmain.ExportToDisk(ExportFormatType.PortableDocFormat, exportReportFullPath);
reportStatus = "Exported_Successfully";
}
else
{
reportStatus = "No_Record_Found";
}
break;
}
case "CATEGORY_WISE":
{
finalReportPath = reportFilePath + @"\MFFundSummary.rpt";
crmain.Load(finalReportPath);
DataSet dsMFFundSummary = mfReports.GetMFFundSummaryReport(reportVo, advisorVo.advisorId);
if (dsMFFundSummary.Tables[0].Rows.Count > 0 || dsMFFundSummary.Tables[1].Rows.Count > 0)
{
crmain.Subreports["OpenPositionReport"].Database.Tables[0].SetDataSource(dsMFFundSummary.Tables[0]);
crmain.Subreports["AllPositionReport1"].Database.Tables[0].SetDataSource(dsMFFundSummary.Tables[1]);
crmain.Subreports["AllPositionReport2"].Database.Tables[0].SetDataSource(dsMFFundSummary.Tables[1]);
setLogo(adviserLogoPath);
crmain.SetParameterValue("CustomerName", customerVo.FirstName + " " + customerVo.MiddleName + " " + customerVo.LastName);
crmain.SetParameterValue("DateRange", "As on: " + reportVo.ToDate.ToString("dd/MM/yy"));
crmain.SetParameterValue("FromDate", reportVo.FromDate.ToString("dd/MM/yy"));
crmain.SetParameterValue("ToDate", reportVo.FromDate.ToString("dd/MM/yy"));
crmain.SetParameterValue("PreviousMonthDate", DateBo.GetPreviousMonthLastDate(reportVo.FromDate).ToString("dd/MM/yy"));
crmain.SetParameterValue("AsOnDate", reportVo.ToDate.ToString("dd/MM/yy"));
AssignReportViewerProperties();
reportFileName = reportVo.SubType + "_" + DateTime.Now.Ticks.ToString() + fileExtension;
exportReportFullPath = savedLocation + @"/" + reportFileName;
crmain.ExportToDisk(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat, exportReportFullPath);
reportStatus = "Exported_Successfully";
}
else
{
reportStatus = "No_Record_Found";
}
break;
}
case "TRANSACTION_REPORT":
{
finalReportPath = reportFilePath + @"\MFTransactions.rpt";
crmain.Load(finalReportPath);
DataTable dtTransactions = mfReports.GetTransactionReport(reportVo);
if (dtTransactions.Rows.Count > 0)
{
crmain.SetDataSource(dtTransactions);
setLogo(adviserLogoPath);
crmain.SetParameterValue("CustomerName", customerVo.FirstName + " " + customerVo.MiddleName + " " + customerVo.LastName);
//if (!String.IsNullOrEmpty(dtTransactions.Rows[0]["CustomerName"].ToString()))
// crmain.SetParameterValue("CustomerName", "Cust");
crmain.SetParameterValue("DateRange", "Period: " + reportVo.FromDate.ToString("dd/MM/yy") + " to " + reportVo.ToDate.ToString("dd/MM/yy"));
crmain.SetParameterValue("FromDate", reportVo.FromDate.ToString("dd/MM/yy"));
crmain.SetParameterValue("ToDate", reportVo.ToDate.ToString("dd/MM/yy"));
AssignReportViewerProperties();
reportFileName = reportVo.SubType + "_" + DateTime.Now.Ticks.ToString() + fileExtension;
exportReportFullPath = savedLocation + @"/" + reportFileName;
crmain.ExportToDisk(ExportFormatType.PortableDocFormat, exportReportFullPath);
reportStatus = "Exported_Successfully";
}
else
{
reportStatus = "No_Record_Found";
}
break;
}
case "DIVIDEND_STATEMENT":
{
finalReportPath = reportFilePath + @"\MFDividend.rpt";
crmain.Load(finalReportPath);
DataTable dtDividend = mfReports.GetDivdendReport(reportVo);
if (dtDividend.Rows.Count > 0)
{
crmain.SetDataSource(dtDividend);
setLogo(adviserLogoPath);
crmain.SetParameterValue("CustomerName", customerVo.FirstName + " " + customerVo.MiddleName + " " + customerVo.LastName);
//if (!String.IsNullOrEmpty(dtDividend.Rows[0]["Name"].ToString()))
//crmain.SetParameterValue("CustomerName", "--");
crmain.SetParameterValue("DateRange", "Period: " + reportVo.FromDate.ToString("dd/MM/yy") + " to " + reportVo.ToDate.ToString("dd/MM/yy"));
crmain.SetParameterValue("FromDate", reportVo.FromDate.ToString("dd/MM/yy"));
crmain.SetParameterValue("ToDate", reportVo.ToDate.ToString("dd/MM/yy"));
AssignReportViewerProperties();
reportFileName = reportVo.SubType + "_" + DateTime.Now.Ticks.ToString() + fileExtension;
exportReportFullPath = savedLocation + @"/" + reportFileName;
crmain.ExportToDisk(ExportFormatType.PortableDocFormat, exportReportFullPath);
reportStatus = "Exported_Successfully";
}
else
{
reportStatus = "No_Record_Found";
}
break;
}
case "RETURNS_PORTFOLIO":
{
finalReportPath = reportFilePath + @"\MFReturns.rpt";
crmain.Load(finalReportPath);
//DataTable dtDividendReturnHolding = mfReports.GetDivdendReport(reportVo);
CustomerPortfolioBo customerPortfolioBo = new CustomerPortfolioBo();
//DataTable dtReturnsPortfolio = mfReports.GetReturnSummaryReport(reportVo, advisorVo.advisorId);
//DataTable dtPortfolioXIRR = customerPortfolioBo.GetCustomerPortfolioLabelXIRR(reportVo.PortfolioIds);
//dtPortfolioXIRR = GetAbsolutereturnToXIRRDt(dtPortfolioXIRR, dtReturnsPortfolio);
DataSet dsReturnsPortfolioHoldings = mfReports.GetReturnSummaryReport(reportVo, advisorVo.advisorId);
DataTable dtReturnsPortfolio = dsReturnsPortfolioHoldings.Tables[0];
DataTable dtPortfolioXIRR = customerPortfolioBo.GetCustomerPortfolioLabelXIRR(reportVo.PortfolioIds);
dtReturnsPortfolio = dsReturnsPortfolioHoldings.Tables[1];
dtPortfolioXIRR = GetAbsolutereturnToXIRRDt(dtPortfolioXIRR, dtReturnsPortfolio);
if (dtReturnsPortfolio.Rows.Count > 0)
{
crmain.SetDataSource(dsReturnsPortfolioHoldings.Tables[0]);
setLogo(adviserLogoPath);
crmain.Subreports["PortfolioXIRR"].Database.Tables["PortfolioXIRR"].SetDataSource(dtPortfolioXIRR);
crmain.SetParameterValue("CustomerName", customerVo.FirstName + " " + customerVo.MiddleName + " " + customerVo.LastName);
//if (!String.IsNullOrEmpty(dtDividend.Rows[0]["Name"].ToString()))
//crmain.SetParameterValue("CustomerName", "--");
crmain.SetParameterValue("AsOnDate", reportVo.FromDate.ToString("dd/MM/yy"));
crmain.SetParameterValue("DateRange", "As on: " + reportVo.ToDate.ToString("dd/MM/yy"));
AssignReportViewerProperties();
reportFileName = reportVo.SubType + "_" + DateTime.Now.Ticks.ToString() + fileExtension;
exportReportFullPath = savedLocation + @"/" + reportFileName;
crmain.ExportToDisk(ExportFormatType.PortableDocFormat, exportReportFullPath);
reportStatus = "Exported_Successfully";
}
else
{
reportStatus = "No_Record_Found";
}
break;
}
case "COMPREHENSIVE":
{
finalReportPath = reportFilePath + @"\ComprehensiveMFReport.rpt";
crmain.Load(finalReportPath);
CustomerPortfolioBo customerPortfolioBo = new CustomerPortfolioBo();
DataSet dsReturnsPortfolio = mfReports.GetPortfolioAnalyticsReport(reportVo, advisorVo.advisorId);
DataTable dtReturnsPortfolio = dsReturnsPortfolio.Tables[0];
DataTable dtPortfolioXIRRComp = customerPortfolioBo.GetCustomerPortfolioLabelXIRR(reportVo.PortfolioIds);
dtReturnsPortfolio = dsReturnsPortfolio.Tables[1];
DataTable dtPortfolioXIRR = GetAbsolutereturnToXIRRDt(dtPortfolioXIRRComp, dtReturnsPortfolio);
if (dsReturnsPortfolio.Tables[0].Rows.Count > 0)
{
crmain.SetDataSource(dsReturnsPortfolio.Tables[0]);
crmain.Subreports["Portfolio_XIRR"].Database.Tables["PortfolioXIRR"].SetDataSource(dtPortfolioXIRRComp);
//crmain.Subreports["MFSchemePerformance"].Database.Tables[0].SetDataSource(dsReturnsPortfolio.Tables[1]);
//crmain.Subreports["MFTopTenHoldings"].Database.Tables[0].SetDataSource(dsReturnsPortfolio.Tables[2]);
//crmain.Subreports["MFTopTenSectors"].Database.Tables[0].SetDataSource(dsReturnsPortfolio.Tables[5]);
setLogo(adviserLogoPath);
crmain.SetParameterValue("CustomerName", customerVo.FirstName + " " + customerVo.MiddleName + " " + customerVo.LastName);
crmain.SetParameterValue("AsOnDate", reportVo.FromDate.ToString("dd/MM/yy"));
crmain.SetParameterValue("DateRange", "As on: " + reportVo.ToDate.ToString("dd/MM/yy"));
AssignReportViewerProperties();
reportFileName = reportVo.SubType + "_" + DateTime.Now.Ticks.ToString() + fileExtension;
exportReportFullPath = savedLocation + @"/" + reportFileName;
crmain.ExportToDisk(ExportFormatType.PortableDocFormat, exportReportFullPath);
reportStatus = "Exported_Successfully";
}
else
{
reportStatus = "No_Record_Found";
}
break;
}
case "DIVIDEND_SUMMARY":
{
finalReportPath = reportFilePath + @"\MFDividendSummary.rpt";
crmain.Load(finalReportPath);
DataTable dtDividendSummary = mfReports.GetDivdendReport(reportVo);
//customerVo = (CustomerVo)Session["CusVo"];
if (dtDividendSummary.Rows.Count > 0)
{
crmain.SetDataSource(dtDividendSummary);
setLogo(adviserLogoPath);
//if (!String.IsNullOrEmpty(dtDividend.Rows[0]["Name"].ToString()))
crmain.SetParameterValue("CustomerName", customerVo.FirstName + " " + customerVo.MiddleName + " " + customerVo.LastName);
crmain.SetParameterValue("DateRange", "Period: " + reportVo.FromDate.ToString("dd/MM/yy") + " to " + reportVo.ToDate.ToString("dd/MM/yy"));
crmain.SetParameterValue("FromDate", reportVo.FromDate.ToString("dd/MM/yy"));
crmain.SetParameterValue("ToDate", reportVo.ToDate.ToString("dd/MM/yy"));
AssignReportViewerProperties();
reportFileName = reportVo.SubType + "_" + DateTime.Now.Ticks.ToString() + fileExtension;
exportReportFullPath = savedLocation + @"/" + reportFileName;
crmain.ExportToDisk(ExportFormatType.PortableDocFormat, exportReportFullPath);
reportStatus = "Exported_Successfully";
}
else
{
reportStatus = "No_Record_Found";
}
break;
}
//Added Three more cases for Display three new report : Author-Pramod
//case "RETURNS_PORTFOLIO_REALIZED":
// {
// finalReportPath = reportFilePath + @"\MFReturnsRealized.rpt";
// crmain.Load(finalReportPath);
// DataTable dtReturnsREPortfolio = mfReports.GetMFReturnRESummaryReport(reportVo, advisorVo.advisorId);
// if (dtReturnsREPortfolio.Rows.Count > 0)
// {
// crmain.SetDataSource(dtReturnsREPortfolio);
// setLogo(adviserLogoPath);
// crmain.SetParameterValue("CustomerName", customerVo.FirstName + " " + customerVo.MiddleName + " " + customerVo.LastName);
// crmain.SetParameterValue("DateRange", "As on: " + reportVo.ToDate.ToShortDateString());
// crmain.SetParameterValue("AsOnDate", reportVo.FromDate.ToShortDateString());
// AssignReportViewerProperties();
// reportFileName = reportVo.SubType + "_" + DateTime.Now.Ticks.ToString() + fileExtension;
// exportReportFullPath = savedLocation + @"/" + reportFileName;
// crmain.ExportToDisk(ExportFormatType.PortableDocFormat, exportReportFullPath);
// }
// break;
// }
case "ELIGIBLE_CAPITAL_GAIN_DETAILS":
{
finalReportPath = reportFilePath + @"\EligibleCapitalGainsSummary.rpt";
crmain.Load(finalReportPath);
DataTable dtEligibleCapitalGainsDetails = mfReports.GetEligibleCapitalGainDetailsReport(reportVo);
if (dtEligibleCapitalGainsDetails.Rows.Count > 0)
{
crmain.SetDataSource(dtEligibleCapitalGainsDetails);
setLogo(adviserLogoPath);
crmain.SetParameterValue("CustomerName", customerVo.FirstName + " " + customerVo.MiddleName + " " + customerVo.LastName);
crmain.SetParameterValue("DateRange", "As on: " + reportVo.ToDate.ToString("dd/MM/yy"));
//crmain.SetParameterValue("AsOnDate", report.FromDate.ToShortDateString());
AssignReportViewerProperties();
reportFileName = reportVo.SubType + "_" + DateTime.Now.Ticks.ToString() + fileExtension;
exportReportFullPath = savedLocation + @"/" + reportFileName;
crmain.ExportToDisk(ExportFormatType.PortableDocFormat, exportReportFullPath);
reportStatus = "Exported_Successfully";
}
else
{
reportStatus = "No_Record_Found";
}
break;
}
case "ELIGIBLE_CAPITAL_GAIN_SUMMARY":
{
finalReportPath = reportFilePath + @"\EligibleCapitalGainsSummary.rpt";
crmain.Load(finalReportPath);
DataTable dtEligibleCapitalGainsSummary = mfReports.GetEligibleCapitalGainDetailsReport(reportVo);
if (dtEligibleCapitalGainsSummary.Rows.Count > 0)
{
crmain.SetDataSource(dtEligibleCapitalGainsSummary);
setLogo(adviserLogoPath);
crmain.SetParameterValue("CustomerName", customerVo.FirstName + " " + customerVo.MiddleName + " " + customerVo.LastName);
//crmain.SetParameterValue("DateRange", "As on: " + report.ToDate.ToShortDateString());
crmain.SetParameterValue("DateRange", "As on: " + reportVo.ToDate.ToString("dd/MM/yy"));
AssignReportViewerProperties();
reportFileName = reportVo.SubType + "_" + DateTime.Now.Ticks.ToString() + fileExtension;
exportReportFullPath = savedLocation + @"/" + reportFileName;
crmain.ExportToDisk(ExportFormatType.PortableDocFormat, exportReportFullPath);
reportStatus = "Exported_Successfully";
}
else
{
reportStatus = "No_Record_Found";
}
}
break;
case "TRANSACTION_REPORT_OPEN_CLOSE_BALANCE":
finalReportPath = reportFilePath + @"\MFOpenCloseTransactionReport.rpt";
crmain.Load(finalReportPath);
DataTable dtOpeningClosingTransactions = mfReports.GetOpeningClosingTransactionReport(reportVo);
if (dtOpeningClosingTransactions.Rows.Count > 0)
{
setLogo(adviserLogoPath);
//crmain.SetDataSource(dtOpeningClosingTransactions);
crmain.Database.Tables["MFOpenCloseTransactionReport"].SetDataSource((DataTable)dtOpeningClosingTransactions);
crmain.SetParameterValue("CustomerName", customerVo.FirstName + " " + customerVo.MiddleName + " " + customerVo.LastName);
crmain.SetParameterValue("DateRange", "Period: " + reportVo.FromDate.ToString("dd/MM/yy") + " to " + reportVo.ToDate.ToString("dd/MM/yy"));
crmain.SetParameterValue("FromDate", reportVo.FromDate.ToString("dd/MM/yy"));
crmain.SetParameterValue("ToDate", reportVo.ToDate.ToString("dd/MM/yy"));
AssignReportViewerProperties();
reportFileName = reportVo.SubType + "_" + DateTime.Now.Ticks.ToString() + fileExtension;
exportReportFullPath = savedLocation + @"/" + reportFileName;
crmain.ExportToDisk(ExportFormatType.PortableDocFormat, exportReportFullPath);
reportStatus = "Exported_Successfully";
}
else
{
reportStatus = "No_Record_Found";
}
break;
case "REALIZED_REPORT":
finalReportPath = reportFilePath + @"\MFRealized.rpt";
crmain.Load(finalReportPath);
DataTable dtMFRealized = mfReports.GetMFRealizedReport(reportVo, advisorVo.advisorId);
if (dtMFRealized.Rows.Count > 0)
{
crmain.SetDataSource(dtMFRealized);
setLogo(adviserLogoPath);
crmain.SetParameterValue("CustomerName", customerVo.FirstName + " " + customerVo.MiddleName + " " + customerVo.LastName);
crmain.SetParameterValue("DateRange", "As on: " + reportVo.ToDate.ToString("dd/MM/yy"));
crmain.SetParameterValue("AsOnDate", reportVo.FromDate.ToString("dd/MM/yy"));
AssignReportViewerProperties();
reportFileName = reportVo.SubType + "_" + DateTime.Now.Ticks.ToString() + fileExtension;
exportReportFullPath = savedLocation + @"/" + reportFileName;
crmain.ExportToDisk(ExportFormatType.PortableDocFormat, exportReportFullPath);
reportStatus = "Exported_Successfully";
}
else
{
reportStatus = "No_Record_Found";
}
break;
case "COMPOSITION_REPORT":
finalReportPath = reportFilePath + @"\SchemePerformance.rpt";
crmain.Load(finalReportPath);
DataSet dsCustomerPortfolioComposition = mfReports.GetPortfolioCompositionReport(reportVo, advisorVo.advisorId);
if (dsCustomerPortfolioComposition.Tables[0].Rows.Count > 0)
{
//crmain.SetDataSource(dsCustomerPortfolioComposition.Tables[0]);
crmain.Database.Tables["PortfolioComposition"].SetDataSource(dsCustomerPortfolioComposition.Tables[0]);
crmain.Subreports["MFTopTenHoldings"].Database.Tables["ToptenHoldings"].SetDataSource(dsCustomerPortfolioComposition.Tables[1]);
crmain.Subreports["MFTopTenSectors"].Database.Tables["TopTenSectors"].SetDataSource(dsCustomerPortfolioComposition.Tables[4]);
setLogo(adviserLogoPath);
crmain.SetParameterValue("CustomerName", customerVo.FirstName + " " + customerVo.MiddleName + " " + customerVo.LastName);
crmain.SetParameterValue("DateRange", "As on: " + reportVo.FromDate.ToString("dd/MM/yy"));
crmain.SetParameterValue("AsOnDate", reportVo.ToDate.ToString("dd/MM/yy"));
AssignReportViewerProperties();
reportFileName = reportVo.SubType + "_" + DateTime.Now.Ticks.ToString() + fileExtension;
exportReportFullPath = savedLocation + @"/" + reportFileName;
crmain.ExportToDisk(ExportFormatType.PortableDocFormat, exportReportFullPath);
reportStatus = "Exported_Successfully";
}
else
{
reportStatus = "No_Record_Found";
}
break;
}
//Filling Emails
}
catch (Exception ex)
{
throw (ex);
}
if (!string.IsNullOrEmpty(reportFileName))
{
return(true);
}
else
{
return(false);
}
}
