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); }