private static string GetHtmlParts(OtRiskPnLConfig aOtRiskPnLConfig, string yyyymmddDate)
{
var resultHtml = string.Empty;
//get Summary part
resultHtml = ConcateHtmlPart(resultHtml, "<br><h2>Summary</h2>");
var aTable = GetDataTableFromQuery(GetSqlSummaryPortfolioSubportfolio(aOtRiskPnLConfig,yyyymmddDate));
var output = RazorHelper.GetHtmlContent(aTable, RazorHelper.PnLEmailTemplate);
resultHtml = ConcateHtmlPart(resultHtml, output);
//get OT SMF part
resultHtml = ConcateHtmlPart(resultHtml, "<br><h2>SMF-OT</h2>");
//PortfolioSubportfolio
aTable = GetDataTableFromQuery(GetSqlOtPortfolioSubportfolio(aOtRiskPnLConfig, "SMF", yyyymmddDate));
output = RazorHelper.GetHtmlContent(aTable, RazorHelper.PnLEmailTemplate);
resultHtml = ConcateHtmlPart(resultHtml, output);
//PortfolioSubperStrategytype
aTable = GetDataTableFromQuery(GetSqlOtPortfolioSubperStrategytype(aOtRiskPnLConfig, "SMF", yyyymmddDate));
output = RazorHelper.GetHtmlContent(aTable, RazorHelper.PnLEmailTemplate);
resultHtml = ConcateHtmlPart(resultHtml, output);
//PortfolioSubperStrategytypeStrategy
aTable = GetDataTableFromQuery(GetSqlOtPortfolioSubperStrategytypeStrategy(aOtRiskPnLConfig, "SMF", yyyymmddDate));
output = RazorHelper.GetHtmlContent(aTable, RazorHelper.PnLEmailTemplate);
resultHtml = ConcateHtmlPart(resultHtml, output);
//get OT MLP part
resultHtml = ConcateHtmlPart(resultHtml, "<br><h2>MLP-OT</h2>");
//PortfolioSubportfolio
aTable = GetDataTableFromQuery(GetSqlOtPortfolioSubportfolio(aOtRiskPnLConfig, "MLP", yyyymmddDate));
output = RazorHelper.GetHtmlContent(aTable, RazorHelper.PnLEmailTemplate);
resultHtml = ConcateHtmlPart(resultHtml, output);
//PortfolioSubperStrategytype
aTable = GetDataTableFromQuery(GetSqlOtPortfolioSubperStrategytype(aOtRiskPnLConfig, "MLP", yyyymmddDate));
output = RazorHelper.GetHtmlContent(aTable, RazorHelper.PnLEmailTemplate);
resultHtml = ConcateHtmlPart(resultHtml, output);
//PortfolioSubperStrategytypeStrategy
aTable = GetDataTableFromQuery(GetSqlOtPortfolioSubperStrategytypeStrategy(aOtRiskPnLConfig, "MLP", yyyymmddDate));
output = RazorHelper.GetHtmlContent(aTable, RazorHelper.PnLEmailTemplate);
resultHtml = ConcateHtmlPart(resultHtml, output);
//get MLP MLP part
resultHtml = ConcateHtmlPart(resultHtml, "<br><h2>MLP-MLP(Flash/Not Final)</h2>");
//PortfolioSubportfolio
aTable = GetDataTableFromQuery(GetSqlMlpPortfolioSubportfolio(aOtRiskPnLConfig, yyyymmddDate));
output = RazorHelper.GetHtmlContent(aTable, RazorHelper.PnLEmailTemplate);
resultHtml = ConcateHtmlPart(resultHtml, output);
//PortfolioSubperStrategytype
aTable = GetDataTableFromQuery(GetSqlMlpPortfolioSubperStrategytype(aOtRiskPnLConfig, yyyymmddDate));
output = RazorHelper.GetHtmlContent(aTable, RazorHelper.PnLEmailTemplate);
resultHtml = ConcateHtmlPart(resultHtml, output);
//PortfolioSubperStrategytypeStrategy
aTable = GetDataTableFromQuery(GetSqlMlpPortfolioSubperStrategytypeStrategy(aOtRiskPnLConfig, yyyymmddDate));
output = RazorHelper.GetHtmlContent(aTable, RazorHelper.PnLEmailTemplate);
resultHtml = ConcateHtmlPart(resultHtml, output);
//log
SLog.log.InfoFormat("Html body generated: [{0}]", resultHtml);
return resultHtml;
}
private static string GetSqlOtPortfolioSubperStrategytypeStrategy(OtRiskPnLConfig aOtRiskPnLConfig, string entity, string yyyymmddDate)
{
var sql = "SELECT Portfolio, SubPortfolio, StrategyType, Strategy, SUM(Daily) As '" + entity + "(OT)-Daily', SUM(MTD) As '" + entity + "(OT)-MTD', SUM(YTD) As '" + entity + "(OT)-YTD' " +
"FROM [PnLEmail].[fnGetOtPnl] () " +
"WHERE " +
"Entity = '" + entity + "' " +
"AND LoadDate = '" + yyyymmddDate + "' " +
"AND Portfolio = '" + aOtRiskPnLConfig.Portfolio + "' " +
"AND SubPortfolio = '" + aOtRiskPnLConfig.SubPortfolio + "' " +
"GROUP BY Portfolio, SubPortfolio, StrategyType, Strategy";
SLog.log.InfoFormat("SQL: [{0}]", sql);
return sql;
}
private static string GetSqlMlpPortfolioSubperStrategytypeStrategy(OtRiskPnLConfig aOtRiskPnLConfig, string yyyymmddDate)
{
var sql = "SELECT Portfolio, SubPortfolio, StrategyType, Strategy, SUM([MLP-Daily]) As 'MLP(MLP)-Daily', SUM([MLP-MTD]) As 'MLP(MLP)-MTD', SUM([MLP-YTD]) As 'MLP(MLP)-YTD' FROM " +
"[PnLEmail].[fnGetFullPnl]() " +
"WHERE " +
"([MLP-LoadDate] = " + yyyymmddDate + " OR [MLP-LoadDate] IS NULL)" +
" AND ([OT-LoadDate] = '" + yyyymmddDate + "' OR [OT-LoadDate] IS NULL)" +
"AND [OT-Entity] = [MLP-Entity] " +
"AND Portfolio = '" + aOtRiskPnLConfig.Portfolio + "' " +
"AND SubPortfolio = '" + aOtRiskPnLConfig.SubPortfolio + "' " +
"GROUP BY Portfolio, SubPortfolio, StrategyType, Strategy";
SLog.log.InfoFormat("SQL: [{0}]", sql);
return sql;
}
private static string GetSqlSummaryPortfolioSubportfolio(OtRiskPnLConfig aOtRiskPnLConfig, string yyyymmddDate)
{
var sql = "SELECT 'SMF(OT)' As [Source], Portfolio, SubPortfolio, SUM([OT-Daily]) As 'Daily', SUM([OT-MTD]) As 'MTD', SUM([OT-YTD]) As 'YTD' FROM "+
"[PnLEmail].[fnGetFullPnl]() " +
"WHERE "+
"[OT-Entity] = 'SMF' " +
"AND ([OT-LoadDate] = '" + yyyymmddDate + "' OR [OT-LoadDate] IS NULL)" +
"AND Portfolio = '" + aOtRiskPnLConfig.Portfolio + "' " +
"AND SubPortfolio = '" + aOtRiskPnLConfig.SubPortfolio + "' " +
"GROUP BY Portfolio, SubPortfolio "+
"UNION "+
"SELECT 'MLP(OT)' As [Source], Portfolio, SubPortfolio, SUM([OT-Daily]) As 'Daily', SUM([OT-MTD]) As 'MTD', SUM([OT-YTD]) As 'YTD' FROM "+
"[PnLEmail].[fnGetFullPnl]() "+
"WHERE "+
"[OT-Entity] = 'MLP' " +
"AND ([OT-LoadDate] = '" + yyyymmddDate + "' OR [OT-LoadDate] IS NULL)" +
"AND Portfolio = '" + aOtRiskPnLConfig.Portfolio + "' "+
"AND SubPortfolio = '" + aOtRiskPnLConfig.SubPortfolio + "' "+
"GROUP BY Portfolio, SubPortfolio "+
"UNION "+
"SELECT 'MLP(MLP)-Flash/Not Final' As [Source], Portfolio, SubPortfolio, SUM([MLP-Daily]) As 'Daily', SUM([MLP-MTD]) As 'MTD', SUM([MLP-YTD]) As 'YTD' FROM " +
"[PnLEmail].[fnGetFullPnl]() "+
"WHERE "+
"([MLP-LoadDate] = " + yyyymmddDate + " OR [MLP-LoadDate] IS NULL)" +
"AND ([OT-LoadDate] = '" + yyyymmddDate + "' OR [OT-LoadDate] IS NULL)" +
"AND [OT-Entity] = [MLP-Entity] "+
"AND Portfolio = '" + aOtRiskPnLConfig.Portfolio + "' " +
"AND SubPortfolio = '" + aOtRiskPnLConfig.SubPortfolio + "' " +
"GROUP BY Portfolio, SubPortfolio "+
"ORDER BY [Source] DESC";
SLog.log.InfoFormat("SQL: [{0}]", sql);
return sql;
}