/// <summary>
/// Returns Dividend Report
/// </summary>
/// <param name="reports"></param>
/// <returns></returns>
public DataTable GetDivdendReport(MFReportVo reports)
{
Microsoft.Practices.EnterpriseLibrary.Data.Database db;
DbCommand getCustomerNPListCmd;
DataSet getCustomerNPDs;
DataSet ds;
try
{
db = DatabaseFactory.CreateDatabase("wealtherp");
getCustomerNPListCmd = db.GetStoredProcCommand("SP_RPT_GetCustomerMFDividend");
//reports.PortfolioIds = "13708,15175";
db.AddInParameter(getCustomerNPListCmd, "@PortfolioIds", DbType.String, reports.PortfolioIds); //35437
db.AddInParameter(getCustomerNPListCmd, "@FromDate", DbType.DateTime, reports.FromDate);
db.AddInParameter(getCustomerNPListCmd, "@Todate", DbType.DateTime, reports.ToDate);
//getCustomerNPListCmd = db.GetSqlStringCommand("select top 20 CMFA_AccountID,CMFT_TransactionDate,PASP_SchemePlanName from CustomerMutualfundtransaction CustomerMutualfundtransaction inner join ProductAMCSchemePlan AS ProductAMCSchemePlan ON CustomerMutualfundtransaction.PASP_SchemePlanCode = ProductAMCSchemePlan.PASP_SchemePlanCode;"); //db.GetStoredProcCommand("SP_GetAdviser");
//db.AddInParameter(getCustomerNPListCmd, "@A_AdviserId", DbType.Int32, 1049);
//db.AddInParameter(getCustomerNPListCmd, "@ValuationDate", DbType.DateTime, "2009-10-08");
getCustomerNPListCmd.CommandTimeout = 60 * 60;
getCustomerNPDs = db.ExecuteDataSet(getCustomerNPListCmd);
ds = getCustomerNPDs;
return(getCustomerNPDs.Tables[0]);
}
catch (Exception ex)
{
throw (ex);
}
}
/// <summary>
/// Get Customer Portfolio Analytics Report Data
/// </summary>
/// <param name="reports"></param>
/// <param name="adviserId"></param>
/// <returns></returns>
public DataSet GetPortfolioAnalyticsReport(MFReportVo reports, int adviserId)
{
Microsoft.Practices.EnterpriseLibrary.Data.Database db;
DbCommand cmdCustomerMFReturns;
DataSet dsCustomerMFReturns;
DataSet ds;
try
{
db = DatabaseFactory.CreateDatabase("wealtherp");
cmdCustomerMFReturns = db.GetStoredProcCommand("SP_RPT_GetCustomerPortfolioAnalytics");
db.AddInParameter(cmdCustomerMFReturns, "@PortfolioIds", DbType.String, reports.PortfolioIds); //35437
db.AddInParameter(cmdCustomerMFReturns, "@FromDate", DbType.DateTime, reports.FromDate);
//db.AddInParameter(cmdCustomerMFReturns, "@AdviserId", DbType.Int32, adviserId);
cmdCustomerMFReturns.CommandTimeout = 60 * 60;
dsCustomerMFReturns = db.ExecuteDataSet(cmdCustomerMFReturns);
//ds = dsCustomerMFReturns;
return(dsCustomerMFReturns);
}
catch (Exception ex)
{
throw (ex);
}
}
private bool ProcessBulkReport(DataTable dtReportParamerValues)
{
bool exportStatus = false;
mfReportVo = new MFReportVo();
mfReportVo = FillReportParamerValues(dtReportParamerValues);
exportStatus = ExportToPDF(mfReportVo);
return(exportStatus);
}
private MFReportVo FillReportParamerValues(DataTable dtReportParamerValues)
{
mfReportVo = new MFReportVo();
foreach (DataRow dr in dtReportParamerValues.Rows)
{
switch (dr["WP_ParameterCode"].ToString())
{
case "STARTDT":
mfReportVo.FromDate = DateTime.Parse(dr["WRD_InputParameterValue"].ToString());
break;
case "ENDDT":
mfReportVo.ToDate = DateTime.Parse(dr["WRD_InputParameterValue"].ToString());
break;
case "RN":
mfReportVo.SubType = dr["WRD_InputParameterValue"].ToString();
break;
case "RT":
mfReportVo.Type = dr["WRD_InputParameterValue"].ToString();
break;
case "AID":
mfReportVo.AdviserId = int.Parse(dr["WRD_InputParameterValue"].ToString());
break;
case "RMID":
mfReportVo.RMId = int.Parse(dr["WRD_InputParameterValue"].ToString());
break;
case "CID":
mfReportVo.CustomerIds += dr["WRD_InputParameterValue"].ToString() + ",";
break;
case "PID":
mfReportVo.PortfolioIds += dr["WRD_InputParameterValue"].ToString() + ",";
break;
}
}
if (mfReportVo.CustomerIds.EndsWith(","))
{
mfReportVo.CustomerIds = mfReportVo.CustomerIds.Substring(0, mfReportVo.CustomerIds.Length - 1);
}
if (mfReportVo.PortfolioIds.EndsWith(","))
{
mfReportVo.PortfolioIds = mfReportVo.PortfolioIds.Substring(0, mfReportVo.PortfolioIds.Length - 1);
}
return(mfReportVo);
}
private MFReportVo GetReportInputData(string reportName, int customerId, int groupCustomerId, ref DateTime dtFrom, ref DateTime dtTo, ref DateTime dtAsOn, string reportDateType)
{
MFReportVo mfReportVo = new MFReportVo();
string customerPortfolioids = string.Empty;
mfReport.ReportName = reportName;
if (reportDateType == "ASON")
{
mfReport.FromDate = dtAsOn;
mfReport.ToDate = dtAsOn;
}
else if (reportDateType == "RANGE")
{
mfReport.FromDate = dtFrom;
mfReport.ToDate = dtTo;
}
mfReport.SubType = "MF";
mfReport.AdviserId = adviserId;
mfReport.CustomerId = customerId;
mfReport.GroupHeadId = groupCustomerId;
if (groupCustomerId != 0)
{
customerPortfolioids = GetGroupCustomerAllPortfolio(groupCustomerId);
}
else
{
customerPortfolioids = GetCustomerAllPortfolio(customerId);
if (!string.IsNullOrEmpty(customerPortfolioids.ToString()))
{
customerPortfolioids = customerPortfolioids.Remove(customerPortfolioids.Length - 1, 1);
}
}
mfReport.PortfolioIds = customerPortfolioids;
return(mfReport);
}
/// <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 void DisplayReport(MFReportVo report, string ReportFormat)
{
MFReportEngineBo MFReportEngineBo = new MFReportEngineBo();
DataSet dsReportHeaders = MFReportEngineBo.GetCustomerReportHeaders(advisorVo.advisorId, report.CustomerId);
//ReportDataSource CustomerDetails = new ReportDataSource("MFReturns_CustomerDetails", dsReportHeaders.Tables[0]);
//ReportDataSource AdviserDetails = new ReportDataSource("MFReturns_AdviserDetails", );
ReportDataSource ReportHeader = new ReportDataSource("MFReturns_ReportHeader", AssignReportHeaderParameter(dsReportHeaders.Tables[0], dsReportHeaders.Tables[1], dsReportHeaders.Tables[2]));
rptViewer.ProcessingMode = ProcessingMode.Local;
rptViewer.Reset();
LocalReport LocalReport = rptViewer.LocalReport;
String reportLogoPath = ConfigurationManager.AppSettings["adviserLogoPath"].ToString();
reportLogoPath = System.Web.HttpContext.Current.Request.MapPath(reportLogoPath + advisorVo.LogoPath);
if (!File.Exists(reportLogoPath))
{
reportLogoPath = string.Empty;
//reportLogoPath = ConfigurationManager.AppSettings[@"//Images//"].ToString();
reportLogoPath = Server.MapPath(@"//Images//spacer.png");
}
reportLogoPath = "file:///" + reportLogoPath;
string ReportName = string.Empty;
switch (report.SubType)
{
case "PORTFOLIO_RETURNS_HOLDING":
LocalReport.ReportPath = Server.MapPath(@"SsrsReportingFiles\MF_Returns_Holding_PDF.rdlc");
DataSet dsMFReturnsHolding = MFReportEngineBo.CreateCustomerMFReturnsHolding(report.CustomerId, report.PortfolioIds, report.FromDate, report.ToDate);
ReportDataSource MFReturnsHolding = new ReportDataSource("MFReturns_MFReturn", dsMFReturnsHolding.Tables[0]);
ReportDataSource PortfolioXIRRHolding = new ReportDataSource("MFReturns_PortfolioXIRR", dsMFReturnsHolding.Tables[1]);
LocalReport.EnableExternalImages = true;
LocalReport.DataSources.Add(MFReturnsHolding);
LocalReport.DataSources.Add(ReportHeader);
//LocalReport.DataSources.Add(AdviserDetails);
LocalReport.DataSources.Add(PortfolioXIRRHolding);
ReportParameter[] paramHolding = new ReportParameter[2];
paramHolding[0] = new ReportParameter("AdviserLogoPath", reportLogoPath);
paramHolding[1] = new ReportParameter("ToDate", report.ToDate.ToString("dd-MMM-yy"));
LocalReport.SetParameters(paramHolding);
ReportName = "MF Returns - Holding " + DateTime.Now.ToString("yyyyMMddhhmmss");
if (ReportFormat == "pdf")
{
ExportInPDF();
Response.Redirect(@"\Reports\TempReports\ViewInPDF\" + PDFNames);
//Download(path, ReportName);
}
else if (ReportFormat == "xls")
{
ExportInExcel();
Download(path, ReportName);
}
else
{
LocalReport.DisplayName = ReportName;
rptViewer.LocalReport.Refresh();
rptViewer.ZoomPercent = 110;
rptViewer.ShowPageNavigationControls = false;
}
break;
case "COMPREHENSIVE_MF_REPORT":
LocalReport.ReportPath = Server.MapPath(@"SsrsReportingFiles\MF_Comprehensive_PDF.rdlc");
DataSet dsMFComprehensive = MFReportEngineBo.CreateCustomerMFComprehensive(report.CustomerId, report.PortfolioIds, report.FromDate, report.ToDate);
ReportDataSource MFComprehensive = new ReportDataSource("MFReturns_MFReturn", dsMFComprehensive.Tables[0]);
ReportDataSource PortfolioXIRRComprehensive = new ReportDataSource("MFReturns_PortfolioXIRR", dsMFComprehensive.Tables[1]);
LocalReport.EnableExternalImages = true;
LocalReport.DataSources.Add(MFComprehensive);
LocalReport.DataSources.Add(ReportHeader);
//LocalReport.DataSources.Add(AdviserDetails);
LocalReport.DataSources.Add(PortfolioXIRRComprehensive);
ReportParameter[] paramComprehensive = new ReportParameter[2];
paramComprehensive[0] = new ReportParameter("AdviserLogoPath", reportLogoPath);
paramComprehensive[1] = new ReportParameter("ToDate", report.ToDate.ToString("dd-MMM-yy"));
LocalReport.SetParameters(paramComprehensive);
ReportName = "MF Comprehensive " + DateTime.Now.ToString("yyyyMMddhhmmss");
if (ReportFormat == "pdf")
{
ExportInPDF();
Response.Redirect(@"\Reports\TempReports\ViewInPDF\" + PDFNames);
//Download(path, ReportName);
}
else if (ReportFormat == "xls")
{
ExportInExcel();
Download(path, ReportName);
}
else
{
LocalReport.DisplayName = ReportName;
LocalReport.Refresh();
rptViewer.ZoomPercent = 110;
rptViewer.ShowPageNavigationControls = false;
}
break;
case "CAPITAL_GAIN_DETAILS":
LocalReport.ReportPath = Server.MapPath(@"SsrsReportingFiles\MF_Capital_Gain_Details_PDF.rdlc");
DataTable dtCapitalGainDetails = MFReportEngineBo.CreateCustomerCapitalGainDetails(report.CustomerId, report.PortfolioIds, report.FromDate, report.ToDate);
ReportDataSource MFCapitalGainDetails = new ReportDataSource("MFReturns_MFCapitalGain", dtCapitalGainDetails);
LocalReport.EnableExternalImages = true;
LocalReport.DataSources.Add(MFCapitalGainDetails);
LocalReport.DataSources.Add(ReportHeader);
//LocalReport.DataSources.Add(AdviserDetails);
ReportParameter[] paramCapitalGainDetail = new ReportParameter[3];
paramCapitalGainDetail[0] = new ReportParameter("AdviserLogoPath", reportLogoPath);
paramCapitalGainDetail[1] = new ReportParameter("ToDate", report.ToDate.ToString("dd-MMM-yy"));
paramCapitalGainDetail[2] = new ReportParameter("FromDate", report.FromDate.ToString("dd-MMM-yy"));
LocalReport.SetParameters(paramCapitalGainDetail);
ReportName = "MF Capital Gain Details " + DateTime.Now.ToString("yyyyMMddhhmmss");
if (ReportFormat == "pdf")
{
ExportInPDF();
Response.Redirect(@"\Reports\TempReports\ViewInPDF\" + PDFNames);
//Download(path, ReportName);
}
else if (ReportFormat == "xls")
{
ExportInExcel();
Download(path, ReportName);
}
else
{
LocalReport.DisplayName = ReportName;
rptViewer.LocalReport.Refresh();
rptViewer.ZoomPercent = 110;
rptViewer.ShowPageNavigationControls = false;
}
break;
case "CAPITAL_GAIN_SUMMARY":
LocalReport.ReportPath = Server.MapPath(@"SsrsReportingFiles\MF_Capital_Gain_Summary_PDF.rdlc");
DataTable dtCapitalGainSummary = MFReportEngineBo.CreateCustomerCapitalGainSummary(report.CustomerId, report.PortfolioIds, report.FromDate, report.ToDate);
ReportDataSource MFCapitalGainSummary = new ReportDataSource("MFReturns_MFCapitalGain", dtCapitalGainSummary);
LocalReport.EnableExternalImages = true;
LocalReport.DataSources.Add(MFCapitalGainSummary);
LocalReport.DataSources.Add(ReportHeader);
//LocalReport.DataSources.Add(AdviserDetails);
ReportParameter[] paramCapitalGainSummary = new ReportParameter[3];
paramCapitalGainSummary[0] = new ReportParameter("AdviserLogoPath", reportLogoPath);
paramCapitalGainSummary[1] = new ReportParameter("ToDate", report.ToDate.ToString("dd-MMM-yy"));
paramCapitalGainSummary[2] = new ReportParameter("FromDate", report.FromDate.ToString("dd-MMM-yy"));
LocalReport.SetParameters(paramCapitalGainSummary);
ReportName = "MF Capital Gain Summary " + DateTime.Now.ToString("yyyyMMddhhmmss");
if (ReportFormat == "pdf")
{
ExportInPDF();
Response.Redirect(@"\Reports\TempReports\ViewInPDF\" + PDFNames);
//Download(path, ReportName);
}
else if (ReportFormat == "xls")
{
ExportInExcel();
Download(path, ReportName);
}
else
{
LocalReport.DisplayName = ReportName;
rptViewer.LocalReport.Refresh();
rptViewer.ZoomPercent = 110;
rptViewer.ShowPageNavigationControls = false;
}
break;
case "ELIGIBLE_CAPITAL_GAIN_DETAILS":
LocalReport.ReportPath = Server.MapPath(@"SsrsReportingFiles\MF_Capital_Gain_Details_PDF.rdlc");
DataTable dtEligibleCapitalGainDetails = MFReportEngineBo.CreateCustomerEligibleCapitalGainDetails(report.CustomerId, report.PortfolioIds, report.FromDate, report.ToDate);
ReportDataSource MFEligibleCapitalGainDetails = new ReportDataSource("MFReturns_MFCapitalGain", dtEligibleCapitalGainDetails);
LocalReport.EnableExternalImages = true;
LocalReport.DataSources.Add(MFEligibleCapitalGainDetails);
LocalReport.DataSources.Add(ReportHeader);
//LocalReport.DataSources.Add(AdviserDetails);
ReportParameter[] paramEligibleCapitalGainDetail = new ReportParameter[4];
paramEligibleCapitalGainDetail[0] = new ReportParameter("AdviserLogoPath", reportLogoPath);
paramEligibleCapitalGainDetail[1] = new ReportParameter("ToDate", report.ToDate.ToString("dd-MMM-yy"));
paramEligibleCapitalGainDetail[2] = new ReportParameter("ReportName", "Eligible");
paramEligibleCapitalGainDetail[3] = new ReportParameter("FromDate", report.FromDate.ToString("dd-MMM-yy"));
LocalReport.SetParameters(paramEligibleCapitalGainDetail);
ReportName = "MF Eligible Capital Gain Details " + DateTime.Now.ToString("yyyyMMddhhmmss");
if (ReportFormat == "pdf")
{
ExportInPDF();
Response.Redirect(@"\Reports\TempReports\ViewInPDF\" + PDFNames);
//Download(path, ReportName);
}
else if (ReportFormat == "xls")
{
ExportInExcel();
Download(path, ReportName);
}
else
{
LocalReport.DisplayName = ReportName;
rptViewer.LocalReport.Refresh();
rptViewer.ZoomPercent = 110;
rptViewer.ShowPageNavigationControls = false;
}
break;
case "ELIGIBLE_CAPITAL_GAIN_SUMMARY":
LocalReport.ReportPath = Server.MapPath(@"SsrsReportingFiles\MF_Capital_Gain_Summary_PDF.rdlc");
DataTable dtEligibleCapitalGainSummary = MFReportEngineBo.CreateCustomerEligibleCapitalGainSummary(report.CustomerId, report.PortfolioIds, report.FromDate, report.ToDate);
ReportDataSource MFEligibleCapitalGainSummary = new ReportDataSource("MFReturns_MFCapitalGain", dtEligibleCapitalGainSummary);
LocalReport.EnableExternalImages = true;
LocalReport.DataSources.Add(MFEligibleCapitalGainSummary);
LocalReport.DataSources.Add(ReportHeader);
//LocalReport.DataSources.Add(AdviserDetails);
ReportParameter[] paramEligibleCapitalGainSummary = new ReportParameter[4];
paramEligibleCapitalGainSummary[0] = new ReportParameter("AdviserLogoPath", reportLogoPath);
paramEligibleCapitalGainSummary[1] = new ReportParameter("ToDate", report.ToDate.ToString("dd-MMM-yy"));
paramEligibleCapitalGainSummary[2] = new ReportParameter("ReportName", "Eligible");
paramEligibleCapitalGainSummary[3] = new ReportParameter("FromDate", report.FromDate.ToString("dd-MMM-yy"));
LocalReport.SetParameters(paramEligibleCapitalGainSummary);
ReportName = "MF Eligible Capital Gain Summary " + DateTime.Now.ToString("yyyyMMddhhmmss");
if (ReportFormat == "pdf")
{
ExportInPDF();
Response.Redirect(@"\Reports\TempReports\ViewInPDF\" + PDFNames);
//Download(path, ReportName);
}
else if (ReportFormat == "xls")
{
ExportInExcel();
Download(path, ReportName);
}
else
{
LocalReport.DisplayName = ReportName;
rptViewer.LocalReport.Refresh();
rptViewer.ZoomPercent = 110;
rptViewer.ShowPageNavigationControls = false;
}
break;
case "MF_TRANSACTION":
LocalReport.ReportPath = Server.MapPath(@"SsrsReportingFiles\MF_Transaction_PDF.rdlc");
DataTable dtTransaction = MFReportEngineBo.CreateCustomerMFTraxnReport(report.CustomerId, report.PortfolioIds, report.FromDate, report.ToDate);
ReportDataSource MFTransaction = new ReportDataSource("MFReturns_MFTraxn", dtTransaction);
LocalReport.EnableExternalImages = true;
LocalReport.DataSources.Add(MFTransaction);
LocalReport.DataSources.Add(ReportHeader);
ReportParameter[] paramTraxn = new ReportParameter[3];
paramTraxn[0] = new ReportParameter("AdviserLogoPath", reportLogoPath);
paramTraxn[1] = new ReportParameter("FromDate", report.FromDate.ToString("dd-MMM-yy"));
paramTraxn[2] = new ReportParameter("ToDate", report.ToDate.ToString("dd-MMM-yy"));
LocalReport.SetParameters(paramTraxn);
ReportName = "MF Transaction " + DateTime.Now.ToString("yyyyMMddhhmmss");
if (ReportFormat == "pdf")
{
ExportInPDF();
Response.Redirect(@"\Reports\TempReports\ViewInPDF\" + PDFNames);
//Download(path, ReportName);
}
else if (ReportFormat == "xls")
{
ExportInExcel();
Download(path, ReportName);
}
else
{
LocalReport.DisplayName = ReportName;
rptViewer.LocalReport.Refresh();
rptViewer.ZoomPercent = 110;
rptViewer.ShowPageNavigationControls = false;
}
break;
case "PORTFOLIO_RETURNS_WITHBANKDETAILS":
LocalReport.ReportPath = Server.MapPath(@"SsrsReportingFiles\MF_Returns_Holding_WithBankDetails_PDF.rdlc");
DataSet dsMFReturnsHoldingCustomRpt = MFReportEngineBo.CreateCustomerMFReturnsHoldingWithBankDetails(report.CustomerId, report.PortfolioIds, report.FromDate, report.ToDate);
ReportDataSource MFReturnsHoldingCustomRpt = new ReportDataSource("MFReturns_MFReturn", dsMFReturnsHoldingCustomRpt.Tables[0]);
ReportDataSource PortfolioXIRR = new ReportDataSource("MFReturns_PortfolioXIRR", dsMFReturnsHoldingCustomRpt.Tables[1]);
LocalReport.EnableExternalImages = true;
LocalReport.DataSources.Add(MFReturnsHoldingCustomRpt);
LocalReport.DataSources.Add(ReportHeader);
//LocalReport.DataSources.Add(AdviserDetails);
LocalReport.DataSources.Add(PortfolioXIRR);
ReportParameter[] paramHoldingCustomRpt = new ReportParameter[2];
paramHoldingCustomRpt[0] = new ReportParameter("AdviserLogoPath", reportLogoPath);
paramHoldingCustomRpt[1] = new ReportParameter("ToDate", report.ToDate.ToString("dd-MMM-yy"));
LocalReport.SetParameters(paramHoldingCustomRpt);
ReportName = "MF Returns - Holding " + DateTime.Now.ToString("yyyyMMddhhmmss");
if (ReportFormat == "pdf")
{
ExportInPDF();
Response.Redirect(@"\Reports\TempReports\ViewInPDF\" + PDFNames);
//Download(path, ReportName);
}
else if (ReportFormat == "xls")
{
ExportInExcel();
Download(path, ReportName);
}
else
{
LocalReport.DisplayName = ReportName;
rptViewer.LocalReport.Refresh();
rptViewer.ZoomPercent = 110;
rptViewer.ShowPageNavigationControls = false;
}
break;
case "MF_CLOSINGBALANCE":
LocalReport.ReportPath = Server.MapPath(@"SsrsReportingFiles\MF_ClosingBalance_PDF.rdlc");
DataTable dtClosingBalance = MFReportEngineBo.CreateCustomerClosingBalance(report.CustomerId, report.PortfolioIds, report.FromDate, report.ToDate);
ReportDataSource MFClosingBalanceRpt = new ReportDataSource("MFReturns_MFClosingBalance", dtClosingBalance);
LocalReport.EnableExternalImages = true;
LocalReport.DataSources.Add(MFClosingBalanceRpt);
LocalReport.DataSources.Add(ReportHeader);
ReportParameter[] paramClosingBalance = new ReportParameter[3];
paramClosingBalance[0] = new ReportParameter("AdviserLogoPath", reportLogoPath);
paramClosingBalance[1] = new ReportParameter("FromDate", report.FromDate.ToString("dd-MMM-yy"));
paramClosingBalance[2] = new ReportParameter("ToDate", report.ToDate.ToString("dd-MMM-yy"));
LocalReport.SetParameters(paramClosingBalance);
ReportName = "MF Closing Balance " + DateTime.Now.ToString("yyyyMMddhhmmss");
if (ReportFormat == "pdf")
{
ExportInPDF();
Response.Redirect(@"\Reports\TempReports\ViewInPDF\" + PDFNames);
//Download(path, ReportName);
}
else if (ReportFormat == "xls")
{
ExportInExcel();
Download(path, ReportName);
}
else
{
LocalReport.DisplayName = ReportName;
rptViewer.LocalReport.Refresh();
rptViewer.ZoomPercent = 110;
rptViewer.ShowPageNavigationControls = false;
}
break;
case "MF_TranxnHolding":
LocalReport.ReportPath = Server.MapPath(@"SsrsReportingFiles\MF_Holding_Transactions_PDF.rdlc");
DataSet dsHoldingTranxn = MFReportEngineBo.CreateCustomerTranxnHolding(report.CustomerId, report.PortfolioIds, report.FromDate, report.ToDate);
ReportDataSource MFHoldingTranxnRpt = new ReportDataSource("MFReturns_MFReturn", dsHoldingTranxn.Tables[0]);
ReportDataSource MFHoldingXIRR = new ReportDataSource("MFReturns_MFHoldingXIRR", dsHoldingTranxn.Tables[1]);
LocalReport.EnableExternalImages = true;
LocalReport.DataSources.Add(MFHoldingTranxnRpt);
LocalReport.DataSources.Add(MFHoldingXIRR);
LocalReport.DataSources.Add(ReportHeader);
ReportParameter[] paramTranxnHolding = new ReportParameter[2];
paramTranxnHolding[0] = new ReportParameter("AdviserLogoPath", reportLogoPath);
paramTranxnHolding[1] = new ReportParameter("ToDate", report.ToDate.ToString("dd-MMM-yy"));
LocalReport.SetParameters(paramTranxnHolding);
ReportName = "MF Holding Transactions " + DateTime.Now.ToString("yyyyMMddhhmmss");
if (ReportFormat == "pdf")
{
ExportInPDF();
Response.Redirect(@"\Reports\TempReports\ViewInPDF\" + PDFNames);
//Download(path, ReportName);
}
else if (ReportFormat == "xls")
{
ExportInExcel();
Download(path, ReportName);
}
else
{
LocalReport.DisplayName = ReportName;
rptViewer.LocalReport.Refresh();
rptViewer.ZoomPercent = 110;
rptViewer.ShowPageNavigationControls = false;
}
break;
}
}
/// <summary>
/// Creating Data Table For "ELIGIBLE CAPITAL GAIN DETAILS & SUMMARY" Report : "Author:Pramod"
/// </summary>
/// <param name="reports">"reports" is a object of "MFReportVo" Contails report parameters </param>
/// <returns> DataTable</returns>
public DataTable GetEligibleCapitalGainDetailsReport(MFReportVo reports)
{
DataTable dtEligibleCapitalGainDetails = new DataTable();
dtEligibleCapitalGainDetails.Columns.Add("CustomerName");
dtEligibleCapitalGainDetails.Columns.Add("CustomerId");
dtEligibleCapitalGainDetails.Columns.Add("PortfolioName");
dtEligibleCapitalGainDetails.Columns.Add("PortfolioId");
dtEligibleCapitalGainDetails.Columns.Add("GainOrLoss", System.Type.GetType("System.Double"));
dtEligibleCapitalGainDetails.Columns.Add("Units", System.Type.GetType("System.Double"));
dtEligibleCapitalGainDetails.Columns.Add("RedDate");
dtEligibleCapitalGainDetails.Columns.Add("RedAmount", System.Type.GetType("System.Double"));
dtEligibleCapitalGainDetails.Columns.Add("DaysInvestedFor");
dtEligibleCapitalGainDetails.Columns.Add("PurchaseDate");
dtEligibleCapitalGainDetails.Columns.Add("PurchaseAmount", System.Type.GetType("System.Double"));
dtEligibleCapitalGainDetails.Columns.Add("FolioNum");
dtEligibleCapitalGainDetails.Columns.Add("SchemePlanCode");
dtEligibleCapitalGainDetails.Columns.Add("SchemePlanName");
dtEligibleCapitalGainDetails.Columns.Add("STCGTax", System.Type.GetType("System.Double"));
dtEligibleCapitalGainDetails.Columns.Add("LTCGTax", System.Type.GetType("System.Double"));
dtEligibleCapitalGainDetails.Columns.Add("Category");
dtEligibleCapitalGainDetails.Columns.Add("CurrNAV", System.Type.GetType("System.Double"));
dtEligibleCapitalGainDetails.Columns.Add("CurrVALUE", System.Type.GetType("System.Double"));
PortfolioBo portfolioBo = new PortfolioBo();
CustomerPortfolioBo customerPortfolioBo = new CustomerPortfolioBo();
DataSet dsReturnsTransactions = new DataSet();
List <MFPortfolioVo> mfPortfolioVoList = new List <MFPortfolioVo>();
try
{
String[] portfolioIds = reports.PortfolioIds.Split(',');
foreach (string strPortfoliioId in portfolioIds)
{
mfPortfolioVoList = new List <MFPortfolioVo>();
Int32 portfoliioId = Convert.ToInt32(strPortfoliioId);
DataSet dsPortfolioCustomer = portfolioBo.GetCustomerPortfolioDetails(portfoliioId);
DataRow drPortfolioCustomer = dsPortfolioCustomer.Tables[0].Rows[0];
mfPortfolioVoList = customerPortfolioBo.GetCustomerMFPortfolio(int.Parse(drPortfolioCustomer["C_CustomerId"].ToString()), portfoliioId, reports.ToDate, "", "", "");
if (mfPortfolioVoList != null && mfPortfolioVoList.Count > 0)
{
foreach (MFPortfolioVo mFPortfolioVo in mfPortfolioVoList)
{
foreach (MFPortfolioTransactionVo mFPortfolioTransaction in mFPortfolioVo.MFPortfolioTransactionVoList)
{
if (mFPortfolioTransaction.Closed == false && mFPortfolioTransaction.BuyDate < reports.ToDate)
{
DataRow drEligibleCapitalGainDetails = dtEligibleCapitalGainDetails.NewRow();
drEligibleCapitalGainDetails["CustomerName"] = drPortfolioCustomer["C_FirstName"].ToString();
drEligibleCapitalGainDetails["CustomerId"] = mFPortfolioVo.CustomerId;
if (drPortfolioCustomer["CP_PortfolioName"] != null)
{
drEligibleCapitalGainDetails["PortfolioName"] = drPortfolioCustomer["CP_PortfolioName"].ToString();
}
drEligibleCapitalGainDetails["PortfolioId"] = portfoliioId;
//drEligibleCapitalGainDetails["GainOrLoss"] = mFPortfolioTransaction.RealizedProfitLoss;
drEligibleCapitalGainDetails["GainOrLoss"] = mFPortfolioTransaction.STCGTax + mFPortfolioTransaction.LTCGTax;
drEligibleCapitalGainDetails["Units"] = mFPortfolioTransaction.BuyQuantity;
drEligibleCapitalGainDetails["RedDate"] = mFPortfolioTransaction.SellDate.ToShortDateString();
drEligibleCapitalGainDetails["RedAmount"] = mFPortfolioTransaction.NetSalesProceed;
drEligibleCapitalGainDetails["DaysInvestedFor"] = mFPortfolioTransaction.AgeOfInvestment;
drEligibleCapitalGainDetails["PurchaseDate"] = mFPortfolioTransaction.BuyDate.ToShortDateString();
drEligibleCapitalGainDetails["PurchaseAmount"] = mFPortfolioTransaction.CostOfAcquisition;
drEligibleCapitalGainDetails["FolioNum"] = mFPortfolioVo.Folio;
drEligibleCapitalGainDetails["SchemePlanCode"] = mFPortfolioVo.MFCode;
drEligibleCapitalGainDetails["SchemePlanName"] = mFPortfolioVo.SchemePlan;
drEligibleCapitalGainDetails["STCGTax"] = mFPortfolioTransaction.STCGTax;
drEligibleCapitalGainDetails["LTCGTax"] = mFPortfolioTransaction.LTCGTax;
drEligibleCapitalGainDetails["Category"] = mFPortfolioVo.Category;
drEligibleCapitalGainDetails["CurrNAV"] = mFPortfolioTransaction.CurrentNAV;
drEligibleCapitalGainDetails["CurrVALUE"] = mFPortfolioTransaction.CurrentValue;
dtEligibleCapitalGainDetails.Rows.Add(drEligibleCapitalGainDetails);
}
}
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return(dtEligibleCapitalGainDetails);
}
public DataTable GetReturnTransactionSummaryReport(MFReportVo reports)
{
DataTable dtReturnTransaction = new DataTable();
PortfolioBo portfolioBo = new PortfolioBo();
CustomerPortfolioBo customerPortfolioBo = new CustomerPortfolioBo();
DataSet dsReturnsTransactions = new DataSet();
List <MFPortfolioVo> mfPortfolioVoList = new List <MFPortfolioVo>();
dtReturnTransaction.Columns.Add("TransactionType");
dtReturnTransaction.Columns.Add("Units", System.Type.GetType("System.Double"));
dtReturnTransaction.Columns.Add("PurchaseDate", System.Type.GetType("System.DateTime"));
dtReturnTransaction.Columns.Add("PurchasePrice", System.Type.GetType("System.Double"));
dtReturnTransaction.Columns.Add("PurchaseCost", System.Type.GetType("System.Double"));
dtReturnTransaction.Columns.Add("SellDate", System.Type.GetType("System.DateTime"));
dtReturnTransaction.Columns.Add("SellPrice", System.Type.GetType("System.Double"));
dtReturnTransaction.Columns.Add("SaleProceed", System.Type.GetType("System.Double"));
dtReturnTransaction.Columns.Add("AsonDateNAV", System.Type.GetType("System.Double"));
dtReturnTransaction.Columns.Add("AsOnDateValue", System.Type.GetType("System.Double"));
dtReturnTransaction.Columns.Add("AgeOfInvestment", System.Type.GetType("System.Double"));
dtReturnTransaction.Columns.Add("ActualPL", System.Type.GetType("System.Double"));
dtReturnTransaction.Columns.Add("NotionalPL", System.Type.GetType("System.Double"));
dtReturnTransaction.Columns.Add("TotalPL", System.Type.GetType("System.Double"));
dtReturnTransaction.Columns.Add("AbsoluteReturn", System.Type.GetType("System.Double"));
dtReturnTransaction.Columns.Add("AnnualReturn", System.Type.GetType("System.Double"));
dtReturnTransaction.Columns.Add("SchemePlanCode");
dtReturnTransaction.Columns.Add("SchemePlanName");
dtReturnTransaction.Columns.Add("FolioNum");
dtReturnTransaction.Columns.Add("CustomerName");
dtReturnTransaction.Columns.Add("CustomerId");
dtReturnTransaction.Columns.Add("PortfolioName");
dtReturnTransaction.Columns.Add("PortfolioId");
try
{
String[] portfolioIds = reports.PortfolioIds.Split(',');
foreach (string strPortfoliioId in portfolioIds)
{
mfPortfolioVoList = new List <MFPortfolioVo>();
Int32 portfoliioId = 0;
portfoliioId = Convert.ToInt32(strPortfoliioId);
DataSet dsPortfolioCustomer = portfolioBo.GetCustomerPortfolioDetails(portfoliioId);
DataRow drPortfolioCustomer = dsPortfolioCustomer.Tables[0].Rows[0];
mfPortfolioVoList = customerPortfolioBo.GetCustomerMFPortfolio(int.Parse(drPortfolioCustomer["C_CustomerId"].ToString()), portfoliioId, reports.FromDate, "", "", "");
if (mfPortfolioVoList != null && mfPortfolioVoList.Count > 0)
{
foreach (MFPortfolioVo mFPortfolioVo in mfPortfolioVoList)
{
foreach (MFPortfolioTransactionVo mFPortfolioTransaction in mFPortfolioVo.MFPortfolioTransactionVoList)
{
DataRow drReturnTransaction = dtReturnTransaction.NewRow();
drReturnTransaction["TransactionType"] = mFPortfolioTransaction.TransactionType;
drReturnTransaction["Units"] = mFPortfolioTransaction.BuyQuantity; //
drReturnTransaction["PurchaseDate"] = mFPortfolioTransaction.BuyDate;
drReturnTransaction["PurchasePrice"] = mFPortfolioTransaction.BuyPrice;
drReturnTransaction["PurchaseCost"] = mFPortfolioTransaction.CostOfAcquisition; //
drReturnTransaction["SellDate"] = mFPortfolioTransaction.SellDate;
drReturnTransaction["SellPrice"] = mFPortfolioTransaction.SellPrice;
drReturnTransaction["SaleProceed"] = mFPortfolioTransaction.RealizedSalesValue;
drReturnTransaction["AsonDateNAV"] = mFPortfolioTransaction.CurrentNAV;//
drReturnTransaction["AsOnDateValue"] = mFPortfolioTransaction.CurrentValue;
drReturnTransaction["AgeOfInvestment"] = mFPortfolioTransaction.AgeOfInvestment;
drReturnTransaction["ActualPL"] = mFPortfolioTransaction.RealizedProfitLoss;
drReturnTransaction["NotionalPL"] = mFPortfolioTransaction.NotionalProfitLoss;
drReturnTransaction["TotalPL"] = mFPortfolioTransaction.TotalProfitLoss;
drReturnTransaction["AbsoluteReturn"] = mFPortfolioTransaction.AbsoluteReturns;
drReturnTransaction["AnnualReturn"] = mFPortfolioTransaction.AnnualReturns;
drReturnTransaction["SchemePlanCode"] = mFPortfolioVo.MFCode;
drReturnTransaction["SchemePlanName"] = mFPortfolioVo.SchemePlan;
drReturnTransaction["FolioNum"] = mFPortfolioVo.Folio;
drReturnTransaction["CustomerName"] = drPortfolioCustomer["C_FirstName"].ToString();
drReturnTransaction["CustomerId"] = mFPortfolioVo.CustomerId;
if (drPortfolioCustomer["CP_PortfolioName"] != null)
{
drReturnTransaction["PortfolioName"] = drPortfolioCustomer["CP_PortfolioName"].ToString();
}
drReturnTransaction["PortfolioId"] = portfoliioId;
dtReturnTransaction.Rows.Add(drReturnTransaction);
}
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return(dtReturnTransaction);
}
public DataTable GetCapitalGainSummaryReport(MFReportVo reports)
{
DataTable dtCapitalGainSummary = new DataTable();
dtCapitalGainSummary.Columns.Add("CustomerName");
dtCapitalGainSummary.Columns.Add("CustomerId");
dtCapitalGainSummary.Columns.Add("PortfolioName");
dtCapitalGainSummary.Columns.Add("PortfolioId");
dtCapitalGainSummary.Columns.Add("GainOrLoss", System.Type.GetType("System.Double"));
dtCapitalGainSummary.Columns.Add("FolioNum");
dtCapitalGainSummary.Columns.Add("PASP_SchemePlanCode");
dtCapitalGainSummary.Columns.Add("PASP_SchemePlanName");
dtCapitalGainSummary.Columns.Add("STCGAmount", System.Type.GetType("System.Double"));
dtCapitalGainSummary.Columns.Add("LTCGAmount", System.Type.GetType("System.Double"));
dtCapitalGainSummary.Columns.Add("Category");
DataRow dtRow = dtCapitalGainSummary.NewRow();
PortfolioBo portfolioBo = new PortfolioBo();
CustomerPortfolioBo customerPortfolioBo = new CustomerPortfolioBo();
DataSet dsReturnsTransactions = new DataSet();
List <MFPortfolioVo> mfPortfolioVoList = new List <MFPortfolioVo>();
try
{
String[] portfolioIds = reports.PortfolioIds.Split(',');
foreach (string strPortfoliioId in portfolioIds)
{
mfPortfolioVoList = new List <MFPortfolioVo>();
Int32 portfoliioId = Convert.ToInt32(strPortfoliioId);
DataSet dsPortfolioCustomer = portfolioBo.GetCustomerPortfolioDetails(portfoliioId);
DataRow drPortfolioCustomer = dsPortfolioCustomer.Tables[0].Rows[0];
mfPortfolioVoList = customerPortfolioBo.GetCustomerMFPortfolio(int.Parse(drPortfolioCustomer["C_CustomerId"].ToString()), portfoliioId, reports.ToDate, "", "", "");
if (mfPortfolioVoList != null && mfPortfolioVoList.Count > 0)
{
foreach (MFPortfolioVo mFPortfolioVo in mfPortfolioVoList)
{
foreach (MFPortfolioTransactionVo mFPortfolioTransaction in mFPortfolioVo.MFPortfolioTransactionVoList)
{
if (mFPortfolioTransaction.Closed == true && mFPortfolioTransaction.SellDate > reports.FromDate && mFPortfolioTransaction.SellDate < reports.ToDate)
{
DataRow drCapitalGainDetails = dtCapitalGainSummary.NewRow();
drCapitalGainDetails["CustomerName"] = drPortfolioCustomer["C_FirstName"].ToString();
drCapitalGainDetails["CustomerId"] = mFPortfolioVo.CustomerId;
if (drPortfolioCustomer["CP_PortfolioName"] != null)
{
drCapitalGainDetails["PortfolioName"] = drPortfolioCustomer["CP_PortfolioName"].ToString();
}
drCapitalGainDetails["PortfolioId"] = portfoliioId;
//drCapitalGainDetails["GainOrLoss"] = mFPortfolioTransaction.RealizedProfitLoss;
drCapitalGainDetails["GainOrLoss"] = mFPortfolioTransaction.STCGTax + mFPortfolioTransaction.LTCGTax;
drCapitalGainDetails["FolioNum"] = mFPortfolioVo.Folio;
drCapitalGainDetails["PASP_SchemePlanCode"] = mFPortfolioVo.MFCode;
drCapitalGainDetails["PASP_SchemePlanName"] = mFPortfolioVo.SchemePlan;
drCapitalGainDetails["STCGAmount"] = mFPortfolioTransaction.STCGTax;
drCapitalGainDetails["LTCGAmount"] = mFPortfolioTransaction.LTCGTax;
drCapitalGainDetails["Category"] = mFPortfolioVo.Category;
dtCapitalGainSummary.Rows.Add(drCapitalGainDetails);
}
}
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return(dtCapitalGainSummary);
}
public DataTable GetDivdendReport(MFReportVo reports)
{
MFReportsDao mfReports = new MFReportsDao();
return(mfReports.GetDivdendReport(reports));
}
public DataSet GetMFFundSummaryReport(MFReportVo report, int adviserId)
{
MFReportsDao mfReports = new MFReportsDao();
DataSet dsMFSummary = new DataSet();
try
{
// Code to combine two datatables (current and previous values data for main report) into one.
DataSet dsPortfolio = new DataSet();
DataTable dtCurrent = new DataTable();
DataTable dtPrevious = new DataTable();
DataTable MFSummaryOpenPosition = new DataTable();
MFSummaryOpenPosition.Columns.Add("CustomerName");
MFSummaryOpenPosition.Columns.Add("CustomerId");
MFSummaryOpenPosition.Columns.Add("PortfolioName");
MFSummaryOpenPosition.Columns.Add("PortfolioId");
MFSummaryOpenPosition.Columns.Add("Category");
MFSummaryOpenPosition.Columns.Add("PreviousValue", System.Type.GetType("System.Decimal"));
MFSummaryOpenPosition.Columns.Add("CurrentValue", System.Type.GetType("System.Decimal"));
MFSummaryOpenPosition.Columns.Add("PreviousNetCost", System.Type.GetType("System.Decimal"));
MFSummaryOpenPosition.Columns.Add("CurrentNetCost", System.Type.GetType("System.Decimal"));
MFSummaryOpenPosition.TableName = "MFSummaryOpenPosition";
DataSet dsPortfolioSummary = mfReports.GetMFFundSummaryReport(report, adviserId);
dtCurrent = dsPortfolioSummary.Tables[0];
dtPrevious = dsPortfolioSummary.Tables[1];
//dtCurrent.Merge(dtPrevious);
foreach (DataRow drCurrent in dtCurrent.Rows)
{
foreach (DataRow drPrevious in dtPrevious.Rows)
{
if (drCurrent["PortfolioId"].ToString() == drPrevious["PortfolioId"].ToString() && drCurrent["Category"].ToString() == drPrevious["Category"].ToString())
{
drCurrent["PreviousValue"] = drPrevious["PreviousValue"];
drCurrent["PreviousNetCost"] = drPrevious["PreviousNetCost"];
break;
}
}
if (drCurrent["PreviousNetCost"].ToString() == string.Empty)
{
drCurrent["PreviousNetCost"] = 0.00;
}
if (drCurrent["PreviousValue"].ToString() == string.Empty)
{
drCurrent["PreviousValue"] = 0.00;
}
if (drCurrent["CurrentValue"].ToString() == string.Empty)
{
drCurrent["CurrentValue"] = 0.00;
}
if (drCurrent["CurrentNetCost"].ToString() == string.Empty)
{
drCurrent["CurrentNetCost"] = 0.00;
}
MFSummaryOpenPosition.ImportRow(drCurrent);
}
foreach (DataRow drPrevious in dtPrevious.Rows)
{
string expression = string.Empty;
expression = "PortfolioId = " + drPrevious["PortfolioId"] + " and Category = '" + drPrevious["Category"] + "'";
if (MFSummaryOpenPosition.Select(expression) == null)
{
MFSummaryOpenPosition.ImportRow(drPrevious);
break;
}
}
dsMFSummary.Tables.Add(MFSummaryOpenPosition);
// Code to combine two datatables (current and previous values data for sub reports) into one.
DataTable dtCurrentSR = new DataTable();
DataTable dtPreviousSR = new DataTable();
DataTable MFSummaryAllPosition = new DataTable();
MFSummaryAllPosition.Columns.Add("CustomerName");
MFSummaryAllPosition.Columns.Add("CustomerId");
MFSummaryAllPosition.Columns.Add("PortfolioName");
MFSummaryAllPosition.Columns.Add("PortfolioId");
MFSummaryAllPosition.Columns.Add("Category");
MFSummaryAllPosition.Columns.Add("PreviousDVP", System.Type.GetType("System.Decimal"));
MFSummaryAllPosition.Columns.Add("CurrentDVP", System.Type.GetType("System.Decimal"));
MFSummaryAllPosition.Columns.Add("PreviousDVR", System.Type.GetType("System.Decimal"));
MFSummaryAllPosition.Columns.Add("CurrentDVR", System.Type.GetType("System.Decimal"));
MFSummaryAllPosition.Columns.Add("PreviousDVI", System.Type.GetType("System.Decimal"));
MFSummaryAllPosition.Columns.Add("CurrentDVI", System.Type.GetType("System.Decimal"));
MFSummaryAllPosition.Columns.Add("PreviousNPL", System.Type.GetType("System.Decimal"));
MFSummaryAllPosition.Columns.Add("CurrentNPL", System.Type.GetType("System.Decimal"));
MFSummaryAllPosition.Columns.Add("PreviousRPL", System.Type.GetType("System.Decimal"));
MFSummaryAllPosition.Columns.Add("CurrentRPL", System.Type.GetType("System.Decimal"));
MFSummaryAllPosition.Columns.Add("PreviousTPL", System.Type.GetType("System.Decimal"));
MFSummaryAllPosition.Columns.Add("CurrentTPL", System.Type.GetType("System.Decimal"));
MFSummaryAllPosition.Columns.Add("PreviousSTCG", System.Type.GetType("System.Decimal"));
MFSummaryAllPosition.Columns.Add("CurrentSTCG", System.Type.GetType("System.Decimal"));
MFSummaryAllPosition.Columns.Add("PreviousLTCG", System.Type.GetType("System.Decimal"));
MFSummaryAllPosition.Columns.Add("CurrentLTCG", System.Type.GetType("System.Decimal"));
MFSummaryAllPosition.TableName = "MFSummaryAllPosition";
DataSet dsPortfolioSummarySR = mfReports.GetMFFundSummaryReportAllPosition(report, adviserId);
dtCurrentSR = dsPortfolioSummarySR.Tables[0];
dtPreviousSR = dsPortfolioSummarySR.Tables[1];
//dtCurrent.Merge(dtPrevious);
foreach (DataRow drCurrent in dtCurrentSR.Rows)
{
foreach (DataRow drPrevious in dtPreviousSR.Rows)
{
if (drCurrent["PortfolioId"].ToString() == drPrevious["PortfolioId"].ToString() && drCurrent["Category"].ToString() == drPrevious["Category"].ToString())
{
drCurrent["PreviousDVP"] = drPrevious["PreviousDVP"];
drCurrent["PreviousDVR"] = drPrevious["PreviousDVR"];
drCurrent["PreviousDVI"] = drPrevious["PreviousDVI"];
drCurrent["PreviousNPL"] = drPrevious["PreviousNPL"];
drCurrent["PreviousRPL"] = drPrevious["PreviousRPL"];
drCurrent["PreviousTPL"] = drPrevious["PreviousTPL"];
drCurrent["PreviousSTCG"] = drPrevious["PreviousSTCG"];
drCurrent["PreviousLTCG"] = drPrevious["PreviousLTCG"];
break;
}
}
if (drCurrent["PreviousDVP"].ToString() == string.Empty)
{
drCurrent["PreviousDVP"] = 0.00;
}
if (drCurrent["PreviousDVR"].ToString() == string.Empty)
{
drCurrent["PreviousDVR"] = 0.00;
}
if (drCurrent["PreviousDVI"].ToString() == string.Empty)
{
drCurrent["PreviousDVI"] = 0.00;
}
if (drCurrent["PreviousNPL"].ToString() == string.Empty)
{
drCurrent["PreviousNPL"] = 0.00;
}
if (drCurrent["PreviousRPL"].ToString() == string.Empty)
{
drCurrent["PreviousRPL"] = 0.00;
}
if (drCurrent["PreviousTPL"].ToString() == string.Empty)
{
drCurrent["PreviousTPL"] = 0.00;
}
if (drCurrent["PreviousSTCG"].ToString() == string.Empty)
{
drCurrent["PreviousSTCG"] = 0.00;
}
if (drCurrent["PreviousLTCG"].ToString() == string.Empty)
{
drCurrent["PreviousLTCG"] = 0.00;
}
if (drCurrent["CurrentDVP"].ToString() == string.Empty)
{
drCurrent["CurrentDVP"] = 0.00;
}
if (drCurrent["CurrentDVR"].ToString() == string.Empty)
{
drCurrent["CurrentDVR"] = 0.00;
}
if (drCurrent["CurrentDVI"].ToString() == string.Empty)
{
drCurrent["CurrentDVI"] = 0.00;
}
if (drCurrent["CurrentNPL"].ToString() == string.Empty)
{
drCurrent["CurrentNPL"] = 0.00;
}
if (drCurrent["CurrentRPL"].ToString() == string.Empty)
{
drCurrent["CurrentRPL"] = 0.00;
}
if (drCurrent["CurrentTPL"].ToString() == string.Empty)
{
drCurrent["CurrentTPL"] = 0.00;
}
if (drCurrent["CurrentSTCG"].ToString() == string.Empty)
{
drCurrent["CurrentSTCG"] = 0.00;
}
if (drCurrent["CurrentLTCG"].ToString() == string.Empty)
{
drCurrent["CurrentLTCG"] = 0.00;
}
MFSummaryAllPosition.ImportRow(drCurrent);
}
foreach (DataRow drPrevious in dtPreviousSR.Rows)
{
string expression = string.Empty;
expression = "PortfolioId = " + drPrevious["PortfolioId"] + " and Category = '" + drPrevious["Category"] + "'";
if (MFSummaryAllPosition.Select(expression) == null)
{
MFSummaryAllPosition.ImportRow(drPrevious);
break;
}
}
dsMFSummary.Tables.Add(MFSummaryAllPosition);
}
catch (Exception ex)
{
throw ex;
}
return(dsMFSummary);
}
/// <summary>
/// Get Customer Portfolio Analytics Report Data
/// </summary>
/// <param name="reports"></param>
/// <param name="adviserId"></param>
/// <returns></returns>
public DataSet GetPortfolioAnalyticsReport(MFReportVo reports, int adviserId)
{
MFReportsDao mfReports = new MFReportsDao();
DataSet dsCustomerMFReturns;
double totalValue = 0;
double holdingstotal = 0;
double otherholdingsTotal = 0;
double sectorTotal = 0;
double otherSectorTotal = 0;
try
{
dsCustomerMFReturns = mfReports.GetPortfolioAnalyticsReport(reports, adviserId);
if (dsCustomerMFReturns != null)
{
if (dsCustomerMFReturns.Tables[4].Rows.Count != 0)
{
DataRow drTotal = dsCustomerMFReturns.Tables[4].Rows[0];
if (!string.IsNullOrEmpty(drTotal["Amount"].ToString()))
{
totalValue = double.Parse(drTotal["Amount"].ToString());
}
}
foreach (DataRow drHoldings in dsCustomerMFReturns.Tables[2].Rows)
{
holdingstotal = holdingstotal + double.Parse(drHoldings["Amount"].ToString());
}
foreach (DataRow drSector in dsCustomerMFReturns.Tables[5].Rows)
{
sectorTotal = sectorTotal + double.Parse(drSector["Amount"].ToString());
}
otherSectorTotal = totalValue - sectorTotal;
if (dsCustomerMFReturns.Tables[3].Rows.Count != 0 && dsCustomerMFReturns.Tables[3].Rows[0]["Amount"].ToString() != "")
{
otherholdingsTotal = totalValue - double.Parse(dsCustomerMFReturns.Tables[3].Rows[0]["Amount"].ToString()) - holdingstotal;
}
if (dsCustomerMFReturns.Tables[2].Rows.Count != 0)
{
DataRow dr = dsCustomerMFReturns.Tables[2].NewRow();
dr["Instrument"] = "Other Holdings";
dr["InsType"] = "Other";
dr["Amount"] = otherholdingsTotal.ToString();
dsCustomerMFReturns.Tables[2].Rows.Add(dr);
dr = dsCustomerMFReturns.Tables[2].NewRow();
dr["Instrument"] = dsCustomerMFReturns.Tables[3].Rows[0]["Instrument"].ToString();
dr["InsType"] = dsCustomerMFReturns.Tables[3].Rows[0]["InsType"].ToString();
dr["Amount"] = dsCustomerMFReturns.Tables[3].Rows[0]["Amount"].ToString();
dsCustomerMFReturns.Tables[2].Rows.Add(dr);
// dr = dsCustomerMFReturns.Tables[2].NewRow();
//dr["Instrument"] = dsCustomerMFReturns.Tables[4].Rows[0]["Instrument"].ToString();
//dr["InsType"] = "";
//dr["Amount"] = dsCustomerMFReturns.Tables[4].Rows[0]["Amount"].ToString();
//dsCustomerMFReturns.Tables[2].Rows.Add(dr);
}
dsCustomerMFReturns.Tables[2].Columns.Add("Percentage");
if (totalValue != 0)
{
for (int i = 0; i < dsCustomerMFReturns.Tables[2].Rows.Count; i++)
{
dsCustomerMFReturns.Tables[2].Rows[i]["Percentage"] = Math.Round(double.Parse((((double.Parse(dsCustomerMFReturns.Tables[2].Rows[i]["Amount"].ToString())) / totalValue) * 100).ToString()), 2);
}
}
if (dsCustomerMFReturns.Tables[5].Rows.Count != 0)
{
DataRow drSectorHoldings = dsCustomerMFReturns.Tables[5].NewRow();
drSectorHoldings["SectorCode"] = "0";
drSectorHoldings["Sector"] = "Others";
drSectorHoldings["Amount"] = otherSectorTotal.ToString();
dsCustomerMFReturns.Tables[5].Rows.Add(drSectorHoldings);
}
dsCustomerMFReturns.Tables[5].Columns.Add("Percentage");
if (totalValue != 0)
{
for (int i = 0; i < dsCustomerMFReturns.Tables[5].Rows.Count; i++)
{
dsCustomerMFReturns.Tables[5].Rows[i]["Percentage"] = Math.Round(double.Parse((((double.Parse(dsCustomerMFReturns.Tables[5].Rows[i]["Amount"].ToString())) / totalValue) * 100).ToString()), 2);
}
}
}
}
catch (Exception ex)
{
throw (ex);
}
return(dsCustomerMFReturns);
}
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);
}
}
private void DisplayReport(MFReportVo report, string ReportFormat)
{
EQReportEngineBo EQReportEngineBo = new EQReportEngineBo();
DataSet dsReportHeaders = EQReportEngineBo.GetCustomerReportHeaders(advisorVo.advisorId, report.CustomerId);
ReportDataSource ReportHeader = new ReportDataSource("EQDataSet_ReportHeader", AssignReportHeaderParameter(dsReportHeaders.Tables[0], dsReportHeaders.Tables[1], dsReportHeaders.Tables[2]));
rptViewer.ProcessingMode = ProcessingMode.Local;
rptViewer.Reset();
LocalReport LocalReport = rptViewer.LocalReport;
String reportLogoPath = ConfigurationManager.AppSettings["adviserLogoPath"].ToString();
reportLogoPath = System.Web.HttpContext.Current.Request.MapPath(reportLogoPath + advisorVo.LogoPath);
if (!File.Exists(reportLogoPath))
{
reportLogoPath = string.Empty;
//reportLogoPath = ConfigurationManager.AppSettings["adviserLogoPath"].ToString();
//reportLogoPath = System.Web.HttpContext.Current.Request.MapPath(reportLogoPath + "spacer.png");
reportLogoPath = Server.MapPath(@"//Images//spacer.png");
}
reportLogoPath = "file:///" + reportLogoPath;
string ReportName = string.Empty;
switch (report.SubType)
{
case "EQ_HOLDING_REPORT":
LocalReport.ReportPath = Server.MapPath(@"SsrsReportingFiles\EQ_Holding_PDF.rdlc");
DataSet dsEQHolding = EQReportEngineBo.CreateCustomerEQReturnsHolding(report.CustomerId, report.PortfolioIds, report.FromDate, report.ToDate);
ReportDataSource EQHolding = new ReportDataSource("EQDataSet_EQHolding", dsEQHolding.Tables[0]);
ReportDataSource PortfolioXIRRHolding = new ReportDataSource("EQDataSet_PortfolioXIRR", dsEQHolding.Tables[1]);
LocalReport.EnableExternalImages = true;
LocalReport.DataSources.Add(EQHolding);
LocalReport.DataSources.Add(ReportHeader);
//LocalReport.DataSources.Add(AdviserDetails);
LocalReport.DataSources.Add(PortfolioXIRRHolding);
ReportParameter[] paramHolding = new ReportParameter[2];
paramHolding[0] = new ReportParameter("AdviserLogoPath", reportLogoPath);
paramHolding[1] = new ReportParameter("ToDate", report.ToDate.ToString("dd-MMM-yy"));
LocalReport.SetParameters(paramHolding);
ReportName = "Equity Holding Report " + DateTime.Now.ToString("yyyyMMddhhmmss");
if (ReportFormat == "pdf")
{
ExportInPDF();
Response.Redirect(@"\Reports\TempReports\ViewInPDF\" + PDFNames);
//Download(path, ReportName);
}
else if (ReportFormat == "xls")
{
ExportInExcel();
Download(path, ReportName);
}
else
{
LocalReport.DisplayName = ReportName;
rptViewer.LocalReport.Refresh();
rptViewer.ZoomPercent = 110;
rptViewer.ShowPageNavigationControls = false;
}
break;
case "EQ_TRANSACTION":
LocalReport.ReportPath = Server.MapPath(@"SsrsReportingFiles\EQ_Transaction_PDF.rdlc");
DataTable dtTransaction = EQReportEngineBo.CreateCustomerEQTraxnReport(report.CustomerId, report.PortfolioIds, report.FromDate, report.ToDate);
ReportDataSource EQTransaction = new ReportDataSource("EQDataSet_EQTranxn", dtTransaction);
LocalReport.EnableExternalImages = true;
LocalReport.DataSources.Add(EQTransaction);
LocalReport.DataSources.Add(ReportHeader);
ReportParameter[] paramTraxn = new ReportParameter[3];
paramTraxn[0] = new ReportParameter("AdviserLogoPath", reportLogoPath);
paramTraxn[1] = new ReportParameter("FromDate", report.FromDate.ToString("dd-MMM-yy"));
paramTraxn[2] = new ReportParameter("ToDate", report.ToDate.ToString("dd-MMM-yy"));
LocalReport.SetParameters(paramTraxn);
ReportName = "Equity Transaction Report " + DateTime.Now.ToString("yyyyMMddhhmmss");
if (ReportFormat == "pdf")
{
ExportInPDF();
Response.Redirect(@"\Reports\TempReports\ViewInPDF\" + PDFNames);
//Download(path, ReportName);
}
else if (ReportFormat == "xls")
{
ExportInExcel();
Download(path, ReportName);
}
else
{
LocalReport.DisplayName = ReportName;
rptViewer.LocalReport.Refresh();
rptViewer.ZoomPercent = 110;
rptViewer.ShowPageNavigationControls = false;
}
break;
case "CAPITAL_GAIN_DETAILS":
LocalReport.ReportPath = Server.MapPath(@"SsrsReportingFiles\EQ_Capital_Gain_Details_PDF.rdlc");
DataTable dtCapitalGainDetails = EQReportEngineBo.CreateCustomerCapitalGainDetails(report.CustomerId, report.PortfolioIds, report.FromDate, report.ToDate);
ReportDataSource EQCapitalGainDetails = new ReportDataSource("EQDataSet_EQCapitalGain", dtCapitalGainDetails);
LocalReport.EnableExternalImages = true;
LocalReport.DataSources.Add(EQCapitalGainDetails);
LocalReport.DataSources.Add(ReportHeader);
//LocalReport.DataSources.Add(AdviserDetails);
ReportParameter[] paramCapitalGainDetail = new ReportParameter[3];
paramCapitalGainDetail[0] = new ReportParameter("AdviserLogoPath", reportLogoPath);
paramCapitalGainDetail[1] = new ReportParameter("ToDate", report.ToDate.ToString("dd-MMM-yy"));
paramCapitalGainDetail[2] = new ReportParameter("FromDate", report.FromDate.ToString("dd-MMM-yy"));
LocalReport.SetParameters(paramCapitalGainDetail);
ReportName = "EQ Capital Gain Details " + DateTime.Now.ToString("yyyyMMddhhmmss");
if (ReportFormat == "pdf")
{
ExportInPDF();
Response.Redirect(@"\Reports\TempReports\ViewInPDF\" + PDFNames);
//Download(path, ReportName);
}
else if (ReportFormat == "xls")
{
ExportInExcel();
Download(path, ReportName);
}
else
{
LocalReport.DisplayName = ReportName;
rptViewer.LocalReport.Refresh();
rptViewer.ZoomPercent = 110;
rptViewer.ShowPageNavigationControls = false;
}
break;
case "CAPITAL_GAIN_SUMMARY":
LocalReport.ReportPath = Server.MapPath(@"SsrsReportingFiles\EQ_Capital_Gain_Summary_PDF.rdlc");
DataTable dtCapitalGainSummary = EQReportEngineBo.CreateCustomerCapitalGainSummary(report.CustomerId, report.PortfolioIds, report.FromDate, report.ToDate);
ReportDataSource EQCapitalGainSummary = new ReportDataSource("EQDataSet_EQCapitalGain", dtCapitalGainSummary);
LocalReport.EnableExternalImages = true;
LocalReport.DataSources.Add(EQCapitalGainSummary);
LocalReport.DataSources.Add(ReportHeader);
//LocalReport.DataSources.Add(AdviserDetails);
ReportParameter[] paramCapitalGainSummary = new ReportParameter[3];
paramCapitalGainSummary[0] = new ReportParameter("AdviserLogoPath", reportLogoPath);
paramCapitalGainSummary[1] = new ReportParameter("ToDate", report.ToDate.ToString("dd-MMM-yy"));
paramCapitalGainSummary[2] = new ReportParameter("FromDate", report.FromDate.ToString("dd-MMM-yy"));
LocalReport.SetParameters(paramCapitalGainSummary);
ReportName = "EQ Capital Gain Summary " + DateTime.Now.ToString("yyyyMMddhhmmss");
if (ReportFormat == "pdf")
{
ExportInPDF();
Response.Redirect(@"\Reports\TempReports\ViewInPDF\" + PDFNames);
//Download(path, ReportName);
}
else if (ReportFormat == "xls")
{
ExportInExcel();
Download(path, ReportName);
}
else
{
LocalReport.DisplayName = ReportName;
rptViewer.LocalReport.Refresh();
rptViewer.ZoomPercent = 110;
rptViewer.ShowPageNavigationControls = false;
}
break;
case "ELIGIBLE_CAPITAL_GAIN_DETAILS":
LocalReport.ReportPath = Server.MapPath(@"SsrsReportingFiles\EQ_Capital_Gain_Details_PDF.rdlc");
DataTable dtEligibleCapitalGainDetails = EQReportEngineBo.CreateCustomerEligibleCapitalGainDetails(report.CustomerId, report.PortfolioIds, report.FromDate, report.ToDate);
ReportDataSource MFEligibleCapitalGainDetails = new ReportDataSource("EQDataSet_EQCapitalGain", dtEligibleCapitalGainDetails);
LocalReport.EnableExternalImages = true;
LocalReport.DataSources.Add(MFEligibleCapitalGainDetails);
LocalReport.DataSources.Add(ReportHeader);
//LocalReport.DataSources.Add(AdviserDetails);
ReportParameter[] paramEligibleCapitalGainDetail = new ReportParameter[4];
paramEligibleCapitalGainDetail[0] = new ReportParameter("AdviserLogoPath", reportLogoPath);
paramEligibleCapitalGainDetail[1] = new ReportParameter("ToDate", report.ToDate.ToString("dd-MMM-yy"));
paramEligibleCapitalGainDetail[2] = new ReportParameter("ReportName", "Eligible");
paramEligibleCapitalGainDetail[3] = new ReportParameter("FromDate", report.FromDate.ToString("dd-MMM-yy"));
LocalReport.SetParameters(paramEligibleCapitalGainDetail);
ReportName = "EQ Eligible Capital Gain Details " + DateTime.Now.ToString("yyyyMMddhhmmss");
if (ReportFormat == "pdf")
{
ExportInPDF();
//Response.Redirect(@"\Reports\TempReports\ViewInPDF\" + PDFNames);
Download(path, ReportName);
}
else if (ReportFormat == "xls")
{
ExportInExcel();
Download(path, ReportName);
}
else
{
LocalReport.DisplayName = ReportName;
rptViewer.LocalReport.Refresh();
rptViewer.ZoomPercent = 110;
rptViewer.ShowPageNavigationControls = false;
}
break;
case "ELIGIBLE_CAPITAL_GAIN_SUMMARY":
LocalReport.ReportPath = Server.MapPath(@"SsrsReportingFiles\EQ_Capital_Gain_Summary_PDF.rdlc");
DataTable dtEligibleCapitalGainSummary = EQReportEngineBo.CreateCustomerEligibleCapitalGainSummary(report.CustomerId, report.PortfolioIds, report.FromDate, report.ToDate);
ReportDataSource MFEligibleCapitalGainSummary = new ReportDataSource("EQDataSet_EQCapitalGain", dtEligibleCapitalGainSummary);
LocalReport.EnableExternalImages = true;
LocalReport.DataSources.Add(MFEligibleCapitalGainSummary);
LocalReport.DataSources.Add(ReportHeader);
//LocalReport.DataSources.Add(AdviserDetails);
ReportParameter[] paramEligibleCapitalGainSummary = new ReportParameter[4];
paramEligibleCapitalGainSummary[0] = new ReportParameter("AdviserLogoPath", reportLogoPath);
paramEligibleCapitalGainSummary[1] = new ReportParameter("ToDate", report.ToDate.ToString("dd-MMM-yy"));
paramEligibleCapitalGainSummary[2] = new ReportParameter("ReportName", "Eligible");
paramEligibleCapitalGainSummary[3] = new ReportParameter("FromDate", report.FromDate.ToString("dd-MMM-yy"));
LocalReport.SetParameters(paramEligibleCapitalGainSummary);
ReportName = "EQ Eligible Capital Gain Summary " + DateTime.Now.ToString("yyyyMMddhhmmss");
if (ReportFormat == "pdf")
{
ExportInPDF();
//Response.Redirect(@"\Reports\TempReports\ViewInPDF\" + PDFNames);
Download(path, ReportName);
}
else if (ReportFormat == "xls")
{
ExportInExcel();
Download(path, ReportName);
}
else
{
LocalReport.DisplayName = ReportName;
rptViewer.LocalReport.Refresh();
rptViewer.ZoomPercent = 110;
rptViewer.ShowPageNavigationControls = false;
}
break;
}
}
/// <summary>
/// Returns DataTable For "Eligible Capital Gain Details & Summary" Report --Author:Pramod
/// </summary>
/// <param name="reports">"reports" is a object of "MFReportVo" Contails report parameters</param>
/// <returns>DataTable</returns>
public DataTable GetEligibleCapitalGainDetailsReport(MFReportVo reports)
{
MFReportsDao mfReports = new MFReportsDao();
return(mfReports.GetEligibleCapitalGainDetailsReport(reports));
}
public DataTable GetCapitalGainSummaryReport(MFReportVo reports)
{
MFReportsDao mfReports = new MFReportsDao();
return(mfReports.GetCapitalGainSummaryReport(reports));
}
public DataTable GetReturnTransactionSummaryReport(MFReportVo reports)
{
MFReportsDao mfReports = new MFReportsDao();
return(mfReports.GetReturnTransactionSummaryReport(reports));
}
/// <summary>
/// Returns DataTable For "Portfolio Returns Realized" Report --Author:Pramod
/// </summary>
/// <param name="reports"> "reports" is a object of "MFReportVo" Contails report parameters</param>
/// <param name="adviserId">Get the data of all the customer belong to This Id</param>
/// <returns>DataTable </returns>
public DataTable GetMFReturnRESummaryReport(MFReportVo reports, int adviserId)
{
MFReportsDao mfReports = new MFReportsDao();
return(mfReports.GetMFReturnRESummaryReport(reports, adviserId));
}