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);
}
}
public DataTable GetCustomerPortfolioEquityTransactions(EquityReportVo reports, int adviserId)
{
Database db;
DbCommand getEquityTransactionsCmd;
DataSet dsGetEquityTransactions;
DataTable dtGetEquityTransactions = new DataTable();
try
{
db = DatabaseFactory.CreateDatabase("wealtherp");
getEquityTransactionsCmd = db.GetStoredProcCommand("SP_RPT_GetCustomerPortfolioEquityTransactions");
db.AddInParameter(getEquityTransactionsCmd, "@PortfolioIds", DbType.String, reports.PortfolioIds);
db.AddInParameter(getEquityTransactionsCmd, "@AsOnDate", DbType.DateTime, reports.FromDate);
db.AddInParameter(getEquityTransactionsCmd, "@AdviserId", DbType.Int64, adviserId);
dsGetEquityTransactions = db.ExecuteDataSet(getEquityTransactionsCmd);
if (dsGetEquityTransactions.Tables.Count > 0 && dsGetEquityTransactions.Tables[0].Rows.Count > 0)
{
dtGetEquityTransactions = dsGetEquityTransactions.Tables[0];
}
}
catch (Exception Ex)
{
throw Ex;
}
return(dtGetEquityTransactions);
}
public DataSet GetEquityHolding(EquityReportVo report, int adviserId)
{
EquityReportsDao equityReportsDao = new EquityReportsDao();
DataSet dsPortfolioSummary = new DataSet();
try
{
dsPortfolioSummary = equityReportsDao.GetEquityHolding(report, adviserId);
}
catch (Exception ex)
{
throw ex;
}
return(dsPortfolioSummary);
}
public DataTable GetEquityTransaction(EquityReportVo report, int adviserId)
{
EquityReportsDao equityReportsDao = new EquityReportsDao();
DataTable dtCurrent = new DataTable();
try
{
DataSet dsPortfolioSummary = equityReportsDao.GetEquityTransaction(report, adviserId);
dtCurrent = dsPortfolioSummary.Tables[0];
}
catch (Exception ex)
{
throw ex;
}
return(dtCurrent);
}
public DataTable GetEquityTransactionSpeculative(EquityReportVo reports)
{
DataTable dt = new DataTable();
return(dt);
}
public DataTable GetEquityTransactionDerivate(EquityReportVo reports)
{
DataTable dt = new DataTable();
return(dt);
}
/// <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;
}
/// <summary>
/// Get Transaction Report
/// </summary>
/// <param name="reports"></param>
/// <returns></returns>
public DataTable GetEquityScripwiseSummary(EquityReportVo report, int adviserId)
{
//EquityReportsDao eqReports = new EquityReportsDao();
//return eqReports.GetEquityScripwiseSummary(reports, adviserId);
EquityReportsDao equityReportsDao = new EquityReportsDao();
DataSet dsPortfolio = new DataSet();
DataTable dtCurrent = new DataTable();
DataTable dtPrevious = new DataTable();
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.Double"));
dtMFSummary.Columns.Add("CurrentValue", System.Type.GetType("System.Double"));
DataSet dsPortfolioSummary = equityReportsDao.GetEquityScripwiseSummary(report, adviserId);
dtCurrent = dsPortfolioSummary.Tables[0];
dtPrevious = dsPortfolioSummary.Tables[1];
//dtCurrent.Merge(dtPrevious);
try
{
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"];
break;
}
}
dtMFSummary.ImportRow(drCurrent);
}
foreach (DataRow drPrevious in dtPrevious.Rows)
{
string expression = string.Empty;
expression = "PortfolioId = " + drPrevious["PortfolioId"] + " and Category = '" + drPrevious["Category"] + "'";
if (dtMFSummary.Select(expression) == null)
{
dtMFSummary.ImportRow(drPrevious);
break;
}
}
}
catch (Exception ex)
{
throw ex;
}
return(dtMFSummary);
}
public DataTable GetCustomerPortfolioEquityTransactions(EquityReportVo reports, int adviserId)
{
EquityReportsDao equityReportsDao = new EquityReportsDao();
return(equityReportsDao.GetCustomerPortfolioEquityTransactions(reports, adviserId));
}
public DataTable GetEquityTransactionSpeculative(EquityReportVo reports)
{
EquityReportsDao equityReportsDao = new EquityReportsDao();
return(equityReportsDao.GetEquityTransactionSpeculative(reports));
}
public DataTable GetEquityTransactionDerivate(EquityReportVo reports)
{
EquityReportsDao equityReportsDao = new EquityReportsDao();
return(equityReportsDao.GetEquityTransactionDerivate(reports));
}
public DataSet GetEquityHolding(EquityReportVo reports, int adviserId)
{
//DataTable dtEquityHolding = new DataTable();
//DataRow drEquityholding;
//DataColumn dcEquityHolding;
//dcEquityHolding = new DataColumn();
//dcEquityHolding.DataType = Type.GetType("System.String");
//dcEquityHolding.ColumnName = "CompanyName";
//dtEquityHolding.Columns.Add(dcEquityHolding);
//dcEquityHolding = new DataColumn();
//dcEquityHolding.DataType = Type.GetType("System.Double");
//dcEquityHolding.ColumnName = "NetHoldings";
//dtEquityHolding.Columns.Add(dcEquityHolding);
//dcEquityHolding = new DataColumn();
//dcEquityHolding.DataType = Type.GetType("System.Double");
//dcEquityHolding.ColumnName = "AveragePrice";
//dtEquityHolding.Columns.Add(dcEquityHolding);
//dcEquityHolding = new DataColumn();
//dcEquityHolding.DataType = Type.GetType("System.Double");
//dcEquityHolding.ColumnName = "NetCost";
//dtEquityHolding.Columns.Add(dcEquityHolding);
//dcEquityHolding = new DataColumn();
//dcEquityHolding.DataType = Type.GetType("System.Double");
//dcEquityHolding.ColumnName = "MarketPrice";
//dtEquityHolding.Columns.Add(dcEquityHolding);
//dcEquityHolding = new DataColumn();
//dcEquityHolding.DataType = Type.GetType("System.Double");
//dcEquityHolding.ColumnName = "CurrentValue";
//dtEquityHolding.Columns.Add(dcEquityHolding);
//dcEquityHolding = new DataColumn();
//dcEquityHolding.DataType = Type.GetType("System.Double");
//dcEquityHolding.ColumnName = "UnrealizedPL";
//dtEquityHolding.Columns.Add(dcEquityHolding);
//dcEquityHolding = new DataColumn();
//dcEquityHolding.DataType = Type.GetType("System.Double");
//dcEquityHolding.ColumnName = "RealizedPL";
//dtEquityHolding.Columns.Add(dcEquityHolding);
//dcEquityHolding = new DataColumn();
//dcEquityHolding.DataType = Type.GetType("System.Double");
//dcEquityHolding.ColumnName = "TotalPL";
//dtEquityHolding.Columns.Add(dcEquityHolding);
//dcEquityHolding = new DataColumn();
//dcEquityHolding.DataType = Type.GetType("System.Double");
//dcEquityHolding.ColumnName = "XIRR";
//dtEquityHolding.Columns.Add(dcEquityHolding);
Microsoft.Practices.EnterpriseLibrary.Data.Database db;
DbCommand getCustomerNPListCmd;
DataSet dsEquityHoldingwise;
try
{
db = DatabaseFactory.CreateDatabase("wealtherp");
getCustomerNPListCmd = db.GetStoredProcCommand("SP_RPT_GetCustomerEquityHoldings");
db.AddInParameter(getCustomerNPListCmd, "@PortfolioIds", DbType.String, reports.PortfolioIds);
db.AddInParameter(getCustomerNPListCmd, "@AsOnDate", DbType.DateTime, reports.ToDate);
db.AddInParameter(getCustomerNPListCmd, "@AdviserId", DbType.Int32, adviserId);
getCustomerNPListCmd.CommandTimeout = 60 * 60;
dsEquityHoldingwise = db.ExecuteDataSet(getCustomerNPListCmd);
//foreach (DataRow dr in dsEquityHoldingwise.Tables[0].Rows)
//{
// drEquityholding =dtEquityHolding.NewRow();
// drEquityholding[0] = dr[0];
// drEquityholding[1] = dr[1];
// drEquityholding[2] = dr[2];
// drEquityholding[3] = dr[3];
// drEquityholding[4] = dr[4];
// drEquityholding[5] = dr[5];
// drEquityholding[6] = dr[6];
// drEquityholding[7] = dr[7];
// drEquityholding[8] = dr[8];
// drEquityholding[9] = dr[9];
// dtEquityHolding.Rows.Add(drEquityholding);
//}
//dsEquityHoldingwise.Tables.Add(dtEquityHolding);
return(dsEquityHoldingwise);
}
catch (Exception ex)
{
throw (ex);
}
}
public DataTable GetEquityTransactionAll(EquityReportVo reports)
{
EquityReportsDao eqReports = new EquityReportsDao();
return(eqReports.GetEquityTransactionAll(reports));
}