예제 #1
0
파일: Form1.cs 프로젝트: sheryllan/Practice
        private void openFile_Click(object sender, EventArgs e)
        {
            using (OpenFileDialog dialog = new OpenFileDialog())
            {
                if (dialog.ShowDialog() == DialogResult.OK) // Test result.
                {
                    #region Read the xls file
                    fileName.Text = dialog.FileName;
                    //var s = ConfigurationManager.ConnectionStrings["xlsx"].ConnectionString;
                    var connStr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=YES'", dialog.FileName);

                    //Excel.Application xlApp = new Excel.Application();
                    //Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(dialog.FileName, 0, false);
                    //Excel.Worksheet xlWorkSheet = xlWorkbook.Worksheets[2];

                    dataTable = new DataTable();
                    var dtColumns = ReportColumnMap.NewDtColumnDict();
                    var sheetName = "ALL$";
                    try
                    {
                        using (OleDbConnection conn = new OleDbConnection(connStr))
                        {
                            conn.Open();

                            // Filter the columns read by intended hardcoded columns
                            var colNameArrayStr = string.Join(",", dtColumns.Keys.Select(k => "'" + k + "'"));
                            var filterStr       = string.Format(@"[TABLE_NAME]='{0}' AND [COLUMN_NAME] IN({1})", sheetName, colNameArrayStr);
                            var schema          = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, null)?.Select(filterStr).ToList().OrderBy(r => r["ORDINAL_POSITION"]);

                            var colMapped = schema?.Select(r => (string)r["COLUMN_NAME"]).ToList();
                            colNameArrayStr = string.Join(",", colMapped?.Select(m => "[" + m + "]"));

                            // Select only the intended columns
                            OleDbCommand     comm    = new OleDbCommand(string.Format("select {0} from [{1}] where PositionId > 0", colNameArrayStr, sheetName), conn);
                            OleDbDataAdapter adapter = new OleDbDataAdapter()
                            {
                                SelectCommand = comm
                            };

                            // Populate the datatable with mapped columns in order read
                            colMapped?.ForEach(c => dataTable.Columns.Add(dtColumns[c]));
                            adapter.Fill(dataTable);
                        }
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.ToString());
                    }
                    #endregion
                    //var interestedPos = new[] { 3581874, 3581875, 3581876, 3581884, 3581885, 3581886, 3583129, 3583130,
                    //    3583131, 4260687, 4395271, 4969299, 4969300, 4969301, 5096217, 5172317, 5172318, 5172319, 5219314,
                    //    5269777, 5275676, 9957149, 10014344, 10014357, 10107257, 10107392, 10526409, 10526410, 10526413,
                    //    10766562, 10796052, 10796055, 10811540, 10811614, 10811769, 10886711, 10982243, 11003554, 11003555,
                    //    11003556, 11005210, 11005211, 11005212, 11005213, 11005214, 11005215, 11005219, 11005220, 11005221,
                    //    11007979, 11007980, 11007981, 11007982, 11007983, 11007984, 11031540, 11031554 };
                    var interestedPos = new[] { 4260687 };
                    var startingDate  = dataTable.AsEnumerable().Min(d => d["EodDate"]);
                    var posGroup      = dataTable.AsEnumerable().GroupBy(r => (int)r["PositionId"])
                                        .OrderBy(g => g.Key)
                                        .OrderBy(g => g.Select(dtRow => dtRow["Region"]).First())
                                        .Where(p => interestedPos.Contains(p.Key));


                    //var posGroup = dataTable.AsEnumerable().GroupBy(r => (int)r["PositionId"])
                    //           .OrderBy(g => g.Key)
                    //           .OrderBy(g => g.Select(dtRow => dtRow["Region"]).First());

                    dataTableExport = new DataTable();
                    var dtExColumns = ReportColumnMap.NewDtColumnDict();
                    dataTableExport.Columns.AddRange(dtExColumns.Values.ToArray());
                    //var rowArray = posGroup.SelectMany(g => g).ToList();
                    //dataTableExport = rowArray.CopyToDataTable();

                    dataTableAnalysed = new DataTable();
                    var analysisColumns = ReportColumnMap.NewAnalysisDtColumns();
                    dataTableAnalysed.Columns.AddRange(analysisColumns);


                    #region Load pfs data


                    var load           = new PfsDataLoad();
                    var summarizedRows = new List <DataRow>();
                    var analysedRows   = new List <DataRow>();
                    foreach (var pos in posGroup)
                    {
                        if ((string)pos.FirstOrDefault()["Region"] == "APAC")
                        {
                            continue;
                        }

                        if (pos.Any(p => p["Reason"] != DBNull.Value && (string)p["Reason"] == "CB/FUT"))
                        {
                            pos.ToList().ForEach(p => p["Reason"] = "CB/FUT");
                            summarizedRows.AddRange(pos);
                            continue;
                        }

                        var eodDates      = pos.GroupBy(r => (int)r["EodDate"]).ToList();
                        var eodDuplicates = eodDates.Where(d => d.Count() > 1).ToList();

                        var eodInconsistent = eodDuplicates.Where(d => d.ToList()[0]["Residual"] != d.ToList()[1]["Residual"]).ToList();
                        eodInconsistent.ForEach(d => d.ToList().ForEach(n => n["Comments"] = "Inconsistent residuals"));

                        foreach (var eod in eodDates)
                        {
                            try
                            {
                                if (eodInconsistent.Any(d => d.Key == eod.Key))
                                {
                                    summarizedRows.AddRange(eodInconsistent[eod.Key]);
                                    continue;
                                }

                                var dtRow             = eod.First();
                                var itemArrayAnalysed = new object[]
                                {
                                    dtRow["Region"],
                                    dtRow["EodDate"],
                                    dtRow["PositionNode"],
                                    dtRow["PositionId"],
                                    dtRow["Delta_T_2"],
                                    dtRow["Quantity_T_1"],
                                    dtRow["Quantity_T_2"],
                                    dtRow["TradeQuantity"],
                                    dtRow["Theta"],
                                    dtRow["IntradayTradingPLUSD"],
                                    dtRow["Residual"],
                                    dtRow["AbsRes"],
                                    false,        // Db_Inconsistency
                                    "",           // Qty_Inconsistency
                                    "",           // Avg_Inconsistency
                                    "",           // Rls_Inconsistency
                                    "",           // Comm_Inconsistency
                                    "",           // Div_Inconsistency
                                    "",           // Coupon_Inconsistency
                                    true,         // MarkingOK
                                    "",           // UCF_CT
                                    DBNull.Value, // UCF_Amt
                                    DBNull.Value, // UCF_Reset
                                    "",           // SuddenUCF_CT
                                    DBNull.Value, // SuddenUCF_USDAmt
                                    "",           // SuddenRCF_CT
                                    DBNull.Value, // SuddenRCF_USDAmt
                                    false         //Cancelled
                                };
                                var dtRowAnalysed = dataTableAnalysed.NewRow();
                                dtRowAnalysed.ItemArray = itemArrayAnalysed;

                                PfsPaaDAL.Region region;
                                if (!Enum.TryParse((string)dtRow["Region"], out region))
                                {
                                    continue;
                                }

                                var t_1  = eod.Key;
                                var t_2  = Utility.DateToRover8(Utility.AddBusinessDays(Utility.Rover8ToDate(t_1), -1, null));
                                var tday = new Dictionary <int, string>
                                {
                                    { t_1, "T-1" },
                                    { t_2, "T-2" }
                                };

                                load.ShowHist(region: region, date: t_1, posId: (int)dtRow["PositionId"], goBack: 2, showFixes: false);

                                var inconsistency = load.ConsistencyDataSet.Where(pl => (!pl.Value.IsOk) && (pl.Key == t_1 || pl.Key == t_2));
                                var marking       = load.PaaDataSet.Where(paa => (paa.Value.MOK == "M") && (paa.Key == t_1 || paa.Key == t_2));
                                var swap_T_1      = load.PfsDataSet[t_1];
                                var swap_T_2      = load.PfsDataSet[t_2];
                                var ucf           = swap_T_1.UnrealisedCF;
                                var sddUcf        = swap_T_1.UcfSudden;
                                var sddRcf        = swap_T_1.RcfSudden;

                                // Inconsistency columns
                                if (inconsistency.Any())
                                {
                                    dtRow["Reason"] = Utility.AttachString(dtRow["Reason"], "Db inconsistency");
                                    dtRowAnalysed["Db_Inconsistency"] = true;
                                    foreach (var t in inconsistency)
                                    {
                                        // TODO : PfsDbConsistencyData
                                        var types = new Dictionary <int, string>
                                        {
                                            { 0, t.Value.QtyOk == "" ? "" : "Qty" },
                                            { 1, t.Value.AvgOk == "" ? "" : "AvgCost" },
                                            { 2, t.Value.RlsdOk == "" ? "" : "RealisedCF" },
                                            { 3, t.Value.CommOk == "" ? "" : "Commision" },
                                            { 4, t.Value.DivOk == "" ? "" : "Dividend" },
                                            { 5, t.Value.CouponOk == "" ? "" : "Coupon" }
                                        }.Where(x => !string.IsNullOrEmpty(x.Value));

                                        var iQty = dataTableAnalysed.Columns.IndexOf("Qty_Inconsistency");
                                        types.ToList().ForEach(x =>
                                        {
                                            dtRow["SubReason"]          = Utility.AttachString(dtRow["SubReason"], x.Value);
                                            dtRowAnalysed[iQty + x.Key] = Utility.AttachString(dtRowAnalysed[iQty + x.Key], tday[t.Key]);
                                        });
                                    }
                                }

                                // Marking error column
                                if (marking.Count() > 0)
                                {
                                    dtRow["Reason"]            = Utility.AttachString(dtRow["Reason"], "Marking error");
                                    dtRowAnalysed["MarkingOK"] = false;
                                }

                                // UCF columns
                                if (ucf.Length > 0)
                                {
                                    double ucfAmt = 0;
                                    foreach (var u in ucf)
                                    {
                                        dtRowAnalysed["UCF_CT"] = Utility.AttachString(dtRowAnalysed["UCF_CT"], Enum.GetName(typeof(ShortCashFlowTypeEnum), u.ShortCashFlowType));
                                        ucfAmt += u.Amount;
                                    }
                                    dtRowAnalysed["UCF_Amt"] = ucfAmt;
                                }

                                // UCF_Reset column
                                var ucfReset = ucf.FirstOrDefault(u => u.ShortCashFlowType == ShortCashFlowTypeEnum.Reset)?.FinAmt;
                                dtRowAnalysed["UCF_Reset"] = (object)ucfReset ?? DBNull.Value;

                                // SudddenUCF columns
                                if (sddUcf.Count() > 0)
                                {
                                    //var usdAmt = Convert.ToDouble(Utility.DBNullToNull(dtRowAnalysed["SuddenUCF_USDAmt"]) ?? 0.0);
                                    double usdAmt = 0;
                                    foreach (var s in sddUcf)
                                    {
                                        dtRowAnalysed["SuddenUCF_CT"] = Utility.AttachString(dtRowAnalysed["SuddenUCF_CT"], Enum.GetName(typeof(ShortCashFlowTypeEnum), s.ShortCashFlowType));
                                        usdAmt += s.USDAmt;
                                    }
                                    dtRowAnalysed["SuddenUCF_USDAmt"] = usdAmt;
                                }

                                // SuddenRCF columns
                                if (sddRcf.Count() > 0)
                                {
                                    //var usdAmt = Convert.ToDouble(Utility.DBNullToNull(dtRowAnalysed["SuddenRCF_USDAmt"]) ?? 0.0);
                                    double usdAmt = 0;
                                    foreach (var s in sddRcf)
                                    {
                                        dtRowAnalysed["SuddenRCF_CT"] = Utility.AttachString(dtRowAnalysed["SuddenRCF_CT"], s.CashType);
                                        usdAmt += s.USDAmt;
                                    }
                                    dtRowAnalysed["SuddenRCF_USDAmt"] = usdAmt;
                                }

                                // Cancelled column
                                if (swap_T_1.TQ == 0 && (swap_T_2.TQ + swap_T_1.IdayTQ == 0) && swap_T_1.SettQty == 0 && swap_T_1.SettPrice == 0)
                                {
                                    dtRow["Reason"]            = Utility.AttachString(dtRow["Reason"], "Closed out position");
                                    dtRowAnalysed["Cancelled"] = true;
                                }

                                summarizedRows.Add(dtRow);
                                analysedRows.Add(dtRowAnalysed);
                            }
                            catch (Exception ex)
                            {
                                summarizedRows.Add(eod.First());
                                Console.WriteLine(ex.ToString());
                            }
                        }
                    }
                    #endregion

                    #region Export report
                    //dataTableExport.Rows.Clear();
                    dataTableAnalysed = analysedRows.CopyToDataTable();
                    dataTableExport   = summarizedRows.CopyToDataTable();

                    dataGrid.DataSource  = dataTableExport;
                    dataGrid2.DataSource = dataTableAnalysed;

                    //var outputPath = @"N:\Issues\High Residual\";
                    //var file = "ResSummary.GLOBAL.20161220_20170120.auto.xlsx";
                    //FileInfo newFile = new FileInfo(outputPath + file);

                    //ExportToExcel(newFile, dataTableExport, "All_Summary");
                    //ExportToExcel(newFile, dataTableAnalysed, "PFS_PAA_Analysis");
                    //var ds = new DataSet();
                    //ds.Tables.Add(dataTableExport);
                    // DataSetHelper.CreateWorkbook(outputPath + "ResSummaryAll.20161220_20170120.xlsx", ds);


                    #endregion
                }
            }
        }
예제 #2
0
        public void StartDataLoad(DataTable dt)
        {
            // Initialisation
            Load = new PfsDataLoad();
            DataTableAnalysed = new DataTable();
            var analysisColumns = ReportColumnMap.NewAnalysisDtColumns();

            DataTableAnalysed.Columns.AddRange(analysisColumns);
            var summarizedRows = new List <DataRow>();
            var analysedRows   = new List <DataRow>();

            // Loop through each dtRow
            var regionGroup  = dt.AsEnumerable().GroupBy(r => (string)r["Region"]);
            var startingDate = dt.AsEnumerable().Min(d => (int)d["EodDate"]);

            foreach (var rg in regionGroup)
            {
                Region region;
                if (!Enum.TryParse(rg.Key, out region))
                {
                    continue;
                }
                var fxHist = Load.InitialiseFxHist(region, startingDate);

                var posGroup   = rg.GroupBy(r => (int)r["PositionId"]).ToDictionary(g => g.Key, g => g);
                var extIdGroup = Load.GroupByExtId(region, posGroup.Select(p => p.Key));

                foreach (var ext in extIdGroup)
                {
                    var posEodDict = new Dictionary <int, IEnumerable <DataRow> >();
                    foreach (var pos in ext)
                    {
                        var eodDates      = posGroup[pos].GroupBy(r => (int)r["EodDate"]).ToList();
                        var eodDuplicates = eodDates.Where(d => d.Count() > 1).ToList();

                        var eodInconsistent = eodDuplicates.Where(d => d.ToList()[0]["Residual"] != d.ToList()[1]["Residual"]).ToList();
                        eodInconsistent.ForEach(d => d.ToList().ForEach(n => n["Comments"] = "Inconsistent residuals"));
                        summarizedRows.AddRange(eodInconsistent.SelectMany(e => e));

                        var eodDistinct = eodDates.Where(eod => eodInconsistent.Any(d => d.Key != eod.Key)).Select(d => d.First());
                        posEodDict.Add(pos, eodDistinct);
                    }
                    // Group positions by the same eod and residual in a pfsId group
                    var eodResidualGroup = posEodDict.GroupBy(pe => pe.Value.Select(row => new { eod = (int)row["EodDate"], res = (double)row["Residual"] }));

                    foreach (var g in eodResidualGroup)
                    {
                        var posRowDict = g.ToDictionary(kv => kv.Key, kv => kv.Value.ToDictionary(e => (int)e["EodDate"], e => e));
                        var eods       = g.Key.Select(k => k.eod).ToList();

                        try
                        {
                            Load.GetOnePositionData(region: region, posId: g.First().Key, dates: eods, fxHist: fxHist);

                            foreach (var e in eods) // Loop through each day
                            {
                                Load.PopulateDateSets(e);
                                var dtRow = posRowDict[g.First().Key][e];

                                AnalyseRowData(dtRow: dtRow, dataTableAnalysed: DataTableAnalysed, eod: e, load: Load);

                                foreach (var p in g) // Loop through each position
                                {
                                    var row = posRowDict[p.Key][e];

                                    summarizedRows.Add(row);
                                }
                            }
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine(ex.ToString());
                        }
                    }
                }
            }
        }