protected void GetData(DateTime runDate, out DataTable detailsTable, out DataTable dpeTable, out DataTable imagineTable)
{
SLog.log.InfoFormat("Retrieving data for {0}", runDate);
using (var db = new DBHelperBase(ConnectableDB.SymmetryRiskDB))
{
detailsTable = dpeTable = imagineTable = null;
detailsTable = db.ExecuteSingleTable(String.Format("SELECT [LoadDate], [Portfolio], [SubPortfolio], [Strategy], [PL-OT], [MTD-OT], [YTD-OT], [PL-DPEImagine], [MTD-DPEImagine], [YTD-DPEImagine], [Diff-PL_OT-MLP], [Diff-MTD_OT-MLP], [Diff-YTD_OT-MLP] FROM [PnLRec].[fnGetPnLRecDetails] () WHERE [LoadDate] = {0} AND [LoadDate2] = '{1}' ORDER BY [LoadDate], [Portfolio], [SubPortfolio], [Strategy] ASC", runDate.ToString("yyyyMMdd"), runDate.ToString("yyyyMMdd")));
dpeTable = db.ExecuteSingleTable(String.Format("SELECT [Source], [LoadDate], [Portfolio], [Strategy], [PL Base], [Pl Month Base], [Pl Year Base], [Trader] FROM [PnLRec].[fnGetDpePnL] () WHERE [LoadDate] = {0} ORDER BY [LoadDate], [Portfolio], [Strategy] ASC", runDate.ToString("yyyyMMdd")));
imagineTable = db.ExecuteSingleTable(String.Format("SELECT [Source], [LoadDate], [Portfolio], [Strategy], [PL Base], [Pl Month Base], [Pl Year Base], [Trader] from [PnLRec].[fnGetImaginePnL] () WHERE [LoadDate] = {0} ORDER BY [LoadDate], [Portfolio], [Strategy] ASC", runDate.ToString("yyyyMMdd")));
}
SLog.log.InfoFormat("Retrieved {0} Details rows, {1} DPE rows, {2} Imagine rows.", detailsTable != null ? detailsTable.Rows.Count : 0, dpeTable != null ? dpeTable.Rows.Count : 0, imagineTable != null ? imagineTable.Rows.Count : 0);
}
protected void GetData(DateTime runDate, out DataTable summaryTable, out DataTable detailsTable, out DataTable fxExpoTableT1, out DataTable fxExpoTableT2, out DataTable dpeTable)
{
var prevDate = runDate.PrevWeekDay();
SLog.log.InfoFormat("Retrieving data for {0} - {1}.", prevDate, runDate);
using (var db = new DBHelperBase(ConnectableDB.RiskDB))
{
summaryTable = detailsTable = fxExpoTableT1 = fxExpoTableT2 = null;
summaryTable = db.ExecuteSingleTable(String.Format("SELECT * FROM CashRec.fnCashflowRecSummary({0}, {1}) ORDER BY Abs(USD_Flow_Diff) DESC", prevDate.ToString("yyyyMMdd"), runDate.ToString("yyyyMMdd")));
detailsTable = db.ExecuteSingleTable(String.Format("SELECT * FROM CashRec.fnCashflowRecDetails({0}, {1}) ORDER BY Portfolio, Strategy, Currency, Source, report_date", prevDate.ToString("yyyyMMdd"), runDate.ToString("yyyyMMdd")));
fxExpoTableT1 = db.ExecuteSingleTable(String.Format("SELECT * FROM CashRec.fnMLPExposure ({0}) ORDER BY report_date, trader, portfolio, micro_strategy DESC", runDate.ToString("yyyyMMdd")));
fxExpoTableT2 = db.ExecuteSingleTable(String.Format("SELECT * FROM CashRec.fnMLPExposure ({0}) ORDER BY report_date, trader, portfolio, micro_strategy DESC", prevDate.ToString("yyyyMMdd")));
dpeTable = db.ExecuteSingleTable(String.Format("SELECT * FROM CashRec.fnDPEPositionSummary({0}) order by reportdate,trader, microstrategy ASC", prevDate.ToString("yyyyMMdd")));
}
SLog.log.InfoFormat("Retrieved {0} summary rows, {1} details rows, {2} CE T-1 rows, {3} CE T-2 rows, {4} DPE rows.", summaryTable != null ? summaryTable.Rows.Count : 0, detailsTable != null ? detailsTable.Rows.Count : 0, fxExpoTableT1 != null ? fxExpoTableT1.Rows.Count : 0, fxExpoTableT2 != null ? fxExpoTableT2.Rows.Count : 0, dpeTable != null ? dpeTable.Rows.Count : 0);
}
protected void GetData(DateTime runDate, out DataTable recTable, out DataTable OTdetailsTable, out DataTable MLPdetailsTable, out DataTable MapTable)
{
SLog.log.InfoFormat("Retrieving data for {0}", runDate);
using (var db = new DBHelperBase(ConnectableDB.RiskDB))
{
recTable = OTdetailsTable = MLPdetailsTable = MapTable = null;
recTable = db.ExecuteSingleTable(String.Format("SELECT * FROM SymmetryRisk.FxRec.fnFxRecSummary({0}) ORDER BY PM, Strategy, CCY, Product ASC", runDate.ToString("yyyyMMdd")));
OTdetailsTable = db.ExecuteSingleTable(String.Format("SELECT report_date, Source, PM, SubPortfolio, Strategy, ExposureCCY As Currency, Product, Description, Exposure, TradeId FROM SymmetryRisk.FxRec.OTMLPFxLines WHERE report_date = {0} ORDER BY PM, Strategy, ExposureCCY, Product ASC", runDate.ToString("yyyyMMdd")));
MLPdetailsTable = db.ExecuteSingleTable(String.Format("SELECT report_date, Source, PM As trader, Strategy As microstrategy, CCY As Currency, Product, security, exposure As Exposure_usd FROM SymmetryRisk.FxRec.MLPFxLines WHERE report_date = {0} ORDER BY PM, Strategy, CCY, Product ASC", runDate.ToString("yyyyMMdd")));
MapTable = db.ExecuteSingleTable(String.Format("SELECT * FROM SymmetryRisk.FxRec.ProductMap ORDER BY OT, MLP, Product DESC"));
}
SLog.log.InfoFormat("Retrieved {0} rec rows, {1} Fx OT details rows, {2} Fx MLP details rows, {3} Product mapping rows.", recTable != null ? recTable.Rows.Count : 0, OTdetailsTable != null ? OTdetailsTable.Rows.Count : 0, MLPdetailsTable != null ? MLPdetailsTable.Rows.Count : 0, MapTable != null ? MapTable.Rows.Count : 0);
}
public void Run(DateTime runDate)
{
try
{
const string YES = "Y";
const string replaceDate = "{yyyyMMdd}";
const string replaceTime = "{hhmmss}";
const string strFINAL = "FINAL_";
const string strFLASH = "FLASH_";
const int intFlashCutOffHour = 5;
DateTime aTime = System.DateTime.Now;
SLog.log.InfoFormat("Generating Orchestrade PnL report for {0}{1}", runDate.ToString("yyyyMMdd"),aTime.ToString("hhmmss"));
//get config from database
SLog.log.InfoFormat("Retrieving config for {0}", runDate);
List<OtRiskPnLConfig> configList = null;
using (var db = new DBHelperBase(ConnectableDB.SymmetryRiskDB))
{
var configTable = db.ExecuteSingleTable(String.Format("SELECT * FROM dbo.OTRiskPnLConfig"));
configList = DataTableHelpers.ToList<OtRiskPnLConfig>(configTable);
/*
//debug print
foreach (OtRiskPnLConfig aOtRiskPnLConfig in configList)
{
SLog.log.Info(aOtRiskPnLConfig.ToString());
}
*/
SLog.log.InfoFormat("Retrieved {0} config rows.", configTable != null ? configTable.Rows.Count : 0);
//generate reports
foreach (OtRiskPnLConfig aOtRiskPnLConfig in configList)
{
try
{
if (YES.Equals(aOtRiskPnLConfig.Active))
{
var sw = System.Diagnostics.Stopwatch.StartNew();
DataTable dtDetail = null, dtSummary = null;
//get Detail data
String aSqlDetail = Regex.Replace(aOtRiskPnLConfig.SqlDetail, replaceDate, runDate.ToString("yyyyMMdd"));
dtDetail = db.ExecuteSingleTable(aSqlDetail);
dtDetail.Columns.Remove("LoadDate");
SLog.log.InfoFormat("Retrieved {0} Detail rows for {1} {2} {3}.", dtDetail != null ? dtDetail.Rows.Count : 0, aOtRiskPnLConfig.Entity, aOtRiskPnLConfig.Portfolio, aOtRiskPnLConfig.SubPortfolio);
//get Summary data
String aSqlSummary = Regex.Replace(aOtRiskPnLConfig.SqlSummary, replaceDate, runDate.ToString("yyyyMMdd"));
dtSummary = db.ExecuteSingleTable(aSqlSummary);
dtSummary.Columns.Remove("LoadDate");
SLog.log.InfoFormat("Retrieved {0} Summary rows for {1} {2} {3}.", dtSummary != null ? dtSummary.Rows.Count : 0, aOtRiskPnLConfig.Entity, aOtRiskPnLConfig.Portfolio, aOtRiskPnLConfig.SubPortfolio);
//determine whether it's FLASH (before 05:00 LDN) or FINAL(on/after 05:00 LDN) report
if (aTime.Hour > intFlashCutOffHour)
{
aOtRiskPnLConfig.SaveAsFileName = strFINAL + aOtRiskPnLConfig.SaveAsFileName;
}
else
{
aOtRiskPnLConfig.SaveAsFileName = strFLASH + aOtRiskPnLConfig.SaveAsFileName;
}
//determine file path
String outputFileName = Regex.Replace(aOtRiskPnLConfig.SaveAsFileName, replaceTime, aTime.ToString("hhmmss"));
outputFileName = Regex.Replace(outputFileName, replaceDate, runDate.ToString("yyyyMMdd"));
String outputFilePath = Regex.Replace(aOtRiskPnLConfig.SaveAsPath, replaceDate, runDate.ToString("yyyyMMdd"));
String outputFile = outputFilePath + outputFileName;
//create folder if not exist
System.IO.Directory.CreateDirectory(outputFilePath);
//determine template and generate output file
String templateFile = aOtRiskPnLConfig.Template;
using (var excelTemplate = ExcelTemplate.LoadFromFile(templateFile))
{
SLog.log.InfoFormat("Generating Excel sheet using {0} as template.", templateFile);
var worksheet = excelTemplate.GetWorksheet("Summary");
//set AUM cell
ExcelTools.SetCell(worksheet, aOtRiskPnLConfig.AUM.ToString("G"), "F", ExcelTools.GetRow(worksheet.Worksheet, 1));
worksheet.Worksheet.Save();
excelTemplate.PasteDataTable("Detail", 2, 1, dtDetail, 3);
excelTemplate.PasteDataTable("Summary", 3, 1, dtSummary, 4);
SLog.log.InfoFormat("Saving generated sheet to {0}.", outputFile);
excelTemplate.Save(outputFile);
System.IO.File.SetAttributes(outputFile, System.IO.FileAttributes.ReadOnly);
}
sw.Stop();
Console.WriteLine( sw.Elapsed.TotalSeconds);
}
}
catch (Exception ex)
{
SLog.log.Error(String.Format("Error generating PnL report for {0}{1}{2}", aOtRiskPnLConfig.Entity, aOtRiskPnLConfig.Portfolio, aOtRiskPnLConfig.SubPortfolio), ex);
}
}
}
}
catch (Exception ex)
{
SLog.log.Error(String.Format("Error generating Orchestrade PnL report at {0}", runDate), ex);
}
SLog.log.InfoFormat("Orchestrade PnL report generation at {0} is complete.", runDate);
}
public void Run(DateTime runDate, string version)
{
try
{
const string YES = "Y";
const string replaceDate = "{yyyyMMdd}";
DateTime aTime = System.DateTime.Now;
SLog.log.InfoFormat("Sending [{0}] Email Notification for PnL report on {1}{2}", version, runDate.ToString("yyyyMMdd"), aTime.ToString("hhmmss"));
//get config from database
SLog.log.InfoFormat("Retrieving config for {0}", runDate);
List<OtRiskPnLConfig> configList = null;
using (var db = new DBHelperBase(ConnectableDB.SymmetryRiskDB))
{
var configTable = db.ExecuteSingleTable(String.Format("SELECT * FROM dbo.OTRiskPnLConfig"));
configList = DataTableHelpers.ToList<OtRiskPnLConfig>(configTable);
SLog.log.InfoFormat("Retrieved {0} config rows.", configTable != null ? configTable.Rows.Count : 0);
//generate reports
foreach (OtRiskPnLConfig aOtRiskPnLConfig in configList)
{
try
{
if (YES.Equals(aOtRiskPnLConfig.Active))
{
var sw = System.Diagnostics.Stopwatch.StartNew();
string bodyHeader;
//determine whether it's FLASH or FINAL etc, from input string version
if ((!String.IsNullOrWhiteSpace(version)) && (version.Equals("FINAL")))
{
bodyHeader = aOtRiskPnLConfig.EmailContFinal;
}
else
{
bodyHeader = aOtRiskPnLConfig.EmailContFlash;
}
var sendTo = aOtRiskPnLConfig.EmailTo;
if (!string.IsNullOrWhiteSpace(sendTo))
{
SLog.log.InfoFormat("Sending [{0}] email for date [{1}] for Entity [{2}], Portfolio [{3}], SubPortfolio [{4}].", version, runDate.ToString("yyyyMMdd"), aOtRiskPnLConfig.Entity, aOtRiskPnLConfig.Portfolio, aOtRiskPnLConfig.SubPortfolio);
var sendFrom = "## SYM - Reports - P&L <reportsP&[email protected]>";
var subject = String.Format("{0} {1} P&L and Risk: {2}", aOtRiskPnLConfig.Portfolio, aOtRiskPnLConfig.SubPortfolio, runDate.ToString("yyyyMMdd"));
bodyHeader = Regex.Replace(bodyHeader, replaceDate, runDate.ToString("yyyyMMdd"));
var htmlBody = bodyHeader;
//get html body parts
htmlBody = ConcateHtmlPart(htmlBody, GetHtmlParts(aOtRiskPnLConfig, runDate.ToString("yyyyMMdd")));
//send email
var sendCc = new List<string>((aOtRiskPnLConfig.EmailCc).Split(','));
MailHelpers.SendEmail(sendFrom, sendTo, sendCc, subject, htmlBody, true);
}
sw.Stop();
Console.WriteLine( sw.Elapsed.TotalSeconds);
}
}
catch (Exception ex)
{
SLog.log.Error(String.Format("Error generating PnL report for {0}{1}{2}", aOtRiskPnLConfig.Entity, aOtRiskPnLConfig.Portfolio, aOtRiskPnLConfig.SubPortfolio), ex);
}
}
}
}
catch (Exception ex)
{
SLog.log.Error(String.Format("Error generating Orchestrade PnL report at {0}", runDate), ex);
}
SLog.log.InfoFormat("Orchestrade PnL report generation at {0} is complete.", runDate);
}
private static DataTable GetDataTableFromQuery(string query)
{
DataTable resultTable = null;
using (var db = new DBHelperBase(ConnectableDB.SymmetryRiskDB))
{
resultTable = db.ExecuteSingleTable(String.Format(query));
}
return resultTable;
}
public void Run(DateTime runDate, string version = "FLASH")
{
try
{
//logging
var sw = System.Diagnostics.Stopwatch.StartNew();
//const
const string replaceDate = "{yyyyMMdd}";
var aTime = DateTime.Now;
SLog.log.InfoFormat("Sending [{0}] EmailGrouped Notification for PnL report on {1}{2}", version, runDate.ToString("yyyyMMdd"), aTime.ToString("hhmmss"));
//get config from database
SLog.log.InfoFormat("Retrieving EmailGrouped config for {0}", runDate);
DataTable configTable;
using (var db = new DBHelperBase(ConnectableDB.SymmetryRiskDB))
{
configTable = db.ExecuteSingleTable(@"SELECT g.ReportNo, EmailTo, EmailCc, EmailSubject, PnLOrdering, PnLHeader, PnLQuery, Active FROM OTRiskPnLConfGroup g, OTRiskPnLConfGroupDetail d WHERE g.ReportNo = d.ReportNo AND Active = 'Y'");
SLog.log.InfoFormat("Retrieved {0} EmailGrouped config rows.", configTable != null ? configTable.Rows.Count : 0);
}
if (configTable == null || configTable.Rows.Count <= 0) { throw new Exception("Error. Please check config table."); }
var configGroupedList = (from aRow in configTable.AsEnumerable()
group aRow by
new
{
ReportNo = aRow.Field<int>("ReportNo"),
EmailTo = aRow.Field<string>("EmailTo"),
EmailCc = aRow.Field<string>("EmailCc"),
EmailSubject = aRow.Field<string>("EmailSubject"),
}
into g
orderby g.Key.ReportNo ascending
select new OtRiskPnLConfigGroup()
{
ReportNo = g.Key.ReportNo,
EmailTo = g.Key.EmailTo,
EmailCc = g.Key.EmailCc,
EmailSubject = g.Key.EmailSubject,
}).ToList();
//for each email
string htmlBody = string.Empty;
foreach (var aOtRiskPnLConfigGroup in configGroupedList)
{
var aOtRiskPnLConfigGroupLocal = aOtRiskPnLConfigGroup;
var aOtRiskPnLConfigGroupDetailList = (from aRow in configTable.AsEnumerable()
where aRow.Field<int>("ReportNo") == aOtRiskPnLConfigGroupLocal.ReportNo
orderby aRow.Field<int>("PnLOrdering") ascending
select new OtRiskPnLConfigGroupDetail()
{
PnLOrdering = aRow.Field<int>("PnLOrdering"),
PnLHeader = aRow.Field<string>("PnLHeader"),
PnLQuery = aRow.Field<string>("PnLQuery"),
}).ToList();
//get pnl content
for (var counter = 0; counter < aOtRiskPnLConfigGroupDetailList.Count; counter++)
{
using (var db = new DBHelperBase(ConnectableDB.SymmetryRiskDB))
{
//get html body parts
SLog.log.InfoFormat("Getting PnL part [{0}] of [{1}] for [{2}]", counter + 1, aOtRiskPnLConfigGroupDetailList.Count, aOtRiskPnLConfigGroup.EmailSubject);
var sqlQuery = Regex.Replace(aOtRiskPnLConfigGroupDetailList[counter].PnLQuery, replaceDate, runDate.ToString("yyyyMMdd"));
var pnLTable = db.ExecuteSingleTable(sqlQuery);
//get header
htmlBody = RazorHelper.ConcateHtmlPart(htmlBody, "<br><h2>" + aOtRiskPnLConfigGroupDetailList[counter].PnLHeader + "</h2>");
//get content
var htmlBodyPart = RazorHelper.GetHtmlContent(pnLTable, RazorHelper.PnLEmailTemplateGrouped);
htmlBody = RazorHelper.ConcateHtmlPart(htmlBody, htmlBodyPart);
}
}
//send email
SLog.log.InfoFormat("Sending [{0}] email for date [{1}] for subject [{2}].", version, runDate.ToString("yyyyMMdd"), aOtRiskPnLConfigGroup.EmailSubject);
var sendFrom = "## SYM - Reports - P&L <reportsP&[email protected]>";
var sendTo = aOtRiskPnLConfigGroup.EmailTo;
var sendCc = new List<string>((aOtRiskPnLConfigGroup.EmailCc).Split(','));
var subject = string.Format("{0} : {1} : {2} ", runDate.ToString("yyyyMMdd"), version, aOtRiskPnLConfigGroup.EmailSubject);
MailHelpers.SendEmail(sendFrom, sendTo, sendCc, subject, htmlBody, true);
}
sw.Stop();
SLog.log.InfoFormat("Total time elapsed for this task (in seconds):" + sw.Elapsed.TotalSeconds);
}
catch (Exception ex)
{
SLog.log.Error(String.Format("Error generating PnlHtmlEmailGrouped at {0}", runDate), ex);
}
SLog.log.InfoFormat("PnlHtmlEmailGrouped generation at {0} is completed.", runDate);
}