Ejemplo n.º 1
0
        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);
        }
Ejemplo n.º 2
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);
 }
Ejemplo n.º 3
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);
        }
Ejemplo n.º 4
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;
 }
Ejemplo n.º 7
0
        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);
        }